Code Library

When writing applications that need to connect to SQL Database, you can use popular programming languages such as .NET, PHP, Java, and many more. Entity Framework, starting with .NET Framework 3.5 Service Pack 1, is also supported. One of the first things you’ll need is the connection string. You can obtain the connection string from the Azure portal by clicking the Show Database Connection Strings link in the Essentials group for the desired SQL Database instance. Doing so opens a new Database Connection Strings blade, as seen in Figure 6-14, displaying the connection string in multiple formats, including ADO.NET, PHP, JDBC, and ODBC (which works for Node.js applications).

The connection string for SQL Database is similar to what you would use for SQL Server. For example, for ADO.NET, the connection string format is as follows:

ADO.NET Database connection string
Server=tcp:{your_db_server_name_here}.database.windows.net,1433;Database={your_db_name_here};User ID={your_username_here}@{your_db_server_name_here};Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Note that the connection string sets the TrustServerCertificate property to False and the Encrypt property to True. This is to provide additional protection while accessing SQL Database over the Internet. Doing so helps thwart potential man-in the-middle attacks. SQL Database will force the connection to be encrypted regardless of the setting.

When writing code against SQL Database, it is important to defend your code against transient errors. Transient errors are errors that are intermittent and likely will be resolved if the command is retried. These errors are more common with SQL Database than with databases accessed via a local area network (LAN). This is due to the inherently unreliable network that is the Internet and the fact that as a managed service, SQL Database might periodically undergo maintenance activities that could cause connections to drop temporarily. Applications should plan for and defend against transient errors by incorporating retry logic when creating connections or executing commands against SQL Database. Be sure to choose a retry strategy that provides sufficient time for the platform to attempt to recover from whatever caused the initial failure, does not saturate the platform with a rapid succession of retry attempts, and has a maximum number of retry attempts and/or retry duration. For example, an exponential back-off approach with a preset maximum number of attempts is often suitable—be sure to validate for your application scenario.

For .NET applications using Entity Framework, Entity Framework 6 contains connection resiliency/retry logic that will detect transient errors from SQL Database and retry the command. For other .NET applications, Enterprise Library 5 and 6 (see http://msdn.microsoft.com/library/ff648951.aspx) from Microsoft Patterns & Practices contain an application block called the Transient Fault Handling Application Block. This library can also be used to detect transient errors and retry commands.

There is much more to designing a resilient cloud-based application than implementing basic retry logic. It may also be necessary to reconsider how an application processes data. For instance, it is often helpful to implement an eventually consistent solution, potentially using queues and background tasks to complete requests. It may also be worth considering breaking read and write operations across separate databases, potentially using the Active Geo-Replication feature in SQL Database to replicate operations on the write database to the read database.

Source of Information : Microsoft Azure Essentials Fundamentals of Azure Second Edition