SQL Server Consolidation Planning – SQL Server Tips

As many organizations are in the process of upgrading and migrating their databases, it is a great opportunity to save money, improve performance and increase the database service offerings for the organization. Typically, the organization is expecting a certain level of SQL Server consolidation during the project to help reduce hardware costs, licensing, hosting costs and administrative costs long term. But how do you approach an upgrade and migration project, so you get the highest consolidation ratio at the end of the project? That’s where the SQL Server consolidation planning becomes a integral first step.

Why would we want to consolidate if it adds complexity to the project? Think about the licensing savings, reduction in hosting costs and administration times if you were able to consolidate your environment by ~25% to ~50%. The average ROI for an environment with 100 SQL Server instances is in excess of $1 million in today’s money that can be repurposed for other business initiatives.

In this article, we are going to share some of the lessons learned at Fortified about SQL Server migrations. We have upgraded and migrated over 10,000 instances over the years and have an average consolidation ratio or ~50% and at times have exceeded 75% in certain environments. In later blogs, we will focus on the design, execution, and post-migration support for SQL Server migration and upgrade projects.

The SQL Server Consolidation Planning

Before we can start planning for the SQL Server database consolidation project, we need to understand the business and technical goals and objectives of the project. Typically, the business goals are to gain new features and capabilities around data while the technical goals might be to save money while upgrading to supported technologies.

The security team typically adds in requirements like encryption, updating the security models and/or enhancing the network security controls. At the end, we need to document these goals and objectives into a single list so they can be interpreted and understood when we plan the project and design the environment.

To get started on planning for consolidating SQL Server, we need to capture metadata (data about the environment), runtime data and configuration data. The data is important because it allows your team to fully understand the environment, architecture, database workloads, performance, and integrations.

Another valuable use of the data is as a performance baseline for each SQL Server single instance which can be used post-migration to show the before and after performance for each database application. The performance baseline data is important so you can validate whether the performance is the same or better than before the migration.

Now you’re probably wondering, “What is all the data we need?” and it depends on the environment but below is a high-level list of data by category which we would recommend:

• Inventory data
• Host list
• SQL Server list
• SQL Server jobs list
• Databases list
• External database objects
• Other installed database features
• Configuration data
• Host properties
• Operating system properties
• Instance properties (sp_configure)
• Instance properties (sp_configure)
• Database properties
• Volume Metrics
• SQLServer:General Statistics\User Connections
• SQLServer:SQL Statistics\Batch Requests/sec
• SQLServer:Databases(*)\Transactions/sec
• SQLServer:Databases(*)\Log Bytes Flushed/sec
• SQLServer:Memory Manager\Target Server Memory (KB)
• Process(sqlservr)\IO Read Bytes/sec
• Process(sqlservr)\IO Write Bytes/sec
• Process(sqlservr)\% Processor Time
• Network Interface(*)\Bytes Total/sec
• Performance Data
• Processor\% Processor Time
• Processor\% Privileged Time
• System\Context Switches/sec
• System\Processor Queue Length
• SQL Server – sys.dm_os_schedulers (CPU)
• LogicalDisk(*)\Avg. Disk sec/Read
• LogicalDisk(*)\Avg. Disk sec/Write
• SQL Server – sys.dm_io_pending_io_requests (IO)
• Memory\Available Mbytes
• Memory\Pages/sec
• VM Memory\Memory Ballooned in MB
• SQLServer:Buffer Manager\Buffer cache hit ratio
• SQLServer:Buffer Manager\Lazy writes/sec
• SQLServer:Buffer Manager\Page life expectancy
• SQLServer:Memory Manager\Memory Grants Pending
• Security and Integrations
• OS and SQL Server Logins
• dm_exec_connections (Capture over time)
• dm_exec_sessions (Capture over time)
• SQL Server Linked Servers
• Cross database queries (scan code)

Once we have the data, we are ready to begin working towards optimal efficiency. Most of the time, we export the data to a SQL Server database so we can easily execute code against the data allowing us to ask different questions. Our goal is to better understand the environment which allows us to estimate the time to migrate, the migration approach and start to design the environment.

For example, if the environment has 100 stand-alone SQL Server instances that support 1 – 3 applications per instance, have no cross-database or cross-server dependencies and each instance is consuming about 30% of the server, the migration and consolidation plan would be low risk, less effort and yield higher consolidation ratios.

Now compare that story to an environment with 25 SQL Server instances with an average consumption of 60%. Each SQL Server instance has between 100 and 250 databases per instance and 15 linked servers on each server. The majority of the code leverages cross database queries on the server to retrieve data from business reports.

This environment has a higher level of complexity, is riskier to migrate and would require a detailed dependency analysis to determine the migration approach. Because of the integration in the environment and the performance and security around the data, there would be a lower chance of consolidation.

Opportunities to Save

Most DBA’s do not change production often because we don’t want to break an application and/or the project timelines do not let us clean-up technical debt which builds up over the years. In our opinion, migration projects are an ideal time to address the design deficiencies of the existing environment while cleaning up and migrating instances, databases and external objects that are relevant to the business.

In our experience, by auditing the environment and sharing the inventory data with the business and requiring signoff on every instance and database, results in an average of 10% reduction in inventory Day 1 without executing a single migration.

Another opportunity to save is around the right sizing of the multiple SQL Server instances and the slotting of the databases into the instances which better align the database workload. When we design a new environment, we like to target the average usage of the instance during peak times to be 40% to 60% which still allows for a 40% burst in capacity. On average across an enterprise, most SQL Server instances are only consuming 5% to 15% of the servers’ resources.

By only slotting to and using ~15% of the server, we are increasing the licensing and hosting costs while not returning the full value to the business. Also, we do understand that determining the server and database capacity is challenging and to address these challenges, Fortified has new technology of determining capacity of systems real-time for servers and database instances coming soon but that is for a later post.

Creating the Plan

Many non-database individuals think database migrations are easy as we only have to install SQL Server and migrate the database, how hard can that be? But as we know, the migrations are very complex because they involve many different groups within the organization from the application owners, developers, infrastructure team, security team and the DBA team.

Everyone has a part in making the migration successful and if one connection string is not updated or a database query is not supported on the new version, it can mean the migration was not successful. So how do we increase the chance of success while reducing the risk of failure for a complex database migration and consolidation project?

At Fortified, we have matured our migration plan and process over the last 20 years which has enabled us to achieve the higher consolidation ratios while minimizing the risk to the business. Now, you might be thinking how a company can be successful 100% of the time and I can say, we are not.

But we do focus on migrating the Tier 1 or mission critical applications without major issues because those applications are critical to the business. For example, for every mission critical application we try and plan a dry run migration to validate the process, identify timing of events and test the application. During the dry run is the ideal place to identify issues with the migration approach.

So, what are some of the other critical steps in a database migration plan? Well, there are many but to help everyone, we will enable you to download the sample migration plan, performance templates and some base SQL Server scripts to help you better plan your migration and upgrade project. The scripts can be received by emailing the Fortified Team.

In the next blog post on SQL Server migrations and consolidations, we will focus on the analysis of the data and the design of the environment. This is also where there are many tough decisions to make which will impact the licensing models, hosting cost, administrative costs, and many other aspects of the database environment.

If you are ready increase the capabilities of your SQL Server through an upgrade and migration or consolidation project, our team of expert DBAs at Fortified, are ready to help you. With vast experience and knowledge on capacity planning and high availability and disaster recovery along with staying up to day on SQL Server licensing and new changes to Microsoft SQL Server Database, we can help to increase efficiency in your business.