[Windows Azure] Development Considerations in Windows Azure SQL Database

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:

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

In addition, a code example provided in How to: Connect to Windows 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 Windows Azure SQL Database applications:

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

When the client application connects to Windows 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.

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

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值