Solved

SQL server 2005 merge replication procedures

Posted on 2012-04-03
2
176 Views
Last Modified: 2012-04-05
I have a database on an SQL server 2005 named SERVER1 which has approximately 70 tables in a database DB1 that need replicating to another database server SERVER2 which has no database on it yet.
I am considering one of two ways of proceeding to set up a merge replication to the SQL Server 2005 database server SERVER2.

Scenario A:
Set up a SERVER1 as a publisher to publish the 70 tables in DB1
Create a blank database DB1 on SERVER2
Add SERVER2 as a subscriber to SERVER1 publication and proceed with synchronisation.

Scenario B:
Set up a SERVER1 as a publisher to publish the 70 tables in DB1 and create a backup to disk.
Restore the backup to SERVER2 thus creating a DB1 on SERVER2
Remove all replication on SERVER2 DB1
Add SERVER2 as a subscriber to SERVER1 publication and proceed with synchronisation.

I am stating that scenario A is better than scenario B, am I correct, and if so, how can i justify my decision ?
0
Comment
Question by:alcindor
2 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 37803953
There's not a lot of difference between the two unless there are tables in db1 on server 1 that for some reason you are not replicating in that case scenario b is better as you will get all of the tables not just the ones that are being replicated.

With scenario a if you are replicating all the tables in db1 it will work fine, however if there are some tables that aren't being replicated these will not be created on server 2 when synchronisation happen, so if you need the non-replicated tables on server 2 as well then scenario a is mot the best way to go.

In summary if you let the replication synchronisation process copy over the database schema it will only copy over the tables that are being replicated.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37804423
It really depends on the size.  If the database is small go with option A, if not option B.  Notice how I have been particularly vague and that is because I do not know your SLAs
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

17 Experts available now in Live!

Get 1:1 Help Now