Fog Creek Software
Discussion Board




Production/Testing Databases w/ SQL Server

Hello All,

I'm working with a new client on a web project (classic ASP).  They have two sites, a production and a development site.  All work is done to the development site and then when completed it's moved to production.

The only wrinkle they have is that they only have one database -- production.  The testing code uses the production database.  I'm already working with them on creating a testing database and using that for development.

I'm fairly new to working with SQL server.  I mostly work with PHP/MySQL on Unix platforms.  When I do this kind of work with MySQL, I generally save up the CREATE TABLE and ALTER statements I use for development so I can replay them in production to update production tables.  I also don't have to worry about stored procedures (there aren't any in MySQL).

So my question is, what's the best way to develop one database and move those changes into production?  What sorts of techniques/tools do you all use for that purpose?

Thank you!

Almost Anonymous
Thursday, August 19, 2004

Script all of your changes to the development database.  Compile together scripts that are related, so as to have fewer files.  When you're ready to move your changes into production, execute your development scripts in the Prod environment.

Works well for me.

muppet
Thursday, August 19, 2004

In other words, don't ever pop into a console and type 'alter table ....'.  Script.  Everything.

This way you have a running account of all of your changes, and an easy way to reapply those changes in any environment.

muppet
Thursday, August 19, 2004

For Data, create DTS package.

For structure. Create scripts, don't try to play around with commands ... life will be much easier.

Raju Patel
Thursday, August 19, 2004

The MSSQL Client is your do-everything tool, all with a lovely GUI.

Consider DTS for complex data transfer work. Otherwise do your MySQL solution: save your scripts and run them in the MSSQL Query Analyzer on your production database.

It sounds like you are helping the new client to do things better already. I hope they are suitably impressed.

Herr Herr
Thursday, August 19, 2004

At our shop, someone made a tool and set of scripts which:

* Reads the system tables of an existing SQL database, to create (as output) a data file which describes the database's schema (tables, fields, indexes, foreign keys, stored procedures)

* Uses the data file (as input) as it reads the schema of another database instance ... and invokes CREATE TABLE and similar commands, if the schema of the second database doesn't match the schema defined in the data file (as extracted from the first database)

We used this to update the schema of our development databases and of the production database.

Unfortunately I can't gve you the source code of the tool he made.

Christopher Wells
Thursday, August 19, 2004

http://www.martinfowler.com/articles/evodb.html


Thursday, August 19, 2004

Don't forget to BACKUP your prod database before applying any changes... :)

Chris Peacock
Thursday, August 19, 2004

Save time with

http://www.red-gate.com/SQL_Compare.htm

Mike
Thursday, August 19, 2004

Script your DB structure changes.  Also script any "vanilla" data changes.

In order to make sure that you apply the scripts in order (and don't apply them twice, etc), add a small table to your DB with only one row.  This will track the version of your DB structure.  Every time you run a script, it's first task will be to SELECT this value and compare it against what it expects to see.  If they don't match, the script aborts.

If the script succeeds, then you UPDATE that row with the new version number.  To keep track of versions and what their purpose was, use a spreadsheet that is stored in your source-code control system (along with the scripts).

example
Thursday, August 19, 2004

We're using plain VB code to do the additions of tables and fields. A Version table keeps track of the changes. So we start with an empty database.

Christer Nilsson
Thursday, August 19, 2004


  We save every database change in scripts, as recomended above. 

  To upgrade our customers database, we have a home grew app that stores the scripts for each release in a single-file database.

  Then we send this file with a little utility that checks the client's database version, and executes the scripts needed to upgrade to the latest version.

Ricardo Antunes da Costa
Thursday, August 19, 2004

==>Script all of your changes to the development database.

Agreed.

One other thing that, in my opinion, is a *must* do.
Create an "undo" script too. In case you have to roll back some changes.

In some of my environments, the undo script works much more quickly than having to restore a backup.

Sgt. Sausage
Thursday, August 19, 2004

NAnt supports database build tasks through NAnt contrib

I find BCP as a quick and dirty alternative to DTS
for simple data export/import needs

BigJimSlade
Thursday, August 19, 2004

Backup the production database and server into Development and go through the entire installation process, then run through your regression tests on the Development servers before you get anywhere near the Production servers.

Tom H
Thursday, August 19, 2004



Tom H has it exactly.


Backup production into development and test it there first.

Even if development has to be your personal box, do it.

KC
Thursday, August 19, 2004

of course Tom is right but it's accessory to the original question which is how to build the install process in the first place.

muppet
Thursday, August 19, 2004

*  Recent Topics

*  Fog Creek Home