FIX: SQL Server 2012 or SQL Server 2014 instance shuts down when you join database as secondary replica during AlwaysOn Availability Groups configuration

Applies to: SQL Server 2012 DeveloperSQL Server 2012 EnterpriseSQL Server 2012 Standard More

Symptoms


Consider the following scenario:
  • You use Microsoft SQL Server 2012 or SQL Server 2014.
  • You have database that has service broker enabled in primary replica.
  • You back up the database in primary server which has service broker enabled and restore it on secondary server.
  • As part of defining the availability group between the primary and secondary servers, you try to join the database to the availability group by using the New Availability Group wizard or the command:
    ALTER DATABASE <dbname> SET HADR AVAILABILITY GROUP = <agname>
In this scenario,the following error is logged in SQL Server error log:
{
Error: 3449, Severity: 21, State: 1.
SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
}

Cause


The issue occurs because if a service broker enabled database is backed up and restored to the secondary server, the service broker in restored database is not enabled. The database still keeps a service broker id internally.

When you add the restored database on the secondary server to the availability group, the service broker is enabled. The same GUID in the availability database is detected, and the following error is raised (silently):
9772 “The Service Broker in database "<dbname>" cannot be enabled because there is already an enabled Service Broker with the same ID”.

This results in error 3449 as the database is joining the availability group, and causes the SQL server to shut down unexpectedly.

Resolution



The issue was first fixed in the following cumulative update of SQL Server.

Cumulative Update 1 for SQL Server 2014

Cumulative Update 9 for SQL Server 2012 SP1

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.