At CodePaLousa Day #1, I attended a session, Tools and Adoption Patterns for Automating Database Deployment, about doing automated change in databases. That’s more than I need to do right now, however, it gave me a +1 on how I’m currently managing change, which is my default method if the customer doesn’t provide one.
What I do:
- For every change, I start or append to a script file.
- When a change gets to prod or “moves on” to branch, I start a different script file.
- Every script file must be re-runnable.
- Every script file must have a precondition check – something that is true of the database before it can run against it.
- For SqlServer, if the precondition check fails, I turn noexec on to prevent the rest of the script from running.
- For SqlServer, DDL statements are wrapped in exec() so that I don’t get compilation errors.
Here’s a sample, with names changed to protect the client:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- This is something from the most recent previous round of changes, which should already exist. PRINT 'Precondition Check' if (not exists (select 1 from information_Schema.COLUMNS where TABLE_NAME='Quadrant' and COLUMN_NAME='ImperialCode')) begin PRINT ' Failed -- skipping rest of script' set noexec on end else begin PRINT ' Passed' end GO -- In support of TA26 (revision 1683): PRINT 'Table NoRebelsAllowedRule' if (not exists (select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME='NoRebelsAllowedRule')) begin CREATE TABLE [dbo].[NoRebelsAllowedRule]( [NoRebelsAllowedRuleID] [int] IDENTITY(1,1) NOT NULL, [CatalogID] [int] NOT NULL, [DeletedOn] [datetime] NULL, [DeletedByUserID] [int] NULL, [CreatedOn] [datetime] NOT NULL, [CreatedByUserID] [int] NOT NULL, [UpdatedOn] [datetime] NULL, [UpdatedByUserID] [int] NULL, CONSTRAINT [PK_NoRebelsAllowedRule] PRIMARY KEY CLUSTERED ( [NoRebelsAllowedRuleID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] PRINT ' Created' end else begin PRINT ' Already Exists' end GO PRINT 'Foreign Key FK_NoRebelsAllowedRule_Catalog' if (not exists ( select 1 from information_schema.REFERENTIAL_CONSTRAINTS where constraint_name='FK_NoRebelsAllowedRule_Catalog' ) ) begin ALTER TABLE [dbo].[NoRebelsAllowedRule] WITH CHECK ADD CONSTRAINT [FK_NoRebelsAllowedRule_Catalog] FOREIGN KEY([CatalogID]) REFERENCES [dbo].[Catalog] ([CatalogID]) PRINT ' Created' end else begin PRINT ' Already Exists' end GO PRINT '04/23/13 Replacing Catalog.CannotFireBlasters with BlasterBehaviorID' if (not exists ( select 1 from information_schema.COLUMNS where table_name='Catalog' and column_name='BlasterBehaviorID' ) ) begin alter table [dbo].[Catalog] add BlasterBehavior INT NOT NULL DEFAULT '0'; PRINT ' Added BlasterBehaviorID' end else begin PRINT ' BlasterBehaviorID Already Exists' end if (exists (select 1 from information_Schema.COLUMNS where table_name='Catalog' and column_name='CannotFireBlasters' ) ) begin exec (N'update [dbo].[Catalog] set BlasterBehaviorID=0 where CannotFireBlasters=0'); exec (N'update [dbo].[Catalog] set BlasterBehaviorID=3 where CannotFireBlasters=1'); PRINT ' Migrated Data from CannotFireBlasters' alter table [dbo].[Catalog] drop column CannotFireBlasters PRINT ' Dropped old column CannotFireBlasters' end else begin PRINT ' Old column CannotFireBlasters does not exist' end GO set noexec off GO
Since the above sample, I’ve started adding dates and work item numbers into the script as well, to make things a bit more traceable. I figured as long as I had a sequence going, I’d append to the same script, though in an environment with multiple developers, you may want to partition more.
Why I didn’t..
- RedGate Sql Compare tools: I don’t own them.
- Sql Server Project in Visual Studio: When you actually get around to using them against real environments, there are a ton of problems around “Server” projects verses “Schema” projects (or some such) which are worse in 2012 than they used to be.
- EF Migrations: Haven’t spent the time to learn them, desire to be productive, sticking with what I already know and can do quickly.