Manage SQL Server Projects With Visual Studio

Introduction

As a DBA, you will be responsible for managing several of the SQL Server instances; some of which may have similar setup and configuration while the other might be having completely different setup and configuration. On each instance there might be some server level objects for which you would like to maintain a change history  (version control) or would like to easily recover the  master database easily in case of failure.

Microsoft Visual Studio 2010 (not all editions, see note below for more details) allows to creation and configuration of  a server project as an offline representation of your master database, which you can add to VSTF (Visual Studio Team Foundation) for version control or to maintain change history. This reduces the risk of overwriting the offline representation, improves the communication among developers and helps you in maintaining and recovering your SQL Server instance .

Getting started…

Microsoft Visual Studio 2010 provides several templates for database project development such as Database Project, Database Project Wizard, Server Project, Server Project Wizard, Data tier application. In this article, I am will discuss  creating SQL Server projects using Server Project / Server Project Wizard template.

Launch Visual Studio IDE by going to START -> Programs -> Microsoft Visual Studio 2010 and then go to File -> New and you will see a screen as below.
In the New Project dialog box, select SQL Server under Database in the left side tree view and the select SQL Server 2008 Server Project (or SQL Server 2005 Server Project depending on your SQL Server version). Specify the name of the project, location and solution name   as shown above and click on OK which will bring up the welcome screen  below.

Click on the Next button to move on to the next screen as shown below:

In the above screen you can specify some of the properties of the project you are creating, for example you can specify whether you want to create a database project (for user databases) or SQL Server project (choose server project as we are creating a project for SQL Server master database), how you want you script files to be created, whether it should be created by schema or by the type of database objects. Next you can specify to include the schema name in the script files being created. Click on Next button to continue to the below screen:

In the above screen you specify different SQL Server instance level options (for example ANSI paddings, ANSI nulls, Arithmetic abort etc. to be either ON or OFF by default), master database default collation and default schema. Click Next to move on to the next screen:

In this screen you need to specify the source server connection from where you want to import master database schema objects (if a connection is already available you can select it from the combo-box, if you want to modify an existing connection click on Edit Connection or click on New Connection if you want to create  a new connection), along with that you can also specify the different import options which will be used during the import and the number of maximum files (the script for each database object is saved in a single file) in a one directory. Click  Next  to move on to the next screen which is shown below:

In this screen,  specify the different options which will be used when you build and deploy your project to a SQL Server instance. For example, specify the target server connection string (you may notice here that the target database name box is disabled and it has an entry for the master database as this is a server project), whether the collation of the project will be used or a default collation of the server will be used etc.  . Click  Finish  to start creating the project, as shown in the progress screen below:

If there are any problems while creating project and importing the master database schema objects, the above screen will display the error message or else click on Finish button to close the wizard and return to the IDE. Here you can see your created server project (for the master database) along with all the schema objects in it.

Above you can see objects in Solution explorer view, below is what you will see when you select Schema view where each schema contains all the objects owned by it and organized under it.

You can double click on the any of the object to open it in editor where you can modify it. If you want to add a new object, simply right click on the folder and click on Add (in Solution Explorer right click on the folder, click on the type of object you want to create or simply click on New Item to specify the type of object in another dialog box). I will be demonstrating to create an object in the next section.

Deploying A SQL Server Project From Visual Studio

Deploying project is very easy; there are two options for this. First you can create deployment script file to run it on the target server later on as per your convenience or you can simply create a deployment script and deploy it  all in one go.

Right click on the project in Solution Explorer and select Properties to configure different deployment properties. To configure the different deployment properties, click the Deploy page on the left side and you will see a screen as below:

In this screen you can specify different properties, for example whether you want to just create a deployment script or you want to create a deployment script and also deploy it, the  name of the deployment script file, the target server connection string and since this is a server project the master database name is already available in the target database name textbox (disabled).

Once you are done with the configuration, you can build your project by selecting the Build option in the Menu bar (or by simply hitting F6 key) or build and deploying by using Deploy option in Menu bar as shown below:

Let’s do some changes in the project and see the impact on the server after deployment. Right click on the project and click on “Add New Item” and then select an object type to be created, specify an appropriate name and then click the Add button. Since I am creating a SQL login  I selected Login (Sql Server) type as shown below:

You can double click on the newly created object to open it in the editor and make the required changes, if any, as you can see in the image below:

Finally you can deploy your changes by following the steps as discussed above and then you can go to the SQL Server instance to verify your changes as shown in the image below where I created a SQL login under the Logins node.

Conclusion

In this article, I showed you how you can use Microsoft Visual Studio 2010 ultimate edition to create an offline representation of your master database by creating a SQL Server project in it. Then I showed you how you can make changes to this offline representation and deploy these changes to the SQL Server instance.