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 |
|
admin001
Posting Yak Master
166 Posts |
 Posted - 2004-07-22 : 23:49:59
|
| Hi ,We are in the course of rebuilding our development Intranet env. which uses SQL Server at the backend . At present we have approx 115 small databases on one single SQL server Enterprise Edition which is capable quite enough . I would like to take this opportunity of this re-structuring to have multiple instances on a Single SQL server with separate instance names for these 115 databases . I know it is not going to be easy and requires lot of planning and testing to finally prove that it works efficiently . Since this kind of architecture is being planned for the first time ,I am going through some articles available on the net and setting up a test lab. But there are still some queries I thought I will post for which I was not clear on few explanations : 1. How is the database grouping done under a named instance . How many databases can I group. Any benchmark ???2. Can I have multiple instances for SQL Server standard Edition ?3. How are the server resources ( Processor and memroy ) allocated to multiple named instances . Does it require to be manually configured ? 4. Can I have the database files of instance2 ( mdf and ldf ) on the same drive path as instance1 ?5. Any disadvantages of having multiple instances although benefits are known . Would be exteremely helpful if you could share experiences or advice on the above and any sites that I can refer to . Thanks in advance . |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-23 : 00:56:55
|
| You want 115 instances of SQL Server on a single machine?Wow!Kristen |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-23 : 01:52:45
|
| what is it you hope to accomplish with having multiple instances? Not that this isn't a good idea, I am just curious as to why you are going through the trouble for a DEV box.Anyway, to answer your questions:1. database grouping? not sure what you mean, but you can have up to 32,767 databases per instance.2. You can have 16 instances per box, even when using Standard edition. I have heard that this is not a hard number though, and that the limit is actually higher (64bit edition specifically).3. You can manually assign memory or cpu on a per instance basis. Or you can leave them both set to auto, it is up to you. With win2k3 you can have more granular control over how much cpu an instance can have, but that is a win2k3 feature, not a SQL server feature.4. Ummm, not sure on this one. If clustered then the answer is no, unclustered I believe you can place your datafiles anywhere you want.5. Disadvantages would be more effort in administering the system, although you get greater control.You might want to create instances that are broken down by department (the finance, HR, Sales, IT and manufacturing instances for example). Or, you could break them down by type (OLTP, BI or OLAP, etc.).You may also need to create separate instances if you have a requirement for a certain intall time parameter being set, or a certain service pack level of SQL or hotfix that may not be supported by other databases. HTH-ec |
|
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2004-07-23 : 02:44:40
|
| Thanks Eyechart. That's exactly the info. I was lookin for .Kirsten , It is not 115 instances on one SQL server , but logically grouping them under different departments as Eyechart mentioned .We have in the process of grouping the DB's into depts. like Finance, HR , Corp. Communications , Sales and so on . We want to cut down on too many SQL boxes for each department and specially for Intranet like the one we have , I believe multiple instances would be a good option . Management may become simpler in a long run since we can stop and start only specific instances that we want and not the entire server . To summarize , it may be easier to manage all SQL instances ( logically ) from a single box then to manually administer the databases and their respective jobs on different servers . I think we save a lot on hardware Infrastructure costs . Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-23 : 04:40:27
|
OK, got it.Are you compromised a bit on how much memory each instance has - whereas if everything was in a single instance [say] then memory is available for whatever it is needed for. Maybe that's true of the "auto" setting, although there must be some [not insignificant] core memory requirement for each instance.But to my mind that's the only "loss" of this route (and the pain to service pack etc., but that's no worse that having 115 SQL boxes).Oh, and one other thing: managing 115 different SQL servers looks better on your CV  Kristen |
|
|
|
|
|
|
|
|
Multiple Instances.