Can MS SQL Server do bidirectional, transactional replication using an Identity column?

I have been struggling for several days trying to setup bidirectional,
transactional replication by Publishing the same table on each server with
NOT FOR REPLICATION and an even/odd partitioning of the IDENTITY columns:
CREATE TABLE test (
col1 INTEGER IDENTITY( 1, 2 ) NOT FOR REPLICATION NOT NULL PRIMARY
KEY,
col2 CHAR(10) );
CREATE TABLE test (
col1 INTEGER IDENTITY( 2, 2 ) NOT FOR REPLICATION NOT NULL PRIMARY
KEY,
col2 CHAR(10) );
I used the Articles/Snapshot Keep the existing table unchanged option and
choose not perform to perform a snapshot automatically to preserve the
schema. Unidirectional replication from the first server to the second
worked perfectly.
Most recently, when using SQL commands instead of Stored Procedures and with
the "Use column names in commands that are not replaced by stored
procedures", I encountered:
Violation of PRIMARY KEY constraint 'PK__test__3D5E1FD2'. Cannot insert
duplicate key in object 'test'.
IDENTITY column values seem to be generated properly on each server, but
when I setup the second subscription I encountered the error. Apparently
when I inserted a row in the second server, it propagated to the first
server which may have tried to send it back to the second server?
I am not interested in new GUIID columns being introduced into our schema,
nor can we tolerate Two Phase Commit as the connection to the servers must
be asynchronous. Primary keys will never change. Inserted rows at each
site will get their own even or odd key values.
Is MS SQL Server up to the task or should I use our own trigger-based,
asynchronous replication solution?
Thanks, Matt
================================================== ========================
Matthew J. Ramuta Enterprise Information Solutions, Inc.
Txt: 6306973359@.mobile.att.net 4910 Main Street, Downers Grove, IL 60515
Off: 630-512-0570 Fax: 630-512-0568 Cell: 630-697-3359
================================================== ========================
Yes it is entirely possible.
Are you doing this through the wizards, because the wizards don't support
bi-directional transactional replication.
What you should do is script out what you have and then edit the script and
change the sp_addsubscription proc to also have a parameter saying
@.loopback_detection='true'
Do this for both sides.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Matthew J. Ramuta" <mattr@.eisolution.com> wrote in message
news:quqUc.3719$Y94.920@.newssvr33.news.prodigy.com ...
> I have been struggling for several days trying to setup bidirectional,
> transactional replication by Publishing the same table on each server with
> NOT FOR REPLICATION and an even/odd partitioning of the IDENTITY columns:
> CREATE TABLE test (
> col1 INTEGER IDENTITY( 1, 2 ) NOT FOR REPLICATION NOT NULL PRIMARY
> KEY,
> col2 CHAR(10) );
> CREATE TABLE test (
> col1 INTEGER IDENTITY( 2, 2 ) NOT FOR REPLICATION NOT NULL PRIMARY
> KEY,
> col2 CHAR(10) );
> I used the Articles/Snapshot Keep the existing table unchanged option and
> choose not perform to perform a snapshot automatically to preserve the
> schema. Unidirectional replication from the first server to the second
> worked perfectly.
> Most recently, when using SQL commands instead of Stored Procedures and
with
> the "Use column names in commands that are not replaced by stored
> procedures", I encountered:
> Violation of PRIMARY KEY constraint 'PK__test__3D5E1FD2'. Cannot
insert
> duplicate key in object 'test'.
> IDENTITY column values seem to be generated properly on each server, but
> when I setup the second subscription I encountered the error. Apparently
> when I inserted a row in the second server, it propagated to the first
> server which may have tried to send it back to the second server?
> I am not interested in new GUIID columns being introduced into our schema,
> nor can we tolerate Two Phase Commit as the connection to the servers must
> be asynchronous. Primary keys will never change. Inserted rows at each
> site will get their own even or odd key values.
> Is MS SQL Server up to the task or should I use our own trigger-based,
> asynchronous replication solution?
> Thanks, Matt
> ================================================== ========================
> Matthew J. Ramuta Enterprise Information Solutions, Inc.
> Txt: 6306973359@.mobile.att.net 4910 Main Street, Downers Grove, IL 60515
> Off: 630-512-0570 Fax: 630-512-0568 Cell: 630-697-3359
> ================================================== ========================
>