Multiple Database in One Server Problems

Question

Our company has 5 different databases. None of the multiple databases relate or utilize cross database joins. We have ample resourcing and budget.

Our company is debating between:

• 5 databases on 1 server instance,

• or 5 databases on 5 different servers.

Question: What are some specific problems, obstacle issues which can occur, if we place everything on 1 single server? My background is not DBA, so would like to learn examples I can discuss with team.

Background:

The argument for 1 server instance at company: We have "Resource governor" and "AlwaysOn Availability Groups" with Failover clustering. Resource governor will handle Memory, Cpu, io workloads between databases. So along with AlwaysOn DR, it can handle runaway queries, bad design, memory issues.

Databases are 100GB each, critical company information, 50 million rows minimum

We process 300 transactions/sec

RTO = 10 min

Answer 1

The answer is classic -"It depends !"

Sit down with your stake holders and present then the pros and cons of the 2 options. Being in the business of client hosting, I have faced this situation and below is my view point :

None of the multiple databases relate or utilize cross database joins. We have ample resourcing and budget.

This means that the dbs are decoupled or independent. I would say, club the dbs depending on the criticality and uptime SLAs and then distribute them between 2-3 servers (VM would be another option as well). This way, you dont put all your eggs in the same basket.

Why ?

• Putting all 5 dbs on one server will be a single point of failure. An issue with a single DB puts all your other DBs at risk. Isolate your risk as much as possible but that will cost you additional licensing and maintenance overhead which you have by saying - ample resourcing and budget.
• Even if you have Resource Governor enabled, it does not govern buffer pool. It only governs "working memory of a query e.g. sort, hash etc". In 2014 and up, you can limit IOPS using Resource Governor.
• You have one solution for a given server hosting all your dbs to do backups - full and log, integrity checks, index maintenance. If there is an issue with your server and that job fails midway, other dbs will be impacted except you have inbuilt mechanism to detect and fix the issue and continue with the rest.
• An external issue with OS affects all your estate being in one place.
• Even with AGs, you have a fix amount of worker threads and they get shared with all your dbs on a single server. Having dbs spread out depending on the criticality will alleviate that problem.
• Tempdb is one per server. So imagine all your dbs being on the same server might be a bottleneck.
• Problem isolation will be difficult if you have one server serving all your dbs.

I have written a sort of similar answer Which is better: one database per application, or just one database?

Answer 2

I agree it does depend, but there are some questions that @Kin is not asking. These are important when you are deciding how to move forward on a project.

• What is your budget for this project?
  • What version of SQL best fits my needs for this project?
• What does your infrastructure look like?
  • VM, Spinning disks or solid state, if VM how many cores are you willing to pay for (licensing question)
• Will you need to replicate your data off for reporting purposes (best practice is to not let reporting queries run against your OLTP database (yes, there are exceptions to this))?
• How do you plan to handle security for each database?
  • Is there any sensitive data in these databases? If so, how do you plan to handle that.
  • Is there one account that is used to access each database, or are there

This is how I would start, as I have about 20 plus servers that fit your requirements.

First option would be one server

• 16 cores on 2 sockets
• 96 gigs of ram (16 gigs to the OS and 80 to SQL)
• Different drive for OS partition
• Different drive for SQL installation (not os partition)
• Different drive for your data files
• Different drive for you log files (minimum of 100 gigs)
• Different drive for tempDB datafiles (minimum of 100 gigs)
  • minimum of 8 tempDB files or more depending on need (start these guys off at 2.6 gigs and allow them to grow at 16mb increments)

Second Option: 5 individual instances

• 1 main IP address for the server, and 5 additional IPs for each instance
  • This allows you run each instance on that IP on the default port of 1433
  • Then configure each instance with a port like 6019 so you can connect via SERVER\Instance.
  • Assign a DNS alias to the IP address for each of the 5 instances so you can configure your applications to connect to that instance via an alias or use servername\instance
• Adjust memory to 136 gigs. Assign each instance 24 gigs of ram (adjust accordingly based on load)

Also make sure to adjust your Cost Threshold for Parallelism and Max Degree of Parallelism settings for both options. Once you have your server in place work with your SA and Network teams to configure and setup your HA and DR plans.

This is how I would go about it. :)