This blogpost continues my series of blogposts on advanced capacity planning techniques on data migration. In my previous blogpost I told about SQL Server Instance consolidation. This blogpost is about Microsoft SQL Server workload database level consolidation in various capacity planning scenarios such as:
- On-premises (new set of physical servers or virtual machines)
- On-premises to Azure IaaS (set of new virtual machines)
- On-premises to Azure PaaS (Managed Instances vs. set of new Managed Instance pools (Public Preview))
- Azure to Azure (IaaS, PaaS)
Business needs
Drivers for consolidating SQL Server databases are typically:
- Unbalanced workload distribution over SQL Servers and instances
- Need to consolidate databases from small SQL Server instances (data sprawl)
- Need to split database workloads from one big SQL Server / Instance into many (over provisioning)
- Need for SQL estate cost savings.
- Business / data strategy -driven transition to the cloud
Microsoft SQL Server estate TCO and Time-To-Solution
TCO depends on what kind of database consolidation we are doing. If we are consolidating databases from small SQL Server instances into bigger ones, we can achieve up to 50-60% savings on TCO compared to Lift-And-Shift, but Time-To-Solution is longer than in rightsizing, or especially on Lift-And-Shift -scenario. This does not typically mean more than few months’ time, depending on factors such as the migration implementation methods, number of source SQL Servers and databases and how much performance telemetry data we need to have for the capacity planning purposes. Even the most extra time and effort is related to the capacity planning itself, this is also where the TCO savings are being made. Nowadays there are great automation technologies such as dbatools to hasten the migration process itself.
Note! When you are planning to split the database workloads from fewer SQL Servers into multiple new ones, it does not necessarily mean more hardware / licensing costs. This is because if the targeted workloads are evenly distributed, the target system needs are less per server and may still result into savings. I have typically implemented 10-20% savings on such scenarios. Time-To-Solution in these cases equals to the ordinary database consolidation.
Company politics
There is less company politics involved with database consolidation than instance level consolidation. This is because the databases are much more fine-grained than SQL Server instances and therefore, they do not affect overall server performance as much as the instances, so the predictability is somewhat better when having a multitude of databases running on one SQL Server instance: This gives us an extra safety buffer when transitioning the databases one-by-one from crowded source server to less busy target server.
Data center and data platform architecture
Database consolidation is the most flexible scenario from data platform architecture point of view: You can basically create any kind of hardware and virtualized setup, or IaaS / PaaS solution to the public cloud. Also, you can vary the topology from single instance to multiple instance setup, standalone, clustered, or Always On AG -based setup.
About Microsoft SQL Server Licensing
Once again, because the number of CPU cores will in most of the cases be less than the original number due to the harmonized and consolidated SQL Server workloads, also the overall licensing costs will be typically less, anywhere in between 10% to 60%, depending on consolidation method (split vs. consolidate).
Are non-functional requirements met?
From a non-functional perspective, consolidating your SQL Server databases needs a software such as SQL Governor to be able to identify the proportional part of each database performance telemetry data against given SQL Server and instance. After this, there is a great opportunity to improve the overall system availability, manageability, and performance.
Consolidating SQL Server databases – how it is done?
Database level SQL Server consolidation is based on telemetry data trend- and CPU benchmark data -extrapolated calculations over each SQL Server and instance. SQL Governor software can split the database workloads into different behavioral categories based on telemetry data and different prioritization classes based on business rules and then fit the system and user database workloads into target servers, VMs or managed instances. This task is basically impossible to do manually.
Excerpt from SQL Governor: database consolidation.
Excerpt from SQL Governor: database consolidation results.
Perhaps you are planning large scale SQL Server migration? We are here to help!
Jani K. Savolainen
CEO & Chairman,
SQL Governor
