One big instance of SQL or several smaller?
-
I'm curious how you guys handle this. If you had a 16-way processor with 32GB of RAM, would you create several instances of SQL Server and partition it that way or just load up the databases vertically on one instance?
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars -
Every instance is another executable task on the server. I'd probably go for one task and multiple data bases. Of course, I'd really just like to have the cash that thing would cost.
-
Currently my strategy has been to consolidate our 40 or so SQL Servers onto 6 Active/Active clusters (12 total servers). It saved quite a bit of costs but this new data center is going to have even more servers to shrink.
What I see as the Pros of multiple instances:
* One instance can't stomp on another instances memory or processors
* SP upgrades, although tools are shared
* Better planned outages (kill one instance, upgrade and move to the other)
* Billing may be easier based on CPU utilization per instance
* It doesn't cost any more with EE of SQL Server
What I see as the cons:
* One instance can't access that huge pool of memory and processors if you restrict it as you should
* Added man power in managing...it might as well be another server.
* More to watch in backups and every management task
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars -
Since you proposed a 16-way with 32 GB of RAM, which is more than I'll get to play with anytime soon, does DataCenter offer any advantages with multiple instances over 1 big one?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
Edited by - bkelley on 12/09/2002 10:34:55 PM
K. Brian Kelley
@kbriankelley -
First off I think I would on a box of this size consider using multiple instances. But to be sure I suggest you package the box and mail to me, I try it for a bit and let you know for sure.
What I see as the Pros of multiple instances:
* One instance can't stomp on another instances memory or processors
- I wouldn't say that 100%. Small bugs in code you produce or within SQL itself can always potentially reference memory that does not belong to it. I would say it is extrememly unlikely unless you have any code where you code acquire memory yourself.
* SP upgrades, although tools are shared
- I would be carefull here. If you are talkign about being able to SPs on individual server. The reason is they all share some components not matter what. In memory they load multiple times as not to affect each other. But an SP can upgrade those chared components and be an issue. This also is rare but you have to watch for.
* Better planned outages (kill one instance, upgrade and move to the other)
- This is a major plus for anything.
* Billing may be easier based on CPU utilization per instance
- CPU utilization is not the greatest billing method as external issues can cause apps to behave erratically and use extraneous memory. My case in point,
I work with an ASPECT call center where I query data across a process called RealTime Bridge for current agent information. Recently after upgrading to a new version
we noticed it was slugish and would die often. The first point was at us as RealTime Bridge was showing 82% average CPU utilization all the time. After several days of testing on other sites and confirming has
to be something else as results cannot reporduce anywhere else we called for an emergency change control. That night they changed the processor and a few other hardware pieces and brought back up. When all was up the server
was running at 25% average. As soon as I reattached it shot back up. After several discussions I asked if anyone there had checked the event logs, of course not. Upon checking it was found that a particular adminstration card was shooting errors
like a mad hatter. This card although not directly part of the RealTime package was in fact hit when queries occurred. WHen that happened the number of errors to the log went thru the roof. So I don't liek that billing method from a personal standpoitn and just wanted to exaplin why. I prefer storage and administrative fees, plus any specific hardware allocations.
* It doesn't cost any more with EE of SQL Server
- That is a good deal as well and does have impact on the choice of to use instances or not. But MS could potentially change this at some point, so this version you may enjoy that but when you go to upgrade they change. This is not anything I have heard it is just a thought to keep in mind, what to do if it does and you go to upgrade.
+ Less potential for access by another customer either intentialy or inadvertently.
What I see as the cons:
* One instance can't access that huge pool of memory and processors if you restrict it as you should
- Wit SQL 2000 you really don't have to do this. Read the information on instances and you will find internally it will control across all instances and over a period of time get all the servers in equilibrium with each other. 7 wasn't a good at this. However, you may still want to do if you know a particular app will need more resources.
* Added man power in managing...it might as well be another server.
- It is, it is another server. But how much man power does it really take to handle a SQL server?
* More to watch in backups and every management task
- This is the biggest issue of all.
+ SQL logins and overall user adminstration goes up.
+ Multiple system databases, so more general space requirements and tempdb becomes a size factor to keep in mind for each instance.
+ Without setting up linked servers which opens extra connections and increases memory resource needs to handle them. You cannot reuse common code as effectively.
+ More overall server connections both server side and client connections. Thus more for the NIC to contend with.
I am sure I will think of more.
-
Whats your storage look like?
Andy
-
I have read several white papers that talk about SQL server not scaling well above 8 procs. With that in mind I would probably go with multi instance as well. I personally don't like huge SMP machines on the hardware side things can get out of whack and cause problems you haven't planned for. Any time I am asked to setup a large scaling project I almost always look at scaling up to 8 procs then breaking out into multiple boxes to handle load. One other issue your going to run into is I/O on a box that large. Overall through put of the application is going to improve with the number of front end servers handleing the I/O load. I'm assuming you are going to attach to some type of san EMC HP or what ever. How many HBA's are you going to be able to put in that box? How about load sharing on those HBA's? Storage is going to be a huge issue.
So, Just to recap, I would feel better with multi instances on a box that large or better yet get two 8 way boxes or 4 4 way boxes and distribute the load in a fed cluster attached to a high end san of some sort. Maybe with each server acting is a failover for the other box in a cluster.
Just my thoughts.
Wes
-
Some kind soul sent me this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_asphosting.asp
Where MS actually shows degregation in performance after a given amount of databases. There answer is actually to create a new instance for scalability. Interesting read!
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars -
Very nice read Brian. I think that virtual instances is one of the best things that they added to SQL that and indexed views.
Wes
-
Haven't been thrilled with instances, not sure they were worth it, but I'm finding more reasons to use them. Brian has some nice reasons and I'm looking forward to an article on the changes
Steve Jones
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply