January 17, 2004 at 9:00 pm
What are peoples opinions of using multiple instancese as opposed to
multiple databases on a single instance.
Right now most of our apps that use sql server are running the app and sql
server on the same box.
We are looking to consolidate down to fewer, more powerful deadicated sql
servers, rather than a app/sql comb server for each app that uses sql
server - which is basically how we are now.
The main disadvantages ive read about with the multiple instance model are
1. you take a fairly significant performance hit if you run multiple
instances
2. slightly more complicated administration of the bigger servers
mainly for development?.
3. some apps dont recognize instances
The main advantages ive read about are
1. Your instances are completely isolated, so you can perfom database
maintenance without impacting other instances.
2. You can maintain instances at different revision/service pack levels.
3. You can provide a deadicated instance for Vendor apps that insist on
being the database on the server. (not sure about this, but it seem
logical.)
Id be happy to go with multiple databases on a single instance, but am
concerned with the fact that:
1. Some Vendors insist on being the only DB app on a server
2. 2 vendors apps may need different revision/service pack levels
3. Maintenance work required for 1 app may impact all Databases.
What is the general School of thought and what are the general bestpractices with regards to multiple instances?
Are multiple instances used much in production environments or are they mostly for development
What is the performance rule of thumb for multiple instances?
Thanks
Bill
January 18, 2004 at 4:10 pm
I typically only use instances when I have a security need - need to let other users have SA maybe, or one of those dreaded 3rd party apps that requires SA. I'd say go with the simplest thing that works, add complexity when you need it.
January 19, 2004 at 7:46 am
Another security reason for a separate instances would be to disable networking protocols for that instance. Having developed some of those "vendor apps" that require separate instance, we have designed the applications so that only a COM+ component running on the server has access to the database. By disabling all network protocols for that instance (TCP, Named Pipes, all), only an application running on the server will be able to access the database using Shared Memory protocol. This also has the additional benefit that Shared memory protocol is much faster than any of the networking protocols.
Mark
January 19, 2004 at 9:54 am
I like the single instance, with multiples you will have to tweak things. Memory mainly,with a single instance, SQL will manage all available memory, hopefully to proper utilization.
Each instance will have it's own buffers, caches etc.
Not to say there isn't reasons for multiples.
KlK
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply