Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jamie
Aged Yak Warrior
542 Posts |
 Posted - 2007-06-25 : 09:06:30
|
| hi guys,I am writing a document to discuss SQL installations.I have many databases across many departments so am thinking about installing multiple instances.advantages I have so far are :Different SA accounts (improved security)Different Server and Agent Services (databases on each instance can be managed independently )Different Tempdb for each instanceConfigurable memory for each instanceCan manage CPU resources using WSRMAre there any other, also any cons regarding instances ?than kyou for any suggestions. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-25 : 09:15:39
|
| "Are there any other, also any cons regarding instances ?...Configurable memory for each instance"Instances may be prevented from robbing memory from lowly used instances - i.e. an all-in=one approach might devote more resources to busier databases more efficiently.Mind you, lowly used database s might slow to a crawl!Kristen |
 |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-06-25 : 09:59:57
|
| but could I allocate say 1 gig to a busy system, then only 100 meg for a least busy db.so is 1 instance better performance wiser than many instances ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-25 : 10:06:48
|
| Yes you could, but if the lowly used system is idle the memory is "Unused", and could perhaps be better used by the Busy database.This is not something I am familiar with, and I hear plenty of the regulars here saying they have multiple instances, so I reckon it has to be the right way to go, I was just thinking out loud really!Hopefully a heavy-instance-using-DBA will be along in a minute!Kristen |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-06-25 : 10:10:09
|
| cheers Kristen.I have read conflicting info. regarding instances, some people say good , some bad. I personally see pro's for multi instances , but if performance deteriates with more instances it may not be worth it. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-06-25 : 11:58:00
|
| hi, does anybody else have any experience with multiple instances ? |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-25 : 11:58:24
|
one con would be it's twice as much to manage. twice as many service packs to install, settings to set, etc.
elsasoft.org |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-25 : 22:39:08
|
| I never do that on prod servers. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-06-26 : 04:26:08
|
| hi rmiao, you never do what ? setup multiple instances on live servers ?why not ?I have over 100 databases , 10 departments and 2 servers. . |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2007-06-26 : 11:10:15
|
| Each additional SQL instance requires it's own footprint, so you don't want to go overboard on instances. Also, don't forget a instance may be fine, but a rogue process may still impact entire server, and thus all instances on it, or even the server itself.Also, anything that creates a disk intensive operation - backups, file growths, massive tempdb usage, etc. etc. may impact all SQL instances, not just the one it is running on. When you have many DB's, or instances, on a server, plan sarefully around resource utilization.While I agree with RMiao, in that I would like never to have to have mutliple critical applications databases on a server, realities and economics are never friends of the DBA, and so many shops don't have the luxury of a server per DB.That said, if you only have 2 servers, then what it comes down to is how best to manage the 100 DB's across the 2 servers? You will be better off if a low usage/low importance DB is on a seperate instance so that if you have issues with a specific instance, you have a chance of not impacting the other.Are both servers of similar spec?Do you know relative importance, to business, of difference DBs and deptartments, so that you are able to define a matrix defining business importance and business usage, against each database? Setting up a 2x2 (Low,High vc Low,High) or 3x3 (Low,Medium,High vs Low,Medium,High) would be a useful exercise when trying to determine how to distribute your databases. If I had to do this, I'd probably look at 2 or at most 3 instances (per my categories), and limit resources per category (probably something like low = 10%, medium = 30%, high = 60%, or low = 25%, high = 75%) in terms of allocating memory, CPUs etc.I think you'll want to explicitly limit (set affinity) for low priotiry instance. You might do the same for the high (for example give 1 CPU to Low, 2 to Medium and 5 to high on an 8 cpu box), or you might decide the high instance is allowed to use all cpu's, and only limit your other instances.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-06-27 : 11:13:08
|
| hi Wanderer, that is really useful.I was thinking about splitting High used databases into their own instances, then grouping the lowly used databases into instances by department, but, I would have about 6 instances per server.Is this a bad idea ?spec of servers would be 2 cpus, 2 - 6 gig ram...regarding tempdb usage, each instance will have its own tempdb, so temp application work will be separate for each instance.. |
|
|
|
RocketScientist
Official SQLTeam Chef
85 Posts |
Posted - 2007-06-27 : 13:50:02
|
| I'm not sure about the idea of using instances to manage performance. There are entirely too many resource constraints on a server that can't be managed efficiently that way. There are 4 resources that effect performance: network, disk, memory, and CPU. By splitting into multiple instances, you can manage the CPU (Somewhat, by limiting the number of CPU's available) and you can manage the memory. If you set up endpoints carefully, you can manage network also, but that's very rarely a constraint. So the big issue is disk. And you have very little control over disk. I suppose you could come up with a partitioning scheme, but unless you have a large number of disks each on it's own disk channel and controller you're not managing the resource effectively. Since we don't have that level of control (oh, unless you just daisy chain USB controllers and drives onto your system lol) it's not really effective. Which is a shame because it's a great idea.There are very good reasons to use separate instances, most that I've seen involved security, applications that were bound to a hard-coded database name, and issues surrounding xp_cmdshell. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-27 : 13:58:25
|
| You don't have a choice of whether or not to use multiple instances if you are using an active/active/... cluster like we are.I've got 11 instances installed on a 4-node SQL Server 2005 cluster. We can control disk resources due to the SAN and use of mount points. 2005 provides much better support for this. 2000 doesn't even support mount points.We've got very beefy machines, so performance is not an issue. We do limit the amount of RAM each insance can consume, which you must do on clusters anyway. The amount of RAM we give to each instance is dependent upon how "big" the application is that is on the instance. Perhaps the other people in this thread who don't like multiple instances on a server do not have beefy servers. If that's the case, I wouldn't recommend them either.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-27 : 14:04:01
|
"do not have beefy servers"Well, I think we have beefy servers, but in practice I'm sure we don't  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-27 : 14:14:44
|
| As an example, our production servers have 16GB of RAM with 8 CPUs. The servers are expandable too, so we can add more if needed.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2007-06-28 : 04:38:49
|
quote:
Originally posted by tkizer
You don't have a choice of whether or not to use multiple instances if you are using an active/active/... cluster like we are.I've got 11 instances installed on a 4-node SQL Server 2005 cluster. We can control disk resources due to the SAN and use of mount points. 2005 provides much better support for this. 2000 doesn't even support mount points.We've got very beefy machines, so performance is not an issue. We do limit the amount of RAM each insance can consume, which you must do on clusters anyway. The amount of RAM we give to each instance is dependent upon how "big" the application is that is on the instance. Perhaps the other people in this thread who don't like multiple instances on a server do not have beefy servers. If that's the case, I wouldn't recommend them either.Tara Kizerhttp://weblogs.sqlteam.com/tarad/
Tara: out of interest, how do the mount points work in terms of the fibre cards for your SAN - are you able to have 11 fibre cards - 1 for each instance, or do those get shared? I know nothing about mount points, so I'd be interested to know what they offer...In terms of 'very beefy' - we (at least, the main client) has pretty much the same spec as you describe ((btw your 8cpus - is it 8 physical cpu, or does that count hyper-threading?)). They are currently on either 8 physical cpu 16 GB's, or 8 physical cpu 32 GB machines, and the pricinple DBs are on their own dedicated 2-node active/passive clusters. All of these are 64 bit. These servers generally tick over at between 10-15% cpu, but during peak trade (Christmas, etc) we see 50-75%, so dedicated clusters are justified imho.Jamie - the comment about tempdb wasn't intended to imply that instances shared tempdb, but rather that several instances could have tempdb growth, which could impact upon you server, especially if you don't have SAN (haven't seen a posting from you regarding what you disk setup will be).Consider a (bad) scenario where you have local disk, and have the following:E: all tempdb's (100 gb)F: all log files (100 gb)G: all data files (200 gb)If a 'lowly' db, on a 'LOW instance' - does something silly , you could have excessive log growth, or excessive tempdb growth. that coudl cause pain not only from the IO performance, but in a worst case scenario, you lowly DB could have log growth that eats all you space on log dirve - and then ALL instances are out of space, and grind to a halt.Granted, that is a worst case scenario, and likely you'll have decent monitoring, and disk quota's, etc. It is just intended to show that the DISK (as Rocket Scientist pointed out) could be the most challanging shared resource.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-06-28 : 04:47:08
|
| so, what would be the recommended server spec to run multiple instances.Disk storage is not an issue, I currently have 2 servers with 2 CPU's and 2 gig ram, but can add more ram.. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-06-28 : 04:49:28
|
| sorry, just to let you know disk storage is on a SAN. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-28 : 06:51:47
|
Dunno anything about SAN, but ideally separate disk channels for:O/SDataLogsTEMPDB - compromise on this one is fairly common IMEBackups - compromise on this one is common IME2 CPUs and 2GB RAM is nothing like enough, unless your databases are tiny - in which case its harder to justify multiple instances  As part of this you should also look at Disaster Recovery. Clustering; Hot/Warm/Cold standby; Log shipping ... that type of stuff. Sounds like you may be changing from multiple machines to all-eggs-in-one-basket, which makes Admin easier, but needs a second machine for the rainy-day-breakdowns!Kristen |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-06-28 : 06:57:31
|
| we currently run 1 server 1 instance, all databases.!databases range from 2 meg to 10 gig.my thoughts are with multiple instances I could allocate more resources to the instances with the bigger db's.if 2 cpu 2 gig is not enough, what should I be looking at - minimum.. ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-28 : 07:08:49
|
| Well it depends on the volume of server activity. If your CPU is not getting above 10% (say!!) then I guess your server is big enough. Ditto if no one is complaining about performance and/or you aren't losing business, showing "Server Busy" on a web site, or similar tell-tale signs.In SQL2000 you are limited to 2GB of RAM unless you spend $-Plenty on Enterprise Edition. In SQL2005 the Standard Edition is limited only by the RAM that the O/S can address. So you can take advantage of cheap-RAM without increases in licence fees. Stick in as much as you can afford / server can hold.We use 4xCPU for heavy-volume servers, lots of RAID disks, lots of RAID channels, lots of RAM. I think those machines perform very well. But I am also in control of the tuning of those applications, so all the queries are well tuned - I don't have to deal with ad-hoc end-user queries, or Applications that I have no control over.If I had a lot of ad-hoc queries, or 3rd party applications, I reckon I would be wanting MORE Horsepower on the assumption that I wouldn't be able to tune the system as well. (Of course 3rd parties may provide a well tuned database, in which case that would be great!)Kristen |
|
|
|
Next Page
|
|
|
|
|
sql instance advantages