SQL Multiple Instances - any point?

iaind · 20 Apr 2010 at 14:52

I'm in the process of moving our creaking old SQL 2000 server onto a new VM with Windows 2008 and SQL 2005.

The current SQL server has been set up with multiple instances, pretty much one per application and the default instance used for everything else. There's 5 instances on there!

It seems like a silly question, but i'm not a DBA so there might be something I'm missing. There's no differences in editions or licensing.......so is there any reason someone set it up like this? It was a long gone predecessor, so cant ask them!

ruffneck · 20 Apr 2010 at 15:13

I would only use a single instance unless I really wanted a security separation. I think MS are poised to take multiple instances away from standard and enterprise edtions of sql anyway!

ruffneck · 20 Apr 2010 at 15:14

turns out they're not removing that feature

http://sqlblogcasts.com/blogs/testa...tion-will-still-allow-multiple-instances.aspx

iaind · 20 Apr 2010 at 15:41

That's what I thought... one of the isntances is our HR system, but it used NT authentication anyway, so really don't see the point

rpstewart · 20 Apr 2010 at 16:24

I'm a DB2 expert rather than SQL Server so I'm not sure if the same still holds true but I tend to use multiple instances wherever possible. Doing so lets you tune each one individually and means that a change which requires an instance restart doesn't affect the rest of the databases on the box.

morfmedia · 20 Apr 2010 at 17:55

Collation, HP Polyserve also lets you setup failover instances to different nodes etc etc.

As above you can give some DB's less memory / different config.

iaind · 20 Apr 2010 at 17:56

Not much I can think of that would ever require tuning or restarting - we're only a small company (90 staff) and this box really isnt doing that much.

My thinking was more instances = more overhead + more complication

brainchylde · 20 Apr 2010 at 22:55

We use SQL server fairly extensively. Using multiple instances does have it's benefits - if an instance 'crashes' for any reason - you don't lose all your databses, just that one instance. Or, if you want multiple people to have 'administrative' rights - i.e. each department to have their own sql instance.

Some applications also need some sort of elevated permissions - useful to give these their own instance to stop them interfering with existing databases.

Generally, for me, it would depend on the size of your company - for a 90 staff firm, I would say it is unlikely that you will require multiple instances. If you have developers, for example, you may want to give them their own instance to 'fiddle' and test with.

Of course, if you find the need for an instance in the future, there is nothing to stop you installing an additional instance anyway.

unclepablo · 21 Apr 2010 at 22:50

Having worked with SQL for 5 odd years, I agree with the other posters at say it's usually done for permission reasons or if a 3rd party vender insists on seperating "their" SQL server. :rolleyes:

You can set the memory limits /range for each instance which can be useful (SQL loves memory as I'm sure you know) - I've seen minor application instances gobble all the memory and then the instance than runs the main business system run like a dog as it has very little memory allocated!

It's obviously more overhead to split, so I'd merge when possible - even if you end up with 2 instances rather than 5.