July 10, 2009 at 8:46 am
Hi. I'm looking for a bit of advice as to when one ought to create a new instance instead of continuing to add new databases to an existing instance.
Is this purely for administrative purposes - i.e. having a smaller number of databases on a larger number of instances - or are there valid performance reasons for doing this as well? I currently have a 2-node Active/Passive fail-over cluster running 2005 Standard x64 with one instance containing over 90 databases. Given that there is currently no budget for an additional active node I figure I need to move some of these databases to other instances, at least for admin purposes.
I'm assuming that performance isn't going to change that much since all resources will continue to be hosted on the same physical server(s). In fact performance may even be slightly poorer. However on the other hand I guess I can allocate memory in a more controlled way if I have specific databases in specific instances. Or am I talking nonsense here?
Any comments, advice, etc, would be very much appreciated.
Regards,
YaHozna
July 10, 2009 at 8:55 am
Adding additional instances could, I believe, create additional maintenance headaches too. Now your instances will share memory/cpu/disk and you would have to carefully configure and monitor these per instance.
July 10, 2009 at 8:57 am
You may need to consider to add additional instance on the same server box when you have more than one project. At this point, security may become an issue.
License is another issue when creating additional instance.
July 10, 2009 at 9:45 am
SQL ORACLE (7/10/2009)
License is another issue when creating additional instance.
I thought sql server licensing is based on number of CPUs AND/OR number of clients AND/OR number of devices...
Ref http://www.microsoft.com/Sqlserver/2005/en/us/licensing.aspx
We usually create more instances for the purpose of segregating types of database. For example, if there is a hardware constraint and you need to keep your dev and prod databases on the same box, consider keeping them on seperate instances. In your case, if it's going to be used only for production, you can consider creating instances based on types of databases.
Also, creating more number of instances hardly gives performance issues as far as i know. Resource consumption is purely based on what queries you are running/what activities are being performed on the databases.
July 10, 2009 at 10:46 am
We have many instances and I have found the following issues:
1. It does use more system resources than one instance: you have two sets of system databases including tempdb and you have to allocate the memory which is tricky. Think about a situation where one instance has a higher demand one day and the other has higher demand the next. You don't want to allocate memory around every day but if they are in one instance they have access to all the memory.
2. Most third party software is priced on a per-instance basis.
My thoughts.
July 10, 2009 at 11:09 am
LeeM (7/10/2009)
We have many instances and I have found the following issues:1. It does use more system resources than one instance: you have two sets of system databases including tempdb and you have to allocate the memory which is tricky. Think about a situation where one instance has a higher demand one day and the other has higher demand the next. You don't want to allocate memory around every day but if they are in one instance they have access to all the memory.
2. Most third party software is priced on a per-instance basis.
My thoughts.
I haven't seen any user applications that are priced per instance. I have seen SQL Server tools that are priced per instance, and you do have to keep that in mind. Personally, I don't care how good those are, I don't want them. If they can't price their products on a per server basis, it really isn't that good IMHO.
I may have reasons to use multiple instances on a single server, such as separating DEV/QA/Production databases. I may have security reasons to separate production instances of databases.
July 10, 2009 at 11:41 am
I don't see the administrative value of instances. It might make security separation easier, but anything else gets more complex. I guess you get administrative skills beefed up, but it's not easier.
Licensing depends on your edition. Enterprise allows unlimited instances. Not sure if Standard does as the licensing constantly changes. I'd check that based on your situation.
I've seen over 200 databases in an instance and it ran fine. I wouldn't be concerned about making another instance. Instead, I'd talk "performance" to get that extra active node.
July 10, 2009 at 11:51 am
Another reason to have different instances would be (again along the security lines) to better segregate client databases. More politics, more administrative duties.
For your additional instance, is your cluster in such a shape that you could place the new instance on the currently passive node - in a new instance?
IE - Instance1 has Node1 active and Node2 passive
while Instance2 has Node1 passive and Node2 active.
For one of our clusters, we have implemented this and offloaded some of the performance to the second node. If it is sitting idle, it is more costly. The downside to this of course is if one of the nodes bounces to the other node. In this scenario, your right back at having everything on the same box with same performance implications. However, at least it would only be temporary.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 13, 2009 at 5:38 am
Thanks to everyone who has replied to my post thus far. I'll reply generally rather than to individual posts if I may.
It would seem that the general consensus is that I don't need to, or perhaps ought not to, be considering an additional instance at this time. Certainly there aren't any licensing issues involved since, as with the Enterprise Edition, I can have an unlimited number of instances as Steve mentioned.
And it is a legitimate exercise to consider segregating databases based on what they do or the applications that they underpin. However it is a valid point, well made by LeeM, that different levels of demand on different days may make that difficult to manage.
Plus, as Steve mentioned, having a considerable number number of database on one instance, can work fine.
So I will do the following: (a) wait until the demand for resources on the current configuration starts to outgrow what's available; (b) ask for more money for a second active node.
Many thanks indeed to everyone who took the time to offer their invaluable advice. It's invaluable to someone like me who is still learning their trade.
Regards,
YaHozna
July 13, 2009 at 5:45 am
Hi SSC Journeyman. Not sure how that would work. Is this not an Active/Active configuration with the instance on one node moving to the other when there is a fail-over? In such a case both instances would then be running on one node. If both nodes have a live instance aren't they both Active?
Also you would need an additional SQL Server license which, at the moment isn't necessary since only one server is active at any one time.
Regards,
YaHozna
July 13, 2009 at 9:02 am
In an active/active cluster each instance is only active on one node. For instance, you have InstanceA and InstanceB, with InstanceA running on Node1 and InstanceB running on Node2. If Node1 goes down, InstanceA failsover to Node2 and at that time you now have both Instances runing on Node2.
July 14, 2009 at 2:09 am
Hi Lynn and thanks for the response. Yes, having an Active/Active configurations is certainly a possibility however it also means extra Windows Server and SQL Server licenses. Plus, as you say, extra load on one node if the other goes down. Guess I'll maintain the status quo for the time being 🙂
Regards,
Gordon.
July 15, 2009 at 12:19 pm
I've got a 6 node cluster with 4 instances on it. Each instance has over 1000 databases attached. Between 4000 and 10000 users connect to each instance daily for their work.
It works pretty good. Better than when I had 2000 databases on each instance in a 3 node cluster with two instances on it. The only problem I run into is when I need to open the databases portion of SSMS... that can take a few seconds to fully expand.
July 16, 2009 at 1:56 am
Hi Mark, many thanks for your input. I would be interested to learn what criteria you used when deciding to create additional instances. Were these instances different versions of SQL Server or different SP levels?
Regards,
YaHozna.
July 16, 2009 at 8:25 am
YaHozna (7/16/2009)
Hi Mark, many thanks for your input. I would be interested to learn what criteria you used when deciding to create additional instances. Were these instances different versions of SQL Server or different SP levels?Regards,
YaHozna.
There were no exact criteria. We had about 4000 databases split on two instances and we had to move data centers. When we did this we decided to bring up a larger cluster and divide the databases evenly across the cluster nodes. At the time about 2000 databases would take forever to expand in SSMS so we decided to try it with 1000. This wasn't about performance for our customers, they hadn't been complaining. But our technicians who would occasionally have to do ad-hoc backups/restores would sometimes wait as long as 2-3 minutes for the databases nodes to expand. This was an effort to assist them. At about 1000 databases per cluster node, expanding and finding databases takes about 15-30 seconds... Which is presently acceptable.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply