SQL Theory: Database source control

How to: Use Source Control with SQL Server Management Studio

Source control is available in SQL Server Management Studio when a source control client is installed on the computer. Database objects are not directly protected by the source control provider, but you can create scripts of database objects and save the scripts under source control. After installing the source control client, follow the following steps to use the source control client.

To enable SQL Server Management Studio to use a source control client

1. In Management Studio, on the Tools menu, click Options, expand Source Control, and then click the Plug-in Selection page.
2. In the Current source control plug-in box, select the source control product.
3. On the File menu, point to Source Control and then click the appropriate option. Solutions and the projects, scripts, and files they contain can be added to source control or opened from source control.
4. On the View menu, click Pending Checkins to see the status of the solution that you have checked out.

When it comes to version control, the database is often a second or even third-class citizen. From what I've seen, teams that would never think of writing code without version control in a million years-- and rightly so-- can somehow be completely oblivious to the need for version control around the critical databases their applications rely on. I don't know how you can call yourself a software engineer and maintain a straight face when your database isn't under exactly the same rigorous level of source control as the rest of your code. Don't let this happen to you. Get your database under version control.

Yes. You should be able to rebuild any part of your system from source control including the database (and I'd also argue certain static data).

Assuming that you don't want to have a tool to do it, I'd suggest you want to have the following included:

• Creation scripts for the basic table structures including schemas, users, tables, keys, defaults and so on.
• Upgrade scripts (either altering the table structure or migrating data from a previous schema to the new schema)
• Creation scripts for stored procedures, indexes, views, triggers (you don't need to worry about upgrade for these as you just overwrite what was there with the correct creation script)
• Data creation scripts to get the system running (a single user, any static picklist data, that sort of thing)
• All scripts should include the appropriate drop statements and be written so they can be run as any user (so including associated schema / owner prefixes if relevant).

The process for updating / tagging / branching should be exactly as the rest of the source code - there's little point in doing it if you can't associate a database version with an application version.

Incidentally, when you say people can just update the test server, I'm hoping you mean the development server. If developers are updating the test server on the fly then you're looking at a world of pain when it comes to working out what you need to release.