The SQL Server Service Broker or Database Mirroring transport is disabled or not configured

The alert “The SQL Server Service Broker or Database Mirroring transport is disabled or not configured” is one that pops up in new installations quite frequently. A typical answer is to look at the Broker part and check if it is set as in this post.

With SQL 2008 you do not have to run the SQL query as it is shown in the properties of the database. Open SQL Server Management Studio, select OperationsManager database and right click and chose Properties.  Click Options and scroll down and there is a section called Service Broker.

image

On my default install this is set to true anyway. But the clue is in the alert. In the alert details it says “The Database Mirroring protocol transport is disabled or not configured.”  Back in the database properties under Mirroring we can see that the database has not been configured for mirroring.

image

If you select Configure Security you will see the following error message.

image

In my test environment the database had defaulted to Simple Recovery Model. There are three – Simple, Full and Bulk-Logged. This is fine for a test environment  but is unlikely in a production environment. With the Recovery Model at Simple you will need to override this SQL MP alert. It would have been nice if they had created two rules for this instead on just the one.

About these ads

3 Comments

  1. About the recovery model, I find it hard to motivate anythin _but_ Simple recovery model for any Operations Manager databases since the need to backup these databases rarely exceeds once or twice a day.
    MS SQL DBAs usually go by the motto “Keep it Simple”. Only use Full recovery model when you really need it.

    I know that MS Technet describes the use of the Simple model as for Dev/Test or Datawarehouses since it lacks the ability to restore to a point-in-time but most likely you have to think about dimensioning and routines too when it comes to actually restoring the data. Do we “need” to be able to recover data to a later point in time than the last full backup? Do we have the administrative resources to handle the translogbackups? Do we have the routines to handle a point-in-time restore?
    Looking at SLAs and most SMB-companies, only a fragment of their databases really need anything more than a Simple Recovery Model.

    http://technet.microsoft.com/en-us/library/ms178052.aspx

    • BlueCollarCritic

      Here he, here he I second that. Too often BULK_LOGGED and or FULL are used when SIMPLE is perfectly fine and all b/c so many DBA’s are bullied into and or sold so strongly on the idea that SIMPLE is NOT for any DBs except development & testing DBs and thats just not true.

      I use to work support for an accounting software app that used SQL Server as the data store and most of the users were not technically savvy. Had we pushed them to use anything other then simple they most likely would not have a backup they could use to restore from if some emergency event occurred.

      If a user does not posses the skills/understanding of how to use the Recovery Models and or they don;t have a third party vendor they use to manage their IT who knows this then the user is better off setting their DB to SIMPLE and understanding that they can only restore from the last full backup they made then risk their having no usable backup to restore from because they didn’t understand how to properly manage a BULK_LOGGED and or FULL Recovery DB.

  2. Great article. I’m getting the same error. Did you manage to identify and fix the problem ? If so please let me know.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: