You are currently offline, waiting for your internet to reconnect

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"

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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.
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.
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 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:
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.
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.
MORE INFORMATION
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates
REFERENCES
For more information about how to upgrade a SQL Server 2000 log shipping configuration, visit the following MSDN Web site:
Properties

Article ID: 959008 - Last Review: 12/16/2008 00:47:03 - Revision: 1.0

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

  • kbsqlservlogship kbtshoot kbsql2005engine kbexpertiseadvanced kbfix kbbug KB959008
Feedback
om/c.gif?DI=4050&did=1&t=">