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

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.

Answer 2

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 3

The best practices will differ considerably on whether it is the first time set-up or a new version being pushed. For the first time set-up yes you need create database and create table scripts. For a new version, you need to script only the changes from the previous version, so no create database and no create table unless it is a new table. Now you need alter table statements becasue you don't want to lose the existing data. I do usually write stored procs, functions and views with a drop and create statment as dropping those pbjects doesn't generally affect the underlying data.

I find it best to create all database changes with scripts that are stored in source control under the version. So if a client is new, you run the create version 1.0 scripts, then apply all the other versions in order. If a client is just upgrading from version 1.2 to version 1.3, then you run just the scripts in version 1.3 source control repository. This would also include scripts to populate or add records to lookup tables.

For transactions you may want to break them up into several chunks not to leave a prod database locked in one transaction.

We also write reversal scripts to return to the old version if need be. This makes life easier if you have a part of a change that causes unanticipated problems on prod (usually performance issues).

Answer 4

Should I include the CREATE DATABASE statement?
Should I create users for the database in the same script?

That depends on your DBMS and your customer.

In an Oracle environment you will probably never be allowed to do such a thing (mainly because in the Oracle world a "database" is something completely different than e.g. in the PostgreSQL or MySQL world).

Sometimes the customer will have a DBA that won't let you create databases (or schemas or users - depending on the DBMS in use). So you will need to supply that information to the DBA in order for him/her to prepare the environment for your script.

May I include the hole script in a transaction?

That totally depends on the DBMS that you are using.

Some DBMS don't support transactional DDL and will implicitely commit any transaction when you execute a DDL statement, so you need to consider the order of your installation script.

For populating the tables with data I would definitely try to do that in a single transaction, but again this depends on your DBMS.

Some DBMS are faster if you commit only once or very seldomly (Oracle and PostgreSQL fall into this category) but will slow down if you commit more often.

Other DBMS handle smaller but more transactions better and will slow down if the transactions get too big (SQL Server and MySQL tend to fall into that direction)

Answer 5

For development purposes it's a good idea to create one script per database object (one script for each table, stored procedure, etc). If you check them into your source control system that way then developers can check out individual objects and you can easily keep track of versions and know what changed and when.

When you deploy you may want to combine the changes for each release into one single script. Tools like Red Gate SQL compare or Visual Studio Team System will help you do that.

Answer 6

Direct answers, please ask if you need to expand on any point

* Should I include the CREATE DATABASE statement?

Normally I would include it since you are creating and owning the database.

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

This is also a good idea, especially if your application uses specific users.

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

If the script includes data population, then it helps to disable it so that the order is not too important, otherwise you can get into complex scripts to insert (without fk link), create fk record, update fk column.

* May I include the hole script in a transaction?

This is normally not a good idea. Especially if data population is included as the transaction can become quite unwieldy large. Since you are creating the database, just drop it and start again if something goes awry.

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

One per database is my recommendation so that they are isolated and easier to troubleshoot if the need arises.