Monitoring and maintaining Azure SQL
Manage your Sitecore databases through the Azure App service with Azure SQL.
The default database engine for Sitecore on the Microsoft Azure® App Service is Microsoft Azure SQL®. It is where Sitecore stores all of its databases.
By default, all Sitecore databases use the Single Database model instead of the Elastic Database model. This is because Sitecore uses only four databases that by default each have varying performance requirements, which means that an Elastic Database model would be more expensive.
However, if you add more databases through additional Sitecore modules, or your own custom databases, you should reassess whether the Single or the Elastic model is more suitable for your requirements. For more information about SQL database options and performance, refer to the Azure SQL SKUs and prices and understand what is available in each service tier.
Securing your databases is one of the most important tasks. With Microsoft Azure SQ, there are several ways to ensure your databases are secure. The Microsoft Azure SQL site describes how you can:
Ensure you have a good understanding of how to configure the Azure SQL database firewall.
Use the Azure portal to create and manage Azure SQL database server-level firewall rules.
Important
When you set up Sitecore, to ensure that Sitecore can communicate with the databases, Sitecore creates both an SQL admin user and a number of role-specific non-admin user accounts. You must reserve the admin user profile for administrative tasks only and never for a runtime site. You can find full details on best practices for user profiles and roles on the Microsoft Azure SQL site.
The Azure SQL service automatically creates database backups at no additional charge and uses the Azure read-access geo-redundant storage (RA-GRS) to provide geo-redundancy. You do not need to do anything to make Azure SQL automatically back up your databases. However, if you want to keep backups in your own storage container, you can configure a long-term backup retention policy. The Microsoft Azure SQL site describes how to back up your databases and how to:
Configure long-term retention of automated backups in an Azure Recovery Services vault.
Azure SQL provides several ways for you to monitor your databases for issues, as well as look for ways to improve performance. You can create alerts using Microsoft Azure Monitor with metrics such as DTU, Database size percentage, and Deadlocks. You can also help improve your performance by using tools such as Query Performance Insight or SQL Database Advisor.
Sitecore does not recommend you apply any changes to the default SQL indexes configuration of the Sitecore databases. The index configuration provided by Sitecore is designed to fit general usage scenarios and it has been thoroughly tested.
There are, however, usage scenarios where changing the index configuration can be something to consider doing. If a Sitecore solution does a large number of reads ("selects"), changing the index types for the Unversioned, Versioned, and Shared fields tables to clustered can improve read performance.
There is a price to pay for this: inserting, saving, and deleting items will be slower, as will publishing.If you decide to change the configuration, you must test you changes with real data to see if the changes have actually improved performance as expected.
You must also be aware that Sitecore cannot support you if these changes introduce unwanted errors and have side-effects.
You can get more from these features if you:
Use the Azure SQL Database Query Performance Insight tool.
Use the SQL Database Advisor through the Azure portal.
Use the Azure portal to create alerts for the Azure SQL database.
Change the service tier and performance level (pricing tier) of a SQL database using the Azure portal.