Overview of SQL Server Data Tools for Microsoft Visual Studio
by Ira Endres on Friday, August 21, 2015
If you have been managing SQL Server software solutions for a while, you've probably developed a process for pushing database changes for your applications to your various database servers. If you've been implementing the latest trends in database code publishing technology, you have probably even implemented database migration scripts for major published versions. If you're really awesome you've even checked SQL scripts in for each development revision that you've checked into your repository. If you're using automated tasks to push and deploy these migrations to various development and production database servers, you've reached the upper echelon of database developers.
It used to be that this level of automation was only obtainable by either writing these features yourself or by purchasing very expensive database development software and comparison tools. With Microsoft SQL Server Data Tools you can have these features available to you for free in Visual Studio and not just the Professional or Premium versions but available for all versions of Visual Studio from 2012 up including the new 2015 Community Edition. Shout out to the Dev Team at Microsoft and who ever made SQL Server Data Tools available; I'm your biggest fan.
The SQL Server Object Explorer and Table Designer
With the installation of the SQL Server Data Tools, the extension installs a new view called the SQL Server Object Explorer. Much like the Server Explorer, this view pane allows you to connect to SQL Server databases and view the databases and their structure. It adds additional functionality to execute SQL queries and create new server objects; it's like having SQL Server Management Studio inside Visual Studio.
Figure 1 - SQL Server Object Explorer and the Table Designer
Along with the SQL Server Object Explorer, a new Table Designer has been included with SQL Server Data Tools that allows you to create Tables from a familiar User Interface. As you make changes to the table from either the SQL pane or the Designer pane, the other updates automatically. Once changes are completed, hitting the Update button generates a script to create or update the table. The resultant script can either be executed or saved to file for later execution and the script will preserve the data much the same way SQL Server Management Studio would as well as warn about potential data loss.
Introducing SQL Server Database Project Types
SQL Server Data Tools installs a new project type in Visual Studio exclusively for database projects. This new project type has some familiar features and also some new ones.
Figure 2 - Creating a new SQL Server Database Project
Just like any other Visual Studio Project type, the project can be added to an existing solution file so you can keep all your code in the same project space.
Schema Compare
The next major feature in SQL Server Data Tools is the Schema Comparison feature. This new feature allows you to compare one database to another database or a database to a database project. This allows you to view the differences between two different schemas no matter where the code resides. To initiate a Schema Compare simply right click on the database project and select Schema Compare.
Figure 3 - The Schema Compare Tool
When the comparison window opens you will notice two drop down menus to choose from: the left side is the source and the right side is the target. To select your source database or database project simply open the source drop down list and choose the Select Source menu option. From the dialog, use the radio buttons to either select a live database or one of your local database projects.
Figure 4 - The Select Schema Dialog
To import an existing schema from a live database into an empty database project, select the Schema Database radio button and edit the connection information in order to point to the correct database. Then set your target to the database project that you created in your solution. Once both the source and target destinations have been correctly set click the Compare button to run the Schema Comparison. When complete the schema comparison will outline all of the major differences between the two database schemas. Finally, to update the target destination with all of the differences selected click the Update button. This creates a schema duplicate from the data source database to the target database project. The database project will be split up into different sections based on the unique features of the database such as schemas, users, and views and stored procedures.
To push changes from a SQL Server Database Project to a SQL Server database instance simply perform the reverse operation. Select the database project as the source and then select the database target to be the destination database you wish to update. It's that easy.
Database Publishing
Just like other deployable Visual Studio projects, SQL Server Database Projects include a Publish feature. Internally, when you run a database publish on a destination database server, it performs a Schema Comparison to update the destination database with any schema changes required. No need to worry about migration files or migration scripts; the Schema Comparison tool that runs during publish will ensure that your destination database is up-to-date with the latest development changes.
Figure 5 - Database Publish dialog
Another wonderful feature built into the database publish operation is that you do not have to run the resultant publish script that is generated. The SQL Server Data Tools Publish feature will allow you to save the resultant SQL statements to file for later storage or running. This allows the developer to compare major versions of database schema and quickly be able to generate migration scripts to the newer versions.
Yet another amazing feature built into the database publish operation is the ability to execute pre- and post-deployment scripts. One very useful thing that can go inside these deployment scripts are database seed values that often have to be created in a destination database. Using commands such as the MERGE INTO SQL statement allows the developer to quickly synchronize database values between development revisions and major versions.
But wait, you say it couldn't possibly get any better, could it? The publish scripts that you generate are executed via SQL Server's built in SQLCMD functions. SQL Server Database Project allows the developer to set SQLCMD variables inside the Project Properties that can be used inside your scripts during execution. This allows the database developer to be able to turn off and on certain functionality that may execute in either a pre- or post-deployment script.
Conclusion
In this article, we have reviewed some of the major features of Microsoft's SQL Server Data Tools for Microsoft Visual Studio versions 2012 and up. These features include the SQL Server Object Explorer and its Table Designer, the SQL Server Database Project type, the Schema Comparison tool and the Database Publishing feature. Utilizing the SQL Server Data Tools for Visual Studio is a no-brainer for database developers that really provides a solid tool set for database development, source control and database publishing.