SQL SERVER – Steps to Backup to Windows Azure storage

Moving to a cloud based world is inevitable, it is something we need to learn soon. Ever since SQL Server 2014 has been released, the concept of uploading your backups to Blobs with Windows Azure has been around. In reality, performing a backup or restore operations with SQL Server 2012 SP1 CU4 and later requires no additional tools actually, and be done with either T-SQL or SSMS. This blog describes how to perform backup operations with T-SQL. This will be part of a series of blogs to come in the future. Let me walk through the initial steps.

Creating Credentials

To perform the backup and restore procedures on your local SQL Server you will need to create a SQL credential using the Windows Azure Storage Account configuration. The following steps will create the necessary credential:

  1. Connect to SQL Server Management Studio.
  2. On the Standard toolbar, click New Query.
  3. Copy and paste the following example into the query window, modifying as needed.

WITH IDENTITY= 'mystorageaccount' --this is the name of the storage account you specified when creating a storage account
, SECRET = '<storage account access key>' -- this should be either the Primary or Secondary Access Key for the storage account.

Steps to backup to Windows Azure storage

The following steps describe how to perform a backup of a database to the Windows Azure storage service. The database can be an on-premises database, or located in a Windows Azure Virtual Machine. The key requirement for this demo variation is that the database must be accessible from SQL Server Management Studio:

If you don’t have your own database, you plan to use for this tutorial, then install AdventureWorks from: http://msftdbprodsamples.codeplex.com/

  1. Connect to SQL Server Management Studio.
  2. In the Object Explorer, connect to the instance of SQL Server on which the database to be backed up is located.
  3. In Object Explorer, connect to the instance of the Database Engine that has the database you plan to backup.
  4. On the Standard menu bar, select New Query.
  5. Copy and paste the following example into the query window, modify as needed, and click Execute.

BACKUP DATABASE [AdventureWorks2014]
TO URL = 'https://mystorageaccount.blob.core.windows.net/privatecontainers/AdventureWorks2014.bak'
/* URL includes the endpoint for the BLOB service, followed by the container name, and the name of the backup file*/
WITH CREDENTIAL = 'mycredential';
/* name of the credential you created in the previous step */

As you can see, the steps to back up to an URL is simple as described above. The pre-requisite for this is to have a storage account in Azure ready before you do the same.

Do let me know if you have ever taken a backup to Azure till date. Please let me know if you want me to write on this topic in the future.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Simple Trick to Backup Azure Database with SkyDrive

To ensure your SQL Server or Azure databases remain safe, you should backup your databases periodically. And it is important to store the backups in a reliable location. Microsoft SkyDrive currently offers 7GB free, Box offers 5GB free – both are reliable and it is simple to send your backups there. SQLBackupAndFTP in it’s latest version 9 added the option to backup to SkyDrive and Box ( in addition to local/network folder, NAS drive, FTP, Dropbox, Google Drive and Amazon S3). Just select the databases that you’d like to backup and select to store the backups in SkyDrive or Box. Below I will show you how to do it in details

azurebackup1 SQL SERVER   Simple Trick to Backup Azure Database with SkyDrive

Select databases to backup

First connect to your SQL Server or Azure Sql Database. Then select the databases you’d like to backup.

azurebackup2 SQL SERVER   Simple Trick to Backup Azure Database with SkyDrive

Connect to SkyDrive or Box cloud

If you have a free version of SQLBackupAndFTP Box destination is included, but SkyDrive destination will be disabled as it is available in the Standard version or above. Click “Try now” to get 30 days trial on all options

azurebackup3 SQL SERVER   Simple Trick to Backup Azure Database with SkyDrive

On the “SkyDrive Settings” form you’ll need to authorize SQLBackupAndFTP to access your SkyDrive. Click “Authorize…” to open SkyDrive authorization page in your browser, sign in your to SkyDrive account and click at “Allow” . On the next page you will see the field with an authorization code. Copy it to the clipboard. Box operation is just the same.

azurebackup4 SQL SERVER   Simple Trick to Backup Azure Database with SkyDrive

After that return to SQLBackupAndFTP, paste the authorization code and click “OK” .

azurebackup5 SQL SERVER   Simple Trick to Backup Azure Database with SkyDrive

After you are authorized, you can enter the path to a backup folder. SQLBackupAndFTP will create the folder if it does not exist.

azurebackup6 SQL SERVER   Simple Trick to Backup Azure Database with SkyDrive

That’s all what has to be done to backup to SkyDrive or Box cloud.  You can now click on “Run Now” button to test this job.

