Microsoft Azure SQL Database Step by Step

---

To create a Customer Web API using ADO.NET, follow these steps:

1. Right-click the Models folder in the WineCloudWebApi project in the Solution Explorer, and choose Add | Class to display the Add New Item dialog.

2. Name the class Customer.cs, and click Add.

3. Replace the template code generated automatically by Visual Studio with the code shown in Listing 8-4 earlier, and build the WineSolution by selecting Build | Build Solution in the menu at the top or pressing Ctrl+Shift+B.

4. Right-click the Controllers folder in the WineCloudWebApi project in the Solution Explorer, and choose Add | Controller to display the Add Scaffold dialog.

5. Choose the Web API 2 Controller - Empty controller, and click Add. This displays the Add Controller dialog.

6. Type CustomerController for Controller name, and click Add.

7. Replace the template code with the code shown in Listing 8-5 earlier, and build the WineSolution by selecting Build | Build Solution in the menu at the top or pressing Ctrl+Shift+B.

You have now added a new ASP.NET Web API controller for the Customer table using ADO.NET instead of Entity Framework. The Customer Web API provides methods to get customer data, but it doesn’t provide methods for adding or updating customers. Similar to the Wine entity model, the Customer entity model, as shown in Listing 8-4, doesn’t have any data-access dependencies and is a simple POCO class. The Customer controller, as shown in Listing 8-5, queries the Customer table using the ADO.NET SqlConnection, SqlCommand, and SqlDataReader classes. It creates and populates instances of the Customer entity model class and returns the Customer objects. ASP.NET Web API then serializes the Customer objects into the appropriate response formats and sends them back to the requestor.

Testing the Customer Web API

Now that you have created a Customer Web API, you should test it to verify that it returns the correct data from the Customer table in the WineCloudDb database:

1. Select the WineSolution in the Solution Explorer, and press F5 or click Debug | Start Debugging. This opens Internet Explorer or your default debugging web browser at the default page of the WineCloudWebApi project.

2. Append the URL in the browser’s address bar with api/Customer, and press Enter. This executes the GetCustomers method on the WineController and responds with the list of Customers from the WineCloudDb database. Internet Explorer’s default behavior asks if you would like to save or open the results from the Web API call.

3. Click Open to view the results.

4. Append the URL in the browser’s address bar with /3, and press Enter. This executes the GetCustomer method on the CustomerController and responds with the Customer record for CustomerId 3.

5. Click Open to view the results.

You have now set up an ASP.NET Web API project to get data from the Wine and Customer tables in the WineCloudDb database. Your project has Web APIs that use both Entity Framework Code First and raw ADO.NET. With this project now in place, we will build upon it in the following sections of this chapter to demonstrate performance tuning and scalability with SQL Database.

Managing SQL Database connections

Database connections in Microsoft Azure SQL Database can have different characteristics and behaviors than you’ve likely experienced with SQL Server on-premises. To provide reliability and great user experiences, these behaviors must be accounted for in your applications that use SQL Database. In addition to these different connection behaviors, you should also implement general best practices for interacting with databases, regardless of whether they are on-premises or in the cloud.

Opening late, closing early

There are limits on most compute resources, both in the cloud and on-premises. In SQL Database, one aspect that is limited is the number of database connections you can have open at any given time. To be a well-behaved consumer of SQL Database resources and connections, you should open database connections only when you need to interact with a SQL Database and as late as possible. When you are finished interacting with the database, you should close and release the connection. Entity Framework abstracts away the database connections, and you expect Entity Framework to be a good consumer of database resources. But when you are explicitly opening database connections using raw ADO.NET, you should open the database connections as close to the database interactions as possible. In Listing 8-5, you can see the call to connection.Open()occurs immediately before the SqlCommand is executed. Because the connection is wrapped in a using statement, as soon as the body of the using statement is exited, the connection will be disposed of, which closes the connection and releases its resources.

Pooling connections

There is overhead and a performance penalty when establishing new database connections. To help minimize this performance penalty, ADO.NET can pool connections. Connection pooling works by keeping a client’s connections open in a pool of managed connections. When the client needs to open a connection, ADO.NET checks for an existing connection in the pool with a connection string that matches. If a connection already exists, it returns that connection to the client to interact with the database. If a connection doesn’t exist, a new connection is established. When the client is finished with the connection and closes the connection, instead of destroying the connection, it is returned to the pool. Only connections with the exact same configuration and connection strings can be pooled, and the connection pool is scoped to an AppDomain. By default, ADO.NET uses connection pooling, and it is a generally recommended best practice to dramatically reduce the cost of opening connections.

