Managing and Deploying SQL Server Databases - PART 1

Sooner or later during a company project comes the time to start thinking about databases... and lets face it... databases usually get thought of last!

When I first joined my team in my current company we had no clear process for managing databases and deltas. SQL scripts were being emailed around teams with all sorts of schema changes. Needless to say that eventually this got out of hand!

To start with we needed to make sure all our databases were stored in source control. Having it stored centrally in somewhere like TFS would enable teams working on these databases to work in an isolated environment for database development and allow team members to get the latest of any changes made. A great way to ensure everyone is working on the same code right?

In this 2 part post I will go through some of the ways to Manage and Deploy SQL server databases using Visual Studio 2010.

PART 1

Managing a SQL Server Database

Creating your database project is very simple. Heres a quick example:

Create a new project and choose "Database sql server" from the template list. In the project type list choose the project type which best fits your needs. In this example i'm using "SQL Server 2008 Wizard" (I'm not adding this project to source control at this time as the machine I'm working on doesn't have it installed)

Click next to enter the "Project Properties" page. On this page you specify the type of project you want to create and how to organize the files in the project.

Click next again to enter the "Set Database Options" page. Here you configure the database level options such as enabling full-text search, database collation, default schema for new object etc.

Next again takes you to "Import Database Schema" page. Here you can choose to import an existing database or click next to skip this stage.

And finally next again to set the "Configure Build and Deploy" options. Here you can set whether to create ONLY deployment scripts when the solution is deployed or create scripts AND deploy to a target server when the solution is deployed amongst other deployment options.

Click next then finish to complete the process. Voilà - you have successfully created a database project!

Once the databases are in source control it becomes very easy to manage the database objects. All your database objects will appear under the "Solution Explorer" or "Schema View" windows like so:

Directly under the database project is "Properties". This is where you can set your database level options. These options are what you will need to care about for when you deploy your database. If you have multiple databases under your database solutions remember each database will have its own set of property files.

Further down the list in solution explorer you can create Data Generation Plans, Schema Comparisons (very useful tool - but I wont go into that now), schema objects and scripts.

Schema objects is probably where all the action will take place. Here you can create and edit database level objects like security, triggers, service broker and storage and schema level objects like tables, functions, stored procedures, views, Synonyms etc.

The last section in the project is for scripts. Here you can create Pre and Post deployment TSQL / SQLCMD statements. As the names suggest, these are the scripts that will run before and after the database is deployed. You can add your own sql scripts to these pre and post folders but remember that they will also be run against your target server with the database deployment. If you do want to store your own scripts but not have them run when you deploy, either create a separate folder to store them or set the "Build Action" to "Not in Build" (it's probably best to check the build action anyway just incase).