July 12, 2010 at 8:38 am
Up until last week, our company had a single database server. It has been this way for several years, and I've developed a number of functions and stored procedures that run against that server. Now that we've added a second database server, I'd like to utilize some of the more general purpose functions on our new database. I'd like to look into the best way to do this.
All of the general purpose functions are located on our master database. A lot of them are datetime functions, a few are for string manipulation, and a few are for general database administration. The most important of these is a stored procedure that monitors for changes in database file sizes. This is the only SP the references objects outside of the master database. It keeps a log table in our Logs database. There are also two user tables in the master database: a time dimension table, and a tally table. I would like all of the user objects in the master database to exist on this new server. Is replication the easiest way? Is this even possible on the master database? If so, will replicating the master database cause problems?
It's worth mentioning the these functions and stored procedures are in source control. I definitely do not want two copies in source control. I also want any changes to be reflected as quickly and effortlessly as possible, hopefully eliminating the human element. They would only change on one side, and infrequently. Also, if possible, I'd like to copy over any synonyms automatically in case anything objects are moved or their names change. Replication does not appear to support this, but synonyms could be managed manually.
If replication won't work, what would be my next best option? Should I get those object out of the master database and then replicate, or should I script it?
--J
July 12, 2010 at 9:24 am
I don't see an advantage to not copying the functions to the new server's master database.
Cross database functions with a synonym are going to be slow, and have an additional complexity of security as well...end users need the same rights to both servers, i think in order for your functions and procs to NOT fail....
if they functions are straight up formatting and manipulations, ie dateformats and such, those would work as sysnonyms, with the conditions i worried about above slow and fraought with security issues.
....but if any of the functions refer to any tables, ie sys.databases or anything else, they will not work correctly right?
a function that exists on server1.master database will infer that all tables it references exist on that server, ie sys.tables, or master.sys.databases, exists on server1, even if it is called from server2;, i think so they would report incorrect info, since you are expecting the functions to get server 2 info even though the function/proc exists on server 1.
Lowell
July 12, 2010 at 9:30 am
I definitely don't want to use cross-database synonyms. I want to use copies of those functions, but I don't want to have to create the copies myself. I'm looking for the best/easiest method to create those copies without my involvment. Also, when I create a new function, I want it copied automatically as well.
I mention synonym because I've change naming schemes in the past and have had to move functions. I'm saying I would like to (if possible) copy the synonyms as well in case I need to maintain old names for backwards compatibility across both databases.
--J
July 12, 2010 at 10:02 am
I just ran through and tried it, and it appears that the master database can not be used for a publication. I am not surprised.
It looks like scripting is my best option?
--J
July 12, 2010 at 3:28 pm
As a general rule I wouldn't put user functions into the master database. As soon as you upgrade you need to recreate everything. A more reliable and consistent option is to create a DBATemplate database and put all your stuff in this.
At a previous company we had a master copy of this database which was replicated (objects only) to all servers. Part of the build instructions for a new server was to create the new database and set it up as a subscriber.
The DB was used for a host of server reporting, trouble shooting and managment tasks.
Changes were only ever made in the master copy, and backwards compatibility and versioning was managed within the code. Note we had about 150 servers including SQL 2000, 2005 & 2008 and they all had the same database.
It takes a bit of work to set up initially, but once it's in place it's a great tool.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 13, 2010 at 7:11 am
I spoke with my boss and we decided to get those objects out of the master database. I've opted instead for a database called "Global", which will store all these objects. I will replicate this database to any new servers.
Thanks for the assist.
--J
July 13, 2010 at 11:11 pm
I have to take pretty good exception to what has been stated so far. Cross Database Functions are NOT slow. Cross Server Functions are can be.
On my current project, I keep all of my UDF's (all iTVF's) in a UTIL database.
I do agree with what most have said. It's generally a bad idea to store UDF's and Sprocs in the Master database. However, there are times where, owing to the special nature of the master database and the magical sp_ prefix, nothing else will do. If the proper precautions are taken, having certain sprocs, functions, and views in Master are sometimes the only way to go.
Heh... time for me to get my umbrella... it's going to be raining purists any second now. 😛
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply