Home / AZURE / Azure SQL Best Practices

Azure SQL Best Practices

Hello again,

As I mentioned in the earlier post , where we looked at how the Azure SQL migration can be performed with different options available from Microsoft, below are some of the best practices to follow when migrating the your SQL server database to Azure.

  • Pricing Tier: Azure SQL comes as multiple options(Standard and Premium). In both SQL tiers, the charges(DTUs) are static regardless the databases are being used all the time on not. You get a certain DTUs assigned to the databases even if you don’t have the database being run at the capacity all the time. Majority of the databases have this case unless, the database is highly transactional. Microsoft recently released shared elastic pools to resolve this issue which helps with the cost by adding the databases in a shared DTU pool(eDTU) and DTUs can be scaled up and down on as needed basis. On top of this, if the databases are added initially in Standard or Premium pool ,the databases are monitored by Microsoft and recommendations are provided on which databases are good candidate for the Elastic Pools. More information of the Elastic Database Pools for Azure SQL can be found here.
  • SQL Firewall: Out of the Box, SQL Azure blocks all access to the databases based on the firewall. External access to the Azure SQL can be blocked with native firewall on the Azure SQL server. This firewall also only allows 1433 port to the endpoint where the database needs to connect. There is also an option to allow the Azure SQL access from All Azure services in the firewall section so that all the Azure services(webapps, VMs,etc) can access the Azure SQL without the firewall rules. More information on the Azure SQL firewall can be found here. Firewalls can be enabled at Server and database levels based on the security needed for your organisation.
  • SQL Authentication: Before Azure V12, Azure SQL only supported the local SQL authentication. But V12 changed all that, now the Azure SQL support Azure AD authentication. This is the recommended approach on how users and applications can connect to the Azure SQL databases in a more controlled and secure manner. You can assign both users and groups to the Azure SQL and provide RBAC to the Azure SQL databases. More information on how to enable Azure AD for Azure SQL can be found here.
  • SQL Monitoring: With the V12 version of Azure SQL, you get better monitoring capabilities, such as Query performance insight,performance recommendations and automatic tuning for the SQL databases. These options allow administrator to look at what is causing bottlenecks in the SQL performance without running the queries. More information on how this can be achieved can be found here. Microsoft OMS(Operations Management Suite) can also be leveraged to ingest the database audit logs from the Azure SQL and provide measurable metrics for reporting.

Thats it for now, till next time.:)

 

About prabthind

Prab Thind is currently working as Senior Consultant at Infront Consulting Group. Works primarily with Azure and System Center. Prab holds multiple Microsoft Certifications in Azure and Private Cloud.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

x

Check Also

Azure MFA Server with Windows VPN

Integrate Azure MFA(Multi-Factor Authentication) Server with Windows VPN Azure MFA is one of the many ...