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=126.96.36.199, Culture=neutral, PublicKeyToken=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified.
File name: ‘Microsoft.SqlServer.SqlEnum, Version=188.8.131.52, 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 = 184.108.40.206. 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!
Read the rest of this entry »
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).