About these ads

Lessons learnt while using the Cloud Adapter   Leave a comment


During the last week of August, I had blogged about how to get your on-premise database to your SQL Server instance running on an Azure virtual machine. I had run into a few issues while trying to run the wizard provided by Management Studio.

The First Stumble

This error is easy to circumvent and pretty much mentioned in the online documentation. The error message would read as:

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.

The above error is self-explanatory. There is a requirement that the source database engine version be lower or equal to the version of the SQL Server instance running on Azure. Eg. You cannot deploy a database from a SQL Server 2014 instance to a SQL Server 2012 instance running on an Azure VM.

The Second Stumble

The second common error that you might run into is:

The Cloud Adapter port configuration is not valid. Verify the virtual machine endpoint configurations.

The above error will be encountered when the endpoint is not configured for the Azure virtual to accept connections from the outer realm! This can be easily rectified by adding a TCP endpoint to your Azure virtual machine for 11435 which is the port that the SQL Server Cloud Adapter Service is listening on. This is also mentioned in the online documentation. Once you have created the endpoint for your Azure virtual for your on-premise server to connect with the Cloud Adapter service, your endpoint configuration should look like the one in the screenshot below:

image

The Third Stumble

The next issue could be with permissions/authentication or it might not be as easy as it seems.

Cloud Adapter operation failed due to invalid authentication. Verify the virtual machine name, user name, and password.

So the first thing to check if you have the correct account name and password. If it is due to an authentication error, then the application event log of the Azure Virtual Machine will show the following error with the source as SQL Server Cloud Adapter service as shown in Screenshot 2. The text of the error message is mentioned below.

Access denied for user <user name>

image

The other error that you might encounter is when the SQL Server Cloud Adapter service tries to enumerate the database engines installed on the virtual machine. The error would still be talking about the authentication which is reported by the management studio wizard but a little investigation into the application event logs of the virtual machine will show the following error:

[Error] <ip address> Exception in GetSqlInstances(): SQL Server WMI provider is not available on <machine name>.. Stack trace:    at Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer.TryConnect()
   at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.get_Proxy()
at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.EnumChildren(String childTypeName, WmiCollectionBase coll)
at Microsoft.SqlServer.Management.Smo.Wmi.ServerInstanceCollection.InitializeChildCollection()
at Microsoft.SqlServer.Management.CloudAdapter.Tasks.GetSqlInstances()
at Microsoft.SqlServer.Management.CloudAdapter.CloudAdapter.GetSqlInstances(String username, String password). Inner Exception: Invalid namespace .

The above error clearly states that the GetSqlInstances() method failed. Microsoft.SqlServer.Management.Smo.Wmi namespace contains classes that provide programmatic access to the Windows Management Instrumentation (WMI) from an SMO application. I had talked about needing the shared management objects in an earlier post. The SQL Server 2014 WMI provider is also required which is available by installing the client connectivity components from any SQL Server 2014 setup including SQL Server Express. The components that I had installed were:

a. Client Tools Connectivity

b. Client Tools Backwards Compatibility

If you are not sure if you have the WMI provider, then look for the file “C:\Program Files (x86)\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof“. This is the SQL Server 2014 MOF file. Another way to test if the WMI provider is working without running the wizard every time and have it fail is to run the PowerShell commands below on your Azure Virtual Machine. This script will tell you where the instance enumeration being performed by the deployment wizard will work or fail.

