Failure during server script upgrade process on database in sp_vupgrade_replication procedure. - by JoeJanhonen

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


5
0
Sign in
to vote
ID 521231 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 12/18/2009 10:14:54 AM
Access Restriction Public

Description

We encountered problems while applying Service Packs and when attaching an older version of a database to a newer version SQL Server instance (requiring the database upgrade process to run).  

---Errorlog snippet---
Upgrading subscription settings and system objects in database [DataWarehouse].
Invalid object name 'MSreplication_subscriptions'.
Error executing sp_vupgrade_replication.
Saving upgrade script status to 'SOFTWARE\Microsoft\MSSQLServer\Replication\Setup'.
Saved upgrade script status successfully.
---

Within the sp_vupgrade_replication procedure a subprocedure sp_vupgrade_subscription_databases is called which in turn calls another procedure sp_vupgrade_subscription_tables.  It is in this last procedure you will find the following snippet of code:
IF EXISTS ( SELECT * FROM sys.objects WHERE name = 'MSreplication_subscriptions' ) or           
EXISTS ( SELECT * FROM sys.objects WHERE name = 'MSsubscription_agents' )      
BEGIN          
IF EXISTS ( SELECT publication, publisher_db, publisher, subscription_type               
FROM MSreplication_subscriptions              
GROUP BY publication, publisher_db, publisher, subscription_type              
HAVING COUNT(*) > 1 )              
RAISERROR (21203, 10, 1, @table_name)          
ELSE
IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'uc1MSReplication_subscriptions' AND                  
id = OBJECT_ID('MSreplication_subscriptions') )                  
CREATE UNIQUE CLUSTERED INDEX uc1MSReplication_subscriptions ON                  
MSreplication_subscriptions(publication, publisher_db, publisher, subscription_type)     

Clearly it is checking for the existence of certain objects; however, it does not distinguish between actual tables and synonyms.  In our case synonyms had been created in the database that pointed to the tables in another database that was a subscriber in a replication topology.  This included synonyms that pointed at the various replication tables.  And therefore there were named objects in sys.objects that incorrectly met the criteria in this internal T-SQL code.  While this is a developer mistake on our part it should not cause the upgrade process to fail.
Sign in to post a comment.
Posted by willspurgeon on 3/19/2012 at 9:45 AM
Also hit a problem with this same script on a server that had been running replication at some point in the past. There was a database that still had a copy of MSsubscription_agents but not MSreplication_subscriptions. Since the above script assumes that if MSsubscription_agents is present then MSreplication_subscriptions will be also, the update failed. This was for SQL 2005 SP4.
Posted by Glenn Wellington on 5/12/2011 at 2:54 PM
I agree. Our production system had a huge fiasco because of this and the error was essentially a "quiet failure" putting us in a very tight spot. Additionally, we were just able to reproduce this issue in our Dev environment by applying SQL 2008 SP2 CU1.
Posted by JoeJanhonen on 12/22/2009 at 12:28 PM
It is only an open ended problem if you choose to make it so. How is it difficult to add in a check for object type = 'U' or to make your select from sys.tables instead of sys.objects? Many of your other internal processes do this already. This oversight is EASY for you to fix by checking for exactly what are intending to check for - a TABLE named MSreplication_subscriptions or MSsubscription_agents.
Posted by Microsoft on 12/22/2009 at 12:19 PM
I agree that the code should be able to handle these nuances in customer deployment. But as you may guess this is an open ended problem and the real issue is that replication objects are reserved and we should have disallowed creation of synonyms on replication objects. Given it is difficult to address the fundamental issue at this point of the release, I am marking this as "wont fix"