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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply