Book on Azure and SQL Server


image

My last contribution to a book was in 2012. With the advent of the cloud and my continuing work with SQL Server, I jumped at the opportunity when my friends and colleagues, Pranab Mazumdar [t] and Sourabh Agarwal [t], talked to me about contributing to a book on running SQL Server on Azure.

The book “Pro SQL Server on Microsoft Azure” attempts to teach the basics of Microsoft Azure and see how SQL Server on Azure VMs (Infrastructure-as-a-Service) and Azure SQL Databases (Platform-as-a-Service) work. This book will show you how to deploy, operate, and maintain your data using any one or more combinations of these offerings along with your on-premise environments. You will also find some architecture details which are very important for an end user to know in order to run operations using Azure.

The book is available on Apress and Amazon.

We would love to hear any feedback about the book. It could be good, bad or ugly. You will find the resources available for download on the site.

Advertisements

Sending a database to Azure


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

Azure SQL Database Export


In one of my previous posts I had talked about creating or rather restoring a deleted database backup. There is another option in preview called the automated export which allows you to create an automated export copy of your Azure SQL Database. The Azure SQL Database Import/Export Service is a REST-based web service that runs in every Microsoft Azure data center. The service provides a free request queuing service and a free Compute service to perform imports and exports from a Microsoft Azure SQL database to Microsoft Azure binary large object (BLOB) storage. The import and export operations are not a traditional physical database backup but a logical backup of the database that uses a special BACPAC format. This logical BACPAC format lets you avoid having to use a physical format that might vary between versions of SQL Server and SQL Database. Therefore, you can use it to safely restore the database to an SQL database and also to a SQL Server database.

As you can see from the screenshot, the Configure tab in the Azure Management portal for my Azure SQL Database, megatron, has an Export Status set to None.

 

image

As usual, this is a PREVIEW feature and the above is true as of today when I am writing this post. This functionality could change in the future.

Read on to find out what happens when I change the setting to Automatic… Is is as easy as 1-2-3?

Continue reading