Sending a database to Azure

Posted on September 2, 2014 by Amit Banerjee

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).

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.

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:

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:

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

Posted on July 23, 2014 by Amit Banerjee

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.

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 →

Terminating an Azure SQL Database Replication

Posted on July 15, 2014 by Amit Banerjee

In my last post, I talked about setting up geo-replication for Azure SQL databases. There might be situations where you need to terminate your replication between your replicas. This could be a need for various reasons. You want to move your replica to a different region or you want to remove replication temporarily or you want to bring your secondary replica online and allow DML operations on it etc.

To remove replication, Azure provides two options: planned and forced termination. Again, if you have worked with on-premise database mirroring or availability groups, then this will seem familiar to you. Planned termination incurs ZERO data loss for the replica. The forced termination has chances of data loss.

Planned Termination is intended for use in planned operations where data loss is unacceptable. Termination can only be performed on the primary database, after the active secondary has been seeded.

Forced termination is intended for when the primary database or one of its active secondary databases is lost or is inaccessible. A forced termination can be performed on either the primary database or the active secondary. Every forced termination results in the irreversible loss of connectivity between the primary database and at least one active secondary. In addition, forced termination on an active secondary causes the loss of any transactions that have not been replicated from the primary database. If the primary database has only one continuous copy relationship, after termination, updates to the primary database will no longer be protected.

You will have to setup the replication again in case you want a synchronized copy of the database.

The steps to accomplish this is mentioned below.

Select the Geo-Replication tab for the database. This tab is only enabled for databases in subscriptions that are enrolled in the Premium preview program. Active Geo-Replication is currently only supported for Premium databases. You should see that the Replication Role for the database is displayed as source.

Select the desired active secondary from the REPLICAS list.
To terminate the continuous copy relationship, click Stop Replica. This launches the Stop Active Geo-Replication dialog which allows you to select the type of termination you want to perform.
The Stop Active Geo-Replication dialog box presents two options when launched from the primary database:Stop replication after synchronization completes: This option ensures that the termination happens after the committed transactions on the primary have been replicated to the active secondary.Stop replication immediately: This option terminates the continuous copy relationship between the primary and the selected active secondary immediately. You should expect some data loss for the active secondary in this scenario.
Select the Stop replication after synchronization completes option and click to confirm.

Reference:

Terminate a Continuous Copy Relationship
http://msdn.microsoft.com/en-us/library/azure/dn741323.aspx

Geo Replication meets Azure SQL Database

Posted on July 14, 2014 by Amit Banerjee

Active Geo-Replication enables continuous copy of your Premium database across geographic regions worldwide or within the same region. This is akin to setting up an availability group for SQL Server 2012 and above. For this you will need the following:

At least two Azure SQL Database Servers will available Premium database quota. The quota available is visible under the SERVERS tab in the Azure Management Portal for SQL Databases. You can alternatively retrieve the available quota by querying the server_quotas view (also in PREVIEW).
One or more Azure SQL Database configured on the Premium tier.

When downgrading from a Premium service tier, you must first terminate all Active-Geo Replication relationships. You can follow the steps described in the Terminate a Continuous Copy Relationship topic to stop the replication process between the primary and the active secondary databases.

The performance level of the secondary replica should be the same or higher than the primary replica.

Setting it up

In screenshot 1, you will see that the GEO-REPLICATION tab shown for the database optimus (Yes, another transformer character!) This will be available only if you have signed up for the PREVIEW feature for the new database tiers offered for Azure SQL Database. And if you have a Premium database configured, then you can setup a replica using the ADD REPLICA button which will be available at the bottom of the page.

On clicking the ADD REPLICA button, you will be provided with an ADD ACTIVE GEO-REPLICA screen. This allows you to pick another target server which will host the replica copy of your Azure SQL Database. See Screenshot 2.

Select the target server where you want your active geo-replica created. Only servers enabled for geo-replication in the same subscription as the source with available Premium database quota are shown. Additionally, only servers enabled for the current reservation size of the source database are shown. Replicas are created with the same database name, edition, maximum size, and reservation size as the source.

In the above screenshot, you can see that I have asked Azure to create a replica of optimus in the North Central US region and I have the primary copy hosted in the South Central US region. Once you proceed with your selection, you will be asked to accept terms and conditions as Premium database hosting attracts a cost!

Once the replication is configured, the Geo Replication tab will show you the status and the configured replicas as shown in Screenshot 3.

Reference:

SQL Database Pricing Details
http://azure.microsoft.com/en-us/pricing/details/sql-database/#service-premium

Changing Database Service Tiers and Performance Levels
http://msdn.microsoft.com/library/azure/dn369872.aspx

Active Geo-Replication for Azure SQL Database
http://msdn.microsoft.com/en-us/library/azure/dn741339.aspx

Restore a deleted Azure SQL Database

Posted on July 3, 2014 by Amit Banerjee

There could be a need where you have to restore a database that was deleted accidentally due to malicious intent or a plain old mistake (people end up writing resumes for such mistakes) or for some other inexplicable need! This is where backups come into the picture! For the Web and Business editions, this is not an option unless you have exported the database or you had made a copy of the existing database. Note that Business and Web service tiers will be retired in 12 months from April 24, 2014.

If you have just entered the world of Azure SQL Database, then this will seem even more difficult because you do not have the traditional scheduled backup job that you can trigger. So what is possible? Read on to find out…

Windows Azure SQL Database automatically creates backups of every active database using the following schedule: Full database backup once a week, differential database backups once a day, and transaction log backups every 5 minutes. The full and differential backups are replicated across regions to ensure availability of the backups in the event of a disaster. In addition, premium database offers the ability to create secondary databases in different regions to protect against datacenter disasters.

If you have played around with the new Azure SQL Database tiers (Basic, Standard and Premium), you will notice that there is a new tab available for Deleted Databases. This tab shows you the option of restoring a database (Basic, Standard and Premium tiers only) which was recently dropped. See screenshot 1.

I had dropped the bumblebee database which was a Basic tier database on 30th June. I can choose to restore this using the RESTORE button available at the bottom of the page. In the restore settings page popup page, you will have to specify the following:

1. Database name – This cannot be same as the original database

2. The time to which you want to restore available through a slider and text boxes. This is similar to the Timeline feature which was introduced in SQL Server Management Studio.

Note that the target server name cannot be changed. You will have to restore the deleted database backup to the same server. You cannot restore a database backup across Azure SQL Database Servers using this Wizard. Cross server restores are not currently supported.

While the database is restoring, you will see the database in the Databases tab but the status will show up as “RESTORING”. This is currently a PREVIEW feature. So you will need to sign up for the new database service tier preview to explore this feature.

Note that the deleted database backup is not retained indefinitely. As of July 1st, the following retention policy applies for the backups as shown in Screenshot 3: