Using Visual Studio database projects in real life

Visual Studio database projects are good to support software development. I have successfully used database projects for years and I think it’s time to share my experiences also to global developers audience. In this posting I will introduce you how to effectively use database projects so developers are working with up-to-date schema and test data all the time.

Standardizing development environments

To make things easier I will always use standardized development environments. It makes also life easier for developers because all problems that appear in environments are similar and after some time there are known solutions for all environment specific problems.

One important aspect in standardization is virtualization. We (or at least those with stronger survival instinct or more experiences) use virtual machines for development because they are easy to restore and when VM is crashed then it doesn’t affect host system where usually office and other work supporting software is running. Plus you can increase or decrease the amount of resources that VM uses.

For databases we usually agree in couple of things to make development easier:

• developers use local SQL Server (usually developer edition),
• names of databases are the same in all development machines,
• credentials to access databases are same in all development machines.

Yes, it is not always possible this way and there are exceptional cases but points given here work for most of projects.

Initializing database project

Some of your team who is responsible for developing database is the one who will be also manager of database project in Visual Studio solution. Writing database objects on Visual Studio, then deploying database to local machine just to test changes is non-sense. Management Studio and other WYSIWYG database management tools are way more productive and usually databases are built using these tools. I don’t want to force people to more work manually and I ‘m happy when they are moving on as fast as possible.

Suppose that database guy has already started building database. There are some tables and maybe views. Maybe even some stored procedures. Let’s also suppose there is empty database project also added to solution.

1. Set target database platform

Here is Windows Azure SQL Database selected as target so Visual Studio will check that modifications done to schema project doesn’t conflict with SQL Azure.

2. Create schema comparison

To get changes made to database to schema project we create new database schema comparison. Schema comparison allows us to compare two different schemas. Right click on database project and select Schema Compare…

Empty schema compare window is opened.

Now you have to specify source and target schema:

• source schema will be your local database (you can make new connection and use Windows authentication in this case because you don’t run schema comparison under application pool or other limited account),
• target schema will be the database schema project in your solution.

Here are my sample settings for source and target dialogs:

When settings are done then click Compare button in schema compare window.

You can see now what objects are in database and what is action to do with object on target. If I don’t have good reason I don’t add roles and user accounts to schema project as sometimes their deployment is problematic. Take a look at object definitions window – you can see diff between source and target there.

3. Update schema project

Now click Update button to get database objects to your schema project and save schema to file in database project folder. I usually call this file as LocalDbToSchema.scmp.

Under your schema project you should see now some new folders and database objects. On sample image on right you can see some database tables that were imported from database.

Now include saved database compare file to your database project. This way this comparison is always available for you and you don’t have to manually configure comparison again and again when you want to compare development database with schema project.

This is the schema compare that is used mostly by database developer. Of course, after making update to schema project you have to try to build it to see that there are no errors in schema project.

Adding schema project to local database comparison

As now you have way to get schema changes from database to schema project you are ready to make another comparison that is mostly used by developers who don’t develop database. They need to compare their local databases against schema project and apply changes or recreate their databases.

Steps are almost same as before:

1. Right click on database project and select Schema Compare…
2. For source schema select your schema project in solution
3. For target schema select your local database
4. Save schema as file to database project folder (SchemaToLocalDb.scmp)
5. Include schema compare file to database project

This is it. As all virtual machines have same database settings then all developers can use the same schema comparison to update or recreate their schemas.

Adding test data

If you made breaking changes that cannot be deployed to existing database with data then developers need to create their databases again. Can you imagine how painful it is when new database is empty and you have to insert all data again to make even elementary things work again? The guy who manages database project will usually also manage data that is deployed to database after it is recreated.

1. Create new post-deployment script

Right click on database project, select Add…, select New Item… and select Post-Deployment Script.

Name it as Script.PostDeployment.sql by example. I’m using often only one script and that’s usually enough for me. Click Add and new post-deployment script will be added to database project. Here you can see the empty window of post-deployment script:

To this script you must add all test data and keep it up to date so developers have only minimum delays in work when something happens to database. Yes, it is additional work to keep data up to date but as database developer you should know very well how to produce test data and how to get data from database to clipboard.

Publish profile

You need two database publish profiles. Schema comparison doesn’t work for you when developer has to delete database and create it again. Yes, schemas are compared but data doesn’t appear to new database automatically. Of course, it is possible to take data with copy and paste from post-deployment script but why so much manual work when we have tools that work nice for us?

Right click on database project, select Publish… You are asked for target database connection string. Add connection to your local development database and then click on button Advanced…

Set publishing options like I have (should work for most projects you have) and then click OK.

Now save publishing settings to file called PublishToLocal.publish.xml and make sure this file is included to database project. When some developer explodes database then it is easy to recreate it again now.

Updating test and production databases

You have also test databases that are used in test environments where users and testers test the system. Also you have production databases. It would be nice if all changes get there automatically but life has shown that for these systems you have to deal mostly manually. You must be sure that you don’t break something and you don’t delete any important data by mistake.

You can still use database comparisons to compare current schema to test or production database but I recommend you to use database accounts that have no permissions to modify data or database objects. Schema comparisons will show you what changes are needed for target databases but you have to do these changes manually and not trust automatics. Even if you have publishing options set correctly you cannot always be sure that automatic modifications are done the way you expected. Make on mistake and face worst problems you have ever seen.

Until you just compare two schemas to find out differences and you use “read-only” accounts you are in safer waters.

How it works in practice

So far, so good. My teams have saved remarkable amount of time to fix mysterious database issues and setting up databases again.

• All developers know how to use database projects regardless their actual role in projects.
• Database and environment related issues are usually easy to find because development environments are standardized and unknown issues appear only when doing something “unusual”.
• Crashed or screwed up database gets back to life with about 15 minutes at maximum.
• Mismatches between database and code are quickly detected and fixed.

You need well disciplined developers to use things like this because guys who don’t really care about quality will just screw up all thing by out-dating schema project and post-deployment scripts. For good developers database projects save time and help them keep work quality high.

Conclusion

Database projects in Visual Studio are powerful tools to use. This posting gave you overview about how to use database projects when developing systems where databases with regular size and structure are used. For more complex and advanced scenarios also rules change and different problems like big amounts of data or impossibility to use local databases may need workarounds. Until it’s regular development you can easily use database projects like described here.