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

2
  • 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, 2016 at 12:45

1 Answer 1

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

1
  • Thank you! This is what I was thinking but wanted to be sure.
    – Dan White
    Sep 16, 2016 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.