Lately I've been trying to catch up on all the changes in replication in SQL
2005 and one in particular struck me as interesting - how transactional
replication handles replication of identity columns. If you've done this with
SQL 2000 you know that if you have a table on the publisher that has an identity
column that when the snapshot creates the table on the subscriber it will not
have the identity column. This makes pretty good sense for two reasons; one is
that the subscriber is generally considered read only, the other is that if you
had an identity column on the subscriber you'd have to deal with the possibility
of a key collision if someone had added a row on the subscriber and ended up
using a key that was later generated on the publisher.
Now to make it a little more complicated there is also the concept of having
subscribers that can be updated and have those changes pushed back to the
publisher. SQL 2000 would let you do this by assigning identity ranges to each
subscriber, so that Subscriber A might have the range 100k-200k, Subscriber B
200k-300k, and the publisher would have 1-100k (and obviously the ranges could
not overlap). As the ranges approached the 80% utilization mark there was a proc
(sp_adjustpublisheridentityrange) that would be called on the publisher to go
and get a new range. Works well enough in most cases.
So that's the way the world was, but in SQL 2005 there is a tiny change -
tables with identity columns keep the identity column on the subscribers even if
they are not updateable. We'll talk about why the change was made in a moment,
but I was curious to see how they implemented the change. If you've ever
looked at the stored procedures SQL generates on the subscribers you've probably
seen something like this:
ALTER procedure [dbo].[sp_MSins_dboEmployees]
@c1 int,@c2 datetime,@c3 varchar(50),@c4 varchar(50)
as
begin
insert into "dbo"."Employees"(
"EmployeeID"
,"DateAdded"
,"FirstName"
,"LastName"
)
values (
@c1
,@c2
,@c3
,@c4
)
end
Now that proc makes perfect sense if there is no identity column on the
EmployeeID column, SQL is explicitly providing the value. Add the identity
column back to the table on the subscriber and things would break, right? My off
the cuff thought when I started looking was that I expected to see something
like this:
ALTER procedure [dbo].[sp_MSins_dboEmployees]
@c1 int,@c2 datetime,@c3 varchar(50),@c4 varchar(50)
as
begin
set identity_insert employees on
insert into "dbo"."Employees"(
"EmployeeID"
,"DateAdded"
,"FirstName"
,"LastName"
)
values (
@c1
,@c2
,@c3
,@c4
)
set identity_insert employees off
end
That's how we would normally add a row to a table with an identity column
when we needed to set the value explicitly. But when I created a new publication
to see, I was surprised to see the proc looked just like it always did. Strange,
so how did they accomplish the insert? My next thought was maybe an instead of
trigger on the subscriber, but there were no triggers. Starting up Profiler and
doing a few inserts, I found this:
Nothing on sp_MSPub_adjust_identity in BOL, so I opened it up to see for
myself. Lots of TSQL, so down the rabbit hole for a few minutes looking at stuff
that while interesting, doesn't seem to be overriding or resetting the identity
value. Go back and test again, make sure I haven't looked at wrong table or
something, still works. I reset the seed on the subscriber, insert from the
publisher still works and the seed not changed on the subscriber. I back up and
look once more, and then I see it - "Not for Replication" on the identity
column. Makes perfect sense, that's the same technique that would be used for
updating subscribers. If you look in BOL at the definition you'll see:
- Foreign key constraints - The foreign key constraint is not enforced
when a replication agent performs an insert, update, or delete operation.
- Check constraints - The check constraint is not enforced when a
replication agent performs an insert, update, or delete operation.
- Identity columns - The identity column value is not incremented when a
replication agent performs an insert operation.
- Triggers - The trigger is not executed when a replication agent performs
an insert, update, or delete operation.
So, it's easy to get off track when you start with a bad assumption! The one
thing I think about as I document this is that I wish you could see the 'not for
replication option' being applied in Profiler, that would have saved me a few
minutes, bad assumption or not.
Now let's talk about why the change is important. Lot's of people use
transactional replication to push data to a reporting server. It's easy to set
up and administer, and putting reports on a different server both isolates the
reports from the OLTP side of things and allows you to index more aggressively
to gain speed in reporting. It's then natural to think that the subscriber
becomes a perfect fail over target if the publisher catches on fire. This is
mostly true, you'll have data to within a few seconds typically, but when you
point your applications at the new server and start doing inserts they would
fail in SQL 2000 - no identity columns. If you're using SQL 2000 and wanted to
use it as a failover you needed to have scripts ready to alter the tables to add
the identity columns. Not terribly hard, but one extra step. With this minor
change in SQL 2005 you don't have to worry about making that change any longer.
I hope you'll find the replication change interesting, and maybe my missteps
on seeing how it works will help you somehow too!
