1

Our developers are currently working on Service Broker and will be implementing in production soon. What do I need to worry about with Log Shipping in regards to DR? The environment they are implementing this on is a 2008 (enterprise) with Log Shipping for DR. We have the other items such as logons, jobs, dbmail, linked servers, etc covered, but want to ensure Service Broker would go along in the event we need to use our DR solution.

Thank you

Marcello Miorelli
  • 16,170
  • 52
  • 163
  • 300
Dan White
  • 148
  • 6
  • It appears that Service broker is not affected with regard to Log Shipping according to this: By default, when you attach or restore a database, the Service Broker identifier and message delivery status are unchanged. Typically, you do not change the Service Broker identifier in the following situations:

    •When you restore a backup for recovery purposes.

    •When you configure a mirrored pair.

    •When you set up log shipping for a standby server. When you are making a copy of the database, you change the instance identifier.

     – rvsc48 Sep 15 '16 at 12:45
  • What should I do when someone answers my question? – Marcello Miorelli Apr 01 '20 at 15:50

1 Answers1

1

All of the service broker components are objects with the database and as such they as backed up when you perform a database backup or log backup.

When you restore the database, it is restored with service broker disabled. You can either enable service broker after you restore the database using:

ALTER DATABASE databasename SET ENABLE_BROKER

or specify the service broker with options on the RESTORE DATABASE command.

https://msdn.microsoft.com/en-us/library/bb522682.aspx

https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(RESTORE_DATABASE_TSQL)%3bk(SQL12.SWB.TSQLRESULTS.F1)%3bk(SQL12.SWB.TSQLQUERY.F1)%3bk(MISCELLANEOUSFILESPROJECT)%3bk(DevLang-TSQL)&rd=true

user2390151
  • 75
  • 2