FIX: Error message when you start database mirroring in SQL Server 2005 on a database that is restored or upgraded from SQL Server 2000: "The Service Broker ID for the remote copy of database "<DatabaseName>" does not match the ID on the principal server"

Article ID: 959008 - View products that this article applies to.
Expand all | Collapse all

On This Page

SYMPTOMS

Consider one of the following scenarios:
  • You restore a database backup from Microsoft SQL Server 2000 on an instance of Microsoft SQL Server 2005 by using the WITH RECOVERY option. You also restore this database backup on another instance of SQL Server 2005 by using the WITH NORECOVERY option.
  • You have two instances of SQL Server 2000 that have a database that is the same on these instances. One instance has the database in the Loading status. The other instance has the database in the regular status. Then, you upgrade these instances to SQL Server 2005.
You configure database mirroring on this database in SQL Server 2005. However, when you start database mirroring, you receive the following error message:
The Service Broker ID for the remote copy of database "<DatabaseName>" does not match the ID on the principal server. (Microsoft SQL Server, Error: 1436)
Note This problem also occurs in Microsoft SQL Server 2008.

You usually encounter this problem when you upgrade a Microsoft SQL Server 2000 log shipping configuration. Consider the following scenario:
  • You have a SQL Server 2000 log shipping configuration.
  • You upgrade the servers that are involved in the log shipping configuration to SQL Server 2005.
  • After the upgrade, you configure database mirroring by using the database that is used previously in the log shipping configuration.
When you start database mirroring, you receive this error message.
Back to the top | Give Feedback

CAUSE

Databases in SQL Server 2000 do not have a Service Broker ID. If you restore these databases on instances of SQL Server 2005 by using the WITH NORECOVERY option, these databases will not be upgraded. Therefore, the Service Broker ID of these databases is NULL after the restore. Additionally, if you upgrade an instance of SQL Server 2000, existing databases that are in the Loading status will not be upgraded. Therefore, the Service Broker ID of these databases is NULL after the upgrade. When you start database mirroring, the Service Broker ID of the principal server is compared to the Service ID of the mirror server. Because the Service Broker IDs do not match, the comparison fails.

In the upgrade scenario of a SQL Server 2000 log shipping configuration, the secondary database is in the Loading status. After you upgrade the server that holds the secondary database, the database does not have a Service Broker ID. After you upgrade the server that holds the primary database, the database has a Service Broker ID. When you start database mirroring, the Service Broker ID of the principal server is sent to the mirror server for comparison. Because the Service Broker ID of the mirror server is NULL, the comparison fails.
Back to the top | Give Feedback

RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for SQL Server 2005. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
913089
(http://support.microsoft.com/kb/913089/ )
How to obtain the latest service pack for SQL Server 2005

Important After you install SQL Server 2005 Service Pack 3, enable trace flag 8446 on the mirror server before you start database mirroring. After you have started database mirroring, you can safely remove this trace flag. For more information about how to enable a trace flag, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/en-us/library/ms188396(SQL.90).aspx
(http://msdn.microsoft.com/en-us/library/ms188396(SQL.90).aspx)
Back to the top | Give Feedback

WORKAROUND

To work around this problem, follow these steps:
  1. Perform a full database backup and log backup on the principal server.
  2. Restore the database backup and the log backup on the mirror server by using the WITH NORECOVERY option.
  3. Configure database mirroring on the database.
  4. Start database mirroring.
Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in SQL Server 2005 Service Pack 3.
Back to the top | Give Feedback

MORE INFORMATION

For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684
(http://support.microsoft.com/kb/824684/ )
Description of the standard terminology that is used to describe Microsoft software updates
Back to the top | Give Feedback

REFERENCES

For more information about how to upgrade a SQL Server 2000 log shipping configuration, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/ms188297(SQL.90).aspx
(http://msdn.microsoft.com/en-us/library/ms188297(SQL.90).aspx)
Back to the top | Give Feedback

Properties

Article ID: 959008 - Last Review: December 16, 2008 - Revision: 1.0
APPLIES TO
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbsqlservlogship kbtshoot kbsql2005engine kbexpertiseadvanced kbfix kbbug KB959008
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top