12/16/2023 0 Comments Create two instances of idatabaseOn the other hand it is 圆4 and I do have lots of memory.Īdditional LUNs aren't a problem - the disk space is available although I suspect the Network guys would prefer to use it for other things. I'd rather assumed it would be useful to allocate memory according to each instances needs however both George and Steve suggest SQL Server isn't that great here. However it seems there are as many disadvantages as there are advantages. Well I'd pretty much decided I'd be going ahead with multiple instances until I read the replies. And apologies for having taken so long to reply - am on a course this week. Many thanks to those who have taken the time to reply with some very useful input. This would be a case for active\active, with the test instance on the other node. This goes away if you have loads of memory and are on 64 bit.ĭo you really want your test instance on the same server, a badly formatted query hogging CPU could still slow the whole server. Multiple instances don't use memory as well as a single instance. Memory can be a pain because you get into having to allocate memory on a per instance basis and can't let SQL server decide what it wants. If you have a heavily used tempdb it can help because you get multiple tempdbs, but again they should be on sperate drives. IO wise it does not really help unless you can split the instances on to different drives, but you don't need a new instance to move databases about. In your case you are on a cluster so you could go active\active and make better use of memory and CPU resources and actually use the second server, there will be license costs though. So there has to be a good reason to split the databases out into multiple instances, such as security, the need to run at different service packs, business requirements (divide databases up by department say) or contention on tempdb I'm not a big fan of multiple instances because to me there is an administration overhead, to me its one of those cases of just because you can do it doesn't mean you have to. I would certainly welcome any and all advice, points of view, comments, etc. I'm sure there are lots of other things I haven't mentioned or indeed thought of. Will having several instances instead of one mean better performance, poorer performance, no change? Does this mean that the multi-threading capabilities of such cores will mean better management of, for example, locking, blocking or even more efficient running of scheduled jobs?Īnd what, if any, advantages/disadvantages are there in terms of I/O? Databases are all held off-server on FAS storage in this case. By the way, the cluster nodes are quad-core Intels running Win 2003 Server. Are there technical advantages as well (or indeed disadvantages)? I figure memory management could be more effective - i.e.the individual instances could be apportioned the optimum amounts of memory. So what then are the pros and cons? I've mentioned having fewer databases in any one instance, I guess thereby making Admin easier, at least in terms of managing scheduled jobs, etc. Apologies for the somewhat lengthy preamble by the way. Naturally, however, I'm being asked to justify that recommendation in terms of increased efficiency both of my time as DBA and of the cluster itself. So I figured that I would recommend the creation of a number of additional instances to host separately in-house databases, third-party databases, SharePoint databases (we're adopting SP big-time at the moment) and a test instance (currently databases are developed on a test server then moved to the live cluster - would be useful to have a test instance on the live cluster). (I'm sure I read somewhere that the recommended limit was 25). Currently there is a single instance overloaded (to my mind anyway) with 92 databases (excluding the systems). I'm trying to make the case for creating a number of additional instances on my company's existing 2-node active/passive SQL Server 2005 cluster. Looking for advice rather than the solution to a specific question here.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |