The Old Joel on Software Forum: Part 3 (of 5) - SQL Server Database Install Question

SQL Server Database Install Question

I was wondering what the standard/preferred practices are for installing and modifying sql server tables/queries/data.

Specifically,

1) Is generating a sql script to re-create the schema on the target server the way to go for new databases?

2) To insert records in the database upon install, do people generally use code to create sql scripts (with insert statements) or package code in the installer that relies on ADO, or is there another preferred method?

3) For modifying databases (i.e., on a version upgrade you need to add or drop some fields), what is generally done?

4) Does anyone use SQLDMO or do most people stick to generating scripts?

Thanks

bg
Tuesday, January 27, 2004

I will comment on what our group does in this respect. Although I am rather interested to see how other teams handle these problems:

1. We generate SQL scripts to create the database.

2. We create scripts to insert the data the system initially requires. We have a tool which automates this process. Since you specifically asked about SQL Server, which is what we use, the tool is SQL Data Compare from Red Gate:
http://www.red-gate.com/SQL_Data_Compare.htm

3. This was a challenge for our group since we needed something to tie into our change management package in order to collectively roll-out updates with our patches and version upgrades. Essentially, we rolled our own tool to manage version control. It also packages the collective updates for a given release into one SQL Script. So, to make a short answer long, we use scripts.

4. We have had good luck with scripts.

Canuck
Tuesday, January 27, 2004

1 and 2. Upon application startup, the application will genesis the database. That means creating all the tables and filling in all the initial data. The installer is not involved (we actually don't have an installer now, but when we did, it wasn't).

3. Our application is self-upgrading. When you upgrade the code and restart it, the database is upgraded.

4. We strictly use ADO.NET with parameterized SQL.

Brad Wilson (dotnetguy.techieswithcats.com)
Tuesday, January 27, 2004

If at all possible, we have a database already set up with the schema and initial data. When installing a new database, just restore the backup.

Tom H
Tuesday, January 27, 2004

1 & 2. I use a bunch of scripts to create the schema if it is missing, add the default data if it is missing, and add any stored procedures. The scripts are structured so that you run the script a second time, it will work out your database is up to date and not do anything.

3. Along with the "base" scripts, I have a series of "upgrade" scripts that allow you to go from one version to another. Again, they check the existance of tables & fields before they add them so you can run the script twice without any side effects.

4. I prefer scripts because I can have more direct control over exactly what happens at install / upgrade time. A script bug can easily be changed in an editor, checked back in, and the whole build process run to generate a new install image.

Better Than Being Unemployed...
Tuesday, January 27, 2004

1 & 2: We ship or application with a utility that creates and secures our database on server at the client site. This uility is, of course, run only once per site regardless of the number of clients installed.

3: We ship our updates with a utility application which will update the database to the latest version.

4: Our update programs are written in .Net. They use a combination of scripts and the SQLDMO library. Basically if it is easier to do in script, we use script. If it easier to use the SQLDMO object model, we use that. For simple tasks we have functions that take parameters and encapsulate the SQLDMO.

Note: I really, really like SQLDMO and I wrote the setup and update utilites so I am not necessarly objective. Of course, everything is working fine at over 100 sites that use the current version of of software.

Eric Budd
Tuesday, January 27, 2004