Microsoft has provided excellent documentation on using Azure SQL. Rather than implementation technicalities, here I am hoping to provide with some things to think about when you start using Azure SQL.
Connection strings supplied to your application via the Azure portal are encrypted.
Connection strings supplied via the powershell cmdlets are not. You will need to implement a mechanism for this.
Your access to, and use of, the database can be throttled.
Ref: https://msdn.microsoft.com/en-us/library/azure/dn338079.aspx
Therefore use the Transient Fault Handling Application block
Ref: https://msdn.microsoft.com/en-us/library/hh680899%28v=pandp.50%29.aspx
Data on an Azure SQL database is on a shared instance of a database. Consider encrypting data your users may consider to be sensitive.
A good rule of thumb is that Azure SQL is approximately 10x slower than Sql Server.
At least part of the reason for this is the synchronous write to 3 instances of your Azure SQL database behind the scenes to help prevent data loss.
Another reason seems to be the data volume and latency of the request over HTTPS. Where possible try to host you Azure Sql database in the same region as the application(s) that consume it.
Given the slow write speed, consider batching writes to the Azure Sql database.
If you are using Entity Framework, the “EntityFramework.BulkInsert” library available via Nuget can be helpful.
Here is a rundown of other options: https://msdn.microsoft.com/library/azure/dn132615.aspx
As deleting is a comparatively slow operation, performing a “soft” delete (using a boolean property) is preferable to a “hard” delete.
If the record needs to be removed from the database, consider using a background process to periodically remove the “deleted” records.
Using Azure Sql, it is more likely that the scalability of your application will be limited by database throughput than for standard Sql Server. The focus should be on designing the application which consumes the database to scale out.
Therefore:
Access
Connection strings supplied to your application via the Azure portal are encrypted.
Connection strings supplied via the powershell cmdlets are not. You will need to implement a mechanism for this.
Your access to, and use of, the database can be throttled.
Ref: https://msdn.microsoft.com/en-us/library/azure/dn338079.aspx
Therefore use the Transient Fault Handling Application block
Ref: https://msdn.microsoft.com/en-us/library/hh680899%28v=pandp.50%29.aspx
Data Security
Data on an Azure SQL database is on a shared instance of a database. Consider encrypting data your users may consider to be sensitive.
Performance
A good rule of thumb is that Azure SQL is approximately 10x slower than Sql Server.
At least part of the reason for this is the synchronous write to 3 instances of your Azure SQL database behind the scenes to help prevent data loss.
Another reason seems to be the data volume and latency of the request over HTTPS. Where possible try to host you Azure Sql database in the same region as the application(s) that consume it.
Batch writes
Given the slow write speed, consider batching writes to the Azure Sql database.
If you are using Entity Framework, the “EntityFramework.BulkInsert” library available via Nuget can be helpful.
Here is a rundown of other options: https://msdn.microsoft.com/library/azure/dn132615.aspx
“Soft” deletes
As deleting is a comparatively slow operation, performing a “soft” delete (using a boolean property) is preferable to a “hard” delete.
If the record needs to be removed from the database, consider using a background process to periodically remove the “deleted” records.
Scale Out
Using Azure Sql, it is more likely that the scalability of your application will be limited by database throughput than for standard Sql Server. The focus should be on designing the application which consumes the database to scale out.
Therefore:
- Keep the database operations (& logic) simple.
- Generally, it is better to read and load more data into the application and discard some than to perform intensive operations in the database.
- Consider a sharding strategy
Comments
Post a Comment