With server hardware getting more and more powerful, consolidation projects are becoming more and more popular. You can now run many SQL Server databases on a single large database server instead of running them on lots of database servers. Let's look at the reasons for consolidating and what you need to consider when planning a consolidation project.
Reasons to Consolidate
In the long term, a SQL Server consolidation might financially help companies a few different ways:
- Consolidation can reduce licensing costs if you're using SQL Server 2012 Enterprise. The Enterprise edition has moved from socket-based to core-based licensing. Having servers with more cores than they actually need isn't a luxury that most companies can afford anymore.
- Consolidation can reduce power costs if you have your own data center. By replacing five or six servers with a single server, you can save on the power costs associated with running and cooling those servers.
- Consolidation can reduce rental costs if you're using a rented data center (e.g., colocation facility). Each server that you remove from the rack saves money, because you might be able to remove racks from your rental agreement. Or perhaps more realistically, you won't need to add more racks as your data needs grow.
Consideration 1: Consolidation Strategy
When planning your consolidation project, you first need to figure out how you want to consolidate the databases. Possible strategies include:
- Having multiple instances, with each instance on a separate virtual machine (VM)
- Having multiple instances on a single physical machine or VM
- Having a single instance on a single physical machine or VM
There are a couple of factors that can help you decide which consolidation strategy will work best for your environment. The first factor is SQL Server version requirements. If you have several applications that require older versions of SQL Server (e.g., SQL Server 2005) and some that can be upgraded to SQL Server 2012, having a single instance won't work. You either need a couple of instances on the same machine or a couple of VMs, each supporting a different version of SQL Server.
Related: Wait Before You Consolidate
The second factor to consider is the permission requirements of the applications running on the SQL Server instances. If an application requires that the login to the server be a member of the sysadmin fixed server role, you probably don't want to place that application's databases on an instance with other applications because that application could access the other data. Many third-party vendors say that their applications require sysadmin rights, but often the applications don't. With a little persistence, you can find out from the vendor the rights that are actually needed for an application to function correctly.
Consideration 2: Hardware Requirements
When planning a consolidation project, probably the most important consideration is whether the consolidated server's hardware is able to physically handle the new combined workload. To ensure that the server can handle the workload, you need to check storage I/O, memory, and CPU requirements.
Storage I/O requirements. Storage is pretty much always going to be the big bottleneck, unless SQL Server is configured using all solid state disks (SSDs). Thus, you need to take great care when configuring a consolidated server that'll be housing many databases from various servers.
You should start by looking at the I/O workload for each of the current servers. The amount of I/O that's currently being generated probably isn't going to go down after the databases have been moved to the new server, unless the current servers are woefully misconfigured.
After you've gathered the current metrics for all the servers to be consolidated, you need to combine those numbers. For example, if there are five servers being consolidated and each generates 500 read I/O operations per second (IOPS) and 200 write IOPS, the consolidated server will need to support at least 2,500 read IOPS and 1,000 write IOPS. Will the consolidated server's storage system be able to handle that much I/O? Can the host bus adapter (HBA) or PCI bus that will be used to talk to the storage system be able to handle that much I/O? If the answer to either question is no, the project needs to be stopped until those problems are resolved.
Memory requirements. When consolidating multiple database instances on a single server (or instance), memory needs to be taken into account when designing the consolidation solution. Otherwise, the databases might end up with less buffer cache and less plan cache than they had before, which will increase the amount of I/O that's being generated or increase the amount of CPU power needed by the databases.
For example, if you consolidate five instances, each of which has 16GB of RAM, on a single server that has 48GB of RAM, many of the databases on the consolidated server will have less buffer cache and plan cache than they had previously. Instead, you should consolidate these five instances on a server that has at least 80GB of RAM (16GB per server ´ 5 servers) so that no database will have to shrink its buffer or plan caches.
Memory consolidation is one reason why the consolidation strategy might need to be changed from a single instance on a single server to multiple instances on a single server. When all the databases are being consolidated into a single instance, SQL Server treats the buffer pool and plan cache as a single resource. Thus, there's no way to control how much memory each database has access to. If you want to guarantee that each database has a specific amount of memory available, you need to put each database on its own instance.
CPU requirements. The CPU workloads of the current servers and the CPU capacity of the new server need to be evaluated, much in the same way that the storage I/O capacity is evaluated. Without reviewing the current workloads of the servers to be consolidated, there's no way to know whether all the databases will fit on the consolidated server. You can obtain some of the information needed by looking at the % CPU Used counter in Performance Monitor on the current servers. You also need to find out the number of cores on the current servers and the amount of processing power that each core has.
Consideration 3: High Availability Requirements
In a server consolidation, you need to group together the applications with similar high availability requirements. This has probably been done already, with the applications grouped into tiers (e.g., tier 1, tier 2, tier 3) or grouped using some other strategy. You'll probably end up with all the tier 1 applications on one server, all tier 2 applications on another server, and so on.
When configuring these applications and looking at their recovery time frames, you need to look at the high availability options for the SQL Server version and edition that you'll be using. For example, if you need multisite failover with no possibility of data loss on SQL Server 2008 R2, using asynchronous database mirroring wouldn't be the correct solution. You would need to use either synchronous database mirroring or multisite clustering (using a stretch Virtual LAN -- VLAN) with storage array synchronous replication.
Consideration 4: SQL Server Edition Selection
Microsoft would love if everyone purchased SQL Server 2012 Enterprise for all their database applications, but the reality is that not every database application needs it. You need to take a long hard look at the applications that'll be placed on the database server you're consolidating to see if any of those applications actually need the Enterprise edition. Although having features such as AlwaysOn Availability Groups, online index rebuilding, and table partitioning are nice, typically tier 3 applications and even some tier 2 applications don't need these features, so the Standard edition can run these applications without issue.
Consideration 5: Application Maintenance
One consideration that people often don't think about is that consolidating databases can make scheduling maintenance windows much more complex. Before consolidating applications that are used by different business units on a single server, it's highly recommended that you meet with the managers of the affected business units and come up with a regular maintenance window that works for everyone. Although you might not need the maintenance window every week or month, having the window available and agreed upon in advance will make taking the system down for patching and other routine maintenance much easier.
Consideration 6: Partially Contained Databases
SQL Server 2012 introduced the new partially contained database functionality. It has two features that can help with consolidation projects: contained users and temporary table collations.
Contained users. This feature lets you create a user within a database and give that user a password. As a result, there's no longer a need for an instance-level login. When an application takes advantage of contained users, moving that application to another server is much easier because you don't have to copy the instance-level logins to the new server. Although this feature won't necessarily help you with your current consolidation project, it'll help you with future consolidation projects if you need to move that database to another instance.
Temporary table collations. Unlike contained users, temporary table collations can help you with your current consolidation project. The temporary table collation feature gives you the ability to host databases that have a different collation than the actual instance, without giving errors when attempting to join temporary tables with physical tables in the database engine.
To understand how this feature works, you need to understand why these errors occur. When a user creates a temporary table, it's created in the tempdb system database and therefore uses the tempdb database's collation. If the user attempts to join the temporary table with a table in a user database, a collation error occurs because the user database has a different collation than the tempdb database. However, if you put the user database into partial containment mode, temporary tables are created with the collation of the user database instead of the collation of the tempdb database, so a collation error doesn't occur.
Consideration 7: User-Defined Server Roles
When you're consolidating several servers together, you usually end up with a mish-mash of permissions that need to be migrated and managed. In SQL Server 2008 R2 and earlier versions, there isn't an easy way to do this because instance-level permissions must be granted to logins. In SQL Server 2012, this problem is resolved with the introduction of user-defined server roles.
User-defined server roles let you create a role at the instance level and grant instance-level permissions to that role. The logins can be placed within the role, making instance-level permissions much easier to manage. For example, if you have various development teams that need the VIEW SERVER STATE permission, you don't need to grant that right to each team separately. You can simply create a user-defined server role named View Server State, grant that role the VIEW SERVER STATE permission, then put all the developers into that role.
User-defined server roles also make the administration of the new AlwaysOn Availability Groups easier because you can grant the rights to manage the AlwaysOn Availability Groups to a user-defined server role. That way, administrators who aren't members of the sysadmin fixed server role (e.g., junior DBAs) can manage Availability Group failover without being given other permissions they shouldn't have.
Not an Easy Task
Although consolidating databases on a single server might seem like an easy task, it typically isn't. The difficulty isn't due to the task's technical complexity but rather due to the fact that there are so many "moving parts" that need to be considered. Any one of these parts can dramatically hurt the success of the consolidation project if the associated potential problems aren't addressed.
