I’ve worked with Azure SQL databases for years, mostly from some flavor of Microsoft’s Entity Framework. These days, all of our new apps are using Entity Framework Core. Here are two small things that I’ve noticed people tend to forget over the years that can improve your app’s Entity Framework/Azure SQL experience.
1. Enable Connection Resiliency on your DbContext
Like most cloud database offerings, Azure SQL databases get shifted around between servers and can be temporarily unavailable for a second or so randomly. While you might get lucky and not encounter this with a lightly used app, it can cause your database connections to randomly hang up/time out. The solution to this problem is to retry the database query after a short period of time. Microsoft recommends you implement “connection resiliency” to combat this. Thankfully, Entity Framework Core has something built-in for this, you just have to turn it on. You can do so by updating your Startup.cs in your Asp.Net Core web app to set up your db context in the following fashion:
The “EnableRetryOnFailure” option enables resilient SQL connections, and we can configure exactly how it works (max retries, the delay between retries, etc). More information about connection resiliency can be found here, and here.
2. Use Contained Database Users
While this second tip isn’t specific to the entity framework, my EF Core applications have benefitted greatly from taking this action. Sometimes even with connection resiliency enabled, you may still notice random timeouts when working with your Azure SQL Database, even when your app isn’t under heavy load. In the past, I’ve seen things like this in my error logs that I just couldn’t explain happening randomly:
System.Data.SqlClient.SqlException – Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
If you are using the server level login created when you set up the database on Azure, or a different server-level login, that may be why. Using server-level SQL Azure logins from apps is not very efficient, because the master DB can be on a different SQL server from the database you are actually accessing, and sometimes this causes bottlenecks and slowdowns because your server-level login has to access master first.
To solve this, you can create a “Contained” user login that only has access to the database you are working with. You can do so by executing the following against your SQL database:
Create user ContainedUser with password = ‘Password’
ALTER ROLE [db_datareader] ADD MEMBER [ContainedUser ]
ALTER ROLE [db_datawriter] ADD MEMBER [ContainedUser ]
ALTER ROLE [db_ddladmin] ADD MEMBER [ContainedUser ]
Note, it’s the use of “Create user” instead of “Create login” that sets up a contained database user vs a user with a login to the master database. The user-created above has just enough permissions to do the typical operations needed by a web application.
Then, update your app’s connection string to use this contained user. You may be surprised at the positive results! You can read more about contained users here.
