Migration script without a commit?

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Migration script without a commit?

Postby bdw429s » Thu Aug 09, 2012 5:49 pm

I have searched the forums and was unable to find this question being asked anywhere, but please feel free to redirect me to an existing thread if it exists.

We have been using SSC for several months now, but with manual migrations. We are just now hooking into our automation server (Jenkins) to automate push-button SQL migrations along side our already-working push-button web-code migrations.

My question is what to do with the handful of DB scripts I've always been manually running that don't seem to fit inside of SSC. Most of these are some sort of back-fill or migration of content in a non-static table. (Our site is a CMS, and while most content is managed directly on production, some content that is closely tied to new development is entered on the dev environment and migrated along side it's dependent code)

The issue with these sort of scripts is that many of them don't correspond with a specific DB commit per se but still are required for the rest of the application code changes on the ticket to work. A made-up example would be a backfill script to lower the price of all products by $10 in conjunction with new discounting logic in the shopping cart.

Is it possible to introduce a migration script into SSC, without having a prior DB commit to piggy back it on? Our goal is that a member of the QA team can click a button in the automation server that, without any programmer intervention, will wipe out the database with a recent backup of production and deploy ALL changes up to the HEAD revision of the VCS.

One of the other developers has suggested also using DBDeploy or Flyaway in conjunction with SSC to handle a "scripts" folder of other stuff that has to be run during a deployment, but I'd like to keep to a single DB versioning/migration tool.

Thanks in advance for your suggestions.

~Brad
bdw429s
 
Posts: 20
Joined: Thu Aug 09, 2012 4:05 pm
Location: Kansas

Found duplicate question

Postby bdw429s » Fri Aug 10, 2012 4:47 pm

Aha, after some more searching, I found another question that was essentially the same as mine.

http://www.red-gate.com/MessageBoard/viewtopic.php?t=15605

Unfortunately, it appears the answer is that migration scripts outside of schema or data changes is not currently supported. This is a blow to our automation attempts. Is there a ticketing system where I can submit this as an enhancement request and have my team members vote on it?

Thanks!

~Brad
bdw429s
 
Posts: 20
Joined: Thu Aug 09, 2012 4:05 pm
Location: Kansas

Postby maturmel » Mon Aug 13, 2012 3:28 pm

Consider this idea: create a 'dummy' table where you will add a new column to it for each migration script you wish to 'attach' to you DB. The dummy table itself would never contain any data. It would be a way for you to integrate your scripts into SSC and therefore benefit from all your automation setups... Of course a dummy table will not please people who tend to keep things ultra clean, but it's tradeoff that seems would offer big wins in terms of automation in this situation...

At least until SSC supports adding straight migration scripts as a whole feature... which, from the link you provided, would come eventually.

Marc-Andre
maturmel
 
Posts: 24
Joined: Tue Jan 25, 2011 9:16 pm

Postby bdw429s » Mon Aug 13, 2012 4:31 pm

Thanks for the suggestion. I had actually considered something similar myself-- like adding whitespace to a stored procedure or something. Unfortunately, I am the guy who usually likes to keep things ultra-clean :)

I would like to get ahold of an ETA, or targeted version number just so I knew what kind of priority this enhancement is. I would gladly vote on it (and have my team vote as well), but I'm not sure if RedGate maintains a public bug-base.
bdw429s
 
Posts: 20
Joined: Thu Aug 09, 2012 4:05 pm
Location: Kansas

Postby bdw429s » Wed Aug 15, 2012 11:06 pm

So, can anyone from RedGate let me know if there is a ticket number I can vote on for this, or an ETA?

Thanks!

~Brad
bdw429s
 
Posts: 20
Joined: Thu Aug 09, 2012 4:05 pm
Location: Kansas

Postby bdw429s » Thu Aug 16, 2012 5:50 pm

For reference, I found this User Voice request and voted on it. If you find this thread and agree, please also go vote :)

http://redgate.uservoice.com/forums/390 ... to-any-dat
bdw429s
 
Posts: 20
Joined: Thu Aug 09, 2012 4:05 pm
Location: Kansas


Return to SQL Source Control 3

Who is online

Users browsing this forum: Yahoo [Bot] and 0 guests