June 2, 2009 at 7:40 pm
Background: we updated all servers about 3 months ago from sql2000 to sql2005.
Our production server is replicated to a second server, and is log shipped to a third server.
All database changes (DML, procs, etc.) are tightly controlled through install scripts through a migration plan through the different environments (dev/test/preprod/prod). There are only 4 people with access to make these changes, are we are all sure that we didn't do this.
Today, I noticed that on the production server only, most of the rowguid columns (type: uniqueidentifier), covering nearly 100 tables, have had their default definition changed from NewID() to NewSequentialID(). None of the servers involved with log shipping or replication, and none of the servers in the migration through the different environments, have been changed. No migration scripts have been run that would have changed these.
Is there anything going on in sql 2005 that would cause these default definitions to be changed? How else could all of these have been changed?
Edit: changed "all" to "most".
Thanks for your help,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 4, 2009 at 3:42 pm
WayneS (6/2/2009)
Is there anything going on in sql 2005 that would cause these default definitions to be changed? How else could all of these have been changed?
None that I've ever heard of and, if something like this did exist, it would probably be classified by most people as a very serious bug.
SQL does not automatically, by itself, change object definitions in any case that I've heard of. Are you sure no one ever ran a script to do the change? No job that contained code to do this? No script accidentally run on production? (I've done that one myself before)
You say that the replicated server and the standby server don't match the publisher/primary? Are the tables in question replicated? Have you brought the standby online to check it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2009 at 4:16 pm
Apparently, merge replication changes to sequential by default - could this have happened in your case?
I can't find a great resource to detail under what circumstances newid changes to newsequentialid, but these might be interesting:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23718796.html
http://msdn.microsoft.com/en-us/library/bb895334.aspx
http://blogs.msdn.com/sqlprogrammability/archive/2006/03/23/559061.aspx
http://msdn.microsoft.com/en-us/library/ms152770(SQL.90).aspx
June 4, 2009 at 4:35 pm
Ok. So when you start the snapshot agent for a merge-replicated table, master.[sys].[sp_MSprepare_mergearticle] is called - which contains:
if @snapshot_ready = 2 or (@article_status 2 and @article_status 6)
begin
-- addguidcolumn needs to be called even if article is active. This is needed to
-- drop and recreate rowguid default constraint - to make it go back and forth
-- between newid() and newsequentialid().
exec @retcode = sys.sp_MSaddguidcolumn @source_owner, @source_table
if @@error 0 or @retcode 0
return (1)
end
(The comment about switching between GUID defaults is interesting!)
This contains a call to master.[sys].[sp_MSaddguidcolumn], which contains the code:
if exists (select * from dbo.sysmergearticles sma join dbo.sysmergepublications smp
on sma.pubid = smp.pubid
where sma.objid = @id
and smp.backward_comp_level < 90
and (sys.fn_MSmerge_islocalpubid(smp.pubid) = 1))
begin
select @default_function = N' newid() '
select @default_function_like = '%newid%'
end
else
begin
select @default_function = N' newsequentialid() '
select @default_function_like = '%newsequentialid%'
end
So it seems that if you select the 'SQL Server 2005' option on the Subscriber Types page of the New Publication Wizard, you get NewSequentialID instead of NewID.
June 4, 2009 at 7:03 pm
GilaMonster (6/4/2009)
WayneS (6/2/2009)
Is there anything going on in sql 2005 that would cause these default definitions to be changed? How else could all of these have been changed?None that I've ever heard of and, if something like this did exist, it would probably be classified by most people as a very serious bug.
SQL does not automatically, by itself, change object definitions in any case that I've heard of. Are you sure no one ever ran a script to do the change? No job that contained code to do this? No script accidentally run on production? (I've done that one myself before)
You say that the replicated server and the standby server don't match the publisher/primary? Are the tables in question replicated? Have you brought the standby online to check it?
The server that is log-shipped to DOES have the NewSequentialID.
The server that is replicated to DOES NOT (schema replication is not checked- just the data, schema changes are applied to this server when applied to prod by the migration script).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 4, 2009 at 7:09 pm
Paul, I do believe that you found what was going on.
Now, I searched the net for hours, and never found any of these articles. How did you do it?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 4, 2009 at 7:20 pm
WayneS (6/4/2009)
Paul, I do believe that you found what was going on.Now, I searched the net for hours, and never found any of these articles. How did you do it?
Just lucky I guess 😉
I was initially frustrated by the lack of documentation - I still haven't found anything definitive for 2K5.
The thread on experts-exchange (expert sex-change lol) pointed me in the right direction, so it was just a case of setting up a test merge replication, tracing the SQL, and checking inside the undocumented procedures.
Just glad you got your answer - it was a good puzzle, and I have learned something new today, which is always good.
June 4, 2009 at 7:21 pm
WayneS (6/4/2009)
Paul, I do believe that you found what was going on.Now, I searched the net for hours, and never found any of these articles. How did you do it?
I agree... I must think differently and use the wrong search words. It's always a PITA for me to do a search on such a thing. I guess I should first ask, which search engine are you using, and the second would be, is there a trick to it so you don't end up have to wade through 10 billion pages of unrelated garbage?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2009 at 7:27 pm
Jeff Moden (6/4/2009)
I agree... I must think differently and use the wrong search words. It's always a PITA for me to do a search on such a thing. I guess I should first ask, which search engine are you using, and the second would be, is there a trick to it so you don't end up have to wade through 10 billion pages of unrelated garbage?
Ok - I resisted the temptation once, but at the risk of being pork-chopped, I think I started with something like this:
http://lmgtfy.com/?q=merge+replication+newsequentialid+default :w00t:
😛 Sorry 😀
June 4, 2009 at 7:33 pm
On a separate note, it's kinda sucky that NEWSEQUENTIALID has to be protected by a mutex don't you think?
I don't suppose having all calls to UuidCreateSequential serialized causes many people a huge problem, but it is unfortunate.
I guess its benefits outweigh this disadvantage, though the whole NEWSEQUENTIALID versus NEWID debate seems quite polarizing from what I have seen so far. Not quite to the same extent as the method requiring the handrails, but still.
June 4, 2009 at 8:11 pm
Paul White (6/4/2009)
On a separate note, it's kinda sucky that NEWSEQUENTIALID has to be protected by a mutex don't you think?
The price or serialization in a multi-processing environment. Unless it's built into the the CPU, but that turned out to be worse.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 4, 2009 at 8:29 pm
RBarryYoung (6/4/2009)
The price of serialization in a multi-processing environment. Unless it's built into the the CPU, but that turned out to be worse.
Sounds intriguing - the thing I read said that the mutex was added because of behaviour specific to the AMD64 - is that what you are referring to?
June 4, 2009 at 10:06 pm
No, I was just referring to the fact that there has to be cross-CPU synchronization of some kind in order to correctly implement a sequential counter, whether it's mutexes, semaphores, dedicated processor, etc.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 4, 2009 at 10:54 pm
WayneS (6/4/2009)
Paul, I do believe that you found what was going on.
Ok, I was assuming you meant it had changed sometime after the replication was set up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2009 at 11:14 pm
RBarryYoung (6/4/2009)
No, I was just referring to the fact that there has to be cross-CPU synchronization of some kind in order to correctly implement a sequential counter, whether it's mutexes, semaphores, dedicated processor, etc.
Oh I see! Ok.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply