Initial installation of the production database

Introduction

Your software has passed all (your only?!) testing phase(s) and it is time to install your database into production, I will outline below how I accomplish this task. This article is concerned with an evolving system i.e. you will perform an initial installation but subsequent installations may be required for such things as customer change requests (no faults – your testing was perfect! ) while retaining all data inserted since the application began use.

Scripts

I create all my database objects from scripts and not the other way around. I never use Enterprise Manager (EM) to create a stored procedure then reverse engineer a script, if you perform unit testing against a database where you have created objects via EM how can you guarantee that your scripts are consistent and that when you install to an external site you won’t introduce a bug. Aside from this reverse engineering can sometimes produce scripts with ugly formatting which have poor readability. After unit testing the script we then copy it to Visual ScouceSafe (VSS) from where all version control is governed.

Testing

Our software has the following testing cycle

·          Unit testing (developer)

·          Factory acceptance testing (FAT) (in-house test team)

·          Site acceptance testing (SAT) (external test team)

For all test phases after unit testing I perform a full installation. The point being that your testing process is not only testing your software but it’s installation too. Again, if you simply start FAT testing against your development database, you can not install to SAT with any confidence in your mechanism (objects missing out of build, necessary look up tables not populated etc…).

Initial installation

After developing my first system using SQL Server I installed to production by simply restoring a backup of the test database. I now use a windows command file to perform all installations following the template from a previous excellent article by Steve Jones (Migrating Objects to Production), the file simply executes multiple scripts using the DOS command OSQL. I will outline below why I believe restoring a backup is the wrong approach.

Your library

This is the main reason why I use this method. If you install from a backup you cannot guarantee you are installing what is in your library under source code control. What happens if you restore a backup, then for your first patch release you need to change a stored procedure. You check it out, make the change, test then install. Problem is your script under version control was inconsistent with the version in the database you restored and you have introduced a bug which causes another part of the system to fail with consequent down time. If you install from your scripts in the first place then test against that you will eliminate any potential errors like these.

Re-producible

You will need to perform the same installation time and again, for the test phases outlined above and maybe multiple client sites have different versions of the same database. Surely better to have one command file which facilitates a completely re-producible build which could be performed by anyone and has been pre-tested. If multiple people are performing a number of different installations by restoring a backup can you be sure all installations are identical?

Documentation / Consistency

Going back to the example above where you perform an initial installation, the system gets used for a bit then one stored procedure needs to change following a change note. Presumably most people would perform this patch release by executing the one script into the database (via command file or not) – you cannot restore a backup in this case as the customer would lose all the data they have entered already. If you had performed the initial release by the restore method, you now have the situation where your two installations were by two different means. I prefer to have one consistent way to do things, this also makes documenting your procedures simpler if your company / client requires this.

Size of build

I have found in a lot of cases all the scripts required to produce the build will fit on a floppy disk whereas taking a backup to install usually involves burning a CD. Not a great benefit here but does make your life slightly simpler.

Commenting

Using a command file allows you to add comments. This makes traceability better as you can document such things as who produced the build and the reason for it etc.

Disadvantages

The greatest disadvantage involved in this method is the overhead of creating the command file to execute the build. It’s less effort just to restore a backup of your test database straight into production. I believe the benefits outlined above offset this minimal effort which is required.

Conclusion

This article outlines the methodology I use to perform my initial database release into production. How does everybody else perform this task? it’s always interesting to see how other people do things.