Initial installation of the production database
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.
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.
has the following testing cycle
acceptance testing (FAT) (in-house test team)
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…).
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.
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
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.
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.
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.
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.