Link to home
Start Free TrialLog in
Avatar of JnavarroMc
JnavarroMcFlag for Peru

asked on

SQL Transactional Replication Scenario / Bi-directional

Hi,

I'm migrating a Database Server that has a Transactional Replication configured. In some tables the replication is bi-directional. From the remote site the transactions come to the main site that works as a central repository and is distributed to the other sites.

I have verified that in order to be able to establish replication I need to have the Databases of Origin and Destination without information.

Is it possible to establish replication without having to empty the tables?

SQL Versions: SQL 2014 Publisher, SQL 2014 Distributor, SQL 2008 Subscriber - Main office to remote office.
SQL Versions: SQL 2008 Publisher, SQL 2008 Distributor, SQL 2014 Subscriber - Remote office to main office.
Primer-Diagrama.jpg
Avatar of lcohan
lcohan
Flag of Canada image

"I'm migrating a Database Server that has a Transaction Replication configured." - is this a Publisher OR as Subscriber? That is the most important thing that will determine the answer to your question "Is it possible to establish replication without having to empty the tables?" and IF that server is a Publisher then obviously the answer will be - you don't even want to empty those tables right? as this IS the source for all the subscribers.
If the server to be migrated is a Subscriber then when the Transactional Replication needs to be re-established - during the initial snapshot the will be all emptied as you noted.

Alternatively...if your migration is done via backup/restore you could reinitialize a subscription using a SQL Server database backup as described here: https://www.sqlshack.com/sql-server-transactional-replication-how-to-reinitialize-a-subscription-using-a-sql-server-database-backup/
>> in order to be able to establish replication I need to have the Databases of Origin and Destination without information.

not necessarily..

>> Is it possible to establish replication without having to empty the tables?
>> Database Server that has a Transactional Replication configured. In some tables the replication is bi-directional.

Kindly clarify whether you have just Transactional Replication or Transactional Replication with Updatable Subscriptions or Merge Replication so that we can share the steps accordingly..
Avatar of JnavarroMc

ASKER

The type of replication is Transactional Replication. The scenario is complex because there are 8 different Remote Sites and 1 Central Repository.

We currently have SQL 2008 R2. We are planning to migrate to SQL 2014 is for this reason that I must find a type of migration that does not have downtime.

The central repository database acts as publisher and subscriber. The databases of the remote sites also current of publisher and subscriber.

There is no way to perform the migration without having to re-synchronize the tables? As I have noticed in different exercises the only way to do it is to leave the tables without data.
>> As I have noticed in different exercises the only way to do it is to leave the tables without data.

Simple answer would be yes, we can configure Transactional Replication without truncating tables provided that we ensure that the data in both Publisher and Subscriber are the same before adding the table article to Replication.

>> The central repository database acts as publisher and subscriber. The databases of the remote sites also current of publisher and subscriber.

Can you kindly clarify What is the original source for all these databases as I can see both Central Repository act as Publisher and Subscriber..
Kindly confirm whether it is in a circular loop from Central Repository -> 8 Remote Sites -> Central Repository..
I talked to the client and the schemes between the Remote Sites Database and the central repository are different.

Is it possible to restore the Database with Transactional Replication to another version of SQL? (SQL 2008 to SQL 2014).

The published database is the only one to restore because I will configure the distribution on a SQL 2014 server.
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial