How to do SQL Source Control with TFS and WinMerge when you don'town "SQL Source Control"

-
I am a huge huge fan of Red Gate's SQL Source Control and use it every day at work. On my side projects though I can't necessarily justify the $400 price tag (yet). So in the mean time, I am using the following somewhat painful process to source control my scripts against TFS. (Microsoft now has a hosted "Team Foundation Service" where you can host your code for free.)

The current solution uses Visual Studio 2010, SQL Server 2008 R2 Express Management Studio, and WinMerge.

Here's the process:

Initial Load:

  1. In your TFS working folder, add a "DB" folder. For me this sits next to my Visual Studio Solution folder.

  2. Add the DB folder to Source Control.

  3. In SSMS, right-click on your database, choose Tasks > Generate Scripts and script out the entire database into your new DB folder. I create a file for each object.

  4. In Source Control, check all that in. You now have your database under version control, kinda.

  5. Add an additional sibling folder called DBCompare. Do NOT add this to Source Control.


Subsequent Check-In's:

  1. Delete contents of DBCompare if there are any.

  2. Re-script your database into DBCompare.

  3. Check out the entire DB folder from Source Control.

  4. Run a WinMerge compare between DBCompare and DB, ignoring DATE to see what REALLY changed. (Just looking at Date, it appears everything changed.)

  5. Use WinMerge to copy the changed files from DBCompare to DB.

  6. Check in the entire DB folder back into Source Control. TFS will only record "new history" on the files that changed.


There are a LOT of ways in which this process is inferior to just using SQL Source Control. Primarily there's no easy way to see what you're checking until after the fact (where you can look at the DB check-in history) b/c the WinMerge tool's file compare isn't working very well for me on SQL files. But this does at least accomplish the main goal of being able to see in TFS what changed over time.

Comments

  1. Will SJune 5, 2013 at 1:55 AM

    Update: Tip: If you'll use "ANSI" instead of "Unicode" during the Script generation, then the WinMerge compare will work better. (I'm sure WinMerge can compare Unicode files but I haven't cracked that nut yet, the results weren't intelligible.)

    ReplyDelete

Post a Comment

Popular posts from this blog

SQL 2005 SP4 and MSreplication_subscriptions

-
If you're trying to apply SQL 2005 SP4 (or any SQL service pack), and it's failing, and you're seeing error "MSP Error:  29537  SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Invalid object name 'MSreplication_subscriptions'", then you've hit this bug which Microsoft is currently saying they "Won't Fix": https://connect.microsoft.com/SQLServer/feedback/details/521231/failure-during-server-script-upgrade-process-on-database-in-sp-vupgrade-replication-procedure#tabs Probably you've done one of 2 things: You're using synonyms that match the system replication table names You've got some vestige replication tables laying around in one of your old databases My case was the latter. The fix was to find the vestige tables and get rid of them. I found them using the following script: [sourcecode language="sql"] /* This will detect db's containing vestiges of Replication
Read more

Hiding an ASPXGridView Delete button with HTMLRowCreated vs. CommandButtonInitialize

-
It's pretty typical in Administration forms to need to hide buttons on certain rows for various reasons. Particularly it is nice to hide the Delete button if in fact the row in question is being used in other places in the app. This is a nice feature to let the user know up front that the row in question is in use, rather than them trying to delete it and getting a foreign key related error after the fact. For years now in DevExpress ASPxGridView's I have been handling this in the HtmlRowCreated event. And I have been doing it wrong. Before: protected void gridGiftCodes_HtmlRowCreated ( object sender, DevExpress.Web.ASPxGridViewTableRowEventArgs e) { if (e.RowType == GridViewRowType.Data){ int commandColumnIndex = - 1 ; //Find the command column commandColumnIndex = e.Row.Cells.Count- 1 ; //assume it's the last visible colum //Get the command cell for this row GridViewTableCommandCell cell = e.Row.Cells[commandColumnIndex] as Gri
Read more

SQL Server Deadlocks - Easy Quick Start Guide

-
If you find the topic of SQL Server deadlocks a little intimidating and mysterious, you are not alone. But fear not, this Quick Start lays out the practical shortcut I bet is sufficient for most scenarios. Definition: What is a Deadlock A deadlock occurs when 2 threads are competing for similar resources, and Thread 1 HAS A and WANTS B, and Thread 2 HAS B and WANTS A. They’re stuck. SQL Server picks a loser and throws error 1205 "Transaction... has been chosen as the deadlock victim." The BIG PROBLEM if your application has a lot of deadlocks is unpredictable application behavior . This can especially show up in apps that call large stored procs where the failing statement gets rolled back, but the proc itself may just keep on trucking with the next statement. This is all based on what kind of error handling you're doing, whether you have XACT_ABORT on, whether you're doing explicit transactions with BEGIN TRAN or not, etc. Very Probably your application is no
Read more