is what I think the best build process for sql objects. Of course my opinion is jaded.
What does this do?
It's common in development to need to deploy database objects (tables, views, procedures, constraints) when your application is deployed. Sounds easy...just script everything and run it for the customer. But that won't work if you are upgrading a customer from a prior release. Now we need to script out a "net new" database (build the db from scratch) as well as the scripts to "port" the database from version x to version y. What if we have customers on 6 different releases and need to bring them all up to current? That's a lot of scripts.
I call this a porting process. There are many ways to write porting routines and even some decent tools to accomplish the task. What I have attached is my method.
Grab the attachment and unzip it. There are a series of folders and files. The key to getting the system to work is to ensure every .sql files is "rerunnable"...meaning I can execute it for a net new database, a ported database, or anything in between and when the files are finished running I have a usable database.
Note that folders are numbered. The setup.vbs file is looking for a starting point and recursively calls itself for each folder, in alphabetical order. When it finds subfolders it calls into those...looking for .sql files, again alphabetically.
.sql files are merged to one file and then executed using osql. Errors are outputted to a logfile for later analysis.
This is accomplished using a variety of techniques. For tables we always run code similar to
IF NOT EXISTS (the table)
ELSE ALTER if needed
Other db objects are rerunnable using a series of DBA_ procedures in 1PortingObjects. For instance, assume I need to change a primary key from a non-clustered to clustered index. If I have foreign keys already declared this can be a nightmare process to code. First I would need to find the foreign keys, drop them, drop the PK index and recreate it as clustered, then recreate the foreign keys. My DBA_ procedures handle this automagically.
I also have similar code to handle indexes, foreignkeys, and defaults. No more creating ALTER scripts!!!
...contains a series of .vbs files that are helpful in scripting an existing db to fit into the porting process framework. Notes are provided in TODO.txt. This folder also houses the DBA_ procedures
It is common to create these .sql files and find out later that some code was not written correctly and now a column is missing in ported dbs but exists in net new. Or an index is missing or has different keys. Or even lookup data is defined incorrectly in a net new vs ported. There is a vbs file that can be called as part of a continuous build loop that will look for two databases and run a compare. A report is output that will show how the dbs differ in schema and lookup data.
- Before a port is kicked off a back up is taken (can be disabled)
- If the porting process encounters an error then that db is backed up and the original db is restored (can be disabled)
- If the database doesn't exist a db will be created for you based on parameters.
Running the Process
This is easy. Just doubleclick setup.vbs. To automate the process just call it from the command line and pass in the parameters. You can integrate this easily in .msi files. It requires just a few parameters: server/instance, sa (or equivalent) and password (or a parameter for NT authentication), the db name to install/port, a version flag, a lang flag, and the folder to contain the logs. Very easy to customize for whatever purpose you need.
I've only scratched the surface of what this tool does. I don't feel like documenting further. I suggest you download the code and look through it. It's really simplistic...yet effective. If you have questions send me an email at email@example.com