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

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

Click here to get free chapters (PDF) in the mailbox

Paras Doshi is known as 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.

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

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 Keywords On Demand, Scalable, Self-service, and Measurable. Now take the first word from each keyword 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 include 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 a primary replica. All read/write happen on primary replica and other replicas are kept in sync with the primary replica. If for some reason, primary goes down, another 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.

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?

The 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 the custom solution at the 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 on 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

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 the shared physical server. Now, a workload can get soft throttled that means that the workload has crossed the safety threshold. A workload can also get 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 (https://blog.sqlauthority.com)

SQL Azure, SQL Server
Previous Post
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 24 of 31
Next Post
SQL SERVER – Interview Questions and Answers – Guest Post by Malathi Mahadevan – Day 26 of 31

Related Posts

20 Comments. Leave new

  • Virat Kothari
    July 25, 2011 8:46 am

    Very informative article. My vote 1.

  • parasdoshi.com
    July 26, 2011 12:48 pm

    Sir, you inspire not only me and thousands of other Tech enthusiasts! Thank you!

  • Pratik Bhonsle
    July 26, 2011 2:54 pm

    Hi Paras, Pinal,

    Recently we started experimenting with MS Azure platform as the new base for about a hundred sites that we host on a shared server elsewhere for clients.

    The biggest hurdle we faced was “Dates”. SQL Azure only handles US date format. This is not only a huge hassle for the end user, but also an absolute showstopper for our proposed project.

    We tried to go up the MS ladder to get someone to help us resolve this issue, and all we get back is that either we handle all dates as US in our code, or that all SQL statements in out code get prefixed with “SET DATEFORMAT DMY;”.

    There is nothing new in both these solutions, but the amount of effort and cost involved in modding those hundred odd sites with the above fixes is unjustifiable. The code wasn’t written with “best practices” in mind and so, changing it will be a laborious unchargable task.

    Do you have a better solution for it? All sites are ASP/SQL.

  • “…All sites are ASP/SQL….” waah ASP is still in use …!!!

  • Good collection on SQL Server Azure. Leart a lot for this.
    Thanks for sharing.

  • Hi Pinal…need some assistance regarding moving file groups…the requirement is as follows
    – Move all non-clustered indexes to INDEX01 file group
    – Move all user (U) type tables and clustered indexes to DATA01 file group
    I need to perform teh above in 3 databases,and in each database we have 4 schemas,and in each database we have more than 100+ tables,can you please provied me some scripts if you have to move the tables all at a time or do we have any alternative to do this…

    Thanks in advance

  • Juvvi pramod
    March 5, 2012 1:46 pm

    Gud article…gives a brief of what exactly is Sql Azur meant…Thanks for the article :)

  • Nice article

  • Azure now supports up to 150 GB databases with pricing @

  • Nice article about SQL Azure. As SQL Azure don’t support trace flags and profiler, I want record all the TSQL’s running in a particular time interval as we are tracing TSQL’S in sql profiler is there any way to sort this?
    Thanks in advance.

  • Srikar (@ananthulasrikar)
    January 21, 2013 3:48 pm

    Informative questions.I would like to know what is the time out period by default for the sql azure ?? Please reply me.

  • what are the differences between sql azure and visualstudio online?,i am using tfs which has servers in cloud,i always confused with windows azure,sql azure and visual studio online

  • Really nice but i want to suggest that add information about SQL Azure the merits and demerits like global table is not possible in SQL Azure…

  • Virender Mehta
    January 8, 2016 4:42 pm

    While Migrating my database to Windows Azure i am getting this below error.Can Any one help me with some suggestions..
    Error SQL71005: Error validating element [dbo].[GetStockDetails_StoreWise]: The reference to the column ItemName could not be resolved.

    Error SQL71564: The permission ‘CONNECT’ is not valid for the target object ‘guest’ in Microsoft Azure SQL Database v12.

  • Virender Mehta
    January 8, 2016 4:48 pm

    While Migrating my database to Windows Azure i am getting this below error.Can Any one help me with some suggestions.
    Error SQL71005: Error validating element [dbo].[GetStockDetails_StoreWise]: The reference to the column ItemName could not be resolved.

    Error SQL71564: The permission ‘CONNECT’ is not valid for the target object ‘guest’ in Microsoft Azure SQL Database v12.

  • I do agree with alll of the ideas you have introduced for
    your post. They’re really convincing and will certainly work.
    Still, the postss are too short for newbies.
    Could you please prolong them a little from subsequent time?
    Thanks for the post.


Leave a Reply