[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') '.'
foreach ($svi in $m.ServerInstances)
{
	$svi.Name;
}

This post was intended to document that common issues that you might run into while deploying a database from an on-premise SQL Server instance to a SQL Server instance running on an Azure Virtual Machine.

About these ads

Sending a database to Azure   Leave a comment


SQL Server 2014 Management Studio provides a wizard to deploy your on-premise database to a Windows Azure SQL Database server. This wizard can be used to export your on-premise database into a .bacpac file and upload it to your Azure SQL Database server. This wizard will help you to deploy your database to Windows Azure SQL Database. You may also use this wizard to deploy a Windows Azure SQL Database to a local instance of SQL Server, or to move a database from one instance of Windows Azure SQL Database to another. One advantage is that the wizard does a pre-validation check to determine if any unsupported object is present in the database which is not supported on Azure SQL Database.

This wizard can be used to deploy a database between an instance of the on-premise Database Engine and a Azure SQL Database server, or between two Azure SQL Database servers. An instance of the Database Engine must be running SQL Server 2005 Service Pack 4 (SP4) or later to work with the wizard. If a database on an instance of the Database Engine contains objects not supported on Azure SQL Database, you cannot use the wizard to deploy the database to Azure SQL Database. If a database on Azure SQL Database contains objects not supported by SQL Server, you cannot use the wizard to deploy the database to instances of SQL Server.

The first step is to launch the wizard. This is done by right clicking on a database and selecting Tasks and Deploy Database to Windows Azure SQL Database (see Screenshot 1).

image

In the Deployment Settings page (see Screenshot 2), you will need to provide the connection to your Azure SQL Database server which would be of the form <alpha numeric name>.database.windows.net. Before you are able to connect, you will need to an exception to the firewall from the Azure management portal to ensure that your on-premise machine is able to connect to your Azure SQL Database server. I had explained about this in a previous post.

image

Once you have connected to the database, you will need to specify the Azure SQL Database settings like the edition and maximum size. Ensure that you do this correctly because you are billed for your database usage on Azure.

Additionally, you will need to provide a temporary file name which will be the .bacpac file. In case you want to change the database name, you can choose to do so as well.

Once this is done, you are done with the Wizard and it will do it’s magic to export the database into a .bacpac file and import the same into an Azure SQL Database created with the same name as the one provided in the wizard.

The heavy lifting for all this activity is done by DLLs the present in C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin folder. If you want to automate this activity using command line operations, then one option is to use the SqlPackage utility which  is available in the same folder.

This can be done using the following commands:


"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /Action:Export /SourceServerName:<server name> /SourceDatabaseName:AzureTest /TargetFile:"G:\Tempdb\Azure\AzureTest_SqlPackageExport.bacpac"

The command line output is shown below:

image

Now that we have the exported file, we need to send to the Azure SQL Database server. This can be done using the command below:


"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /Action:Import /TargetServerName:<server name>.database.windows.net /TargetDatabaseName:AzureTest2 /TargetUser:troubleshootingsql /TargetPassword:<password> /SourceFile:"G:\Tempdb\Azure\AzureTest_SqlPackageExport.bacpac"

The output of the command is shown in the screenshot below which is similar to what you will see in the Management Studio UI:

image

To summarize the long post, we saw two ways of sending your on-premise database to an Azure SQL Database server using:

a. SQL Server Management Studio

b. SqlPackage utility

Reference:

Deploy a Database By Using a DAC
http://msdn.microsoft.com/en-us/library/jj554810.aspx

SQLBangalore UG Meeting – Sep 6   Leave a comment


Come join us on Saturday, September 6th for another SQL Bangalore User Group meeting to learn more about Azure Backups, SQL Server 2014 In-memory OLTP, Hybrid cloud scenarios and Power BI.

The session line up is available below.

14:00PM 14:45PM – Azure Backup by Ravikanth
14:45PM 15:30PM – Performance Improvement and In-memory – Balmukund Lakhani
15:45PM 16:30PM – Hybrid Cloud Scenario with SQL Server – Vinod Kumar
16:30PM 17:15PM – Power BI Overview – by yours truly

I will show you how you can use Power BI and Excel can be used to analyze your Facebook data and much more. Register for the event here. As usual, I will post the content used during that day on this blog. Stay tuned in case you are not able to attend in person. And don’t forget to spread the word! Hope to see in person on the 6th!

Venue:
Microsoft Corporation, Signature Building,
Embassy Golf Links Business Park,
Intermediate Ring Road,
Domlur, Bangalore – 560071

Posted September 1, 2014 by Amit Banerjee in Webcasts and Events

Tagged with , ,

Follow

Get every new post delivered to your Inbox.

Join 1,291 other followers