Solved

Merge replication-right approach for my situation?

Posted on 2012-03-23
6
343 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 69

Accepted Solution

by:
Scott Pletcher earned 500 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 69

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 69

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL - How to declare table name 26 30
SQL - Update field defined as Text 6 17
sql 2008 how to table join 2 16
SQL Query Help Top 1 and Distinct? 6 26
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

778 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