Compare SQL Database Schema and deploy changes using Visual Studio

Many a times, you would need to identify the difference in database schema for two SQL databases so that you can take certain course of action. There are a lot of tools in market which can do this, but you would need to pay for them to get full difference or to use them on continuous basis. However, Microsoft Visual Studio has this functionality built-in for you and if you happen to use Visual Studio as your code development tool, this functionality is basically free. So in this scenario, it also prevents hassle of learning another tool. In this blog post, we’ll learn how to do the same using Visual Studio.

Caution – I am going to demo this Visual Studio Enterprise. I’m not sure what all sku’s it is associated with, so if you cannot repeat steps below, you might want to check that. 

Create a SQL Database project

First, we need to create a new SQL database project in Visual Studio. For this, go to File -> New Project -> Select SQL server database project:

Create Sql Server Database Project in Visual Studio

Once its created, we’ll need to specify the source and target sql server databases for it to compare.

We’ll be using sample sql server database for this post’s purpose. They are officially available at https://github.com/Microsoft/sql-server-samples. However, you can do the steps on sql databases of your choice.

Specify the target SQL database version

Right click on project in Visual Studio, select properties and open Project Settings. Set target SQL server version here:

Specify target sql server properties

This step is very important if you not only want to see the results, but also want to auto-generate sql script for the difference.

Specify source and target sql database

Again, right click project and select ‘Schema compare’:

Select schema compare from project
Select schema compare from project

In the schema compare window, select dropdown to the left and then select source:

Select source from schema compare window

This will open a pop-up wizard. Select database and then click ‘Select Connection’:

Select database connection from pop-up wizard

Now go to browse and specify the source sql connection details as per your environment and then click connect:

Specify source sql server connection details

Click ok again once the wizard closes.

Now, click the dropdown on the right side and then select target:

Select target from schema compare window
Select target from schema compare window

Again, we need to specify target sql database connection details.

The middle option allows you to switch source and target sql database.

Start schema comparison process

To start comparison process, now click the compare button enabled at top of left drop-down:

Select compare button to start schema comparison

Just for reference purposes, one of table named dbo.BuildVersion contains an extra column named BuildDate in our source database(and this is not present in target sql database):

Added a column to buildversion table

Let’s start comparison process within Visual Studio. It might take some time to finish depending upon your network connection, etc. Once its completed, you should be able to view results like below:

Default result view generated

As you can see, it does not seem to of much info. Also, as mentioned earlier, in our case, we have only difference in one of the sql tables.

To get more information about difference, we need to select the object, say, table in our case:

Select an sql object to view difference

Now, we would be able to view the difference clearly.

Generate SQL schema difference

To auto-generate the sql script that needs to be executed at target sql database to nullify the schema difference, we need to click generate button just right to the compare button.

auto generate script to nullify difference

This will initiate a process and you would be able to see and save the schema difference.

Deploy/Publish schema changes

Besides generating the schema difference, we can also choose to deploy the changes to target SQL database as well. For this, we just need to click the update button just right to the compare button:

Deploy difference to target sql

This will initiate a process in other window to deploy the schema changes. You would also be able to see the progress for same:

Viewing progress of sql schema publish

This action is also known as publishing of sql schema. Once its complete, we should be able to see our changes in target sql database as well:

Viewing deployed changes using ssms
Viewing deployed changes using SSMS

Modifying report generation or deployment options

As you can see, there are other options in toolbar, once the schema comparison is generated, which will allow to select view or group results. There is also a checkbox in front of the each sql object, which can be checked or unchecked to include/remove that object from publishing/reporting.

Automation options

It’s good to be able to do it using GUI or graphical interface. However this is a cumbersome  and time taking process. If you are in DevOps or you need to do above on a frequent basis or at large scale, chances are like that you would like to automate the whole process. In one of the upcoming posts, we would discuss how to generate/publish the schema difference using command line tool as well and automate the same.

3 thoughts on “Compare SQL Database Schema and deploy changes using Visual Studio

  1. HI, I use these steps in my environment. do you have anything new for the automation of the synchronization and especially the execution of the update with Visual Studio ? thanks

    Like

      1. Very good, i tried other products like red gate, toad, forge but i have licence for visual studio and it will be usefull to use the same product

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s