Take the 2-minute tour ×

My SQL Server 2008 has

  1. one central database that has some global functions, e.g. mapping to specified client DB.
  2. 50+ client DBs
  3. Master DB.. (MS Default)
  4. ASPState for session.

Now I'm trying to add this stored procedure usp_RethrowError (http://msdn.microsoft.com/en-us/library/ms179296.aspx) which is return a message from try...catch.. of SQL to my server.

Since the stored procedure is a common sp, I want to use it in the central DB and all client DBs. Where should I put it? in the central or duplicate it in each client DB?

Is there any issue or performance drawback if the stored proc is placed in the central, caz in the client DB, I have to write something like [central].dbo.usp_RethrowError

What about put it in the master db?

Many Thanks

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

This question is largely a matter of taste, and of the specific circumstances. There are no performance implications to calling a procedure from another database.

I generally recommend against putting stored procedures in the master DB and generally reserve that for system use.

There are some cases where replicating the procedures makes sense (such as when you think there is a chance you may eventually separate those procedures into multiple servers). I frequently put utility procedures and functions in a separate database named utility.

This makes it easier to maintain than giving each database their own copy (only one place to change code if I want to add functionality or discover a subtle bug), but it also avoids poluting the master database, and makes it easy to backup and migrate all of my utility functions when a new server is brought online.

share|improve this answer
If there are no performance implications, then the StoredProc should be added in Master or My Central. So what's your option for these two dbs. Does Master cause maintenance/upgrade problems? –  Dan An Dec 13 '11 at 10:25
Your central sounds like the same as my utility database, which is what I normally recommend. As for using master, I don't like to put them there just because it seems disorganized to put any user defined object in the master DB, and the master DB is often treated differently by the DBA when it comes to making backups. –  TimothyAWiseman Dec 13 '11 at 14:43
thank u TimothyAWiseman. –  Dan An Dec 14 '11 at 12:23
add comment

It depends on whether any of your client database's will ever have to move server, without the rest of the databases.

Moving the client database to another server, which doesn't also include your central database will cause an error to be thrown. So in this case, I would add the stored procedure to each database.

If the databases are never going to be individually moved to another server, then I would add the stored procedure to the master database.

share|improve this answer
The dbs won't be moved individually to another server. So what the benefits that adding it in Master than Central. –  Dan An Dec 13 '11 at 10:03
I think the question should be the other way round? What's the purpose of creating a Central database? –  Curt Dec 13 '11 at 10:05
I understand that a SP that contains common business logic should be added in Central, but not sure about a sys utility-like SP. I guess you probably mean never add things in Master, since I got a customlised Central DB. –  Dan An Dec 13 '11 at 10:30
add comment

In addition to the other considerations, you may also want to look at using a synonym. That way you could create a centralized database for your common procedures [central], but have your code pointing to a synonym for those procs. In the future, if you need to move to a self-contained version, move the centralized procs to your local databases, and drop the synonym.

share|improve this answer
Have read about synonym from MSDN, seems it is more used for user data operations (select). Does it suit for Utility/Common Function? –  Dan An Dec 13 '11 at 10:19
Any time you need to reference a specific object that is either remote or local to your current operation, then a synonym may be used. It's simply a shortcut; the benefit is that if you ever need to update the location, you can drop and recreate the synonym without touching your calling code. –  Stuart Ainsworth Dec 13 '11 at 12:01
I see, do you use synonym for every table or StoredProc in Central or Master in your project? May be the only drawback for a synonym is that it has to be duplicated in every client DB, which increase a bit maintenance works. –  Dan An Dec 13 '11 at 12:26
Anyway it is still a good suggestion. So what's your option of Master or my Central? –  Dan An Dec 13 '11 at 12:28
add comment

Your Answer


