Steps to Restore Service Broker Enabled Database on New SQL Server Instance

Problem

I’m using Service Broker for asynchronous messaging between several SQL Server databases and instances. Should I change my backup and restore routines for the Service Broker enabled databases?

Solution

Sometimes disaster strikes. After a SQL Server crash, I lost the system drive of one of my development virtual machines. I used this machine as a “target” in the Service Broker setup, I walked through this in my previous tip. The SQL Server installation and the system databases were gone; however, I still had the disk where I kept the .mdf and .ldf files for the user defined databases.

This is the configuration I’ve worked on:

Initiator

• Windows 10 Enterprise version 1703 OS Build 15063.7026 (my dev physical machine)
• SQL Server 2014 SP2 version 12.0.5207.0
• Ports 1433 and 4023 are open
• TCP/IP protocol is enabled

Target

• Windows Server 2012 R2 Standard (virtual machine)
• SQL Server 2014 SP2 version 12.0.5000.0
• Ports 1433 and 4022 are open
• TCP/IP protocol is enabled

Bringing the “target” system online

Prerequisites

First, I had to set up a new virtual machine to install a new instance of SQL Server 2014 SP1. I won’t detail this, since it is outside the scope of this article.

Setting up (again) the network access on the new target machine

Unfortunately, during the server crash the system databases were gone and I had no backups. Consequently, I had to re-create:

• The database master key which protects the private keys of certificates
• The certificate I’ll use as the authentication option for the Service Broker endpoint
• The Service Broker endpoint
• The login that will have the right to connect to the endpoint

I tried to re-create the certificates (Certificate_SB_Source and Certificate_SB_Target) from the files I managed to copy to another location before the crash.  Before doing this, I needed to set up file permissions to allow the SQL Server service account to restore the certificates.

CREATE CERTIFICATE Certificate_SB_Source
   AUTHORIZATION User_SB_Target
   FROM FILE = '<Certificate_SB_Source.cer file location>'
GO
CREATE CERTIFICATE Certificate_SB_Target
   AUTHORIZATION User_SB_Target
   FROM FILE = '<Certificate_SB_Target.cer file location>'
GO

The restore seemed to work fine, the certificates were imported and I could see them in sys.certificates, with pvt_key_encryption_type_desc = NO_PRIVATE_KEY. However, when I tried to use Certificate_SB_Target for endpoint authentication I obtained an error message:

CREATE ENDPOINT SB_EndPoint
   STATE = STARTED
   AS TCP (LISTENER_PORT = 4022)
   FOR SERVICE_BROKER 
   (
      AUTHENTICATION = CERTIFICATE Certificate_SB_Target,
      ENCRYPTION = SUPPORTED
   )
GO

Here is the error:

Turns out that if I need to use a certificate for pretty much anything I need to specify a private key file when backing it up.  I had to delete the existing Certificate_SB_Target and create a new one which I backed up properly this time:

USE master;
GO
DROP CERTIFICATE Certificate_SB_Target;
GO
CREATE CERTIFICATE Certificate_SB_Target
   AUTHORIZATION User_SB_Target
   WITH SUBJECT = 'SB target endpoint certificate',
   START_DATE = '02/19/2017', EXPIRY_DATE = '01/01/2100' 
   ACTIVE FOR BEGIN_DIALOG = ON;
 
BACKUP CERTIFICATE Certificate_SB_Target
   TO FILE = '<certificate file location>\Certificate_SB_Target.cer'
   WITH PRIVATE KEY (FILE = '<private key file location>\Certificate_SB_Target.pvk',
                     ENCRYPTION BY PASSWORD = '<strong_pwd'>);
GO
 

Certificate_SB_Source, imported into the master database as part of the certificate exchange used for authentication can stay as it is, with no private key file.  But, for coherence and for future similar situations, it’s better to apply the same change.

And, of course, I had to delete the old Certificate_SB_Target in the source database and re-import:

USE master;
GO
DROP CERTIFICATE Certificate_SB_Target;
GO
CREATE CERTIFICATE Certificate_SB_Target  
    FROM FILE = '<certificate file location>\Certificate_SB_Target.cer'
    WITH PRIVATE KEY 
      ( 
        FILE = '<private key file location>\Certificate_SB_Target.pvk',
        DECRYPTION BY PASSWORD = '<strong_pwd>' 
      ) 
GO
 

Attaching the SBTarget database

By default, a newly restored or attached database is not Service Broker enabled, even if sys.databases.service_broker_guid is not null. It is a good idea to restore or attach the Service Broker enabled database with the ENABLE_BROKER option. The database will retain the old service_broker_guid and it will be Service Broker enabled; therefore, any existing conversation will resume (please see the next paragraph, though) and you won’t have to change the guid in your code.

USE master;
GO
CREATE DATABASE GFR_source  
      ON (FILENAME = '<.mdf file location>')   
      FOR ATTACH 
WITH ENABLE_BROKER;
GO 
--the same option is available for restore
--check the database status
USE SBTarget;
GO
SELECT [name], service_broker_guid, is_broker_enabled   
FROM sys.databases
WHERE database_id = DB_ID();

There are two other options available for both restore and attach. NEW_BROKER will assign a new service_broker_guid to the database. ERROR_BROKER_CONVERSATIONS retains the old service_broker_guid, but ends all conversations with an error stating that the database is attached or restored.

Other details to check

Make sure that the ports for SQL Server (typically 1433) and Service Broker (typically 4022) are open on the target machine. TCP/IP should also be enabled.

If a different IP number will be assigned to the machine, review your code and the routing information.

After the restore or attach, check the owner of the database. Even if the name of the new account that owns the database is the same as on the old instance (in my case SQL2014\Administrator), the principal sid won’t be the same and hence the database owner won’t be valid. Change the owner of the database using sp_changedbowner (deprecated) or ALTER AUTHORIZATION. If you forget to do this the target queue activation stored procedure will return this error message and your SQL Server error logs will grow quickly:

Next Steps

• Basit Farooq describes an interesting situation – a copy of a Service Broker enabled database is copied on the same instance.
• K. Brian Kelley’s tip is about restoring a TDE encrypted database, but the details about how to back up and restore the certificates are helpful.
• Make sure that your backup routine includes your system databases and database master keys. Back up the certificates specifying a private key file. I worked with a simple test environment and it was not very difficult to recover. In production it is not so easy to recover.