I just started using SQL Server Project template in Visual Studio 2012 and my first impression? This is great!
There are several ways we traditionally create databases and manage the schema over time. Sometimes you just go to SQL Server Management Studio and simply create database objects such as tables, views, indices, and stored procedures directly into the database. I use this approach for proof-of-concept type projects or super small projects that I know it will be faster to just design the schema and create the database objects right away using SQL Server Management Studio. Also, the expectation in this approach is that once I’m done with the database, there is very minimal change that I will change the schema again.
That’s the outlier scenario though when it comes to projects. Typically, when we work on application development project where we’re also tasked to design and implement the database too, we create database scripts in order to make deployments not only in production, but in development, test, and staging environments reproducible also. These database scripts, which contains SQL statements to create tables, views, stored procedures and so forth, typically are updated during the duration of the project, as we add more tables, maybe modify tables, and adjust database objects according to the project requirements.
In my past projects, the initial database scripts are mostly CREATE in nature. Once the databases are deployed to production, we start adding ALTER statements and drop-if-exist prefixes-before-create statements into the scripts. By doing this, staging our different database environments are still repeatable. But then, you run into the “dependency challenge” with this approach. Say, you updated a stored procedure to take into account new columns in a table because of feature enhancements in the application, you need to remember first to run the alter-table-statement that adds those new columns in the first place. It’s doable but this approach is prone to human error. Sometimes, during a deployment, some SQL scripts that need to be executed very early in the deployment process gets forgotten such that by the time a latter script, which is dependent on the earlier script command which was forgotten, the latter script errors out and fails to execute on the database.
Want to make your life easier managing SQL Server schema? Use the SQL Server Project template in Visual Studio 2012! The template is one of the default VS 2012 templates so it’s already there–no additional downloads. I’ve tested it managing SQL Server 2008 databases and SQL Server 2012 databases and works well with both editions of SQL Server.
Creating a new SQL Server Database Project
To create a new SQL Server Project, select New Project from the menu, then on the dialog, expand Templates, look for SQL Server, and click SQL Server Database Project:
For this exercise and blog post, I use the free downloadable Adventureworks database. Once the project is created, you can right-click the project on the Solution Explorer, click Properties – this will display the Project Settings. By default, “Create script” checkbox is unchecked. I recommend you tick the checkbox so that the SQL files will be generated when you build the project:
If you have created the database and already created tables, views, and stored procedures, you can use the import function. Right-click the project, expand the Import menu, then click Database:
The Import Database dialog appears. Select/setup your database connection and adjust settings per your preference. I like my scripts organized by object types (tables, views, etc.), so I confirm that I select Schema\Object Type as my folder structure:
Once you click the “Start” button on the Import Database dialog, the project files will be created. The result of importing Adventureworks database looks like the following. See how the objects are organized by schema and then by object-types? Nice!
After importing, you’ll probably want to check-in the scripts to your source code repository.
Now, you expect changes to the database are coming. New tables, new columns, updated stored procedures, whatever it is. So how do you proceed updating the database objects? You have 2 options:
- Update the scripts in Visual Studio 2012 and then apply the changes to the SQL Server databases.
- Update the SQL Server database and then update the scripts per database changes.
If you prefer updating the scripts first
Go ahead and make changes to the schema by modifying the script source files. In the example below, I rename [HumanResources].[Employee].[BirthDate] to Birth_Date:
Build the project just to make sure there are no syntax errors. If the project builds okay, you can publish the changes to the database. Right-click the project in Solution Explorer, then click “Schema Compare” in the menu:
The SQL Schema Compare window appears. The project is already selected on the left-side dropdown on the top of the window. On the right-side dropdown, Select Target, and choose the database you want to push the changes to. After selecting the target database on the left-side dropdown, click “Compare” on the toolbar:
The changes will be displayed and highlighted for you. You can review the deltas or changes and if everything is good, click “Update” on the toolbar. You’re updating the database objects per definitions in the scripts.
Go to the database using SQL Server Management Studio just to verify that the changes were published indeed. Now, don’t forget to check-in your scripts to your source-control.
If you prefer modifying the DB objects directly
If you prefer modifying the database schema and objects using SQL Server Management Studio first before updating your scripts, you can do this as well. Modify the DB objects, then just sync up your scripts. In the example below, using SQL Server Management Studio, I modify the Adventureworks ErrorLog table:
After making changes directly to the database, go back to VS 2012 and initiate a Schema Compare. Select the target database on the right-side dropdown. Now, you will want to flip this such that the target database is on the left-side dropdown and the SQL Server DB Project is on the right-side dropdown. After you’ve switch the two (by clicking the image button in between the two dropdowns), you can now click the “Compare” button on the toolbar. What you’re effectively doing here is comparing the database to the script and updating the scripts per definitions in the database:
After updating the scripts, don’t forget to check-in to your source-code repository.
Hopefully, I am able to demonstrate how easy it is to use the SQL Server Database Project. Years ago, I remember having to download some “database-diff” tool to analyze differences and mismatches in DB instances. This capability is built-in in SQL Server Database Project via Schema Compare.
If you have additional tips on how to better use/leverage this tool, please feel free to comment!
Leave a Reply