Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Won't Fix Help for as Won't Fix


5
0
Sign in
to vote
Type: Bug
ID: 521231
Opened: 12/18/2009 10:14:54 AM
Access Restriction: Public
1
Workaround(s)
view
1
User(s) can reproduce this bug

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.
Details
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"
Sign in to post a workaround.
Posted by HarshDeep_Singh on 6/27/2013 at 6:26 PM
I've come across this issue multiple times. The troubleshooting steps and solution are documented here:
http://blogs.msdn.com/b/sqljourney/archive/2012/08/09/an-interesting-issue-with-sql-replication-and-a-rogue-system-spid.aspx