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 have two databases, prod and dev. Each DB is about 500GB. At the end of each work day, the prod database gets several million new rows and several thousands of updates. After the data is loaded in prod, I would like to refresh dev database from production. Both servers are SQL Server 2012.

Ideally I need something lightweight, easy to setup and maintain. The synch process needs to have a minimal performance impact on the prod server. Open to 3rd party solutions.

What solution would you recommend: transactional replication, change tracking, change data capture, or something else entirely?

Thanks for your help.

PS. Would using Change Tracking + Sync Framework be an suitable alternative?

share|improve this question

migrated from stackoverflow.com Dec 12 '12 at 14:46

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

    
What is the database's recovery model? –  Jon Seigel Dec 12 '12 at 15:57
    
simple, but I can change that full, if needed –  user1044169 Dec 12 '12 at 16:03
    
Okay. When you say "refresh dev from production" do you mean merge changes, or do you mean replace dev with what is currently in production? Which edition of SQL Server are you using for both instances? –  Jon Seigel Dec 12 '12 at 18:18
add comment

4 Answers 4

Use RedGate Sql Data Compare to sync Sql Server databases easily

share|improve this answer
add comment

Sync Framework - Appears to be geared more towards cloud environments, and would likely be more complex than necessary for your needs.

Transactional Replication should be the simplest to setup and manage and will keep your Dev database nearly up-to-date (actual latency depends on your transaction rate and network latency, etc). I would not necessarily look at 3rd party applications, unless you find the MS provided tools unacceptable. But for a basic A -> B replication, they should have you covered.

share|improve this answer
    
As I understand, transactional replication uses distributed transactions to update subscribers. Does it mean that an issue with trx replication (dev DB is tied up, internal network is down, etc) will affect the original production update? I had some experience with trx replication in SQL 2000, and recall that often times we had to do a full resynch of subscribers when replication failed. Not sure if it's still the case with SQL 2012. Thanks. –  user1044169 Dec 12 '12 at 17:36
    
I deal primarily with Sybase Replication Server, so I'm not as familiar with the nitty gritty of how SQL Server manages transactional replication. In Sybase, the default is for the Prod to send the transaction off, and trust that it reaches it's destination, regardless of what happens, the Production server continues chugging along even if the transactions fail on the Replicate. The behavior you are talking about sounds like what Sybase calls two-phase commit, which is used to guarantee consistency between databases. Changes will not happen at Prod, if they can't happen at the Replicate. –  Michael Gardner Dec 12 '12 at 17:45
add comment

You can also use ApexSQL Data Diff. It has command line, so you can schedule the synchronization I've used both RG and ApexSQL. ApexSQL has only 1 version (not basic and Pro, like RG), so the fetures that are available only in RG SQL Data Compare Pro version available in ApexSQL 'basic' version. And ApexSQL is cheaper

share|improve this answer
add comment

The problem with Red Gate's SQL Data Compare, Sync Framework, and ApexSQL Data Diff is that they all either require extra licensing and/or development time.

The most lightweight, easiest to setup and maintain synchronization solution that meets your needs of a daily refresh is Snapshot Replication. Snapshot Replication will have less of a performance impact on the production database than Transactional Replication as it does not require a Log Reader Agent agent.

However, all of this might be overkill. A backup and restore solution might better suit your needs.

share|improve this answer
    
The problem is that the database is 500GBs. I am not sure it's practical to do a snapshot with this much data. Backup and restore is tricky since in dev there may be new changes that are not in prod yet. –  user1044169 Dec 12 '12 at 17:32
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.