Recovering from connection faults

SQL Database connections can be unexpectedly terminated for many reasons. The network architecture of SQL Database includes firewalls, load balancers, gateways, and database nodes. There are many components between the client and the database. Errors in these components can cause connections to terminate unexpectedly. SQL Database might also terminate connections when failing over to another node in the three-node clusters. In addition to errors and failover, SQL Database analyzes the activity of connections and the resource-consumption metrics of the database node. If SQL Database determines that it needs to throttle a connection to keep the overall service healthy, and to avoid negatively affecting other tenants of SQL Database, it may throttle and terminate the connection. These types of connection terminations are referred to as transient faults. Transient faults are temporary issues or errors that are expected to recover quickly. If your application is aware of these transient faults, it can try to reconnect over a short period of time and the connection loss might not even be noticeable to the user. If not, the application will throw an exception and the user will likely be presented with an error.

Not all terminated connections are caused by transient errors. However, errors raised by SQL Database return an error number you can use to determine what type of error occurred. Your application can compare the error number to the known transient error numbers. If there’s a match, your application can try to reconnect and retry the transaction; if there’s not a match, the error is not temporary and will need to be handled differently. If that sounds like a lot of work, don’t worry—the Microsoft Azure Customer Advisory Team, in conjunction with Microsoft Patterns & Practices, has simplified this by creating the Transient Fault Handling Application Block. This library is part of the Enterprise Library family of application blocks, and it can be integrated into your application to simplify the recovery during transient faults. It can be used with SQL Database and other Microsoft Azure services that might have transient errors, including Service Bus and Storage.

In the following sections, you will integrate the Transient Fault Handling Application Block into the WineCloudWebApi project. Specifically, you’ll modify the Wine and Customer Web APIs to recover gracefully from transient fault conditions.

Adding the Transient Fault Handling Application Block

To get started with the Transient Fault Handling Application Block, you must add a reference to it and integrate it into your project. In the following steps, you will add a reference to the Transient Fault Handling Application Block using NuGet. If you’ve not used NuGet before, there are a few basic facts we should tell you. It is a package manager that simplifies adding references to your projects with all of their associated dependencies. NuGet also makes it easy to update your dependencies and libraries when new versions are released. Documentation about NuGet and a repository of the publicly available NuGet packages are available at www.nuget.org.

Follow these steps to add a reference to the Transient Fault Handling Application Block:

1. Right-click the WineCloudWebApi project in the Solution Explorer, and choose Manage NuGet Packages to display the Manage NuGet Packages dialog.

2. Choose Online on the left, type transient fault sql database in the search box in the upper right corner, and press Enter.

3. Select Enterprise Library - Transient Fault Handling Application Block - Microsoft Azure SQL Database Integration from the search results, and click Install as shown in Figure 8-10.

FIGURE 8-10 Installing the Transient Fault Handling Application Block using the Manage NuGet Packages dialog

4. In the License Acceptance dialog, click I Accept as shown in Figure 8-11. This downloads and adds a reference to Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.Data.dll and its dependencies.

FIGURE 8-11 Accepting the license for the Transient Fault Handling Application Block

5. When installation completes, click Close.

You have now added a reference to the Transient Fault Handling Application Block using NuGet and are now ready to get started integrating it into the WineCloudWebApi project. In the next two sections, you will modify the Wine and Customer Web APIs to recover from transient errors.

Using the Transient Fault Handling Application Block with ADO.NET

The Transient Fault Handling Application Block provides components and classes you can use to configure how transient faults are detected and handled in your application. Table 8-1 shows the major components of the Transient Fault Handling Application Block.

TABLE 8-1 Major components of the Transient Fault Handling Application Block

Detection strategies are used by the Transient Fault Handling Application Block to determine if an error is transient and whether the failed service call should be retried. Detection strategies are classes that implement the ITransientErrorDetectionStrategy interface, and the Transient Fault Handling Application Block has implementations for SQL Database, Service Bus, Storage, and Caching. The procedures in this chapter use the SqlDatabaseTransientErrorDetectionStrategy class to detect SQL Database transient error conditions, but you can implement your own detection strategy using the ITransientErrorDetectionStrategy interface.

