Best practices for writing SQL scripts for deployment

Question

I was wondering what are the best practices in order to write SQL scripts to set up databases for production and/or development, for instance:

• Should I include the CREATE DATABASE statement?
• Should I create users for the database in the same script?
• Is correct to disable FK check before executing the body of the script?
• May I include the hole script in a transaction?
• Is better to generate 1 script per database than one script for all of them?

Thanks!

Answer 1

The problem with your question is is hard to answer as it depends on the way the scripts are used in what you are trying to achieve. you also don't say which DB server you are using as there are tools provided which can make some tasks easier.

Taking your points in order, here are some suggestions, which will probably be very different to everyone elses :)

• Should I include the CREATE DATABASE statement?

What alternative are you thinking of using? If your question is should you put the CREATE DATABASE statement in the same script as the table creation it depends. When developing DB I use a separate create DB script as I have a script to drop all objects and so I don't need to create the database again.

• Should I create users for the database in the same script?

I wouldn't, simply because the users may well change but your schema has not. Might as well manage those changes in a smaller script.

• Is correct to disable FK check before executing the body of the script?

If you are importing the data in an attempt to recover the database then you may well have to if you are using auto increment IDs and want to keep the same values. Also you may end up importing the tables "out of order" an not want checks performed.

• May I include the whole script in a transaction?

Yes, you can, but again it depends on the type of script you are running. If you are importing data after rebuilding a db then the whole import should work or fail. However, your transaction file is going to be huge during the import.

• Is better to generate 1 script per database than one script for all of them?

Again, for maintenance purposes it's probably better to keep them separate.

Answer 2

This probably depends what kind of database and how it is used and deployed. I am developing a n-tier standard application that is deployed at many different customer sites.

1. I do not add a CREATE DATABASE statement in the script. Creating the the database is a part of the installation script which allows the user to choose server, database name and collation

2. I have no knowledge about the users at my customers sites so I don't add create users statements also the only user that needs access to the database is the user executing the middle tire application.

3. I do not disable FK checks. I need them to protect the consistency of the database, even if it is I who wrote the body scripts. I use FK to capture my errors.

4. I do not include the entire script in one transaction. I require from the users to take a backup of the db before they run any db upgrade scripts. For creating of a new database there is nothing to protect so running in a transaction is unnecessary. For upgrades there are sometimes extensive changes to the db. A couple of years ago we switched from varchar to nvarchar in about 250 tables. Not something you would like to do in one transaction.

5. I would recommend you to generate one script per database and version control the scripts separately.