Making Migration Seamless for Microsoft SQL Server Environments

  • 3 May 2017
  • 2 replies
Making Migration Seamless for Microsoft SQL Server Environments
Userlevel 7
Badge +34
This post was authored by Mike McGhee, Technical Marketing Engineer at Nutanix

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 Xtract for databases. Xtract 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 Xtract work?

For starters Xtract is agentless, meaning no software is required to be installed on your source or target environments. Xtract 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 Xtract. 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 Xtract is operated via its HTML 5 based interface using any standard internet browser. Installation to use takes only a few minutes.

Upon connecting to Xtract 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.

Xtract 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 Xtract 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.

Xtract Scans: Discovering your Environment

The scan operation of Xtract 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. Xtract performs this discovery directly through SQL Server, so an account with permission to the targeted SQL instance is required. Once connected, Xtract runs a set of transact SQL select statements and a small set of host commands. The first release of Xtract 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
  • Backup history
  • Performance statistics:
  • 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 Xtract can be used to better understand your existing environment, or as a baseline of requirements for migration planning. But Xtract 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.

Xtract 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 Xtract 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 Xtract process, which is deploy.

Xtract Deploy: Automatically creating your migration target

Xtract performs migrations by copying your databases between a source and target instance of SQL Server. Prior to executing a migration plan, Xtract 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, however, target Nutanix environments can be running either AHV or ESXi hypervisors.

Xtract needs just a handful of information to start the deployment process, including:
  • 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, Xtract 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.

Xtract Migrate: Automated database centric replication

The Xtract 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, Xtract 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. Xtract will query the SQL instance for any available log backups taken by existing schedules. Xtract 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, Xtract provides two options, to perform a test cutover or migrate to the new instance.

For a test cutover, Xtract 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, Xtract 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. Xtract 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.

Xtract 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 Xtract a try, we’ve made this part easy as well. Xtract 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 Nutanix for databases a spin and let us know what you think!

Disclaimer: This blog may contains links to external websites that are not part of 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.

2017 Nutanix, Inc. All rights reserved. Nutanix is a trademark of Nutanix, Inc., registered in the United States and other countries. All other brand names mentioned herein are for identification purposes only and may be the trademarks of their respective holder(s).

This topic has been closed for comments

2 replies

Badge +2
Wow, this sounds great. We are in the process of planning our SQL environment migration to Nutanix. How can we check it out?


Userlevel 3
Badge +17
Hi otc_kylee

You can download Nutanix SSMS from the Nutanix Portal here: