Manage and Monitor Identity Ranges in SQL Server Transactional Replication

Points to consider and comments

First
Method

  • Allows
    for more than 9 servers to be managed at the same replicated environment.
  • The
    identity ranges have to be selected carefully to assure the max value of the
    range is never reached.
  • It
    is suggested to monitor the identity values so you know a head if any table is
    about to reach the max range value
  • Defining
    the ranges requires knowing the activity on site and taking into account future
    growth and you may need to modify int data type columns to bigint to allow for
    ranges to exceed the 2^32 figure supported by the int data type.
  • Every
    new table that has an identity column added to the replication has to be seeded
    (a one time event) to match the range for the given server using the DBCC
    CHECKIDENT command (see the accompanied scripts). A failure to do so results in
    a PRIMARY KEY Violation error until the situation is corrected.
  • Requires
    more maintenance / management and is prone to more errors.

Second
Method

  • Limited  for up to 9 servers to be managed at the same replicated
    environment.
  • Every
    new table that has an identity column added to the replication has to be
    created using a slightly different command because of the identity seed and
    increment values and this can effect the way you load
    versions to your servers because of the differences in the schema scripts.
  • This
    method is safer – once the table is correctly created there is no additional maintenance
    / management and points of failure.
  • Consider
    if you need to modify int data type columns to bigint to assure the int data
    type 2^32 figure is never reached.

Scripts in this article:

MonitorReplicationIdentityRange.sql
SetReplicationIdentityRange.sql
IdentityRangeLog.sql

All three scripts can be downloaded

here


Using
the scripts

The scripts
assume the existence of a database called PerfDB.

The
procedures are designed to be executed from a monitoring tool periodically. i.e. daily or every few hours.

The stored
procedure MonitorReplicationIdentityRange works on all published
databases on the given sql server instance and checks for any identity column with
a seed (not a value) falling outside the predefined identity range of the given
server. Any such table that is found is logged to table IdentityRangeLog
and then the logged/ inserted data is retrieved and returned to the calling
application. In case the calling application is a monitoring tool you send the
returned results set in a notification email.

When the
procedure is executed with the @Reseed input parameter set to 1 (true) then
it calls the procedure SetReplicationIdentityRange that makes the seed
correction/ modification to match the range of the given server.

— Excecute the
procedure in a read-only mode: @Reseed = 0

— Find identity
seeds that fall outside the predefined range but no modification takes place.

EXEC PerfDB.dbo.MonitorReplicationIdentityRange @Reseed = 0; 

— Excecute the procedure in a read-write mode.

— Find identity seeds that fall outside the predefind range and modify
the seed to the correct value within the range.

EXEC PerfDB.dbo.MonitorReplicationIdentityRange @Reseed = 1; 

]]>

Leave a comment

Your email address will not be published.