May 8, 2008 at 3:24 pm
Hi
I have a number of 3rd party apps that are supported by the vendors. These include Finance, Marketing and soon HR. We are considering the implications on security, especially given that the apps all have a system account running as a sysadmin and/or the vendor account has sysadmin.
It seems to me that given we have processor licensing I can create multiple instances on the server and have each of these 'problem apps' have there own instance. This would limit the sysadmin rights to the one DB associated with the appropriate app.
However I'm aware that SQL Server is designed to share an instance between numerous DBs. Is there a down side to this multiple instance approach?
Regards
Karl
Karl Hewlett
Database Administrator
Meteorological Service on New Zealand Limited
ddi: +64 4 470 0838
mob: +64 27 570 0838
May 8, 2008 at 3:39 pm
Karl,
Hi, hows the weather down there?
I see only two reasons for going for multiple instances, security issues and apps needing to run at different release levels. I guess you have the security driver here and given the nature of the apps they should be seperated if the users connect with sysadmin rights (a no-no, see later).
However, multiple instances are harder to manage and do not use the resources of the hardware as well as a single instance as they compete with each other, especially for memory, which you will need to cap for each instance. the apps will need to be able to connect to a named instance. Seperate instances will mean greater independance of the apps from each other if you need do something intrusive at the instance level.
If this is a cluster it is an opportunity to go active\active and make beter use of the nodes in the cluster.
However (again) maybe you are going at this from the wrong angle, I would ask why these apps need sysadmin, an app should never require that. Look to reduce their permissions to at least dbo on their respective databases and they will be secure from each other.
---------------------------------------------------------------------
May 8, 2008 at 3:49 pm
I'm a single instance fan, and then using security to manage the rights between the applications. You get better hardware use.
However, you share tempdb (limited resource) and you also can have a runaway query on one app, say ERP, affect the other apps. This could happen with multiple instances, but not as severely.
May 8, 2008 at 3:53 pm
Hi
Thanks for the reply George. Its a beautiful day down here, winter has started though ...
When you say compete for resources, is this an issue other than with RAM? I can, as you say cap the RAM and as the underlying server is 64 bit so I can access a large amount without the overhead of PAE and/or AWE. Does SQL Server play nicely with itself regards CPU, etc?
Don't get me started on the security side; my background is Oracle on Unix in banks and similar environments. The idea that any thing other than administration happens as sysadmin is just bizarre to me. But it seems to be common in the Windows world and I can only tilt at so many windmills 🙂
As an aside, I have encountered another reason for a separate instance, collations. I once supported an app that required objects in the Master DB and a specific collation that conflicted with every other app at the site.
Regards
Karl
May 8, 2008 at 4:17 pm
Karl Hewlett (5/8/2008)
HiThanks for the reply George. Its a beautiful day down here, winter has started though ...
spring just sprung here , glorious weather, 25 centigrade, more like summer.
seriously thiough, chase up these vendors, a lot of them say they must have sa but it turns out they just need it for the install process, most of the others have just been too lazy to work out correct permissions or its an app designed to run on multiple flavours of dbms so they keep security 'generic'.
collation should not be a problem as from SQL 2000 onwards SQl supports multiple collations in an instance. Potential problems here with tempdb but thats quite rare and easy to code a fix for (set collation when defining temp tables)
Contention - unless you can seperate instances onto their own drives i/o contention still just as much a problem, CPU not so sure, but I would bet its slightly worse due to duplication of system processes. If you have enough CPUs you could set the affinity for each instance to different cpus. Its all making it more complicated though
Steve has a good point about tempdb, one advantage of multiple instances, but again that would be partly negated if tempdbs were on same drive.
In a nutshell I guess I am saying I prefer a single instance, and would cerainly not be happy about having to go the multiple instance route just because a vendor supplied an app requiring sa.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply