Best practices or tools for installing a SQL Server database

Question

Best practices or tools for installing a SQL Server database

I have a SQL Server database designed with the SQL Server GUI database editor/Visual Studio.

What is the best way to "install" that database on other systems. Said another way how should I ship this thing?

I know I can save the scripts and set the primary/foreign keys with T-SQL but I suspect their is something better. I guess you could have people restore from backup but that does not seem very professional.

What other choices are there and what are the pluses and minuses?

Answer 1

For it to look professional make a small setup program.

You currently have sql scripts that you use to create your db.

Make yourself a small xml file that contains the path to your scripts.

Create a small c# library that will connect to the db server, and run those scripts.

You can test this outside of the setup, in visual studio, then add it to the setup like this.

To do this from your setup all you have to do is put the xml file in a component so it is deployed,

And create a custom action in your setup, that will call your C# lib, read the xml and run the scripts on the sql server to create your db.

Also, from a setup program it's easy to set a registry key to identify the version of the your db that you just installed.

The minuses and pluses: It's a bit of work to start with, but with this you'll have all the ground work done to handle upgrades automatically later on, to do so, just add an upgradeScript section to your xml, an attribute called version for each upgrade script, and simply compare it against the version of the db you have save in the registry. The advantage is this way it can easily scale with your project.

Answer 2

My previous answer is mostly to keep full control on the deployment and upgrades.

I have searched for more built-in and streamlined solution that goes along with the DB designer mode you have used.

I found that in the version Studio 2005 Team Edition for Database Professionals of visual studio there might be deployment features.

Build and Deployment

You've seen that you can generate a T-SQL update script manually via the Schema Comparison tool. However, as part of the build process, DB Pro edition can generate a complete script for deploying your database project. This deployment script can do either a complete build or an incremental update. The build process can even consolidate all of your pre- and post-deployment scripts into one complete deployment script. You can deploy the script via the Build | Deploy Selection command right from within Visual Studio 2005. Under project properties, you will find a number of options to control and adjust the build process. The Build tab contains the core settings, such as Target connection, Target database name, and Block incremental deployment if data loss might occur. You'll note there is also a Build Events tab that you can use to type pre- or post-build event commands. DB Pro edition uses MSBuild for its build process and supports integration with Team Build if you're using Team Foundation Server.