Lesson Learned #205: Best Practices connecting to Azure SQL Database and Managed Instance

[Jose_Manuel_Jurado]

We often get a lot of questions from our customers about best practices when connecting to a database. In this video we are going to cover the most relevant.

In this video you could find out some interesting topics about, for example:

• Coding
  • Open / Close the connection as fast as possible.
  • Avoid idle connections.
  • Connection Pooling.
  • Connection may fail, so, retry Logic is key in Azure SQL.
  • Try .. Catch for all connections and command executions.
•  Client:
  • Keep your connectivity drivers updated.
• Service Database Tier:

  • If you are using Single database

    • Serverless / Autoclose (Azure): Use a robust connection retry logic in the resume process.

  • If you are using Elastic Database pool (Azure)

    • All databases are running on the same SQL Server Instance.

    • So, your database might be impacted about other workload databases.

• Others:

  • Contained User instead of Logins.

  • Redirect connection policy.

  • Use TCP:servername and port 1433.

  • Disable MARS.

  • Use application_name parameter in the connection string to identify your application. 
•  

Self-Help tool to identify the connectivity issue:

In the following two links you could find some Powershell scripts that could help you to identify possible connectivity issues, the first link will help you to identify connection and execution latency and the second one will help you to identify possible connectivity issue checking, for example, DNS resolution, taking network traces, etc..

• Lesson Learned #196: Latency and execution time in Azure SQL Database and Azure SQL Managed Instance...
• Azure/SQL-Connectivity-Checker: This PowerShell script will run some connectivity checks from this m...

Enjoy!

•