One of the largest US national energy suppliers wanted to upgrade their SQL Server system and deploy it on VMware. As a consulting partner, we offered a new architecture for their virtualized system. In addition, we found some server consolidation opportunities which would improve the overall system’s performance.
As a result, we helped our customers deploy the newly created system in production. Moreover, we delivered a comprehensive list of best practices for evident and accessible management of their environment.
Learn more about the technical details of this project from the following video:
A Big Mix of SQL Servers
Our customer was using a mix of SQL Server installs ranging from 2008 through 2014. The broad database system comprised of a number of physical and virtual machines. The total number of SQL Server installs exceeded one hundred, while the staff included just 2 database administrators to manage and support this system.
The customer wanted to upgrade all databases to the latest version of SQL Server. Moreover, they decided to stop using physical machines while moving all their SQL Server installs to VMware.
After we made a review of the existing system, we decided to create a new architecture before moving the SQL Servers to virtual machines. Moreover, we found some opportunities to consolidate servers. This solution allowed us to improve overall database system performance while decreasing SQL Server license costs for the customer.
For example, we discovered that sometimes it may be expedient to host multiple databases on the same virtual machine without any performance decrease. In this case, we consolidated the servers before virtualization.
In the next step, we upgraded all databases to SQL Server 2016 and guided the customer to successfully install them on VMware. According to the new architecture, each virtual machine now included active and passive servers for failover clustering.
In order to deploy the system to production, we needed to upgrade the customer’s applications to meet the SQL Server 2016 standards. However, due to security concerns and a lack of time, we launched some of the existing applications in compatibility mode. However, the compatibility mode may become unavailable in the upcoming versions of the SQL Server, so the customer will still need to upgrade these applications.
Best practices and recommendations
Despite implementing the failover clusters, we strongly recommended our customer to set up Always On Availability Groups. This would allow the customer to take full advantage of the High Availability features built in the latest version of SQL Server.
Additionally, we provided some purposeful recommendations and a set of best practices relevant to the customer’s newly created environment. It included over 70 pages of very helpful information. Below you can find some of the highlights of that document.
For example, we recommended avoiding lazy zeroing of the storage, using small LUNs for better manageability and performance, as well as optimizing the IP network for iSCSI and NFS.
In regards to RAM, we proposed enabling Lock Pages in Memory right for SQL Server service account, using Max Server Memory and Min Server Memory options when running multiple SQL Server instances in the same VM, as well as disabling unnecessary processes within Windows.
Finally, we suggested running with mixed SQL Server workloads. Combining OLTP and batch workloads, the customer could achieve better server utilization, improve consolidation ratios and save money on licensing.
Results and Benefits
Staying on-site, we deployed an upgraded database system to the customer’s virtual machines. Now they can take full advantage of all the SQL Server features implemented in the latest release. In addition, we provided a rich documentation pack which includes a set of suggestions and best practices on running the newly created system in the current customer’s environment.
We could go even further with our in-house tool ‘DBMSys’ which allows for deep analysis of the customer’s data infrastructure and capacity planning. Using DBMSys for this current project could help improve the overall result.
If you are considering a database upgrade project, feel free to contact DB Best to get qualified support.