So, you want to consolidate SQL Server instances! This is a laudable aim. More to the point, today you’re going to learn the exact step-by-step consolidation process our local SQL Server Microsoft Certified Master uses in very, very big* shops!
Going on vacation is fairly simple…after you choose the location, book everything, and pack. In the same way, consolidating one server with another is fairly simple…after you do the initial research.
Moving databases, logins, and jobs is no harder than getting on a plane with your bag. It’s all the planning that’s troublesome.
Before how, let’s briefly talk about why.
*1,000 instances? Been there, done that.
Interested in consolidation? Watch our recorded discussion, “Learn SQL Server: Consolidation” Live!
First: why consolidate at all?
To save and to simplify.
A CIO will want to consolidate to save on costs: licensing, hardware, power, upkeep cooling, maintenance, and so on.
A DBA will want to consolidate to save on a lot of important intangibles: downtime, maintenance activities, and complexity.
A very simple (and underpriced) example: Let’s pretend that a single SQL Server instance costs $100 and 100 hours to keep and maintain annually.
If your SQL environment has 100 servers, each of which is running at 50% capacity and utilization, then SQL costs the company $10,000 and 10,000 hours.
Combine all of that into 67 instances running at 75% utilization each, and we’ve brought costs and hours down to $6,700 and 6,700 respectively. What CIO doesn’t want to save 33% of costs? What DBA wouldn’t want 33% more time to devote to innovation?
What’s the expert’s process for consolidation?
Step by step:
- Tier the applications – Each one is either a top, middle, or bottom tier.
- Decide on a consolidation model – All top tier databases on on server? One top tier + 24 bottomr tier on one server?
- Review security requirements – Does anything have the 100% separation requirement?
- Review specifics of data requirements – Data retention, archival, growth, and so on.
- Determine if databases are multi-tenant, or single – Single-tenant applications means you’ll always have new databases.
- Get picky about resources – Which databases or servers take up a ton of resources, whether the destination server can handle multiple instances (if that’s requried), etc.
- Plan physical resources – Disk layouts, varying space needs, speed requirements, etc.
- And, finally…
Today we will cover steps 1 through 4; next week, we’ll cover the rest!
1. Tier the applications
Categorizing your databases is a critical step of consolidation.
You’ll have to decide whether you want to keep any given server to all top tier databases, or whether you’re going to intentionally mix SLAs. To do that, you first have to know what SLA to assign to each DB.
In short, sort them out by importance. For instance, if you get a call from the CEO when an application goes down, that’s a top tier application!
2. Decide on consolidation model
Now that we figured out where we stand, let’s determine which consolidation model works best per server.
- Homogenous model – Each server can have only 1 SLA. For example, Server1 and Server2 host all top tier databases; Server3 holds middle tier; and so on.
- Mixed model – Maybe instead it makes sense to put one really important top tier database on a sever, along with 20 bottom tier databases.
On a homogenous server you can have consistent maintenance and maintenance windows, response and uptime agreements, alerting, and so on. Everything on a top tier server gets top tier service.
The mixed model is another categorization strategy, where you deliberately mix tiers. On a mixed SLA server, you might host a single, super-important database…along with a couple of dozen lower tier databases. The little databases don’t take up much space or resources and are only used a handful of times a day.
The thinking behind the mixed model is that we don’t want to dedicate a whole server to just those little databases, and they’re usually in no risk of fighting over resources with the important database.
Just remember that the lesser SLAs benefit from a mixed-SLA server. If one database on a server is top tier, the entire box is top tier. You have to treat it as a high priority system, because any issue with a smaller database could affect the top tier database.
3. Review security requirements
The first thing to watch out for in regard to security is the question: “Do any of the databases have a 100% separation (dedicated instance) requirement?”
Any new database opens you up for unsafe database settings, like ownership chaining. And it’s really easy for someone to give the wrong permissions to the wrong person.
That’s why some entities, like government, require that they be on a separate instance. So that there’s no possibility for crossover. Some even require that the database is on a completely separate server!
Also ask: “What compliance and privacy requirements are your databases subject to?” If you have any really strict requirements, or very high-risk factors, be careful about mixing databases.
Adding another database means possibly adding code that’s susceptible to SQL injection.
And, because adding another database means possibly adding stupid code doing stupid things. For example, a good security concern would be whether one of these other databases has unsafe code that gives themselves sysadmin membership. Or, as we’ve mentioned elsewhere, stupid code that gives someone access to MSDB, and all the issues that entails.
And, because if you mix teams…that broadens out who has control over that server, that DB, etc.
Let’s say I’ve got my big super-secret tier one database that has to have locked down – security to the max. Then we consolidate, and I move another team’s systems in with it. That team has always managed their own jobs… which means that they have to have access to MSDB to manage their own jobs.
4. Review specifics of data requirements
Review the requirements for space and data retention:
- How often are the databases backed up? How much space will those backups take up?
- What are the data archival requirements?
- How large are the data files? And how much do the files grow? Will the database outgrow the disk space really fast?
- Are there any databases that have a big need for moving space? That is, for databases that grow and drop size a lot.
- Are any of the applications single-tenant? If so, it will add new databases as time goes on. Eventually an instance could end up with 30,000 databases, eating up all the space.
For the exciting conclusion…
Tune in here next week for part 2 of “8 steps to consolidate SQL Server instances“.
Recorded event – “Learn SQL Server: Consolidation!”
This event has passed, but you can watch the recording here: “Learn SQL Server: Consolidation“.
