You can, I normally backup the publisher, fix the identity columns,
constraints and triggers and make them not for replication. I also make the
identity seed odd on one side with an increment of 2, and even on the other
with an increment of 2. Do the same on the other side only make the seed
even.

Once you have done this use dbcc checkident to reseed all even values on the
publisher to the next odd value and vice versa.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

Hi everyone,

Hope that you an help me with this one - I'm new to the whole
replciation business. I'm going to implement bi-directional
transactional replication on 2 servers that I have - I'll call them "A"
and "B" for the sake of simplicity.
The protocol that I am following is the one at the Microsoft site:

http://support.microsoft.com/default.aspx?scid=kb;en-us;820675

Server "A" is the primary and server "B" the warm stand-by backup.
There are 2 databases on A that I am interested in backing up.
My question is should I DTS over the databases from A to B before I
set up the bi-directional transactional replication?

Any comments/suggestions/user-experiences much appreciated.

Al.

Hilary,

Thanks a million for the advice but unfortunately I don't understand
what you have said. (I'm definitely going to buy your book!)
I get the bit about "not for replication" on the tables (thanks for
that - didn't realise that)but what is the identity seed that you are
referring to? Afraid I don't understand that stuff. Can you help me
please?

Fascinated and appreciative,
Al.

for a more complete explanation consult

http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/

However to be brief if you don't do this you will get pk collisions on each
side.

Consider this case you have an empty table with identity(1,1). You insert a
row on the publisher and one on the subscriber, both dist agents run, and
both will attempt to insert another row with a pk value of 1 on the other
side. This is a pk collision and your distribution agent will fail. To get
around this you have odd on one side and even on the other.

You need to run checkident to make all current values on the publisher odd,
and all current value on the subscriber as even as the increment will
otherwise not partition correctly.

Consider this case. You give your publisher an odd seed. The table already
has values in it, the last one 2. You back it up and restore it on the
subscriber, and give it an even seed.

The first row you insert in the publisher will have a value of 4, unless you
fix the current identity value to 3. Hence you blow your partitioning unless
you fix the seeds and the current identity value.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

Hi Hilary,


Thanks for the link to the article. I'm actually at the stage now
where I want to implement this approach on the Publisher. I'm trying to
do it programatically and I'm going to use the following commands. Let
me know if you think these commands will do the trick will you please
Hilary? I'm not entirely sure they will work. The table name is "A" for
the sake of simplicity:-

Firstly change the identity increment value to 2:

ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)

Then reseed all even values to the next odd value (not sure about the
next bit):

DBCC CHECKIDENT('A', RESEED, 2)


Comments/corrections/suggestions greatly, greatly appreciated.
Al.

Al, I can't get this to work, for example when I try the below:

create table al(pk int identity (1,1))
GO
alter table al
alter column pk identity(1,2)
GO

I get this message - does it work for you?
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'identity'.

The only way I have been able to do this is thru EM or SSMS.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

This is a great idea. I didn't think that SQL Server supported two identity
columns per table however.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

Hilary,

Got it figured - its a lot simpler than I thought. I create a new
column called "New" and set seed to 1 and increment to 2. Pressing save
caused SQL Server to populate "New" using the sequence 1, 3, 5, ...
I then delete the old identity column and rename "New" to the old
identity column name. Works like a cham - no coding necessary.

Thanks for all your help. I have read several of your articles online
and found them very useful. BTW, what is your new book on SQL Searches
coming out???

Al.