Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Aug 30 '12 at 2:25

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers

You can purchase SQL Compare from Red-Gate.

share|improve this answer
    
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. –  Oded Aug 29 '12 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. –  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 2:13
add comment
up vote 1 down vote accepted

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!

share|improve this answer
    
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. –  zlatan Aug 30 '12 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 –  Head of Catering Sep 5 '12 at 15:36
    
@HeadofCatering i think that those answers speak for themselves. speed. backward compatibility. less expensive than the cost of ultimate/premium. –  zlatan Sep 5 '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 5 '12 at 15:54
    
The speed difference is probably not worth purchasing a separate product. Go with Schema Compare –  zlatan Sep 5 '12 at 16:01
add comment

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:
How to create copy of production SQL database?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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