Retry strategies are used to define the frequency and number of times the Transient Fault Handling Application Block will retry and attempt to automatically recover from a transient error. Retry strategies are classes that derive from the RetryStrategy class. Table 8-2 shows the retry strategies that are implemented in the Transient Fault Handling Application Block.

TABLE 8-2 Retry strategies in the Transient Fault Handling Application Block

Using a detection strategy and a retry strategy, you create a retry policy using the RetryPolicy class. The retry policy will inspect errors that occur using the detection strategy and execute the retry strategy if an error is identified as a transient error. The Transient Fault Handling Application Block provides multiple ways for you to integrate it into your applications. You can use new classes that encapsulate existing classes to make them transient-fault and retry aware. ReliableSqlConnection is a class that encapsulates SqlConnection. You can use it in place ofSqlConnection to automatically handle retrying when transient faults occur. There are also extension methods for existing classes, including the SqlConnection and SqlCommand classes. These extension methods can be used to utilize retry policies, as shown in the following procedures.

To handle transient faults in the GetCustomers method, follow these steps:

1. Expand the Controllers folder beneath the WineCloudWebApi project in Solution Explorer.

2. Double-click CustomerController.cs, and add the following using statement to the list of using statements at the top: using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling;

3. Locate the GetCustomers method, and type the following two lines of code just below the assignment of the connectionString variable:

var retryStrategy = new Incremental(5, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(2)); var retryPolicy = new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(retryStrategy);

4. Replace connection.Open(); with connection.OpenWithRetry(retryPolicy);

5. Replace command.ExecuteReader() with command.ExecuteReaderWithRetry(retryPolicy)

To handle transient faults in the GetCustomer method, follow these steps:

1. Locate the GetCustomer method, and type the following two lines of code just below the assignment of the connectionString variable:

var retryStrategy = new Incremental(5, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(2)); var retryPolicy = new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(retryStrategy);

2. Replace connection.Open(); with connection.OpenWithRetry(retryPolicy);

3. Replace command.ExecuteReader() with command.ExecuteReaderWithRetry(retryPolicy)

You are now handling transient fault conditions that might occur when using SQL Database using the Incremental retry strategy in the Customer Web API. The Incremental retry strategy works with three parameters.

The first parameter defines the number of times to retry. In the preceding procedure, you configured the Incremental retry strategy to retry five times. The second and third parameters together define the amount of time between each retry attempt. The second parameter defines the time to wait before the first retry, and the third parameter defines the amount of time to add to each subsequent retry attempt. In the preceding procedure, you set the second parameter to one second and the third parameter to two seconds. This will retry after one second, three seconds, five seconds, seven seconds, and nine seconds.

By defining a retry policy using the Transient Fault Handling Application Block, and by using extension methods on the SqlConnection and SqlCommand classes provided by the Transient Fault Handling Application Block, the Customer Web API is now resilient to transient error conditions, and minimal code changes were needed. In the next section, you will handle transient error conditions with Entity Framework.

Using the Transient Fault Handling Application Block with Entity Framework

In the previous section, you implemented retry logic when transient error conditions occurred using the Transient Fault Handling Application Block. You did that using extension methods on the SqlConnection and SqlCommand classes provided by the Transient Fault Handling Application Block. For the Customer Web API, that solution works really well because the data-access code is raw ADO.NET that uses these classes. The Wine Web API uses Entity Framework for data access, and the ADO.NET SqlConnection and SqlCommand classes are not exposed. Therefore, handling transient error conditions using Entity Framework requires a slightly different solution.

In the following procedure, you will use the same RetryPolicy class you used earlier with the extension methods on the SqlConnection and SqlCommand classes. The RetryPolicy class has an ExecuteAction method that is designed to execute code that encapsulates ADO.NET. This is useful when working with object relational mapping (ORM) and data-access platforms such as Entity Framework. Listing 8-6 shows a modified WineController class with Entity Framework calls that result in queries to SQL Database surrounded by the ExecuteAction method. The manual changes you need to apply to the code generated automatically by the scaffolding is indicated in bold type. Any exceptions that bubble up to the RetryPolicy will be inspected for transient error conditions and retried appropriately.

LISTING 8-6 The WineController.cs class using the Transient Fault Handling Application Block to handle transient error conditions