Solved

Merge replication-right approach for my situation?

Posted on 2012-03-23
6
346 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

734 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