Multiple SQL Server Instances or Not?

This is often a challenging question as many companies undergo consolidation initiatives. Many organizations have experienced challenges from physical server sprawl and look at multiple instances as a potential solution. This can be a win but it can also be a headache for a DBA or Architecture team.

This is a wiki article and I encourage you to jump in and make some edits based on your experiences. It's easy to create a log in and just as easy to make some modifications.

Why Would You Use Multiple SQL Server Instances?

• Server Sprawl - Let's face it. Our data center space isn't growing. The cost of power and cooling isn't going down. Our management is often asking us to reduce servers at all costs.
• License "Sprawl" - License costs for SQL Server currently are by machine, not by instance on machine. Doubling up the instances on a machine can be attractive to a company watching the bottom line.
• Underutilized Hardware - Maybe you didn't spec out that new environment properly and you have a powerful box sitting there, not taking advantage of all of that money thrown at the hardware. At least that is the perspective of management or an infrastructure team.

Why Wouldn't You Use More Than One Instance?

• Unknown Future - Perhaps you have a potential to add load any day now to a new instance, it's being talked about just not being realized. Loading up the instances can cause issues down the road if not planned properly.
• Resource Hogs - Maybe you already have a well utilized instance on a box. If you are trending with high usage on the resources for an instance, you should think twice about adding to that load.
• Security/Audting/Regulations - Let's face this fact also. The data we maintain in our databases is more valuable than the hardware it sits on often. Perhaps there is processing power and memory available but sharing financial databases with an environment that needs more loose permissions because of a vendor that won't budge means a potential risk of that critical data.

Consideration Points

What should you think about before embarking on a multi-instance crusade? When do you have the ammo you need to start tearing down multi-instance environments you have inherited?

First off, let me say there is no right answer. This can fall squarely into the, "It Depends" category. What would be ideal for one organization is not for another. Empirical evidence speaks loudly and, as with any advice, you must test to see what the answer is for 'your' environment.

Got SQL Benchmarks?

If you don't do benchmarks of your environment at regular intervals, stop reading this and get into a practice of performing benchmarking. There are plenty of tools, both paid and free, to allow you to trend resource utilization and performance on your database servers. Without this information, how can you make a good decision?

Think of What is Shared

Hopefully you understand the basics of separation of I/O. If you know even the basic thoughts of separating your Logs, Data and TEMPDB data files, you are ahead of the game. When it comes to multiple instance you will likely want to continue giving separate I/O to each instance. Other resources are not so easily divied up.

CPU Resources can collide with multiple instances. You have choices, you can use processor affinity masks to separate CPU cores/sockets to separate instances. The pro for this is you are keeping each instance from colliding with each other on CPU usage. The con is that you are not allowing each instance to take advantage of CPU resources during the others downtime. Perform benchmarks and see how your servers are doing. If your SQL instances are already having resource allocation headaches, going multiple instances is likely only going to increase your headache unless you are really spending a lot of money on hardware.

Memory This is important. You need to leave enough for the OS. You need to make sure that each instance has enough without robbing the other, this likely means setting a max on each instance. Do you use SSIS, Linked Servers, Other applications on the DB Server? These also compete for memory. Do you have enough memory to handle all of this? If not, you will want to address that before installing a lot of SQL Instances side by side.

Network Access Yes servers can have multiple NICs and that may be a necessity based on your application. Even with the 1Gb network adapters that are mostly common in our data centers, you want to make sure that no one instance can affect others. Look at benchmarks and pay attention to wait types. Are you fine with bandwidth? If you are potentially having issues here (a common theme), you will want to address before moving on.

I/O A bit ago, I said that I/O should be separate in most multi instance scenarios still. So why did I mention it here? The I/O may be separate on the SAN/DAS/NAS/etc. but what kind of paths do you have to those disks? Again many of the methods of connecting to disk today are performant with wide enough pipe for multiple instances but make sure you will not overload your channels to I/O with all of these instances.

Think of Good Candidates

Perhaps you have some instances that are slamming at night during loading/processing and then get quieter during the day. Then you have other systems that are hammered during the day with heavy OLTP loads but quiet at night. This may be a great candidate for sharing two instances on one box. You will save hardware costs, license costs and make maintenance a bit easier perhaps.

Maintenance Windows

In the above example, we talked about the improved maintenance of one less server. That is great but what about the flip side of the coin with the above example? You now have a server with a potential for a diminished maintenance window. Are you a 24*7 kind of shop with no weekend down time? Perhaps you currently can do maintenance on that batch load system during the day and on the OLTP system at night. Combine the systems and your options just got more complex. I'm not saying don't combine, but think of the impact to maintenance windows.

Touchy Applications

We've all supported them. Need lots of TLC, maybe even have a lot of patching/vendor interaction. Do you really want to load other production database instances onto an environment that has some vendor code running (yes, it is best to have only the DB on the DB server but not all apps roll that way). What about the frequent patching/support from the vendor? Do you have to do a lot of server reboots because of that software? Now you have a more complex notification chain and approval process for work to that one physical machine.

Testing, 1.2.3

No matter what you decide and what you contemplate, testing is key. If you are going to do something to production you need to do it to Dev, SYS, UAT, etc. You need to test it for common load, normal workdays, maintenance processes (what is the impact of taking 3 instances that each have the same maintenance window with index rebuilds all occurring on the same box? Test it and see for your environment), etc. Take all of the scenarios and test the installation, load and compatibility. Test it for awhile, consider running a parallel production setup and mimic production like activity for a couple weeks at least to get a good sense of activity. Making a decision to move to multiple instances is not one to be taken lightly. Empirical evidence speaks volumes here. It will tell you if things will work or not and it will provide a documentation trail showing the scenarios you tested.

Summary

I'm not trying to scare you. There are a lot of places taking advantage of multi instance servers with great success. They took the time to understand their load, look at benchmarks and see what kind of room they had. You can have the same success if you apply careful thought to the process. The fact that you are here, searching for info, is a good first step.

Related Reading about SQL Server Instance Planning

• SQL Server Consolidation - things to consider before attempting a SQL consolidation project.
• Perfmon Counters for Consolidation Planning - learn which counters to monitor first.
• Virtualization Killer Features - when choosing consolidation or virtualization, find out what advantages virtualization brings to the table.