Click here to monitor SSC
  • Av rating:
  • Total votes: 12
  • Total comments: 0
John Rummell

SQL Source Control - no more database development without it

03 August 2010

John Rummell had a problem that is shared by most database developers. How can one use SQL Server Management Studio to put source control files into a source control system such as SubVersion? The processes he tried were all error-prone and awkward to use. And then he found a solution.

Like many developers, I have been waiting a long time for a good source control solution for SQL. I wanted something that would easily track changes to table definitions, views, and stored procedures. In the past I’ve used SQL Server Management Studio to generate a script that would create all of the objects in my schema. Inevitably, I would forget to change the options to script table indexes or to include if not exists or some other setting that wasn’t the default. At this point I would regenerate the script again with the correct settings and then save it to a file and add it to my project which was in source control.

Now the script itself is quite a mess. Due to the nature of object dependencies, the users go first, then tables, then stored procedures and finally the foreign key constraints. Here’s an example from Facebook Steam Achievements, an open source Facebook application for sharing Steam Achievements (PC gaming achievements on the Steam network) with your Facebook friends.

USE [Steam]

GO

/****** Object:  User [steam]    Script Date: 07/26/2010 20:00:33 ******/

CREATE USER [steam] FOR LOGIN [steam] WITH DEFAULT_SCHEMA=[dbo]

GO

/****** Object:  Table [dbo].[steam_User]    Script Date: 07/26/2010 20:00:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[steam_User](

    [FacebookUserId] [bigint] NOT NULL,

    [SteamUserId] [varchar](50) NOT NULL,

 CONSTRAINT [PK_steam_User] PRIMARY KEY CLUSTERED

(

    [FacebookUserId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[steam_Achievement]    Script Date: 07/26/2010 20:00:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[steam_Achievement](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [Name] [varchar](100) NOT NULL,

    [GameId] [int] NOT NULL,

    [Description] [varchar](500) NOT NULL,

    [ImageUrl] [varchar](250) NOT NULL,

 CONSTRAINT [PK_steam_Achievement] PRIMARY KEY CLUSTERED

(

    [Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[steam_UserAchievement]    Script Date: 07/26/2010 20:00:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[steam_UserAchievement](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [SteamUserId] [varchar](50) NOT NULL,

    [AchievementId] [int] NOT NULL,

    [Date] [datetime] NOT NULL,

    [Published] [bit] NOT NULL,

 CONSTRAINT [PK_steam_UserAchievement] PRIMARY KEY CLUSTERED

(

    [Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  StoredProcedure [dbo].[GetAchievements]    Script Date: 07/26/2010 20:00:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Name

-- Create date:

-- Description:   

-- =============================================

CREATE PROCEDURE [dbo].[GetAchievements]

    -- Add the parameters for the stored procedure here

    @steamUserId VARCHAR(50)

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

 

    -- Insert statements for procedure here

    SELECT achievement.*

    FROM

        steam_Achievement AS achievement

        LEFT JOIN steam_UserAchievement AS userAchievement ON achievement.Id = userAchievement.AchievementId

    WHERE

        userAchievement.SteamUserId = @steamUserId

END

GO

/****** Object:  ForeignKey [FK_steam_UserAchievement_steam_Achievement1]    Script Date: 07/26/2010 20:00:33 ******/

ALTER TABLE [dbo].[steam_UserAchievement]  WITH CHECK ADD  CONSTRAINT [FK_steam_UserAchievement_steam_Achievement1] FOREIGN KEY([AchievementId])

REFERENCES [dbo].[steam_Achievement] ([Id])

GO

ALTER TABLE [dbo].[steam_UserAchievement] CHECK CONSTRAINT [FK_steam_UserAchievement_steam_Achievement1]

GO

/****** Object:  ForeignKey [FK_steam_UserAchievement_steam_User]    Script Date: 07/26/2010 20:00:33 ******/

