Solved

Merge replication-right approach for my situation?

Posted on 2012-03-23
6
348 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

623 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