Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL server 2005 merge replication procedures

Posted on 2012-04-03
2
Medium Priority
?
194 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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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 …
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

650 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