SQL Source Control

Source/Version Control goals are:

• Control of the versions of objects that are deployed in the various environments.
• Change and difference tracking.
• Labeling of objects.
• Recovery and troubleshooting.

While the application code is always managed with Source Control tools, the DB is usually not managed in any way, from 2 main reasons:

• No one think about it...
• SQL source control is less structured and clear process than code source control.

But, SQL source control is not complicated more that code source control.

SQL Source Control principles:

1. Re-runnable. Each script should be re-runnable.
2. Not depend on specific database/server/etc. you should be able to run the same script for any environment, server, …
3. Pay attention not to damage your data. Data scripts should be written carefully.
4. Execution order: is important in database upgrade. See below.

This is the recommended SQL Source Control process:

(As I like generally – I "keep it simple").

1. Create he baseline schema

Generate a baseline schema. This is the starting point for the database versioning.

The baseline schema includes:

• DB creation (DB, files, etc.).
• Users and permissions.
• Base schemas and tables.
• Other base objects (depends on the status of the database when you start manage it in a source control).

The baseline schema can be saved as:

1. Scripts – recommended.
2. Backup / MDF files.

2. Create a SQL file for each DB object (SP, functions, etc.)

The file should be built in a way that you can update an exists object and create the object using the same script.

2.a. SPs/Functions/Views

Those objects actually can always be dropped and re-created.

BUT – it's not recommended – from performance issues (cached plans etc.).

So, the best way to write the scripts to those objects is Create+Alter:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_NAME]') AND type in (N'P', N'PC'))

   EXEC (N'CREATE PROCEDURE [dbo].[SP_NAME] AS RETURN')

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FUNCTION_NAME]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

   EXEC (N'CREATE FUNCTION [dbo].[FUNCTION_NAME]() RETURNS @ret TABLE (ID INT) AS BEGIN RETURN END')

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trg_dbo_AAAAAA]') AND type IN (N'TR'))

       EXEC (N'CREATE TRIGGER [dbo].[Trg_dbo_AAAAAA] ON [dbo].[AAAAAA] AFTER UPDATE AS RETURN');

GO

Etc…

2.b. Tables

Tables can't be dropped because of the data.

Tables should be created only in the first time, and than check each change:

IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TABLE_NAME]') AND type in (N'U'))

       CREATE TABLE [dbo].[TABLE_NAME]

.....

Table schema changes:

IF NOT EXISTS ( SELECT * FROM syscolumns where id=object_ID(N'TABLE_NAME') and name='COLUNM_NAME')

       ALTER TABLE TABLE_NAME ALTER COLUMN COLUNM_NAME int NULL

GO

And so on (cinstaints, indexes, etc.)

2.c.  Data

New data insertion - very importrnt that:

1. Will be re-runnable.
2. Won't damage current data
3. Will be runnable to any enviromrnt - not "hard-code" like a specific enviromrnt/database.
4. Be very very very careful when deleting!

IF NOT EXISTS ( SELECT * FROM TABLE_NAME WHERE CHANGED_COLUMN_NAME='NEW VALUE')

BEGIN

       INSERT INTO TABLE_NAME .....

END

ELSE

BEGIN

       UPDATE TABLE_NAME SET ....

END

3. Each change should be updated in the script. 

In order not to delete other DB developer changes, it's important to check the changes between the last version to the new one, and make sure that the only changes are the knowen changes of the last development.

4. Labeling and tagging

Labeling and tagging will be done like standard code source control labeling and tagging.

Database Upgrade Execution order:

1.     Database objects and users,

2.     Schemas.

3.     Tables.

a.     Tables also have internal ordering: depend on Triggers, FKs and other constraints.

b.    You can also create all tables and after it create all Triggers, FKs and constraints.

4.     Basic Initial Data.

5.     Types.

6.     Functions.

7.     Views.

8.     Stored procedures.

9.     Data.

But - and envirement can have specific properties that can make this order to be changed. Think and make adjustments to your envirement.

SQL Source Control tools:

There are few tolls of SQL source control. But, if a code source control is already in use - it is good enough to the DB also.

That all - basically.

Take those principles and build your structure.

If you need and want - you can comment beloa and/or email me.