Making Migration Seamless for Microsoft SQL Server Environments
Simple to deploy and consume enterprise cloud environments enable administrators to focus on their applications. One of the most frequently installed and virtualized mission critical applications today is Microsoft SQL Server. Virtualizing SQL Server brings many benefits including more efficient utilization of resources, improved availability and flexible mobility options across hardware platforms. Ensuring your mission critical applications like SQL Server gain the benefits of virtualization, without compromise, is ultimately why we choose specific private or public cloud technologies.
The ability to migrate SQL Server seamlessly is critical for taking advantage of modern technologies which improve performance, ease administration and ultimately improve business outcomes. Migrations, however, can be daunting to perform. Sizing for current needs and future growth, deploying SQL Server with best practices which apply to the target environment and executing the migration with limited downtime are just some of the important considerations and concerns.
The Nutanix Enterprise Cloud is an ideal platform for Microsoft SQL Server. With the goal of making migration to Nutanix as simple and easy to perform as possible, Nutanix has developed the SQL Server Mobility Service (SSMS). SSMS is an end to end tool which can help you with the most challenging migration tasks including
Understanding your current SQL Server environment
Sizing a target Nutanix environment for your SQL Server workloads
Defining Nutanix platform, virtualization and SQL Server best practices
Deploying the target environment including virtual machines and SQL instances
Automating the migration of SQL Server databases including cutover to the Nutanix environment
So how does SSMS work?
For starters SSMS is agentless, meaning no software is required to be installed on your source or target environments. SSMS itself is a virtual appliance, in OVA format, running a minimal installation of Linux. Within the Linux installation are Docker based containers which run the Nutanix images that comprise SSMS. Upon deployment of the OVA, an IP address is assigned to the VM either via DHCP by default, or statically by an administrator. From that point SSMS is operated via its HTML 5 based interface using any standard internet browser. Installation to use takes only a few minutes.
Upon connecting to SSMS you will find four core operations, scan, design, deploy and migrate. These operations are grouped under “projects” which you define. Let’s step through these concepts in more detail.
SSMS Projects: Getting Started
A project is a user defined logical construct which simply helps to organize your environment. Specific SQL instances can be placed into individual projects or multiple SQL instances can share a project, it’s really up to you. You can return to a project at any time and execute specific operations or review what has been performed.
When first logging into SSMS you are greeted by the home page which allows you to create a new project or search a list of existing projects.
The first step after creating a project is scanning your SQL instances.
SSMS Scans: Discovering your Environment
The scan operation of SSMS performs an agentless discovery of your SQL Server environment. Within a project you can specify multiple scans which can include one or several SQL instances. SSMS performs this discovery directly through SQL Server, so an account with permission to the targeted SQL instance is required. Once connected, SSMS runs a set of transact SQL select statements and a small set of host commands. The first release of SSMS performs around 40 queries to gather information which includes:
Operating system and SQL Server version
SQL Server instance name and used network ports
Active Directory details
Memory assigned to the operating system and allocated to SQL Server
Processor cores assigned to the host and SQL affinity settings
Storage allocated and consumed
Database configuration and layout
Current memory consumption
Historical CPU utilization
Note: CPU counters are a rolling average over a 4 hour window
Historical storage bandwidth and IOPs
Note: storage statistics are from views which result in averages from the last time the SQL Server service was restarted for that instance
Scans generally take under a minute to perform per instance and are non-intrusive. You will see an overview summary of the scan and a database level summary which shows the discovered databases within the instance.
The scan output provided by SSMS can be used to better understand your existing environment, or as a baseline of requirements for migration planning. But SSMS was designed to not just capture this information, but use it to automate creating the ideal target configuration. This begins in the next step, which is the design phase.
SSMS Design: Defining best practices
The design operation takes the captured scan data and uses a best practice rules engine to automatically construct a target configuration. You can view the recommended configuration within SSMS as below, or download the design including both the captured input from the scan and design template formatted in a simple YAML TOSCA specification
The rules engine includes a number of best practices at the Nutanix, virtual machine and SQL Server levels, including:
Nutanix storage container configuration with inline compression
Specifying virtual hardware, like vmxnet3 network adapters and multiple PVSCSI adapters
Multiple virtual disks for tempdb and user databases, taking number of vCPU into account
Formatting virtual disks targeted for SQL files with a 64KB allocation unit size
Allocating virtual disk sizes to accommodate current needs and future growth
SQL Settings such as
Instant file initialization
Lock pages in memory
Avoid creating mixed extent sizes
Autogrow files equally
Use of large-page allocations for memory, dependent on allocated memory to SQL Server
The design specification is then used to create the target configuration in the next part of the SSMS process, which is deploy.
SSMS Deploy: Automatically creating your migration target
SSMS performs migrations by copying your databases between a source and target instance of SQL Server. Prior to executing a migration plan, SSMS will first deploy the base operating system and SQL instance to be used. For the initial release, the target operating system and SQL instance should match the versions used in production. Also for the first release, target Nutanix environments must be running ESXi.
SSMS needs just a handful of information to start the deployment process, this includes:
Prism management address with credentials
Vcenter management address with credentials
A virtual machine template with the appropriate version of Windows
Domain credentials for adding Windows to Active Directory
SQL Server service account information
An .iso image which has the correct version of SQL Server.
Once entered you will see a summary screen and deployment from that point on will be an automated operation.
During deployment, SSMS will step through a series of tasks which begin to create the target environment, including storage container configuration within the Nutanix cluster. The designated VM template will be cloned, which is an extremely fast operation with Nutanix as VAAI based fast clones are used. The cloned VM will be added to the specified active directory, SQL Server is installed and best practices as previously mentioned are applied.
Upon completion you will have a virtual machine with SQL Server running on your Nutanix target environment ready for the last phase, migration.
The SSMS migration process automatically enables the replication of databases, instance security (user accounts) and jobs (via the msdb) between your source SQL instance and newly deployed target. Databases are migrated by using native SQL server backup and restore, very similar to the concept of log shipping.
The process is easy to execute, you simply create a migration plan where the targeted SQL instances are selected and existing backup operations are discovered. If existing native SQL Server backups exist, the backup target is queried for accessibility. If the target share is available, existing backups are leveraged. If needed, SSMS will prompt for an SMB share backup location to execute full and log backups should existing backups not be available.
When ready, you choose to start the migration, which as a first step performs a full backup if needed and restores the full backup on the target. SSMS will query the SQL instance for any available log backups taken by existing schedules. SSMS will continue to the monitor the instance and apply these log backups to the target as they occur. Once the log backup monitoring process is underway, SSMS provides two options, to perform a test cutover or migrate to the new instance.
For a test cutover, SSMS will leave the existing instance in place and simply bring the target databases out of recovery mode into a read write state. At that point the migration process is considered complete and you now have your original production instance and a copy of your databases available within the new SQL Server instance. If at a later time you want to perform a migration of your production instance, you would simply create another migration plan and step through the same backup and restore process again. Test migrations are great for gaining a comfort level with the migration process, staging a test environment before final cutover, or for even creating a POC environment.
If you select to perform a migration, the source SQL databases will be placed into single user mode and a final backup and restore of the transaction logs will be performed. For a full migration to be complete, clients and applications need to be redirected to the new SQL instance. If you leverage aliases in your environment you’d simply need to register that alias for the new target instance. But before either final test cutover or migration steps are complete, SSMS will perform some additional actions against the migrated databases.
One important aspect of SQL Server best practices involves the number of data files supporting the file groups within a database. SSMS uses Microsoft recommendations and can add additional data files to a database to help with write allocation contention and to spread the storage workload across more disks.
SSMS does this by first adding additional data files to databases based on the number of CPU assigned to the instance and the detected storage workload. If additional files are added, an optional step is presented to shrink existing data files and recreate existing indexes. This process is used to balance existing data across the new files and remove fragmentation. After this statistics are updated and a checkdb is issued.
These final steps ensure the best possible performance for the migrated databases with Nutanix and remove the burden of performing these operations from the database administrator.
Ready to give it a try?
Configuration management is becoming more and more important for maintaining consistency in the deployment and performance of your critical applications. Using configuration management principles to ensure migrations are done right the first time, while applying best practices simply makes sense. If you like what you’ve read so far and want to give SSMS a try, we’ve made this part easy as well. SSMS is free for all Nutanix customers, whether your target cluster is licensed at Standard, Pro or Ultimate editions. And if you like this framework, please let us know, as we are targeting additional applications and use cases beyond SQL Server in the future. Please join the Nutanix NEXT community, give SQL Server Mobility Services a spin and let us know what you think!
This post was authored by Mike McGhee, Technical Marketing Engineer at Nutanix
Disclaimer: This blog may contains links to external websites that are not part of Nutanix.com. Nutanix does not control these sites and disclaims all responsibility for the content or accuracy of any external site. Our decision to link to an external site should not be considered an endorsement of any content on such site. Please send us a note through the comments below if you have specific feedback on the external links in this blog.