azurebackup7 SQL SERVER   Simple Trick to Backup Azure Database with SkyDrive


Whatever is your preference for storing SQL backups, it is easy with SQLBackupAndFTP. Note that at the time of this writing they are running a very rare promotion on volume licenses:

  • 5–9 licenses: 20% off
  • 10–19 licenses: 35% off
  • More than 20 licenses: 50% off

Please let me know your favorite options for storing the backups

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Windows Azure Training Kit Updated October 2012

Microsoft has recently released the updated to Windows Azure Training Kit. Earlier this month they have updated the kit and included quite a lot of things. Now the training kit contains 47 hands-on labs, 24 demos and 38 presentations. The best part is that the kit is now available to download in two different formats 1) Full Package (324.5 MB) and 2) Web Installer (2.4 MB). The full package enables you to download all of the hands-on labs and presentations to your local machine. The Web Installer allows you to select and download just the specific hands-on labs and presentations that you need.

This Windows Azure Training Kit contains Hands on Labs, Presentations and Videos and Demos. I encourage all of you to try this out as well. The Kit also contains details about Samples and Tools. The training kit is the most authoritative learning resource on Windows Azure.

You can download the Windows Azure Training Kit from here.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Download SQL Azure Labs Codename “Data Explorer” Client

Microsoft SQL Azure labs has recently released Data Explorer client. I was looking forward to visualizing tool for quite a while and I am delighted to see this tool. I will be trying out this tool in coming week and will post here my experience. I have listed few of the resources which are related to Data Explorer at the end. Please let me know if I have missed any and I will add the same.

With “Data Explorer” you can:

  • Identify the data you care about from the sources you work with (e.g. Excel spreadsheets, files, SQL Server databases).
  • Discover relevant data and services via automatic recommendations from the Windows Azure Marketplace.
  • Enrich your data by combining it and visualizing the results.
  • Collaborate with your colleagues to refine the data.
  • Publish the results to share them with others or power solutions.

The Data Explorer Client package contains the Data Explorer workspace as well as an Office plugin that integrates Data Explorer into Excel.


Download Data Explorer
Data Explorer Blog
Desktop Client
Video of  Contoso Bikes and Frozen Yogurt (Data Explorer)

Please note that this is not the final release of the product. Please do not attempt this on production server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Azure Interview Questions and Answers – Guest Post by Paras Doshi – Day 25 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Please read the Introductory Post before continue reading interview question and answers.

paras SQL SERVER   Azure Interview Questions and Answers   Guest Post by Paras Doshi   Day 25 of 31Paras Doshi is known SQL Azure Expert has written following guest blog post to keep alive the spirit of Interview Questions and Answers Series. I encourage all the readers to read his excellent blog and follow him on twitter.

1.What is SQL Azure?

SQL Azure is a cloud based relational database as a Service offered by Microsoft. Conceptually it is SQL server in the cloud.

2.What is cloud computing?

National Institute of standards and computing definition of cloud computing:

Cloud computing is a model for enabling ubiquitous, convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, servers, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction.

[TIP: Remember Key words On Demand, Scalable, Self-service, and Measurable. Now take the first word from each key word which gives us OSSM which can be pronounced as Awesome. Thus remember cloud computing is Awesome! Tip courtesy: Dave Nielsen]

3.How is SQL Azure different than SQL server?

SQL Azure is a cloud based service and so it has own set of pros and cons when compared to SQL server. SQL Azure service benefits includes on demand provisioning, high availability, reduced management overhead and scalability. But SQL Azure abstracts some details from the subscriber which can be good or bad which depends on the context of the need.

4.How many replicas are maintained for each SQL Azure database?

For each database, three replicas are maintained for each database that one provisions. One of them is primary replica. All read/write happen on primary replica and other replicas are kept in sync with primary replica. If for some reason, primary goes down, other replica is promoted to primary. All this happens under the hood.

5.How can we migrate from SQL server to SQL Azure?

For Data Migration, we can use BCP or SSIS. And for schema Migration we can use Generate Script Wizard. Also, we could use a Tool called SQL Azure migration wizard available on codeplex.

6.Which tools are available to manage SQL Azure databases and servers?

We can manage SQL Azure database using SQL server management server 2008 R2. Also, we can manage SQL Azure databases and servers through a Silverlight app integrated in Azure management portal.

7.Tell me something about security and SQL Azure.

SQL Azure service allows blocking a request based on its IP address through SQL Azure firewall. It uses SQL server Authentication mechanism to authenticate connections. Also connections to SQL Azure are SSL-encrypted by default.

