Deprecated: __autoload() is deprecated, use spl_autoload_register() instead in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_class_loader.funcs.php on line 55

Deprecated: Array and string offset access syntax with curly braces is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_misc.funcs.php on line 5137

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_misc.funcs.php on line 8521

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\files\model\_file.funcs.php on line 1442

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\files\model\_file.funcs.php on line 1447

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\files\model\_file.funcs.php on line 1453

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\files\model\_file.funcs.php on line 1460

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\files\model\_file.funcs.php on line 1465

Deprecated: define(): Declaration of case-insensitive constants is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\model\db\_db.class.php on line 49

Deprecated: define(): Declaration of case-insensitive constants is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\model\db\_db.class.php on line 50

Deprecated: define(): Declaration of case-insensitive constants is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\model\db\_db.class.php on line 51

Deprecated: Function get_magic_quotes_gpc() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_param.funcs.php on line 2112

Deprecated: Function create_function() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_url.funcs.php on line 817

Deprecated: Function create_function() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_url.funcs.php on line 818

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\plugins\model\_plugins_admin.class.php on line 1466
Keeping Databases In-Synch With Your Source Control
 


Deprecated: Function create_function() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_url.funcs.php on line 817

Deprecated: Function create_function() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_url.funcs.php on line 818
Keeping Databases In-Synch With Your Source Control


Deprecated: Array and string offset access syntax with curly braces is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\plugins\_auto_p.plugin.php on line 502

Deprecated: Array and string offset access syntax with curly braces is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\plugins\_auto_p.plugin.php on line 500

Deprecated: Array and string offset access syntax with curly braces is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\plugins\_auto_p.plugin.php on line 500

Deprecated: Array and string offset access syntax with curly braces is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\plugins\_texturize.plugin.php on line 116

Problem: Your development, test and production databases tend to accumulate cruft (aka. old stored procedures or schema) and you need a way to manage changes in a simple automated way.

Solution: I've had the great fortune to work with some excellent teams that have crafted elegant solutions to this problem. The following is a boiled down simple approach that provides a great way to tame database source control and upgrade issues.

Development Tools of Choice: VS.Net 2003 Ent., VSS, Nant, SQL Server 2K

Steps:

1. Using VS.Net and VSS, create 2 database projects. One to contain all objects that can be recompiled without data-loss (Stored Procs, UDFs, Views, Triggers). The second database project to contain a database_release.sql file to contain your schema changes for each release (make sure they are repeatable eg. "if exists ....") Use these 2 projects to manage all you database changes from release to release. Be sure to clean up any re-named or deleted objects from VSS.

2. Create an Nant build file that does the following:

  • Allows you to call it with "dev", "test", or "prod"
  • Does a recursive get from VSS on the database projects
  • Applies an auto-revisioned version label to these projects in VSS
  • Stage these files to a well known network location to appropriate sub-directory eg. "dev\AppXDatabase_1.03.22.31"
  • Drops all re-runnable objects from the corresponding target database for dev, test or prod
  • Apply repeatable schema changes for release
  • Recursively apply all the re-runnable objects to the database

3. For each release you can wipe and build the release schema project as needed with new schema for the upcomming release.

Benefits of Approach: By doing this you get a versioned history of changes to your database. You also get versioned stage directories for dev, test and prod environments that have all the schema and re-runnable database objects at that point in time. These versioned stage directories correlate to a label in VSS. By automating the application of the schema and the dropping and recreating of the re-runnable objects your database will reflect your source control.

No feedback yet