Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Don't put user objects in master. –  JNK Mar 27 '12 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 Mar 27 '12 at 14:33
1  
@JNK care to elaborate? –  ivanmp Mar 27 '12 at 14:40
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
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 Mar 27 '12 at 18:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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