1

I want to setup a bunch of Event Notifications on SQL Server using Service Broker for our production database.

I've setup a new database on my testing machine, and everything works well. The problem is that I'd rather not have to deploy a whole database for this notification purposes and I can't use one of the user databases already in place (that would be a mess, anyway).

If possible, I'd preferably deploy the notification objects on master, but first I'd like to be sure this is actually a good idea (if it actually is).

From Books Online:

Do not create user objects in master. If you do, master must be backed up more frequently.

Books Online recommends I do not create user objects in master, but in this case, I don't think it's that much of a problem since I don't really care if I eventually lose some notification data. Besides, we already do backup our master database frequently.

Should I use the master database or something else in this case?

Is there something I should know about the master database, Service Broker or Event Notifications before setting this up?

Improve this question
3
  • 1
    Don't put user objects in master.
    – JNK
    Commented Mar 27, 2012 at 14:19
  • 1
    I've seen it both ways. I think user objects in master are prone to be forgotten, whereas a DBA database that contains those objects you use to enable you to do your job is more self-documenting.
    – jl01
    Commented Mar 27, 2012 at 14:33
  • 1
    @JNK care to elaborate?
    – ivanmp
    Commented Mar 27, 2012 at 14:40

1 Answer 1

Reset to default
5

I can think of a few questions about this implementation that I would want to know before continuing:

  1. What happens if, somehow, microsoft creates an object with the exact same name in a new version?

  2. If databases are migrated to a new server, how will anyone know that there's something in master to migrate?

  3. What happens if your objects corrupt the master database?

  4. What adverse affects would having extra data in master have on the instance?

  5. What would happen if an in-place upgrade was performed on this instance? (ie, upgrading 2008 to 2008 R2)

  6. How would restoring the entire instance from backups be affected?

Most of these I don't know the answer to, but they're questions that come up when I contemplate putting objects in master.

Improve this answer
1
  • 1
    That was the kind of answer I was looking for! Indeed, very valid and good questions. Much more useful than a "don't put user objects in master" ;) But stupid people like to make themselves look smart. Thank you!
    – ivanmp
    Commented Mar 27, 2012 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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