Vinod Kumar (Blog home)

Following the post around Multi-tenancy, there were interesting comments that have come my way to write more on such topics. Well, thanks for reading and dropping a line. In this post, thought will write around another interesting yet very common topic which I get a chance to discuss with customers – Consolidation of Servers. Though these can be driven by very specific business reasons, these do need some thought before implementing.

There are a significant number of manageability, availability, performance, scalability, and political considerations when deciding between dedicated (physical/virtual), instance level, database level or schema level consolidation. Fortunately, most of these are covered in the SQL Server 2008 Consolidation Guidance technical article. The article comes complete with excellent explanations and decision trees, and though it’s primarily focused on the decision between virtualization vs. instance level vs. database level – it is noted in the article that “Other possibilities include further optimizations on an existing approach such as schema-level consolidation. The key decision factors are similar to the higher-level consolidation options mentioned previously, so this paper will focus only on those.”

As mentioned before, business might see Consolidation from (not in any specific order):

1. High-Availability – Instead of giving redundant multiple HA databases, some times consolidating will give an advantage for all the applications on a better server with a common HA option (like clustering).
2. Centralized Management – Customers look at this as an opportunity to consolidate all the departmental applications and more so consolidate the DBAs inside their organization.
3. Cost Saving – This is most likely the first thing that business sees as an opportunity. Ultimately, they want to look at maximizing the utilization of the hardware they have bought or more so get one beefy server to manage 10s of applications database away from outdated hardwares.
4. Risk management – As discussed on above, centralization means it also standardizes the way DB code is developed, managed, deployed and maintained. Also it becomes easier for servicing, implementing processes and automated system administration.

Other Consolidation Considerations

Well, all the above reasons are valid, there are more that are missed between the lines.

1. Operational Cost – Consolidation on newer hardware means reduced servers and power savings can also be achieved. And moving virtual also means you are saving on hosting costs.
2. Increased uptime – Server consolidation makes it more economic to provide high-availability configurations and dedicated support staff. Organizations also benefit from implementing better storage management and service continuity solutions.
3. Predictable Performance – Moving to a more standardized systems means we can assure a more predictable performance and can behind the scenes implement isolation of resources per application and a DBA can go ahead and implement compression like techniques without change in application code.
4. Integration Benefits - A consolidated platform provides for easier, consistent and cheaper, systems integration, which improves data consistency and reduces the complexity of tasks such as extract, transform, and load (ETL) operations across systems.

Technical Considerations

For code already written, migrating to an alternative solution than was originally designed may require rework by developers – i.e. if they’ve already written their code in database A to access database B objects with 3-part name identifiers, they would have to make accommodating changes. Additionally, any database users with a database-wide role (db_datareader, db_ddladmin, etc) that are consolidation candidates might have to be changed.

Though schema consolidation with a single database is nice for logical groupings of data that benefit from being kept in synch [especially during a recovery] or must maintain referential integrity, the thought of combining unrelated entities raises all kinds of management and political questions.

Unrelated applications might have differing availability, maintenance, and isolation requirements – and combining them at a database level complicates this. And if these are financially sensitive data then the process of auditing or tracking becomes even more harder than ever.

Finally, It is great to collapse multiple databases from applications, but if these applications are in-house applications you are good. If these were third party applications – then application compatibility needs to be checked. Certifying the application to Versions of database, x86 Vs x64 architecture, version of the OS etc are all extra activities to be handled – not to mention on dependency services.

Misc Considerations

Well, Planning is the name of the game. Loved to see this hidden somewhere in documentation – a Checklist. Worth a read and consideration, though this is not complete – you will need to create one for your infrastructure needs.

But there are tons of other things that also come to mind -

Special Security context of databases
Limitations or dependencies that prevent consolidation (Agent jobs, Maintenance Plans, SQLMail, ETL or others)
Third party add-on dependency for application
OLTP VS OLAP features and frequency of use
Dependencies of Server / Instance names (hard coded inside application)
How many databases for a single app and proximity of all the dependent DBs
Data growth rate, Data Retention Policies followed by Archival
Backup windows and Special Backup technologies
Peak usage / Low usage time windows of each server
Replication frequency, duration, and volume
Specific Connectivity requirements (Protocols, SSL and others)
Internet / Public Access vs. Internal only
SLA’s to business units for uptime

In the 60 page whitepaper on Consolidation Using SQL Server 2008, the option to collapse multiple databases into a single database [via multiple schema management] is essentially written off citing challenges with security, object naming conflicts, performance issues, etc. It leaves off with an ominous warning that schema-level consolidation should be “used carefully” – but I’d just avoid it all-together for unrelated applications. And you have more dope above on why I say so.

HomePage for SQL Server Consolidation and Virtualization

If you did read it this far, feel free to drop a comment with suggestions if any. Obviously, your experience are also unique and valuable.