How should you build your database from source control?

Here are some some answers to your questions:
----

1. Should both test and production environments be built from source control? **YES**
- Should both be built using automation - or should production by built by copying objects from a stable, finalized test environment?
- **Automation for both. Do NOT copy data between the environments**
- How do you deal with potential differences between test and production environments in deployment scripts?
- **Use templates, so that actually you would produce different set of scripts for each environment (ex. references to external systems, linked databases, etc)**
- How do you test that the deployment scripts will work as effectively against production as they do in test?
- **You test them on pre-production environment: test deployment on exact copy of production environment (database and potentially other systems)**
2. What types of objects should be version controlled?
- Just code (procedures, packages, triggers, java, etc)?
- Indexes?
- Constraints?
- Table Definitions?
- Table Change Scripts? (eg. ALTER scripts)
- Everything?
- **Everything, and:**
- **Do not forget static data (lookup lists etc), so you do not need to copy ANY data between environments**
- **Keep only current version of the database scripts (version controlled, of course), and**
- **Store ALTER scripts: 1 BIG script (or directory of scripts named liked 001_AlterXXX.sql, so that running them in natural sort order will upgrade from version A to B)**
3. Which types of objects shouldn't be version controlled?
- Sequences?
- Grants?
- User Accounts?
- **see 2. If your users/roles (or technical user names) are different between environments, you can still script them using templates (see 1.)**
4. How should database objects be organized in your SCM repository?
- How do you deal with one-time things like conversion scripts or ALTER scripts?
- **see 2.**
- How do you deal with retiring objects from the database?
- **deleted from DB, removed from source control trunk/tip**
- Who should be responsible for promoting objects from development to test level?
- **dev/test/release schedule**
- How do you coordinate changes from multiple developers?
- **try NOT to create a separate database for each developer. you use source-control, right? in this case developers change the database and check-in the scripts. to be completely safe, re-create the database from the scripts during nightly build**
- How do you deal with branching for database objects used by multiple systems?
- **tough one: try to avoid at all costs.**
5. What exceptions, if any, can be reasonable made to this process?
- Security issues?
- **do not store passwords for test/prod. you may allow it for dev, especially if you have automated daily/nightly DB rebuilds**
- Data with de-identification concerns?
- Scripts that can't be fully automated?
- **document and store with the release info/ALTER script**
6. How can you make the process resilient and enforceable?
- To developer error?
- **tested with daily build from scratch, and compare the results to the incremental upgrade (from version A to B using ALTER). compare both resulting schema and static data**
- To unexpected environmental issues?
- **use version control and backups**
- **compare the PROD database schema to what you think it is, especially before deployment. SuperDuperCool DBA may have fixed a bug that was never in your ticket system :)**
- For disaster recovery?
7. How do you convince decision makers that the benefits of DB-SCM truly justify the cost?
- Anecdotal evidence?
- Industry research?
- Industry best-practice recommendations?
- Appeals to recognized authorities?
- Cost/Benefit analysis?
- **if developers and DBAs agree, you do not need to convince anyone, I think (Unless you need money to buy a software like a [dbGhost][1] for MSSQL)**
8. Who should "own" database objects in this model?
- Developers?
- DBAs?
- Data Analysts?
- More than one?
- **Usually DBAs approve the model (before check-in or after as part of code review). They definitely own performance related objects. But in general the team own it [and employer, of course :)]**


[1]:

[To see links please register here]

I basically agree with every answer given by [van][1]. Fore more insight, my baseline for database management is [K. Scott Allen series][2] (a must read, IMHO. And [Jeff's opinion][3] too it seems).

- Database objects can always be rebuilt from scratch by launching a single SQL file (that can itself call other SQL files) : `Create.sql`. This can include *static* data insertion (lists...).
- The SQL scripts are parameterized so that no environment-dependent and/or sensitive information is stored in plain files.
- I use a custom batch file to launch `Create.sql` : `Create.cmd`. Its goal is mainly to check for pre-requisites (tools, environment variables...) and send parameters to the SQL script. It can also *bulk-load* static data from CSV files for performance issues.
- Typically, system user credentials would be passed as a parameter to the `Create.cmd` file.

IMHO, *dynamic* data loading should require another step, depending on your environment. Developers will want to load their database with test, junk or no data at all, while at the other end production managers will want to load production data. I would consider storing test data in source control as well (to ease unit testing, for instance).

Once the first version of the database has been put into production, you will need not only build scripts (mainly for developers), but also upgrade scripts (based on the same principles) :

- There must be a way to retrieve the version from the database (I use a stored procedure, but a table would do as well).
- Before releasing a new version, I create an `Upgrade.sql` file (that can call other ones) that allows upgrading version N-1 to version N (N being the version being released). I store this script under a folder named `N-1`.
- I have a batch file that does the upgrade : `Upgrade.cmd`. It can retrieve the current version (CV) of the database via a simple SELECT statement, launch the `Upgrade.sql` script stored under the `CV` folder, and loop until no folder is found. This way, you can automatically upgrade from, say, N-3 to N.

Problems with this are :

- It is difficult to automatically compare database schemas, depending on database vendors. This can lead to incomplete upgrade scripts.
- Every change to the production environment (usually by DBAs for performance tuning) should find its way to the source control as well. To make sure of this, it is usually possible to log every modification to the database via a trigger. This log is reset after every upgrade.
- More ideally, though, DBA initiated changes should be part of the release/upgrade process when possible.

As to what kind of database objects do you want to have under source control ? Well, I would say as much as possible, but not more ;-) If you want to create users with passwords, get them a default password (login/login, practical for unit testing purposes), and make the password change a manual operation. This happens a lot with Oracle where schemas are also users...

[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]