8.What is SQL Azure Firewall?

SQL Azure firewall is a security mechanism that blocks requests based on its IP address.

9.What is the difference between web edition and business edition?

SQL Azure Web edition database Max Size is 5 GB whereas the business edition supports Max Size up to 50 GB. The size of a web edition database can be increased (/decreased) in the increments (/decrements) of 1 GB whereas the size of a business edition can be increased in the increments of 10 GB.

10.How do we synchronize On Premise SQL server with SQL Azure?

We could use a No code solution called DATA SYNC (currently in community technology preview) to synchronize on-premise SQL server with SQL Azure. We can also develop custom solutions using SYNC framework.

11.How do we Backup SQL Azure Data?

SQL Azure keeps three replicas of a database to tackle hardware level issues. To tackle user level errors, we can use COPY command that allows us to create a replica of a SQL Azure database. We can also backup SQL Azure data to local SQL server using BCP, SSIS, etc. but as of now, point in time recovery is not supported.

12.What is the current pricing model of SQL Azure?

Charges for SQL Azure consumption is based on 1) Size 2) Data Transfer.

[For contemporary pricing model, read: http://www.microsoft.com/windowsazure/pricing/ ]

13.What is the current limitation of the size of SQL Azure DB?

Maximum size of a SQL Azure database is 50 GB.

14.How do you handle datasets larger than 50 GB?

As of now, we have to build custom solution at application level that can handle scale out of underlying SQL Azure databases. But Microsoft has announced, SQL Azure Federations that will assist scaling out of SQL Azure databases. And scale out means that we are splitting the data into smaller subsets spread across multiple databases.

15.What happens when the SQL Azure database reaches Max Size?

Read operations continue to work but create/insert/update operations are throttled. You can drop/delete/truncate data.

16.How many databases can we create in a single server?

150 databases (including master database) can be created in a single SQL Azure server.

17.How many servers can we create in a single subscription?

As of now, we can create six servers under a single subscription.

18.How do you improve the performance of a SQL Azure Database?

We can tune a SQL Azure database using information available from execution plan and statistics of a query. We could use SQL Azure’s Dynamic Management views to monitor and manage SQL Azure database.

Also, SQL Azure performance is affected by network latency and bandwidth. Considering this, code near application topology gives the best performance.

19.What is code near application topology?

Code near application topology means that the SQL Azure database and the windows azure hosted service consuming the data are hosted in the same Azure datacenter.

[FYI: in the code far application topology, the app connects to SQL Azure from outside the Microsoft data center]

20.What were the latest updates to SQL Azure service?

Latest SQL Azure updates include multiple servers per subscription, SQL Azure co administrator support, creating Firewall rules for servers with IP detect.

[A constantly updated list of SQL Azure features is available at http://beyondrelational.com/whatisnew/sqlserver/azure.aspx]

21.When does a workload on SQL Azure get throttled?

When database reaches its maximum size update/insert/create operations get throttled. Also there are policies in place that does not allow to a workload to exploit a shared physical server. In other words, the policies make sure that all workload get a fair share of shared physical server. Now, a workload can get soft throttled that means that the workload has crossed the safety threshold. A workload can also got hard throttled which means that a SQL Azure machine is out of resources and it does not accept new connections. We can know more about what happened by decoding reason codes.

These were SQL Azure specific interview questions but remember that SQL server specific interview questions that we have seen so far can also appear in SQL Azure context since SQL Azure is “SQL server in the cloud”. Also questions specific to supported/unsupported features can be asked. I have not included the questions specific to unsupported features because it keeps on changing. Read general guidelines and limitations. Also note that SQL Azure is evolving very fast and it is important to keep a tab on the features that are added in service releases.

List of all the Interview Questions and Answers Series blogs

Reference: Paras Doshi, Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Extending SQL Azure with Azure worker role – Guest Post by Paras Doshi

paras SQL SERVER   Extending SQL Azure with Azure worker role   Guest Post by Paras DoshiThis is guest post by Paras Doshi. Paras Doshi is a research Intern at SolidQ.com and a Microsoft student partner. He is currently working in the domain of SQL Azure.

SQL Azure is nothing but a SQL server in the cloud. SQL Azure provides benefits such as on demand rapid provisioning, cost-effective scalability, high availability and reduced management overhead. To see an introduction on SQL Azure, check out the post by Pinal here

In this article, we are going to discuss how to extend SQL Azure with the Azure worker role. In other words, we will attempt to write a custom code and host it in the Azure worker role; the aim is to add some features that are not available with SQL Azure currently or features that need to be customized for flexibility. This way we extend the SQL Azure capability by building some solutions that run on Azure as worker roles. To understand Azure worker role, think of it as a windows service in cloud. Azure worker role can perform background processes, and to handle processes such as synchronization and backup, it becomes our ideal tool.

First, we will focus on writing a worker role code that synchronizes SQL Azure databases.

Before we do so, let’s see some scenarios in which synchronization between SQL Azure databases is beneficial:

  1. scaling out access over multiple databases enables us to handle workload efficiently
  2. As of now, SQL Azure database can be hosted in one of any six datacenters. By synchronizing databases located in different data centers, one can extend the data by enabling access to geographically distributed data

Let us see some scenarios in which SQL server to SQL Azure database synchronization is beneficial

  1. To backup SQL Azure database on local infrastructure
  2. Rather than investing in local infrastructure for increased workloads, such workloads could be handled by cloud
  3. Ability to extend data to different datacenters located across the world to enable efficient data access from remote locations

Now, let us develop cloud-based app that synchronizes SQL Azure databases. For an Introduction to developing cloud based apps, click here

Now, in this article, I aim to provide a bird’s eye view of how a code that synchronizes SQL Azure databases look like and then list resources that can help you develop the solution from scratch.

Now, if you newly add a worker role to the cloud-based project, this is how the code will look like.

(Note: I have added comments to the skeleton code to point out the modifications that will be required in the code to carry out the SQL Azure synchronization. Note the placement of Setup() and Sync() function.)

Click here (http://parasdoshi1989.files.wordpress.com/2011/06/code-snippet-1-for-extending-sql-azure-with-azure-worker-role1.pdf )

Enabling SQL Azure databases synchronization through sync framework is a two-step process. In the first step, the database is provisioned and sync framework creates tracking tables, stored procedures, triggers, and tables to store metadata to enable synchronization. This is one time step. The code for the same is put in the setup() function which is called once when the worker role starts.

Now, the second step is continuous (or on demand) synchronization of SQL Azure databases by propagating changes between databases. This is done on a continuous basis by calling the sync() function in the while loop. The code logic to synchronize changes between SQL Azure databases should be put in the sync() function.

Discussing the coding part step by step is out of the scope of this article. Therefore, let me suggest you a resource, which is given here. Also, note that before you start developing the code, you will need to install SYNC framework 2.1 SDK (download here). Further, you will reference some libraries before you start coding. Details regarding the same are available in the article that I just pointed to.

You will be charged for data transfers if the databases are not in the same datacenter. For pricing information, go here

Currently, a tool named DATA SYNC, which is built on top of sync framework, is available in CTP that allows SQL Azure <-> SQL server and SQL Azure <-> SQL Azure synchronization (without writing single line of code); however, in some cases, the custom code shown in this blogpost provides flexibility that is not available with Data SYNC. For instance, filtering is not supported in the SQL Azure DATA SYNC CTP2; if you wish to have such a functionality now, then you have the option of developing a custom code using SYNC Framework.

Now, this code can be easily extended to synchronize at some schedule. Let us say we want the databases to get synchronized every day at 10:00 pm. This is what the code will look like now:


Don’t you think that by writing such a code, we are imitating the functionality provided by the SQL server agent for a SQL server? Think about it. We are scheduling our administrative task by writing custom code – in other words, we have developed a “Light weight SQL server agent for SQL Azure!” Since the SQL server agent is not currently available in cloud, we have developed a solution that enables us to schedule tasks, and thus we have extended SQL Azure with the Azure worker role!

Now if you wish to track jobs, you can do so by storing this data in SQL Azure (or Azure tables). The reason is that Windows Azure is a stateless platform, and we will need to store the state of the job ourselves and the choice that you have is SQL Azure or Azure tables.

Note that this solution requires custom code and also it is not UI driven; however, for now, it can act as a temporary solution until SQL server agent is made available in the cloud. Moreover, this solution does not encompass functionalities that a SQL server agent provides, but it does open up an interesting avenue to schedule some of the tasks such as backup and synchronization of SQL Azure databases by writing some custom code in the Azure worker role.

Now, let us see one more possibility – i.e., running BCP through a worker role in Azure-hosted services and then uploading the backup files either locally or on blobs. If you upload it locally, then consider the data transfer cost. If you upload it to blobs residing in the same datacenter, then no transfer cost applies but the cost on blob size applies. So, before choosing the option, you need to evaluate your preferences keeping the cost associated with each option in mind.

In this article, I have shown that Azure worker role solution could be developed to synchronize SQL Azure databases. Moreover, a light-weight SQL server agent for SQL Azure can be developed. Also we discussed the possibility of running BCP through a worker role in Azure-hosted services for backing up our precious SQL Azure data.

Thus, we can extend SQL Azure with the Azure worker role.

But remember: you will be charged for running Azure worker roles. So at the end of the day, you need to ask – am I willing to build a custom code and pay money to achieve this functionality?

I hope you found this blog post interesting. If you have any questions/feedback, you can comment below or you can mail me at Paras[at]student-partners[dot]com

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL Azure – SQL Azure Throttling and Decoding Reason Codes

I was recently reading on the subject SQL Azure Throttling and Decoding Reason Codes and end up reading the article over here. What I really liked is the explanation of the subject with Graphic. I have never seen any better explanation of this subject.

DecodingCode SQL Azure   SQL Azure Throttling and Decoding Reason Codes

I really liked this diagram. However, based on reason code one has to adjust their resource usages. I now wonder do we have any tool available which can directly analysis the reason codes and based on it gives output that what kind of the throttling is happening. One of the idea I immediately got that I can make a Stored Procedure or Function where I pass this error code and it gives me back right away the throttling mode and resource type based on above algorithm.

Any one has the T-SQL code available for the same?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – A Quick Notes on SQL Azure

I was recently attending a small meeting where I was asked if I can share few things to be considered when designing SQL Azure database. Today I am sharing the same notes over here.

Disclaimer: This is no way a complete guide – this is some notes which are compiled in short meeting. SQL Azure is evolving product. All the notes are accurate when this article was written.

  • Each Table must have clustered index. Tables without a clustered index are not supported.
  • Each connection can use single database. Multiple database in single transaction is not supported.
  • ‘USE DATABASE’ cannot be used in Azure.
  • Global Temp Tables (or Temp Objects) are not supported.
  • As there is no concept of cross database connection, linked server is not the concept in Azure at this moment.
  • SQL Azure is shared environment and because of the same there is no concept of Windows Login.
  • Always drop TempDB objects after their need as they create pressure on TempDB.
  • During buck insert use batchsize option to limit the number of rows to be inserted. This will limit the usage of Transaction log space.
  • Avoid unnecessary usage of grouping or blocking ORDER by operations as they leads to high end memory usage.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Copy Database from Instance to Another Instance – Copy Paste in SQL Server

SQL Server has feature which copy database from one database to another database and it can be automated as well using SSIS.

Make sure you have SQL Server Agent Turned on as this feature will create a job. The same job will execute the task. Make sure that SSIS is properly configured as well with necessary security permissions. You can automate this process as well control error logging.

Following are the steps to copy database from one instance to another instance.

copywizard1 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server

copywizard2 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server
Specify Source Server
copywizard3 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server
Specify Destination Server
copywizard4 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server
Here you can select option if you want to keep the database ONLINE when it is being copied.
copywizard5 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server
You can also select option of MOVE or COPY database as well.

copywizard6 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server
Give appropriate database name.
copywizard7 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server
On this screen you can select additional options to copy as well.
copywizard8 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server
You create the package over here.
copywizard9 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server
You can schedule the package using SQL Server Agent.
copywizard10 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server

copywizard11 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server

copywizard12 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server
When this process is over it will show the success message and database will be copied to another server.
copywizard13 SQL SERVER   Copy Database from Instance to Another Instance   Copy Paste in SQL Server

You can see how easy is the process to copy the database to another server.

Watch SQL in Sixty Seconds Episode on same subject.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Generate Database Script for SQL Azure

When talking about SQL Azure the common complain I hear is that the script generated from stand-along SQL Server database is not compatible with SQL Azure. This was true for some time for sure but not any more. If you have SQL Server 2008 R2 installed you can follow the guideline below to generate script which is compatible with SQL Azure.

azurescript1 SQL SERVER   Generate Database Script for SQL Azure

azurescript2 SQL SERVER   Generate Database Script for SQL Azure

azurescript3 SQL SERVER   Generate Database Script for SQL Azure

azurescript4 SQL SERVER   Generate Database Script for SQL Azure

As above images are very clear I will not write more about them. SQL Azure does not support filegroups. Let us generate script for any table created on PRIMARY filegroup for standalong SQL Server and compare it with the script generated for SQL Azure.

azurescript5 SQL SERVER   Generate Database Script for SQL Azure

You can clearly see that there is no filegroup in the code generated for SQL Azure. Give it a try and please your comment here about what do you think about the same.

Reference: Pinal Dave (http://blog.SQLAuthority.com)