Compliant Database DevOps
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
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.