Exportera (0) Skriv ut
Visa allt
EN
Det här innehållet finns inte tillgängligt på ditt språk men här finns den engelska versionen,

Azure SQL Database Development Considerations

Updated: December 6, 2013

Developing applications for Microsoft Microsoft Azure SQL Database is very similar to developing applications for SQL Server. This topic describes a few differences and some considerations when developing Microsoft 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 Microsoft Azure SQL Database, you must first create a Azure platform account. The Azure account is used to set up and manage your subscriptions and to bill for consumption of Azure, Azure AppFabric, and Microsoft Azure SQL Database. Once the Azure account is created, you can use the Azure Platform Management Portal to add or drop a Azure SQL Database server and a database. You can also use the Azure SQL Database Management API to programmatically add or drop Azure SQL Database servers and manage firewall rules associated to a server.

A Azure SQL Database server is a logical group of databases and acts as a central administrative point for multiple databases. When you create a 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 CREATE, ALTER, or DROP logins or databases. For more information on security administration in Microsoft 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 Database Management API. Before you can use the Database Management API, you must add a certificate for authentication as described in Authenticating Azure SQL Database Management API 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:

  • By using the Management Portal. 

  • By using the CREATE DATABASE statement.

For information about how to migrate a database from an on-premise instance of SQL Server to Azure SQL Database, see Migrating Databases to Azure SQL Database (formerly SQL Azure).

In addition, a code example provided in How to: Connect to Azure SQL Database Using ADO.NET demonstrates how to use the CREATE DATABASE statement in client application code.

noteNote
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 Microsoft 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 a 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 Microsoft 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 Microsoft Azure SQL Database. Microsoft 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.

Microsoft Azure SQL Database provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all Microsoft Azure SQL Database customers, your connection to the service may be closed due to the following conditions:

  • Excessive resource usage

  • Long-running queries

  • Long-running single transactions, between the BEGIN TRAN and END TRAN statements

  • Idle connections

This is different from how an on-premise 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 Microsoft Azure SQL Database, see Azure SQL Database General Guidelines and Limitations.

When the client application connects to Microsoft 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 statements demonstrate how to modify your application to trace the connectivity.

// Define global variables.
private static Dictionary<SqlConnection, Guid> _cache = new Dictionary<SqlConnection, Guid>();
public static SqlConnection conn;

// Connect to the sample database.
using (conn = new SqlConnection(connStringBuilder.ToString()))
{
   // Define the event handler.
   conn.StateChange += new StateChangeEventHandler(OnConnectionStateChange);
   conn.Open();
   using (SqlCommand command = conn.CreateCommand())
   {
      // Perform a query or an update.
      // Retrieve the session ID.
      Guid id = SessionId(conn);
      conn.Close();
    }
}

// Retrieve the session ID to track the connectivity. 
public static Guid SessionId(this SqlConnection conn)
{
    return _cache[conn];
}

// Implement your event handler.
public static void OnConnectionStateChange(object sender, StateChangeEventArgs e)
{
   SqlConnection conn = (SqlConnection)sender;
   switch (e.CurrentState)
   {    
      case ConnectionState.Broken:
           Console.WriteLine("Connection is broken...");
           _cache.Remove(conn);
           break;
      case ConnectionState.Closed:
           Console.WriteLine("Connection is closed...");
           _cache.Remove(conn);
           break;
      case ConnectionState.Open:
           Console.WriteLine("Connection is open...");
           using (SqlCommand cmd = conn.CreateCommand())
           {
               cmd.CommandText = "SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())";
              _cache[conn] = new Guid(cmd.ExecuteScalar().ToString());
            }
            break;
   }
}

See Also

Gruppinnehåll

Lägg till
Visa:
© 2014 Microsoft