If you have played around with a SQL Server installation on an Azure Virtual Machine, then you will invariably have the need to move a database from an on-premise environment to your Azure Virtual Machine for testing, deployment and a host of other activities which you are involved with on a regular basis at work!

Books Online has complete documentation on this wizard. In this post, we will attempt to understand what happens under the hood. Read on to find out more.

First the vanilla stuff…

The “Deploy a SQL Server Database to a Windows Azure Virtual Machine” wizard available in SQL Server 2014 Management Studio allows you to take a database from your on-premise SQL Server instance and deploy it to your Windows Azure Virtual Machine running SQL Server (see screenshot 1).

The source settings screen requires you to input the details of the Windows Azure VM i.e. your destination along with the database name. You will need to specify a temporary location to store the backup as well. You will need to specify a shared folder that will be accessible to the Windows Azure VM service. (see screenshot 2).

 

The next screen is the Windows Azure Sign-in screen where you will provide management certificate or publishing profile details. The next screen is your Deployment Settings screen where you will specify the destination server and to provide details about your new database.

The SQL Server Cloud Adapter service is required to be installed on an Azure VM which is running SQL Server 2012. I had mentioned in a previous blog post on how to install the SQL Server Cloud Adapter service.

You will need the Cloud Adapter service which is available in the SQL Server 2014 Feature Pack download. I had talked about how to install this on a VM which is running only SQL Server 2012 instances in my earlier blog post.

You will need to click on Settings and provide the credentials for the account on the Windows Azure VM which has rights to enumerate the SQL Server instances on the Virtual Machine.

SQL Server database versions that can be deployed to a Windows Azure VM using this wizard:

• SQL Server 2008

• SQL Server 2008 R2

• SQL Server 2012

• SQL Server 2014

SQL Server database versions running in a Windows Azure VM database can be deployed to:

• SQL Server 2012

• SQL Server 2014

There is a specific validation that is done to determine if the version of the deployment server (source) is the same or higher version of the SQL Server instance running on the Windows Azure VM. Eg. You cannot deploy a database running on an on-premise SQL Server 2014 instance to a SQL Server 2012 instance running on your Windows Azure VM. If you attempt to do that, the Wizard will throw back an error which would make Grumpy Cat frown! The error message reported will be:

Failed to locate a SQL Server of version 12.0.2000 or later installed on the remote machine. Please verify that a SQL Server of the same or higher version than the source SQL Server is installed on the remote machine.

Let’s talk about what happens behind the scenes. The first thing that will happen is that a backup will be created in the drive that you specified in Screenshot 2. This backup is performed using the following command:

BACKUP DATABASE [<database name>] TO  DISK = N’\\<path>\db_backup_<characters>.<characters>.bak’ WITH  COPY_ONLY, NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 1

Note that the database backup name always starts with the prefix db_backup_ followed by other characters. This is the sanitized backup name. This backup file is uploaded to your Azure storage account specified in Screenshot 3. If you are performing this upload operation for the first time in the said storage account, then a new storage container called “backups” will be created. The file name of the backup file which was uploaded to the storage account would be different from the name of your on-premise backup file (see screenshot 4).

The backup will then be downloaded from your storage account to your virtual machine with a different name and restored on a drive which has sufficient free space other than the C: and D: drive if one exists on your server. In the above example, I had picked a scenario where my virtual machine already existed.

The log file for this deployment will be available at C:\Users\<user name>\AppData\Local\SQL Server\Deploy to SQL in WA VM. The DeploymentSettings.xml file in the same folder can be modified to increase the logging level to DEBUG using the following modification

OtherSettings TraceLevel=”Debug”

You might wonder what information you get from these log files. I will provide the snippets from the log files with the relevant snippets.

Any errors that were encountered

Aug 28 2014 22:10:07 [Error] Task:d1b0faeb-022e-4581-aa29-9bbb917343ee:Backup: Stopped: 0%: Failed: System.Data.SqlClient.SqlError: Cannot open backup device ‘\\<path>\<filename>.bak’. Operating system error 5(Access is denied.).

Aug 28 2014 22:10:07 [Error] Job:30e023c6-d75a-43d1-a858-1b474625168c: Running: System.Data.SqlClient.SqlError: Cannot open backup device ‘\\<path>\<filename>.bak’. Operating system error 5(Access is denied.).: Failed

The logging level

Log opened. TraceLevel:Debug

The configuration settings selected in the wizard

Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: Migration machine name: <machine name>.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: Source SQL Server: <SQL instance name>.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: SourceDatabase: <database name>.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: BackupLocation: <path>.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: Target SQL Server Instance: MSSQLSERVER.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: Target Database Name: <database name>.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: Publish Profile: Visual Studio Ultimate with MSDN.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: Use Existing VM: True.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: Cloud Service: <service name>.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: Deployment Name: <name>.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: VM Name: <VM Name>.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: Image Name: .
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: VM Location: .
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: VM Size: Medium.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: New Storage Account: False.
Aug 28 2014 21:30:10 [Informational] Submit Job:00f68f9b-e85b-4ceb-b45b-6e9fd40370a7: Storage Account: <storage account>.

The uploaded files names and the restore location

Aug 28 2014 21:31:07 [Debug] Task:a04ee0b3-d61b-4b39-bb42-a7bc0407930c:Restore: Running: 0%: None: Schedule Backup Restore: Blob:db_backup_-764200392af346a5ab653f6f24edbff6.bak, Source:E:\Temp\IaaSDeployment\Download\db_backup_ckddqytd.0v4.bak, Destination:E:\IaaSDeploymentRestore\userdb

Aug 28 2014 21:31:07 [Debug] Task:a04ee0b3-d61b-4b39-bb42-a7bc0407930c:Restore: Running: 0%: None: Schedule Backup Restore: Blob:db_backup_-764200392af346a5ab653f6f24edbff6.bak, Source:E:\Temp\IaaSDeployment\Download\db_backup_ckddqytd.0v4.bak, Destination:E:\IaaSDeploymentRestore\userdb

There is a host of other information that you will get in debug mode trace level. The above were the more salient ones which are useful for any deployment operation.

Reference:

Deploy a SQL Server Database to a Windows Azure Virtual Machine
http://msdn.microsoft.com/en-us/library/dn195938.aspx