SQL Server Database Development in Visual Studio

Introduction

One of the challenging tasks on any software project is to manage the database changes and keep all changes in sync. The work is tougher when we have multiple test environments or multiple servers in place. Unlike the database changes, managing code changes is easy due to availability of version control. It is a very responsible task for any DBA to keep track of all the changes and deploy these changes to multiple servers. This is quite error prone and needs lots of testing.

We have the above issue until we work with SQL database projects using Visual Studio. We can develop, manage, compare and deploy the database changes using Visual Studio very easily. We can also keep all the database object changes under version control.

The SQL database development was present on Visual Studio since VS.NET version 2010 but in 2013, we have many powerful features present. We can create a new database project from an existing database with just a button click or we can create a database project from scratch. For now, let’s see how to create a DB project from scratch. Later, we will show how to create a database project from an existing database.

Create Database Project on Visual Studio 2013

To create a new SQL server database project, open “New Project” dialog and from there, select SQL Server from the default template. In the right side pane, select SQL server database project and provide the project name. I have given the project name as ProjectTracker.Database.

Once we are happy with the project location and project name, click ok. After that, the project will be created. Then go to the project properties, from the solution explorer and select the project settings. Change the target platform to the correct version of the database where we are going to deploy our changes. For my case, I am selecting it as SQL server 2008. Then select the checkbox underneath the properties button saying "Create script (.sql file)". We can also change the database collations and other setting from the Database Settings button.

If we observe the SQL Server Object Explorer at the right side panel, we can find a blank database being created with no objects in it.

From the Solution explorer, we can add objects like tables, stored procedures, functions, views, etc. in the same way as we add pages while working with web projects in Visual Studio. Here, all the objects will be added as script files to the project. Once we add an object, we can check if that is available on the SQL server object explorer under the respective nodes.

Let’s add a new table and we can see Visual Studio is opening the table in 2 ways. We will get a designer view and the T-SQL editor underneath it with the DDL statement which creates it. We can change either of those and the other one will be reflected the changes automatically. If you make any changes on the script incorrectly, it will immediately show you the list of errors.

In the same way, we can add other database objects. It is very easy to work with the database projects. Once everything has been done, we can build projects in the same way as web projects and then publish them to a database server.

Deploying Database

Database created on the database project can be easily published to the server. From the solution explorer, right click on the project and select Publish. From the publish database dialog, select edit connection string. From the connection properties, choose the correct target server. Once you provide all the details, select ok. Test Connection might not work now as the database is not created on the server.

If we want to generate the scripts and execute those manually on the server, then we can click on the Generate Script button. It would create the database deployment script which can be executed on the server.

If we click on Publish button, Visual Studio will create the database directly on the target server. So make sure which way we want to deploy the database to the server. Using the Advance button, we can select many additional options for publishing database like, recreate database, take backup before publish, etc.

Schema Compare in Database Project

This is a very cool feature in the database project that everyone would like. We can compare the local development database schema with the target server schema before deployment. That way, we can synchronize all database versions easily.

From the solution explorer, right click and select Schema Compare and it will ask for the source database and target database. Select source database as your project in the solution and Target database as the database where you want to update the schema. Once these are selected, you can click on compare and it will show all the differences in the schema in the 2 databases. Form there, you can select the difference and changes will be shown in the underneath window. If we are fine to move all the changes to the target server all at once, we can click on update all. Else, we can exclude some of the changes by right clicking on any row and selecting Exclude. We can easily toggle between source and target by using the 2 way arrow button. If we want to move changes to server using the deployment scripts, then we can click on the generate script button.

We can save the schema compare files for future use. Create a new Folder as “SchemaCompare” and then include the saved files in the project. We can have numbers of schema compare files for different target servers. That way, we can easily sync schema with multiple deployment servers.

Conclusion

Visual Studio Database project is a very powerful tool that needs to be used very carefully. The above details provide basic knowledge on how to create a new database project. Later, we will create projects from existing database and play with many advanced functionalities.