Tips & Tricks on ‘cloning’ Azure SQL virtual machines from captured images

[mssql-support]

First published on MSDN on Jul 06, 2016

While we have documentation on how to create a VM from captured image under “ How to capture a Windows virtual machine in the Resource Manager deployment model ”, it doesn’t address some specifics on SQL Server.  We have seen more and more users wanting to configure SQL Server and a set of databases so that they can use that image repeatedly for testing and development.  If you simply follow the “ How to capture a Windows virtual machine in the Resource Manager deployment model ”, you may face a couple of challenges.

Unable to Start SQL Server or recover databases

When you create VM from a captured image, the drive letters for data disks may not preserved.  For example if you have system database files on E: drive, it may get swapped to H: drive.  If this is the case, SQL Server can’t find system database files and will not start.  If the driver letter mismatch occurs on user database files, then the user databases will not recover.   After VM is created, you just need to go to disk management to change the drive letters to match your original configuration.

Unable to log onto SQL Server using Windows account

This issue is a bit more complex.  When you use sysprep to generalize the image, the SID for the machine is deleted and Windows users you created will be deleted as well.  If you rely on Windows login to access SQL Server, you won’t be able to log in.  Let’s assume that you create your original VM that contains SQL Server using administrator called azureadmin.   azureadmin will be added as sysadmin as part of VM creation.  After you use sysprep, the user will be deleted.  When you create a new VM out of that image, you can specify the same user name and password.  But that will be a totally new user. As a result, you can not log in using the ‘same’ Windows user anymore.  Let’s summarize what you need to do in order to solve the issue. The key is to add a few steps to prepare yourself before capturing the VM.

Prepare your SQL VM before image capture/sysprep

1. Configure SQL Server to accept SQL authentication
2. Create a SQL Server login as sysadmin and remember the password.
3. Make sure no databases or objects are owned by the Windows login.  Instead, make sure all objects and databases are owned by sa or an SQL login.

After VM creation (from the captured image)

1. Log onto SQL Server using the SQL Server login you created in the step 1 above.
2. Delete the original Windows user and add the new windows user back. Note that even you use same name, the SID will be different and it can’t be used to log onto SQL Server.
3. Follow steps in “ Rename a Computer that Hosts a Stand-Alone Instance of SQL Server ” if your new computer name is different from the original computer.

If you didn’t follow the preparation steps and can not log into SQL Server, do the following

1. Use “ Connect to SQL Server When System Administrators Are Locked Out ” to gain access to SQL Server.
2. If  the old windows admin login owns any databases, use sp_changedbowner to change the database owner to be sa.
3. Then following steps 2 and 3 of “After VM creation (from the captured image)”.
   
   

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter | pssdiag | Sql Nexus