Is RedGate SQL Source Control for me?
I've just inherited an SQL Server database. One of the things I will need to sort out is versioning and automated builds.
It has been suggested that I should think seriously about recommending RedGate SQL Compare, but I have to admit I am a little uneasy about it.
My reservations are...
- it appears to promote use of the gui tools for db work?
- for live apps, I prefer to work with change scripts, this avoids the last minute panic to create migration scripts at the end of each scrum cycle, and it means your update scripts can be tested by the CI. I can't see how the RedGate tool addresses this.
My gut instinct tells me to stick with the tried and tested approach of an MSBuild file and a stack of .SQL files.
I would be interested to hear if anyone has any experience on using this tool.
Answer
We use Red Gate to generate scripts for deployment and to control versioning.
"Deployment" and "versioning" are separate issues for SQL code.
Important to note: your production database is master with all it's data. So arrange of regular copies to a test server and use this as a baseline. A database generated by NUnit every night with basic data (seen it, had a laugh) is generally useless. What if you have a billion rows and need to test a query against it?
Versioning: You can use the Red Gate tools to generate a schema as a baseline and then compare this to this copy (or your QA or whatever). The Red Gate tools allows comparison to a folder, which is under SVN control in our case and is updated every release. So we have full history of every object
Deployment: we apply our development scripts (also in SVN) against a clean "build" DB and compare to another clean DB. This becomes our deployment script.
This is quite simplified of course.
The pro version offers an API to synch and compare so you can integrate into your tool chain if needed. No GUI needed. Incidently, we use this to provide a one click synch of some special user sandboxes complete with client code.
As Remus mentioned, they aren't foolproof for some operations. If you are changing stuff on 1.5TB tables, I'd lovingly handcode my script. Another irritation is that Red gate's tool has a habit of dropping SCHEMABINDING on a related view or udf for a simply check constraint change.
I also recommend reading Martin Fowler's "Evolutionary Database Design" for some inspiration