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.
The Cloud Adapter is a stateless, synchronous service that receives messages from the on-premise instance of SQL Server. This service is required when you are deploying a database from your on-premise SQL Server instance to a SQL Server deployed on an Azure Virtual Machine.
Cloud Adapter is supported with SQL Server 2012 and higher. On SQL Server 2012, the Cloud Adapter for SQL Server requires SQL Management Objects.
For your SQL Server 2012 installation, you will need the SQL Server Cloud Adapter to be installed. This is available for download from the SQL Server 2014 Feature Pack. The filename that you need to download is SqlCloudAdapter.msi.
When you try to install this on your Azure VM, you might end up with the error message below:
Service cannot be started. System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.SqlServer.SqlEnum, Version=220.127.116.11, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Microsoft.SqlServer.SqlEnum, Version=18.104.22.168, Culture=neutral, PublicKeyToken=89845dcd8080cc91′
at Microsoft.SqlServer.Management.CloudAdapter.Service.CloudAdapterService.OnStart(String args)
at System.ServiceProcess.ServiceBase.ServiceQueuedMainCallback(Object state)
The above error message clearly states that the version number that the installer is looking for is SQL Server 2014 i.e. version = 22.214.171.124. You can install this assembly when Microsoft® SQL Server® 2014 Shared Management Objects (SharedManagementObjects.msi) is installed from SQL Server 2014 Feature Pack.
Cloud Adapter for SQL Server
In the last SQL Bangalore UG meeting, I had talked about how to use the Custom Scripting component in Azure to run the post configuration operations on an Azure VM which was hosting a SQL Server instance. The post configuration options that I am going to talk about in this post are necessary for you to be able to connect to your SQL Server instance on an Azure VM from a Management Studio running on your on-premise machine.
Before you can connect to the instance of SQL Server from the Internet, the following tasks must be completed:
- Configure SQL Server to listen on the TCP protocol and restart the Database Engine.
- Open TCP ports in the Windows firewall.
- Configure SQL Server for mixed mode authentication.
- Create a SQL Server authentication login.
- Create a TCP endpoint for the virtual machine. This would normally be done while providing the endpoint configuration if you are using the Azure Management Portal wizard.
If you had used an Image from the Image gallery, then you will get a default database engine installed with the TCP/IP port configured as 1433. I had written a post earlier which walks through an Azure VM creation using a SQL Server image from the image gallery.
Here I am going to talk about how to automate the bulleted points mentioned above using PowerShell and the Custom Script extension that the Azure provides. This is going to be a long read… So I suggest you get a coffee before you start reading further!