3

I need to sync table structures, stored procedures, triggers, data, etc. between environments. There are dev, QA, and production environments. I am in a Microsoft-centric environment.

What tools within SSMS and/or Visual Studio can sync SQL Server machines/databases? Thanks.

0

3 Answers 3

2

You can purchase SQL Compare from Red-Gate.

4
  • SQL Compare looks like a great option. Unfortunately, my client doesn't want to make the investment.
    – Tarzan
    Aug 29, 2012 at 15:06
  • 1
    @Tarzan - Your client should consider how much more they will pay by getting this done bespoke... Programmer hours are not cheap.
    – Oded
    Aug 29, 2012 at 15:08
  • 3
    @Tarzan also have your client read bertrandaaron.wordpress.com/2012/04/20/… - cutting a check is quite often the cheaper option than engineering it yourself. Aug 29, 2012 at 15:57
  • @AaronBertrand, that is a good point. However, corporations frequently have budgets that can't be used for other things. If you want to purchase software, then you have to get approvals from other execs and other complications arise. It is not always logical from a developer's perspective.
    – Tarzan
    Aug 30, 2012 at 2:13
2

I found a Schema Compare utility in Visual Studio. It is included with the premium, ultimate, and perhaps other editions of Visual Studio.

To use it:

  1. Open Visual Studio
  2. Click "Data" on the menu bar
  3. Click "Schema Compare"

This utility is awesome. It is like WinMerge or BeyondCompare but optimized for SQL Server database objects. This should definitely be better known within the DBA and developer communities. I've worked with MS development tools since 1993 and just learned about it today. Cheers!

5
  • I'm quit familiar with it, and its limitations. It's available only in premium and ultimate which carry premium prices too So are you going to wire up a plugin for VS to actually sync things or just use this to analyze the differences.
    – swasheck
    Aug 30, 2012 at 2:56
  • @swasheck, what are its limitations compared to SQL Compare? Several posts indicate functionality is essentially the same: dba.stackexchange.com/q/2036/2848 and stackoverflow.com/a/685073/138938 Sep 5, 2012 at 15:36
  • @HeadofCatering i think that those answers speak for themselves. speed. backward compatibility. less expensive than the cost of ultimate/premium.
    – swasheck
    Sep 5, 2012 at 15:52
  • Good deal. I'm not working with SQL Server 2000 and already own VS Ultimate, so I'll go with it for now. Is the speed difference significant? Sep 5, 2012 at 15:54
  • The speed difference is probably not worth purchasing a separate product. Go with Schema Compare
    – swasheck
    Sep 5, 2012 at 16:01
0

Very cheap/simple solution:

If the sync is one way only (for example, only from production to dev/QA), then how about just making full backups of your database and restoring them on the dev/QA servers?

At work, we do all our development in a dev database that's essentially a restored backup of the production database from yesterday evening.

Here's how we set this up:
https://stackoverflow.com/questions/7229799/how-to-create-copy-of-production-sql-database/7229959#7229959

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.