Server requirements for multiple instances | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Server requirements for multiple instances

Hi, I have been asked to provide a solution (architecture/hardware req’s) to host multiple instances of SQL Server 2000 on a single box for a development/test environment. I realize the server will need to be powerful enough to accomodate up to 16 instances. Has anyone installed and administered such configuration? We use Unisys ES7000 Wintel servers fiber-connected on SAN arrays (RAID 10) in production, however I fail to see how we could use lower-end stuff for such a request in development. The rationale for all these instances is for a project life cycle using Microsoft BizTalk 2004 which requires SQL Server in the back-end. So far, I beleive I will need a box with 32 processors, 64GB RAM with multiple drives to seperate the I/O and reduce contention. You input would be kindly appreciated. Cheers!
Hi ya, off the cuff I’d say that a 32 way box is way over the top… you’d be better off with 16 boxes… would be way cheaper and way easier to manage… questions:
– why would you need so many instances, why not just have different databases within one instance?
– how many ‘instances’ would be in use at a single point in time?
– have you considered something like vmware or the ms equivalent Cheers

And, developer people need same box than production?
Luis Martin
Moderator All postings are provided “AS IS” with no warranties for accuracy.
In my environments I always try to give developers a lesser spec box than production, not so much for cost savings but to ensure that any performance issues are discovered during the development cycle and not during the support cycle… ;-0 Twan
Hi Twan, The reason for the many instances comes from all the verticals that I need to setup and the lack of available boxes in our section (budget issues). I need to install 3 instances of SQL Server for development teams, 3 additional ones for testing group, 3 for user acceptance testing and another 3 for integration. These instances will only contain the Microsoft BizTalk 2004 databases. Therefore, since it it specific to BizTalk, I cannot have different databases under a single instance. Moreover, if I allow all these groups to connect to a single instance, this may be prone to deadlocks and data corruption. What is the main reason not to run multiple instances onto a single powerful machine? In a single point in time, I estimate at least 6 instances that will run concurrently. I’ve never used SQL in VMWare or MS equivalent.. is this a better solution? Thanks!
Hi ya, I would have thought 4 dual CPU boxes with about 2GB RAM and 3 instances on each (so a dev, test integrate and UAT box) would be sufficient, and cheaper than a 32-way box…? Or are you saying that you have a spare fully populated 32-way box? this also means that they can be on different OS/SQL versions/patch levels and an administrator on one is not automatically an administrator on another. vmware or the ms equivalent (called virtual server I think) would allow you to have mulitple virtual servers running on the same box but in complete isolation from each other. Again they can be different OS/SQL versions and virtual server administrators would not be able to cause damage to another virtual server Cheers
I have a similar situation here. My production environment is housed on multiple 4-8 processor boxes with multiple instances on each box. A couple have four instances on them. The development environment is 2 4-processor boxes with 4-5 instances apiece. I have 8 – 10 GB of RAM in all the boxes including development. The development environment includes seperate instances for QA, User Acceptance Testing, Release Testing, ER, a mirror of production, and development. I house all server instances on an EMC SAN and use cloning with my warm standby server as the source to refresh the miscellaneous environments. It works fine, since the load is distributed evenly across the multiple instances and not all instances are heavily used at the same time. I have SQL managing the memory and it’s working great. I’ve been using this configuration for about 6 months now. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Hi Derrick, Thanks for your reply, I was also considering EMC SAN for my proposal. So how do you assign the CPU usage? Do you assign a specific processor per instance or you let SQL manage the whole thing with the OS? Also, do all your instances have their own disk spindle with split datafiles/logfiles? Thanks
Each instance has their own instance. Some have split data/log files depending on the requirement of the instance and how the source is setup. Whenever you do this, the clone destinations need the same LUN/size configuration as the clone sources. I have left the CPU to be automanaged by the OS. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.