[Windows Azure] Development Considerations in Windows Azure SQL Database

Love The Way You Lie 2022-10-02 06:52 5阅读 0赞

# Development Considerations in Windows Azure SQL Database #

3 out of 5 rated this helpful \- [Rate this topic][]

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

A SQL Database server is a logical group of databases and acts as a central administrative point for multiple databases. When you create a 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 Windows Azure SQL Database, see [Managing Databases and Logins in Windows Azure SQL Database][].

By default, all access to your SQL Database server is blocked by the SQL Database firewall. To begin using your SQL Database server, you must specify one or more server-level firewall rules that enable access to your 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 Windows 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 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 [Windows Azure SQL Database Firewall][].

You can access the billing details of your SQL Database accounts on the SQL Database server by using **sys.database\_usage** and **sys.bandwidth\_usage** system views. For more information, see [Accounts and Billing in Windows Azure SQL Database][].

## Creating SQL Databases ##

There are two ways to create 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 SQL Database, see [Migrating Databases to Windows Azure SQL Database (formerly SQL Azure)][Migrating Databases to Windows Azure SQL Database _formerly SQL Azure].

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

<table> 
 <tbody> 
  <tr> 
   <th align="left"><img title="note" src="http://i.msdn.microsoft.com/dynimg/IC101471.gif" alt="note">Note </th> 
  </tr> 
  <tr> 
   <td>To change the edition and name of your database after creation, you can use the <a href="http://msdn.microsoft.com/en-us/library/windowsazure/ff394109.aspx" rel="nofollow">ALTER DATABASE</a> statement. <p>&nbsp;</p> </td> 
  </tr> 
 </tbody> 
</table>

## Building and Hosting SQL Database Applications ##

There are two ways to build and host Windows Azure SQL Database applications:

*  Host your application code on-premises at your own corporate data center, but host your database in SQL Database. Your application code uses client libraries to access the database(s) in SQL Database. For more information about the client libraries that are available, see [Guidelines and Limitations (Windows Azure SQL Database)][Guidelines and Limitations _Windows Azure SQL Database]. For example code, see [How to: Connect to Windows Azure SQL Database Using ADO.NET][How to_ Connect to Windows Azure SQL Database Using ADO.NET] topic.  
      
    
 *  Host your application code in Windows Azure and your database in SQL Database. Your application can use the same client libraries to access the database(s) in 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 SQL Database. For example code, see [How to: Connect to Windows Azure SQL Database Through WCF Data Services][How to_ Connect to Windows Azure SQL Database Through WCF Data Services].

You can minimize the network latency of requests to the SQL Database by hosting your application in the Windows Azure platform. Deploying your application to Windows Azure provides more efficient transactions between your application and SQL Database compared to an application hosted outside Windows Azure. For more information about hosting your application and data in the cloud, see [Windows Azure SQL Database Data Access][].

Bandwidth used between SQL Database and Windows Azure or Windows Azure AppFabric is free within the same sub-region or data center. When deploying a Windows Azure application, locate the application and the SQL Database in the same sub-region to avoid bandwidth costs. For more information, see [Accounts and Billing in Windows Azure SQL Database][].

## Developing SQL Database Applications ##

Developing applications for Windows 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 Windows Azure SQL Database. Windows 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.

Windows Azure SQL Database provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all Windows 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 Windows Azure SQL Database, see [General Guidelines and Limitations (Windows Azure SQL Database)][General Guidelines and Limitations _Windows Azure SQL Database].

When the client application connects to Windows Azure SQL Database, [CONTEXT\_INFO (Transact-SQL)][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.

C\#

Copy

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

#### Concepts ####

[Development (Windows Azure SQL Database)][Development _Windows Azure SQL Database]  
[Windows Azure SQL Database Concepts][]

#### Other Resources ####

[Guidelines and Limitations (Windows Azure SQL Database)][Guidelines and Limitations _Windows Azure SQL Database]  
[Windows Azure SQL Database Performance and Elasticity Guide][]

转载于:https://www.cnblogs.com/licheng/p/3266384.html

[Rate this topic]: http://msdn.microsoft.com/en-us/library/windowsazure/ee730903.aspx#feedback
[Windows Azure SQL Database Management API]: http://go.microsoft.com/fwlink/p/?LinkId=210815
[Managing Databases and Logins in Windows Azure SQL Database]: http://msdn.microsoft.com/en-us/library/windowsazure/ee336235.aspx
[Authenticating Windows Azure SQL Database Management API Requests]: http://msdn.microsoft.com/en-us/library/windowsazure/gg715282.aspx
[Windows Azure SQL Database Firewall]: http://msdn.microsoft.com/en-us/library/windowsazure/ee621782.aspx
[Accounts and Billing in Windows Azure SQL Database]: http://msdn.microsoft.com/en-us/library/windowsazure/ee621788.aspx
[CREATE DATABASE]: http://msdn.microsoft.com/en-us/library/windowsazure/ee336274.aspx
[Migrating Databases to Windows Azure SQL Database _formerly SQL Azure]: http://msdn.microsoft.com/en-us/library/windowsazure/ee730904.aspx
[How to_ Connect to Windows Azure SQL Database Using ADO.NET]: http://msdn.microsoft.com/en-us/library/windowsazure/ee336243.aspx
[Guidelines and Limitations _Windows Azure SQL Database]: http://msdn.microsoft.com/en-us/library/windowsazure/ff394102.aspx
[How to_ Connect to Windows Azure SQL Database Through WCF Data Services]: http://msdn.microsoft.com/en-us/library/windowsazure/ee621789.aspx
[Windows Azure SQL Database Data Access]: http://msdn.microsoft.com/en-us/library/windowsazure/ee336239.aspx
[General Guidelines and Limitations _Windows Azure SQL Database]: http://msdn.microsoft.com/en-us/library/windowsazure/ee336245.aspx
[CONTEXT_INFO _Transact-SQL]: http://go.microsoft.com/fwlink/p/?LinkId=168941
[Development _Windows Azure SQL Database]: http://msdn.microsoft.com/en-us/library/windowsazure/ee336225.aspx
[Windows Azure SQL Database Concepts]: http://msdn.microsoft.com/en-us/library/windowsazure/ee336256.aspx
[Windows Azure SQL Database Performance and Elasticity Guide]: http://go.microsoft.com/fwlink/p/?LinkID=221876