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
> ================================================== ========================
>

Can I prevent customers from viewing schema?

Hi all,
I'm struggling with the pros and cons of developing a new desktop app using
Jet or MSDE/SQL Server. If I use MSDE/SQL Server, my installer would need
to be able to either install MSDE (if it's not installed already) or locate
an existing SQL Server in which to create my database.
One thing that I would like to accomplish is a little protection of my
intellectual property. Specifically, I'd like to prevent customers from
looking at my database schema. Is this possible (or practical) in a
production SQL Server environment? If so, what is involved?
TIA!
- BobBob,
The schema definitions in a database are avialable to public, which means
that anyone can read them. Trying to subvert this would probably cause you
some real problems.
For the future: http://tinyurl.com/th9k "An Overview of SQL Server "Yukon"
for the Database Developer" makes the following interesting comment:
SQL Server "Yukon" ships with a new security model that separates users from
objects, provides fine-grain access and provides greater control of data
access. Additionally, all System tables are now implemented as Views,
providing greater control over database system objects.
Wait and see on that one, I guess.
Russell Fields
"Bob Altman" <rda@.nospam.com> wrote in message
news:ekz#SlWTEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I'm struggling with the pros and cons of developing a new desktop app
using
> Jet or MSDE/SQL Server. If I use MSDE/SQL Server, my installer would need
> to be able to either install MSDE (if it's not installed already) or
locate
> an existing SQL Server in which to create my database.
> One thing that I would like to accomplish is a little protection of my
> intellectual property. Specifically, I'd like to prevent customers from
> looking at my database schema. Is this possible (or practical) in a
> production SQL Server environment? If so, what is involved?
> TIA!
> - Bob
>|||Thanks Russell.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:e47QyGZTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> Bob,
> The schema definitions in a database are avialable to public, which means
> that anyone can read them. Trying to subvert this would probably cause
you
> some real problems.
> For the future: http://tinyurl.com/th9k "An Overview of SQL Server "Yukon"
> for the Database Developer" makes the following interesting comment:
> SQL Server "Yukon" ships with a new security model that separates users
from
> objects, provides fine-grain access and provides greater control of data
> access. Additionally, all System tables are now implemented as Views,
> providing greater control over database system objects.
> Wait and see on that one, I guess.
> Russell Fields
> "Bob Altman" <rda@.nospam.com> wrote in message
> news:ekz#SlWTEHA.384@.TK2MSFTNGP10.phx.gbl...
> using
need[vbcol=seagreen]
> locate
>