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

Select databases to backup

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

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

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.

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

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

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.


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

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

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)