I work for a not for profit where we are looking at bringing in some better practices when we are managing our SQL databases.
Basically I am looking for some advice here. Should we have one well resources SQL Server with multiple instances for each critical databases? or should we have individual SQL servers for each critical databases?
Basic background to our setup is:
The databases in question are central donor database which is about 30gb with 70 users Data warehouse system for segmentation etc... is about 15gb with 5 users Our case management database which is about 5 gb with 100 users Our organisational accounts system which is about 5gb with about 100 users Reporting Services for business critical reports We have about 3 smaller inhouse developed databases which are used mainly by us in IT Then we have about 4 or 5 IT system databases for monitoring system and network performances.
Currently we have majority of these systems on one large SQL server with multiple instances. We have basically split each business critical database into its own instance. We have also put all the IT system monitoring databases into its own instances for each of management.
I feel a little uneasy about how important some of these databases are all sharing 1 single server. It feels like we are putting all our eggs into one basket. I am also concerned about performance issues.
We have a fully virtualised environment so creating new servers isnt a real issue but I wanted to get some basic feedback from my fellow professionals out there. Any advice would be much appreciated.
I don't like having multiple instances on the same server. My main plaint is in allocating the memory pool (min/max server memory) per instance. I'd rather have the different databases within a single server instance. Having said that, the question becomes do you need multiple servers. It's usual to separate transactional processing databases and reporting databases onto separate servers but none of your databases are that large. What are the demands on the different databases in terms of response times and frequency of use?
It's just an opinion, but at least it's mine...
================================================= The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen
Another factor is uptime consideration. For instance, if one of these db is supporting a web app that has users from all over the places connecting to it, then rebooting the box because of one of the other db had a memory leak becomes harder to do.