ALTER TABLE [dbo].[steam_UserAchievement]  WITH CHECK ADD  CONSTRAINT [FK_steam_UserAchievement_steam_User] FOREIGN KEY([SteamUserId])

REFERENCES [dbo].[steam_User] ([SteamUserId])

GO

ALTER TABLE [dbo].[steam_UserAchievement] CHECK CONSTRAINT [FK_steam_UserAchievement_steam_User]

GO

Wouldn’t it be nice if this was organized into separate scripts for each object? It would certainly make it easier to read. Another method I’ve used is SQL Server Management Studio projects. This involves creating a script file for each object. In order to keep the database and scripts in sync, I would only make changes to the database by changing the scripts and executing them (instead of using the Design, Modify, or Script as ALTER options in Object Explorer). If you want to use Subversion to put this project in source control, you won’t have the convenience of add-ins like VisualSVN or AnkhSVN for Visual Studio. You could use External Tools in place of an add in, but it leaves much to be desired.

Red Gate has recently released a product called SQL Source Control that solves all of the issues I’ve mentioned. SQL Source Control allows you to edit your database as you normally would, but without worrying about updating or generating scripts. You don’t have to remember to script your database after you add a new stored procedure or add a column to a table. If you’ve made a change, it will display with a blue dot in the Object Explorer. You can think of this as the red modified overlay you’ve seen with TortoiseSVN.

To commit your changes, you can simply right click on the object and select Commit changes to source control... At this point you’ll see your changed objects and a diff view of the currently selected object. Simply type in a comment click Commit.

To get the latest version, simply right click on the database and select Get latest changes from source control…. Once you review the changes and click Get Latest, SQL Source Control will actually update your database with the latest changes. This way, you don’t have to worry about getting the latest scripts and then making the necessary changes in order to run it on your current version.

SQL Source Control also keeps your scripts organized. You probably won’t need to view the scripts, but if you are curious, you can view them in your working copy. In my Steam Achievements project I linked the database to https://facebooksteamachievements.googlecode.com/svn/trunk/Database. So now when I browse the Database folder in my working copy, I’ll see the scripts created by SQL Source Control.

As you can see, the scripts are organized by object type. If you browse the Tables folder you’ll see all of the table scripts.

There are a few things that I think would make SQL Source Control even better. Merge support would be very helpful as well as integration with the Visual Studio database tools. Merge would allow you to branch your database development, and would be a requirement for teams that use the branch-per-feature strategy. Visual Studio tooling support would allow developers to stay within one IDE for all of their development needs.

In summary, SQL Source Control is an SSMS add-in that:

  • Tracks changes to your database as you make them,
  • Doesn’t require you to generate scripts,
  • Updates your database when you get the latest version,
  • Keeps your scripts organized,
  • I don’t want to do database development without.

SQL Source Control can be downloaded from the Red Gate website.

John Rummell

Author profile:

John Rummell is a .NET web developer in Northeast Ohio. In addition to his day job, he manages a few open source projects. Steam Achievements for Facebook (code.google.com/p/facebooksteamachievements) allows him to put his interest in gaming to good use, and xVal for WebForms (xvalwebforms.codeplex.com) aims to help ASP.NET developers validate better. You can read his blog at john.rummell.info.

Search for other articles by John Rummell

Rate this article:   Avg rating: from a total of 12 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.
 
Simple-Talk Database Delivery

DLM
Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Microsoft and Database Lifecycle Management (DLM): The DacPac

The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx), provides an... Read more...

 View the blog

Top Rated

Working with SQL Server data in Power BI Desktop
 What's the best way of providing self-service business intelligence (BI) to data that is held in... Read more...

Microsoft and Database Lifecycle Management (DLM): The DacPac
 The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx),... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Archiving Hierarchical, Deleted Transactions Using XML
 When you delete a business transaction from the database, there are times when you might want to keep a... Read more...

Rollback and Recovery Troubleshooting; Challenges and Strategies
 What happens if your database deployment goes awry? Do you restore from a backup or snapshot and lose... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.