Solved

SQL server 2005 merge replication procedures

Posted on 2012-04-03
2
186 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
[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
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

717 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