If you’re anything like me, managing and developing a SQL Server database has always been synonymous with working inside SQL Server Management Studio. It provides most of the essential elements of an integrated development environment– syntax highlighting, debugging tools, object browser – and it comes free with every installation of SQL Server, so it’s a no-brainer for most BI professionals. Recently, though, I’m becoming a huge proponent of SQL Server Data Tools, Microsoft’s set of BI plugins that run inside Visual Studio. Its features have evolved over the years and today I’ve found it’s possible, except for a very limited number of tasks, to completely switch my workflow over to SSDT full-time. Here are three reasons you should too.
Reason #1: Integrated Source Control
Any team development activity requires source control, and databases are no exception. Unfortunately, only about 58% of database developers use any source control system at all, according to a Red Gate DevOps survey. This is unfortunate, since database objects are just as vulnerable to development conflicts as any other type of source code, and often require point-in-time recovery. Fortunately, Visual Studio is designed to run with Team Foundation Server (TFS), one of the best centralized source control platforms around. Of course, there are multiple third-party source control plugins available for SSMS, but none have tight integration the way that Visual Studio and TFS do. A typical workflow involving a central TFS server and three developers is illustrated below.
Reason #2: Offline Development
How many times have you run into this scenario: developer A is actively working in a shared dev database. He or she changes the name of a column. Developer B is writing a stored procedure that refers to the old column name. Suddenly, the stored proc breaks, and they have no idea why. It worked just fine a minute ago! Developer B then spends the next 10 minutes hunting down the issue, only to discover it was a result of A’s work. Wouldn’t it be nice if there was a way for both A and B to make changes to dev without the risk of overwriting each other’s work? Visual Studio is designed exactly for this. You’ll notice that when you install SSDT, you’re given the option of installing SQL Server Express on your local machine. Usually this will be given the name ProjectsV13 (or something similar, depending on your version of VS). This local instance is meant to be used as your own personal debugging database, and you can deploy objects to it and query it without breaking the central development database. Any conflicts can be resolved at the project level in Visual Studio before the final deployment to dev.
Reason #3: DAC Deployment
A DAC is a data-tier application, which is basically a file containing the shell of a database, without the data. It gives databases portability, upgradeability, and are a godsend to database administrators (DBAs) everywhere. Prior to this construct, developers would often need to keep track of all the changes they made to a database since the last deployment and manually script out the ALTER statements to give to the DBA as part of a change management process. Now, instead of manually scripting the changes, the developers can build their project in Visual Studio, generate a .dacpac file (which happens automatically during a build), and hand that DACPAC file over to the DBA. The DBA can then generate a change script automatically by having Visual Studio compare the contents of the DACPAC to the existing database. This is done by right-clicking the database project and choosing Deploy in Visual Studio.
If you’re involved in any kind of Microsoft-based BI development project, SQL Server Data Tools should be in your toolkit. It’s introduced tons of powerful features that enable developers focus more on the development of their code, and less on the management of their code. Going from SSMS to Visual Studio requires a bit of a learning curve and a habit change, but I’m confident that almost all developers can increase their long-term productivity by making the switch.
To learn more about creating elevating your current Data Warehouse or BI solutions reach out to 3Cloud today.
