Consolidating data changes in SQL Server by team

Question

What is a good way to manage developers' data changes?

I am using RedGate SQL Source Control to monitor changes to tables and stored procecures, so that when we do a production build, I can include all the changes in a SQL update script.

My question is: what is a good way to consolidate and monitor changes made to the data? (As this is something RedGate Source Control cannot track)

For example, new records included in lookup tables? And new records included in tables used for routing procedure calls. Thank you!

Answer 1

Red Gate has a product that does data comparing as well as schema comparing and will create scripts to merge content.

I personally use Idera's schema / data compare tools. I am very happy with them as well. I only pointed you to Red Gate as you already use their source control product.

I probably would have gone Red Gate myself as we use their backup tool but I was able to secure the Idera tools at a steal of a price and where I work budget is king.

Follow up:

You could also get to work writing your own compare script and/or merge statements. I do some ETL processes that make heavy use of the MERGE statement. I believe that single statement to be one of the greatest recent additions to the TSQL library.

Here's a walkthrough of how the MERGE statement works that I posted to a stackoverflow a few weeks ago.

Answer 2

We also use source control for managing schema changes. When it comes to deployment of new versions, I find it irreplaceable. It saves a lot of time and saves oneself from headaches

Red Gate is a good choice, though we went for ApexSQL Diff and ApexSQL Version

But none of these can track actual data changes. And data changes are not stored in source control

The tool that we use for monitoring data changes is ApexSQL Log, so we can see what data changed (records added, deleted or modified), who made a change and when. It also shows the schema changes - a table modified, a store procedure dropped, etc. Its main request is a database in Full recovery model, which requires regular backups and hard disk space - but on the other hand, you'll be ready if it comes to a disaster recovery ;)