While it’s easy to create a new database or SQL Server instance, it’s much harder to know what to do with it years down the road – and it’s inevitable, there will be a build-up of unused systems and databases with unclear ownership over time.
Should you remove unused systems? Move lesser-used systems? If so, what resources can be shared? Then there are the SQL Server licenses that are really only needed for one or two instances across the whole environment. Â
Database consolidation can be a logical solution – but it’s not an easy one. Consolidating a database environment often becomes a large project because applications, users, dependencies, resource demands, and feature requirements must all be understood before anything changes.
Implementing database consolidation can reduce costs, improve efficiency, and simplify management. However, it can also cause downtime and data loss and take years to complete.  A successful database consolidation project begins with an assessment, so you know the scope of the project and how you’ll execute it.
To get everyone on the same page, we’ll start by defining what we’ll cover before moving into best practices for planning database consolidation.
What is Database Consolidation?
Database consolidation is the process of reviewing a database environment and reorganizing suitable workloads onto fewer, appropriately sized systems. For each database, the assessment should lead to one of three broad recommendations:
- Consolidate it with other compatible workloads.
- Keep it on a separate database instance.
- Place it offline and investigate whether it can be retired.
The plan helps you consolidate to the minimum number of servers and create a more manageable environment. Normally, consolidation and decommissioning work hand in hand. Decommissioning removes databases that are no longer needed while consolidation determines how the remaining workloads can be grouped, separated, upgraded, and licensed appropriately.
Why Database Consolidation is Difficult
The technical act of moving a database is only one part of a consolidation project. The more difficult work often happens before migration begins.
It’s common to have a current inventory of your SQL Server environment, but usually no one knows the stakeholders involved with the usage. Database teams still need evidence to answer practical questions:
- Is the database still used, and how frequently?
- Who owns the application or business process behind it?
- How much CPU, memory, storage, and database activity does it require?
- Does its workload vary by time of day, month, quarter, or year?
- Which SQL Server version, edition, and features does it depend on?
- Can it share resources safely with other databases?
Workload compatibility matters. For example, disk-intensive databases on the same host could compete for the same storage resources and memory-intensive workloads create similar contention. In some cases, combining workloads with different resource profiles can improve utilization; in others, a demanding or unpredictable database should remain isolated.
It’s almost impossible to use a spreadsheet or one-time performance snapshot to answer these questions reliably. Continuous monitoring over a representative period provides a stronger basis for deciding what should be retired, consolidated, or kept separate.
Database Consolidation Best Practices: A Five-Step Assessment
Before deciding where a database should go, you need enough evidence to understand how it behaves and what it depends on. The following five steps turn that evidence into practical consolidation decisions, from building the initial inventory through to deciding which databases can safely share resources.
1. Build a Complete Database Inventory
Before deciding what to do, you need to know what you have. Start by inventorying what exists.
The inventory should include SQL Server hosts, instances, databases, versions, editions, storage use, clustering, and availability configuration. Unknown or undocumented systems must be found before the target environment can be designed.
In addition, you’ll need to track the applications that connect to each database. If an application connects directly to a physical server name, moving the database may require configuration changes across multiple clients. Finding the responsible owner and coordinating those changes can take longer than the database migration itself.
Best Practice:
How you find the systems depends on what software you’re using. The old manual method involves spreadsheets and interviews. As a best practice: keep the inventory current through ongoing monitoring rather than treating it as a spreadsheet created only for the consolidation project.
Assigning each database or application a unique DNS entry allows applications to connect through an alias rather than being permanently tied to a physical server. This can be introduced before migration with little or no effect on uptime and makes both the current move and future migrations easier to manage.
Here’s a checklist to follow before migration: find these six items for each database you migrate.
- Document the application owner
- Connection method
- Dependencies
- Maintenance requirements
- Validation process
- Rollback plan
dbWatch has some jobs made especially to help with the database inventory. First there’s Autodiscovery, which can help identify instances and databases that are not already included in the documented environment. The dashboards in dbWatch provide a practical inventory of server names, versions, editions, Always On participation, and cluster information.
2. Find Databases That Are No Longer in Use
There’s no immediate way to assess usage. There are two options, and they both take a considerable amount of time.
First, there’s the social method of tracking down individuals who ‘own’ the database, which can be problematic. Jane from accounting, who used to own the databases, has moved to a different company; no one knows where her notes are, and John from sales should have been keeping track but didn’t write things down.
Second, there’s the gathering data constantly over time method, where monitoring jobs gather indicators such as connections, data changes, database activity, and activity observed over time. Here the decision is simple: activity = keep it, no activity = delete it.
Nevertheless, to justify deletion, you’ll need to walk through the decommissioning steps, like placing a database offline for an agreed observation period, keeping a backup, and documenting the process.
Best Practice:
Review potentially unused databases regularly – monthly or quarterly, depending on the size of the estate – so decommissioning becomes routine database operations rather than a large clean-up project.
When you turn on the Usage Tracking Job in dbWatch, it identifies the databases with little or no observed use. On the databases with low or no activity, you could consider decommissioning or consolidation. We recommend tracking for at least six months before making decisions.
3. Measure Workload and Resource Requirements
To anthropomorphize, databases are like people with personality traits. If you were trying to match roommates on a work trip, you’d need to understand which traits match with another. Some people are more introverted and might need a room to themselves or to share a room with another quieter person, just like databases need more space. Other people might be very socially active and be happy to share with a few others.
When consolidating, or making databases be roommates, so to speak, each database should be noted on its size, CPU, memory, storage, logical reads, and workload patterns for 1 to 3 months. Once you know the behavior of the database, you can choose if it can or should be combined with another.
Best Practice:
- Assess allocated space, activity, and resource profiles.
- Avoid placing workloads that compete heavily for the same constrained resource on one host.
- dbWatch workload and resource monitoring can combine server- and database-level information and support recommendations about which databases may share an instance and which should remain separate.
- The consolidation method in the source material uses more than 100,000 logical reads per second as one possible indicator that a database may warrant a separate instance. This is not a universal threshold; application requirements and the wider workload profile must also be considered.
dbWatch gives you historical workload and resource data at both database instance and individual database level, making it easier to spot peaks, competing resource demands, and databases with very different workload profiles. That helps you avoid consolidating workloads that look compatible on paper but behave very differently in production.
4. Review all your Versions, Editions, and Feature Usage
You’ll also want to be careful with Microsoft SQL Server licensing and how that affects compatibility. An environment may contain several Enterprise Edition instances, and everything on that instance could require Enterprise functionality – or they may not need it. Be careful; sometimes an index is set up in a way that requires enterprise features, but those features aren’t needed. It’s simply that someone read an article about a fancy command and tried it. Just rewrite the index without the enterprise features, and you can drop it.
Before selecting a destination, identify the current SQL Server version and edition, compatibility requirements, Enterprise Edition features in use, and whether those features are still necessary. Group databases that require Enterprise Edition on appropriately licensed servers where their workloads are compatible.
Feature usage should also be investigated: an Enterprise-only feature may be enabled even where the database no longer gains a meaningful benefit from it.
Best Practice:
- Assess where you need Enterprise Edition.
- Use evidence to avoid paying for it where it is unnecessary while retaining it for workloads that depend on its capabilities.
In dbWatch, there are monitoring jobs that check if you’re using Enterprise Edition. When the job sees Enterprise Edition usage, it checks if the components related to that license (i.e., partitioning) are also in use.
5. Classify Each Database
Once all the relevant information has been collected, assign each database a preliminary recommendation:
- Consolidate: compatible databases with low-to-medium workloads may be suitable for a shared instance. Databases belonging to the same application may also need to remain together.
- Separate instance: high-load, unpredictable, or business-critical databases may require isolated resources.
- Not in use: databases with no meaningful connections or data changes should be placed offline and investigated before retirement.
Best Practice: document the evidence and owner decision behind each classification so that the migration plan can be reviewed and defended.
dbWatch consolidation reporting can combine the recommendation with allocated and used space, database count, server activity, and database activity. The report supports the decision; it does not replace the judgment of the DBA or application owner.
Make Consolidation Part of Database Operations
Database consolidation should not have to become a multi-year clean-up project that starts again as soon as it is finished. The same monitoring used for day-to-day database operations can keep the consolidation assessment current.
DBA teams that make consolidation part of the regular schedule usually follow these steps.
- Database teams decide how long databases need to have ‘no activity’ before they should be considered for decommissioning or consolidation. Every three to six months, they review those databases that have had no activity and decide which action to take.
- They begin the decommissioning process for suitable candidates, and identify active workloads that could move to newer or better-utilized servers.
When new capacity is introduced, the existing evidence helps select migration targets that will deliver the greatest benefit while reducing load elsewhere.
dbWatch supports this workflow through five monitoring functions used across the assessment: estate discovery and inventory; database-use monitoring; server and database workload monitoring; SQL Server edition and feature-usage monitoring; and post-migration performance monitoring and reporting.
Before you migrate, assess the environment. The quality of the consolidation plan depends on the quality of the evidence behind it.
