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=184.108.40.206, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Microsoft.SqlServer.SqlEnum, Version=220.127.116.11, 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 = 18.104.22.168. 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!
I am always amazed by the turnout on Saturdays for the SQLBanglaore UG Meetings. This time around it was an all Azure day where we had four sessions talking about different features that Azure offers.
The first session as about Machine Learning by Govind. He showed us how Machine Learning can make the machines smarter. It doesn’t mean that the machines are taking over but they can become your assistants with a bit of training. He went on to demo what Azure Machine Learning (in Preview) has to offer in this space.
This was followed by Angshuman’s session on Azure Redis Cache (in Preview). It gives you access to a secure, dedicated Redis cache, managed by Microsoft. A cache created using Azure Redis Cache is accessible from any application within Microsoft Azure.
The next session by Pranab was on the Smart Backup Feature in SQL Server 2014 which allows you to backup your SQL Server databases directly to Windows Azure Storage accounts. This enables you to pull down those backups for restore from anywhere in the world!
I had the last session and I was the one who was standing between attendees and their lunch! Not an enviable position to be in on a Saturday afternoon! This time around I had attempted to do things a bit differently. I started with the demo and moved onto my presentation. I used a video from PowToon to create a story line for provisioning a SQL Server virtual machine in a short span of time! That is available below. Lately, I have been using animation videos for a few of my presentations. And the view below is the one that I used for this session.
The presentation that I had used for my session is available below:
If you want the PowerShell script which performed the magic after creating the Azure SQL VM, you can from the files section of the SQLBangalore Facebook Group. Look forward to further posts on this blog detailing additional possibilities for that script.
Once again, a big THANK YOU to all the attendees as without them, these sessions will never be a success!
If you want to get notified about future posts, then you can follow me on one of these channels: Facebook | Twitter or simply subscribe to this blog (Available in the side-bar on the left).
There might be explicit requirements from an application standpoint which requires the D: drive to be available. While re-mapping your application to use another drive may be a very simplistic suggestion, it might not be viable in a certain scenarios. If you have used an Azure Virtual Machine, it is made clear in various articles that the temporary drive i.e. the D: drive should not be used. In this blog post, I shall show you how to re-claim that the letter D from your Azure Virtual Machine and assign that to another drive that might be craving for this particular letter of the English alphabet.
The first thing that you will need to do is assign a new data disk or an existing data disk to your Azure VM if you do not have a spare disk. This can be accomplished easily from following the steps mentioned here.
Once you have initialized the disks, your disk management view should be something similar to what you see in Screenshot 1. The temporary disk shows that it hosts the Page File. Remapping this first requires you to move the page file to a new disk or some other data disk that was already present on the server. You will have to reboot the machine for the changes to take effect.
Once the machine is back up, change the drive letter mapping from Disk Management.
Change the page file settings to use the temporary storage but this time the drive letter would be a different one. Once you reconfigure the page file settings, you will need to reboot the virtual machine again.
When the virtual machine is finally online again, you will have your desired drive letter mapping. As you can see in Screenshot 2, the page file and my temporary storage is now the Z: drive where as the D: drive is assigned to a data drive.