Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Initial Installation of the Production Database

By Andy Jones,

Initial installation of the production database

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.

 

 

 

 

 

 

 

Total article views: 6213 | Views in the last 30 days: 1
 
Related Articles
ARTICLE

Powershell Database Backup Script

Learn how to write a script for regular database backups using Powershell and SMO.

FORUM

xmla script for automate ssas database backup

xmla script for automate ssas database backup

SCRIPT

Script to Check the Database Backup duration

Script to Check the Database Backup duration of entire instances

SCRIPT

Backup & Restoration Script

This script provides very useful information about database backup and restoration.

FORUM

backup scripts

all backup scripts (full, diff, trans)

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones