Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Merge replication-right approach for my situation?

Posted on 2012-03-23
6
Medium Priority
?
357 Views
Last Modified: 2012-04-06
I will soon have a VM clone of one of my production servers that I will be using for development before deployment to production.  The problem is that these 2 servers will be in different datacenters with slow links between them, and I'm going to occasionally need to refresh the data on the dev server with new prod data.

My thoughts are that I could probably do this easier if I could only import changes made to the prod db since the last synchronization, but this could be weeks or months between these events.  Would merge replication be the best way to do this?  The prod db is a fairly busy database.  Would weeks of changes be intesive to synchronize?  Affect the performance on the db?  Is there a better way to do this?
0
Comment
Question by:IntercareSupport
  • 3
  • 3
6 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37758543
IMO, replication would not be a good choice in this situation.

For that long a period of time, just take a full db backup, compress it, copy and restore it.

If that takes too long, then if possible schedule it every <n> weeks, to start, say, early Sat, for some/all databases (set whatever schedule you need for each db).  

Then when everyone comes in Monday, the dev dbs are in sync with Fri's prod dbs.
0
 

Author Comment

by:IntercareSupport
ID: 37758960
Thanks.  That may be the way I need to go, but since these files are so big I'd really like someway of sending only the changes.  Would the overhead of tracking the changes for this long affect the production system much?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37759043
>> Would the overhead of tracking the changes for this long affect the production system much? <<

Depends on how it's implemented.  

If the source is 2008, Change Data Capture (CDC) would be your best bet.  When you need to re-sync, you could use the CDC function and it would generate only the final changes with only the last active data.  Thus, it would do all the work of giving you *just* the data that needs pushed over.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:IntercareSupport
ID: 37759450
I can't believe I never heard of this.  Very cool stuff.  Thanks.

The more I read, though, the more problematic it seems for my situation.  You have to set up cdc on any tables you need to track.  Well, my main db has over 5000 tables and this would mean that I need to enable this feature on all my tables.  And how to apply these changes back to the development system does not look trivial.  Am I not getting the right picture about this feature?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37765980
Hmm, If it's that many tables, you probably want to look into a commercial, 3rd party compare-and-sync product, like the one from RedGate SQL Compare & Data Compare (there are *many* other products, just do a web search).
0
 

Author Closing Comment

by:IntercareSupport
ID: 37816559
Thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question