Home Forums SQL Server 2005 Administering Migrating from Dev to Prod RE: Migrating from Dev to Prod

  • March 25, 2008 at 7:34 am

    #793481

    I'm with Jeff and others about using a source code control system.

    In our environment we continually create new databases (for new customers) and upgrade all existing databases (customers) when we make changes to the product.

    We also use a Dev->QA->Stage->Prod environment. In addition, we only have a small maintenance window in which to apply the changes in the production environment. So we have to get it 100% right the 1st time.

    To top it off, I'm the major (90+%) database developer.

    All DDL code and stored procedures is stored as separate files within the source code control system. We then utilize a "build" script, with appropriate recipe files that takes these individual files and concatenates them together in the correct order into a single CreateSchema.sql file. So all new databases are exact copies of each other.

    All database changes (e.g., adding a new column, new tables, etc.) are made to both the master DDL file(s) and a separate "TableChanges" file. Similar to the schema creation "build" process, a single "UpgradeSchema.sql" file is created by a schema upgrade build process.

    Note that each schema change can have all sorts of dependencies.

    Side note: The body of all procedures is filled in via ALTER PROCEDURE. Therefore, the same procedure code (as a separate file) is used in the build recipe for both the new schema and the scham upgrade. Similar to Oracle's CREATE OR REPLACE syntax. The same goes for VIEWs.

    This UpgradeSchema.sql file is then applied to an existing database (in Dev, QA, Stage, Prod, etc.) to bring them up to the same level as new databases.

    The process is 100% repeatable and by using what is in the source code control system I know exactly what the outcome will be.

    To often, a "diff" of two different databases and/or reverse engineering out the DDL yields incorrect results. I've been burnt too many times (on multiple database systems) with these "magical" tools. If it's not 100% accurate (handling all of the complex dependencies) and I still have to make some manual tweaks, then I haven't gained anything.

    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]