CODEWITHSUNDEEP
sql-serversql-server-2008replication
Seph
Seph

Reputation: 8703

SQL server bi-directional replication and auto-increment primary key

I have read here that a simple way to manage auto-increment primary keys with (bi-directional) replication between two instances, is to have one server always be even numbers and the other number always odd.

I understand that will cause problems if you have a clustered index on the primary key, epically if one server is used significantly more than the other.

Is this correct? How can I prevent the table from getting too fragmented if server A is always used much more than server B?

Can I use index padding to pad out my PK index to make sure that the table doesn't fragment but just takes up more disk space? What percentage should I be using? 50%?

Upvotes: 2

Views: 3278

Answers (2)

TomTom
TomTom

Reputation: 62127

I have read that the best practice to manage auto-increment primary keys with (bi-directional) replication between two instances, is to have one server always be even numbers and the other number always odd.

In this case that's not really the best way to do it because it requires manual intervention. You probably aren't aware that SQL Server has automatic identity range management for transactional publication (it really can all just be done automatically).

Here's a few nice articles to get you started:

Surprisingly the documentation on this topic actually is very useful and complete - which is why there's not too many other sources talking about it.

Upvotes: 1

Brandon Williams
Brandon Williams

Reputation: 3755

This can be done in SQL Server by utilizing Automatic Identity Range Management in which you specify range sizes for Publisher and Subscribers and replication automatically manages the assignment of new ranges when they've become exhausted.

Have a look at Replicating Identity Columns.

Upvotes: 4

Related Questions