November 19, 2004 at 5:12 pm
Didn't see my original post so I'm trying again...
My developers wrote a Java application using a JDBC driver called Merlia. Long story short, the driver needs two user tables added to the master database. The driver uses two-phase commits and these tables would be used to facilitate that process and they'd be updated constantly but they would not be expected to grow because rows are deleted as commits are completed.
This would be on a production SQL 2000 server which hosts about a dozen science databases.
This seems like a really bad idea to me but other than impacting SQL server upgrades, I can't come up with a solid argument why we don't want to do this. I can foresee potential issues with database integrity, performance, disaster recovery... Can anyone comment on this?
Neil
November 19, 2004 at 5:28 pm
The master database is the heart of the entrie sql server and should be protected against user interaction at all cost which now you have created a security risk by granting access to the master db. If a user table corrupts inside master DB during transactions it will in most all likelyhood cripple the server until a master restore is performed. But then if someone got hold of the app and drivers they could also potentially get access on your server you don't want them to have.
But alas I did check out the Merlia website (http://www.inetsoftware.de/English/Default.htm) and based on the info unless you find another driver (which is what I suggest and if the java developers whine because this is what they want to have I suggest get better develoepres personally).
But limit you potenial issues which could and most likely at somepoint will affect other DB users not involved I suggest try setting up an instance situation and make sure this application and it's own resources that could be affected if this app isn't the only one using SQL. On top of that I would place the connection in a role and explciitly give access to the user tables and deny access to all questionable pieces for more safety.
November 22, 2004 at 2:01 am
Having accidentally created user tables in master and then loaded data into them by simply forgetting to change DB I know how painful it is to restore the master database from dumps etc.
I would suggest that you avoid putting user tables in master almost at all costs.
However if your developers are pigheaded as some I know then one suggestion you might consider if you have no alternative is putting the tables in another database and then creating a view in master to that table. At least that would prevent you actually updating master.
hope this helps
November 22, 2004 at 9:09 am
There are a few issues with using Master. 1 you are changing the base system of SQL Server, no strong recovery on this db, contains system level information, etc., so it's probably not the place. MSDB may be better, but if you really need tables, then create a small db for your application to ensure that the DBA is aware of it.
2nd, in a DR situation, your tables may be lost. I don't always restore master when I recover a server, might just recover user dbs, so having tables there can be an issue.
The advice above is pretty good, I'd take a hard look at it.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 22, 2004 at 10:32 am
Your comments are much appreciated. It is pretty much as I thought. There is a better implementation of the Merlia driver that does not require modification to the master db and I needed to build a strong enough case to postpone deployment of this application until the developer figured out how to implement that solution.
-Neil
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply