Azure SQL Database Development Considerations
Updated: November 25, 2014
Developing applications for Microsoft Azure SQL Database is very similar to developing applications for SQL Server. This topic describes a few differences and some considerations when developing Azure SQL Database applications. In addition, the topic provides the basic steps that you need to take as a developer and lists the recommended coding practices.
Creating SQL Database Servers
To use Azure SQL Database, you must first create an Azure platform account. The Azure account is used to set up and manage your subscriptions and to bill for usage of Azure, Azure AppFabric, and Azure SQL Database. Once the Azure account is created, you can use the Azure management portal to add or remove an Azure SQL Database server and a database. You can also use the Azure SQL Service Management API to programmatically add or remove Azure SQL Database servers and manage firewall rules associated with a server.
An Azure SQL Database server is a logical group of databases and acts as a central administrative point for multiple databases. When you create an Azure SQL Database server, a read-only master database is created automatically. The master database keeps track of which logins have permission to create databases or other logins. You must be connected to the master database whenever you
DROP logins or databases. For more information on security administration in Azure SQL Database, see Managing Databases and Logins in Azure SQL Database.
By default, all access to your Azure SQL Database server is blocked by the Azure SQL Database firewall. To begin using your Azure SQL Database server, you must specify one or more server-level firewall rules that enable access to your Azure SQL Database server. The server-level firewall rules can be managed using the management portal user interface, or programmatically using the Service Management API. Before you can use the Service Management API, you authenticate the request using one of the methods described in Authenticating Service Management Requests.
After you have created a server-level firewall setting, you can use the server-level principal login and the master database to view and edit your firewall settings. In the master database, the firewall settings are referred to as rules. The sys.firewall_rules view displays the current server-level firewall settings, and the sp_set_firewall_rule and sp_delete_firewall_rule stored procedures allow you to change the server-level firewall rules.
Further, if you want to control access to certain databases in your Azure SQL Database server, you can create database-level firewall rules for those databases. You can create database-level firewall rules for the master and user databases. You can connect to a database and view the database-level firewall rules in the sys.database_firewall_rules view. The sp_set_database_firewall_rule and sp_delete_database_firewall_rule stored procedures in the master and user databases allow you to change the database-level firewall rules for the respective database. For more information about the server-level and database-level firewall rules, see Azure SQL Database Firewall.
You can access the billing details of your Azure SQL Database accounts on the Azure SQL Database server by using sys.database_usage and sys.bandwidth_usage system views. For more information, see Azure SQL Database Accounts and Billing.
Creating SQL Databases
There are two ways to create Azure SQL Databases:
For information about how to migrate a database from an on-premises instance of SQL Server to Azure SQL Database, see Migrating Databases to Azure SQL Database.
|To change the edition and name of your database after creation, you can use the ALTER DATABASE statement.|
Building and Hosting SQL Database Applications
There are two ways to build and host Azure SQL Database applications:
Host your application code on-premises at your own corporate data center, but host your database in Azure SQL Database. Your application code uses client libraries to access the database(s) in Azure SQL Database. For more information about the client libraries that are available, see Azure SQL Database Guidelines and Limitations. For example code, see How to: Connect to Azure SQL Database Using ADO.NET topic.
Host your application code in Azure and your database in Azure SQL Database. Your application can use the same client libraries to access the database(s) in Azure SQL Database. In this case, your client application may be a desktop or Silverlight application that uses the benefits of the Entity Data Model and the WCF Data Services client to access data that is hosted in Azure SQL Database. For example code, see How to: Connect to Azure SQL Database Through WCF Data Services.
You can minimize the network latency of requests to the Azure SQL Database by hosting your application in the Azure platform. Deploying your application to Azure provides more efficient transactions between your application and Azure SQL Database compared to an application hosted outside Azure. For more information about hosting your application and data in the cloud, see Azure SQL Database Data Access.
Bandwidth used between Azure SQL Database and Azure or Azure AppFabric is free within the same region or data center. When deploying an Azure application, locate the application and the Azure SQL Database in the same region to avoid bandwidth costs. For more information, see Azure SQL Database Accounts and Billing.
Developing SQL Database Applications
Developing applications for Azure SQL Database is very similar to developing applications for SQL Server. You can choose from many application types and technologies when you develop an application that accesses Azure SQL Database. Azure SQL Database works with third-party applications, PHP, and many Microsoft applications, such as ADO.NET, the Entity Framework, WCF Data Services, and ODBC.
Azure SQL Database provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all Azure SQL Database customers, your connection to the service may be closed due to the following conditions:
Excessive resource usage
Long-running single transactions, between the BEGIN TRAN and END TRAN statements
This is different from how an on-premises instance of SQL Server works.
To provide a seamless user experience when a connection is closed, incorporate retry logic in your application to detect a closed connection and then attempt to complete the interrupted action. For more information on connection limitations in Azure SQL Database, see Azure SQL Database General Guidelines and Limitations.
When the client application connects to Azure SQL Database, CONTEXT_INFO (Transact-SQL) is set with a unique session specific GUID value automatically. Retrieve this GUID value and use it in your application to trace the connectivity problems.
The following C# code statement demonstrates how to modify your application to trace the connectivity.