Instances: Yes or No?
-
I have two physical servers representing a total of nine instances. It is a total management PITB (pain-in-the-butt). There seem to be no valid reasons for having so many instances, and with one exception, it is one database per instance! (well, sometimes there's a Dev version of the DB in the instance).
From what I have read, there are two good reasons to have multiple instances: when you need different binary versions, or when you need isolation between databases such as in a commercial database hosting environment. We're neither of those. I've been proverbially pulling my hair out because the root instances have been patched to at least SP3 while all of the sub-instances are still at the RTM level! I can also see it being useful if you have a badly-implemented DB that requires the server to be occasionally stopped and restarted to free it up, but again, that's not in our shop: all of our DBs run quite nicely, thank you.
So, does anyone have any other arguments for or against having multiple instances on boxes?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
I am with you Wayne. It makes no sense to me either. I've seen this in the past, and generally speaking it's what I like to refer to as a G-implemention or (Ghetto implementation). There's really no reason for it. Try mentioning how much they'd save with licensing if they moved all the dbs to one instance rather than 9 separate ones.
A.J.
DBA with an attitude -
I think you're confused about the licensing issue there Adam, you can run multiple instances on the same physical hardware without purchasing additional licenses. If you're using CALs, you might need more for multiple instancing (I'd suppose...).
I'm in general agreement with the both of you that there is a small but definite overhead associated with multiple instances, however I've seen a great number of situations where it is appropriate. For various audits I've gone through we have to demonstrate certain levels of separation between systems and multi-instancing meets those requirements. I've administered applications which were supported to run on specific service packs and not on others. Oftentimes it makes more financial sense in terms of hardware cost, licensing and upkeep to buy more powerful servers and use mutiple instances. Sure, if possible and if appropriate run everything on a single instance per server... but I think there's a time and a place to run multiple instances.
-
Just make sure you consider memory usage when you move to 1 or fewer instances. Each instance can now use up to 2 (3 if you use the /3GB switch in boot.ini) GB of RAM. If you have lots of memory in your box, and want to use 1 instance consider using AWE to make more RAM available to SQL.
For more info on AWE you could have a look at http://msdn2.microsoft.com/en-us/library/aa213764(sql.80).aspx
-
I can add one other reason to those mentioned (audit, isolation, hardware and software licensing costs). With instances, you can tie each to specific processors thus limiting how much processing power a particular instance can suck out of the server. You could even go so far as to set the thread priority of the service(s) (NOT recommended!!). RAM can also be allocated per instance.
NOTE: any/all of these reasons are not, IMHO, sufficient to warrant a bunch of instances for well-minded-single-db operations. Consider all of them and decide what is right for your needs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Only instances I would think is Dev, QA and UAT if ran on the same box. This way you can have a single DB name and backup from each other if on the same server. Although in my case I have Dev and UAT on the same server and the db names have _DEV and _UAT on the end and we alter the webconfig or global.asa file.
-
They didn't have a staff DBA position before I started here two months ago and there's waaay too much stuff that I just go "Whaaaa?"
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Couple other things to add.
First on licensing. Single license per machine for SS2K5. If SS2K, you need Enterprise to run unlimited. Standard requires separate license. Lesson here, upgrade one instance to SS2K5 - http://www.microsoft.com/sql/howtobuy/multipleinstances.mspx
Second, there are other reasons for multiple instances. First is tempdb separation. If one db/appl requires lots of tempdb use, you can separate to a separate drive with a 2nd instance.
The other reason for a separate instance is if you need to have logins the collide. Like for QA, you don't want a login on prod that has access to QA. Sure you can work with rights, but you might have someone with two accounts (for auditing purposes) or you don't want the chance of rights being granted inappropriately.
I'm a single instance/multiple DB guy for the most part, but there are some reasons why you'd want multiple instances. Consolidation can make sense, but I'd want to put apps together that I could patch all at once.
-
Scenerio:
I need you to apply SQL Server hotfix KBxxxxxx to my database and it requires the services restarted. Please do this at 1 PM as that is our business downtime for this database.
All you have to do is apply the hotfix to the instance and restart the services for that instance.
If all the databases are in one instance, you first have to let everyone else know their database will be temporarily unavailable. Can all of your database owners/users afford to have their database off line at the same times? Or do your database owners/users have different downtimes?
-SQLBill
-
I think this is another reason for separate servers. I am personally leery of having different versions of the same server code running on one machine.
I think the overriding factor (at least in my experience) is ensuring sufficient hardware is available for the production box. Secondarily is keeping one instance (QA/Test anyone??) from starving the production instance. This is most likely not going to be the case if multiple instances are on the same server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I'll agree that could be a perfectly valid scenario. However, we're a fairly small shop (city gov't, largely a 7am-6pm operation), 1000~ employees, probably no more than 60 people or so on SQL Server at a given time, and probably 40-50 of those on a server that I have no admin responsibilities for. So I would be the one to decide if a hotfix must be installed, and I would normally be able to schedule it for an after hours install. I have one DB that has vendor access for maintenance, and considering how that database is split into an n-tier framework, I can't imagine the SQL DB requiring that sort of "right now" patch. Which is not to say that it couldn't happen, of course!
If absolutely required, bouncing the server during the day to install a hotfix could be done without too much disruption. I would have to make 3 or 4 phone calls to clear users off.
That being said, it looks like my boss is firmly wedded to the multi-instance architecture. His main concern is having to give remote vendors access and to keep them locked in to a specific instance. Still, I'll be losing two instances once the move is done, and I'll be down to 7 from 9. And with them being on one box instead of two, I'll only need one remote desktop session.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
I like that idea, having those three functions on one box in seperate instances. However, we don't do a lot of in-house development (i.e. currently none). All of our apps are bought from consultants, so we don't follow that cycle at this time.
I can see that changing once we get rid of our mainframe next year and I see our developers going to SQL & Client/Server hard-core.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
One more thing in favour of separate instances is collation. Although you can have different collations for each database (indeed you can specify collation down to the column level), if your database has a different collation from the instance's default collation and it creates temp tables and joins to them, you'll be in trouble.
John
-
Multiple sql instances on one box can be useful for sql server consolidation. (Seems better to me than having multiple virtual machines, each housing a single sql instance, on a host.)
Barkingdog
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply