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.

Tarzan
  • 529
  • 1
  • 5
  • 16

3 Answers3

2

You can purchase SQL Compare from Red-Gate.

Oded
  • 334
  • 1
  • 7
  • SQL Compare looks like a great option. Unfortunately, my client doesn't want to make the investment. – Tarzan Aug 29 '12 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. –  Aug 29 '12 at 15:08
  • 3
    @Tarzan also have your client read http://bertrandaaron.wordpress.com/2012/04/20/re-blog-the-cost-of-reinventing-the-wheel/ - cutting a check is quite often the cheaper option than engineering it yourself. – Aaron Bertrand Aug 29 '12 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 '12 at 02: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!

Tarzan
  • 529
  • 1
  • 5
  • 16
  • 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 '12 at 02:56
  • @swasheck, what are its limitations compared to SQL Compare? Several posts indicate functionality is essentially the same: http://dba.stackexchange.com/q/2036/2848 and http://stackoverflow.com/a/685073/138938 – Head of Catering Sep 05 '12 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 05 '12 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? – Head of Catering Sep 05 '12 at 15:54
  • The speed difference is probably not worth purchasing a separate product. Go with Schema Compare – swasheck Sep 05 '12 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

Community
  • 1
Christian Specht
  • 355
  • 5
  • 13