Important: Moving Projects to Production?
When you move projects to production, you need to be absolutely certain that you have the right pieces deployed, the right changes completed. You need to be able to check data, objects and synchronize it all, making sure the changes and updates are reflected in the production environment. Idera’s SQL Comparison Toolset will take care of all of this for you – and even keep track of version control information, along with automating your deployment processes. Idera’s sure you’ll love the tools – in fact so sure they are offering a free trial so you can use it yourself on your systems. You’ll even be entered in a drawing to win an Amazon Kindle if you download the trial before March 31 (so hurry!).
Managing Database Change – Community Responds II
Dave takes the approach of building a database to a specific release through change scripts and version control. He says:
"Ben, read your post re managing database change. You recognized what most data developers haven’t, that change management for the db requires more thought than C# code. But you mention this is due to the fact that the code contains the data as well. It’s actually simpler than that, and when we recognize that, the build process becomes much easier. Compiled/procedural code is only concerned with compiling *now*, with db code, we need to be concerned with the database "compiling" for a new db, or any older version. So db change management is really making sure that every single script you run will always change any database (blank, v2, v10, whatever) to be exactly what you need it to be for this release. DB source code needs to know "history".
If you understand those rules then you never need a vendor tool to look at before and after images of a db to generate scripts, you never have to have special folders in your source control system to handle SQL that goes "from releaseA to releaseB", you don’t need to worry *at all* about branching/merging, you can deploy much easier, etc etc.
I have yet to find a vendor tool (for SQL server) that handles the concept of evolutionary database design. My gut tells me this is because DBA-types hate agile and using "evolutionary" and "database" in the same sentence is anathema.
I actually have a process that handles all of these issues. We have a series of SQL scripts stored in a folder structure with subfolders named 1Tables, 2Constraints, 3ForeignKeys, 4Indexes, 5Functions, 6Views, 7Procedures, 8ModelData, 9CustomerData. All scripts are "rerunnable" without being destructive and always bring *any* db (new or any version to be upgraded) up to the "current" branch/release. All SQL scripts are called via a .vbs file that merges the scripts (saves on context switching) and executes them via osql (it’s a 10 year old process, but it works so well I’ve never upgraded it). The files are merged alphabetically (note that 1Tables is called before 7Procedures) which eliminates 90% of the dependency problems. Note that 5Functions is called before 6Views, which means a given function may fail if it relies on a view not yet created. In that case we move the script to the other folder and mark the original file with a SQL "–" comment noting its new location due to dependencies. Also, 5FunctionsFunctionA.sql may depend on 5FunctionsFunctionB.sql, which will fail due to dependencies…this is accomplished by renaming FunctionB.sql to 00FunctionB.sql or similar, and noting its new location. Other than 5Functions and 6Views there are *never* any other dependency problems.
"Rerunnability" is the key to all SQL scripts. We perform an IF EXISTS DROP THEN CREATE for functions, views, and procedures. For tables the paradigm is IF NOT EXISTS THEN CREATE ELSE "ALTER".
The "ALTER" is custom stored procedures that we have written that absolve the developer from knowing/understanding very complex ALTER syntax. The "alter procedures" take a series of parameters that list how the table (or index, constraint, default, whatever) should "look", then the procedure figures out how to do the work to accomplish that. Sounds like overkill for changing a col from NOT NULL to NULL, until you think about an alter procedure to change a primary key from nonclustered to clustered. The alter procedure first has to save the FK properties that are dependent on the PK, drop the FKs, drop the PK constraint, drop the existing clustered index, recreate the PK as clustered, create the other formerly clustered index as nonclustered, then rebuild the dropped FKs. My scripts handle all of that. One little call similar to this does everything:
EXEC DBA_CreatePrimaryKey
@Table = ‘FOO’
,@Schema = ‘dbo’
,@PKName = ‘PK_FOO’
,@IndexKey = ‘colA,colB’
,@IsClustered = ‘Y’
Notice how easy it would be for a developer to change the PK from colA/colB to add a 3rd col in the compound key?
You mentioned a problem when data itself needs to be modified. No problem. Those scripts are stored in 8ModelData, one file per table. The SQL script lists a series of INSERT statements to a temp table that is identical to the underlying table. The developer merely changes the INSERT statement to contain whatever data is required. A DELETE or UPDATE is never needed. How? Because we compare the temp table to the underlying using MERGE, INTERSECT, and EXCEPT…we make the underlying look identical to the temp table.
Error logging is very simple since we use osql. Basically, search the -o log file for "msg " since *most* SQL errors start with that string (we also search for authentication/connectivity errors too). I teach our developers that custom error handling errors should always start with "Msg ".
The process also works for Oracle as well, with obvious modifications to the ALTER procedures.
There’s obviously more to it than this, but the key is that any good db build process must view the db as distinct from the procedural code and must handle history. This is actually simple when you remember the rerunnability concept."
Featured Article(s)
Undocumented SQL Server 2008 Database Maintenance Plan Stored Procedures
In this article, Alexander Chigrik looks at seven undocumented SQL Server database maintenance plan stored procedures that shipped with SQL Server 2008.