Enabling Transactional Subscriber with Identity Ranges for Updates

Transactional Replication published tables containing Identity Ranges are updated on subscriber via Replication Distribution Agent.  This processes does not increment the subscriber’s table Identity Seed starting value. If you drop a Subscriber and want to bring it online as an updatable user database, first execute CHECKINDENT reseeding the table to highest value + 1. DBCC
Read More »

Peer 2 Peer Replication fails with Incorrect syntax near ‘sp_MS…’ (Source: MSSQLServer, Error number: 102)

Symptom Creating a Peer 2 Peer publication from SQL scripts and you observe the Distribution Agents failing with “incorrect syntax near”. These messages can be observed in SQL Server Agent job history, in SQL Profiler Trace, or in Agent -Output logging. Errors show below were raised on (insert) stored procedure for Production.Product table for AdventureWorks2017
Read More »

The insert failed. It conflicted with an identity range check constraint

Taiyeb Zakir Microsoft SQL Server Escalation Support Services When replicating identity columns https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-identity-columns  and using Auto Identity management, you need to make sure ranges are defined properly. The default “ranges” for automatic identity range management, while good 20 years ago, may need to be tuned for today’s higher workloads.