August 25, 2009 at 9:10 am
Hi,
I am attempting to setup merge replication using SQL Server 2008. I would like to know how to configure an article (table) to only replicate in a specifc direction. Here is my sample databases:
/********** R1 **********/
USE master
GO
CREATE DATABASE R1;
GO
USE R1
GO
CREATE TABLE T1
(
IDINT IDENTITY(1, 1) NOT NULL PRIMARY KEY
,[rowguid]UNIQUEIDENTIFIER ROWGUIDCOL CONSTRAINT DF_T1_rowguid DEFAULT(NEWID())
,ValueVARCHAR(5) NOT NULL
);
CREATE TABLE T2
(
IDINT IDENTITY(1, 1) NOT NULL PRIMARY KEY
,[rowguid]UNIQUEIDENTIFIER ROWGUIDCOL CONSTRAINT DF_T2_rowguid DEFAULT(NEWID())
,ID_T2INT NOT NULL CONSTRAINT FK_T2_T1 REFERENCES T1 (ID)
,ValueVARCHAR(5) NOT NULL
);
/********** R2 **********/
USE master
GO
CREATE DATABASE R2;
GO
USE R2
GO
CREATE TABLE T1
(
IDINT IDENTITY(1000, 1) NOT NULL PRIMARY KEY
,[rowguid]UNIQUEIDENTIFIER ROWGUIDCOL CONSTRAINT DF_T1_rowguid DEFAULT(NEWID())
,ValueVARCHAR(5) NOT NULL
);
CREATE TABLE T2
(
IDINT IDENTITY(1000, 1) NOT NULL PRIMARY KEY
,[rowguid]UNIQUEIDENTIFIER ROWGUIDCOL CONSTRAINT DF_T2_rowguid DEFAULT(NEWID())
,ID_T2INT NOT NULL CONSTRAINT FK_T2_T1 REFERENCES T1 (ID)
,ValueVARCHAR(5) NOT NULL
);
/********** R3 **********/
USE master
GO
CREATE DATABASE R3;
GO
USE R3
GO
CREATE TABLE T1
(
IDINT IDENTITY(2000, 1) NOT NULL PRIMARY KEY
,[rowguid]UNIQUEIDENTIFIER ROWGUIDCOL CONSTRAINT DF_T1_rowguid DEFAULT(NEWID())
,ValueVARCHAR(5) NOT NULL
);
CREATE TABLE T2
(
IDINT IDENTITY(2000, 1) NOT NULL PRIMARY KEY
,[rowguid]UNIQUEIDENTIFIER ROWGUIDCOL CONSTRAINT DF_T2_rowguid DEFAULT(NEWID())
,ID_T2INT NOT NULL CONSTRAINT FK_T2_T1 REFERENCES T1 (ID)
,ValueVARCHAR(5) NOT NULL
);
R1: Primary
R2/R2: Secondary
Please note that all identity columns will be managed manually..
Here is my scenario that I would like to fullfill (Item 2 more specifically):
1. Replicate all T1's content from Primary-to-Secondary and Secondary-to-Primary. So this is normal bi-directional merge replication, I have already configured a publication to handle this scenario.
2. Replicate all T2's content from Secondary-to-Primary, not from Primary-to-Secondary. So, all content in T2 is only in the database where it was created and the primary database. Data in T2 can only be modified on the secondary databases, and will never be modified at the Primary. This is maintained by the existing application.
I have read that in SQL 2005 had an -EXCHANGETYPE flag that one could set, but haven't been able to find more information on this for 2008. Do I need to set this flag for each subscription or only the publication? How would I go about setting this scenario up?
If someone would please assits me I would greatly appreciate it.
How would I go about
August 25, 2009 at 9:23 pm
In my limited experience with replication, wouldn't it make sense to set up a separate publication that is only one-way transactional and have T2 set up that way.
In the end, you would have 1 merge publication for T1 (two-way updates), and 1 transactional publication for T2 (one way updates) on your secondary DB, and subscribe from your primary.
I apologize if that's not exactly what you are looking for, but should get you set up the way you need.
Hope that helps,
Steve
August 26, 2009 at 1:07 am
Hi Sk,
Thanks for the feedback. I thoguh of this but unfortunatly some of the server will be express addition which can only be a subscriber. I did however get this working:
One publisher for bi-directional merge replication and another publisher for one way replication using the "-EXCHANGETYPE 1" flag on each of these subscribers.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply