Solved

Merge replication-right approach for my situation?

Posted on 2012-03-23
6
342 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:
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
>> 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:IntercareSupport
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
Thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now