This is by no means a complete list but more of a personal list of features I have seen not setup or just missed out when looking at Azure SQL DB. After reading, not only will I hope that you agree but it may provoke you to double check your setups.

1. Wrong Tiers

You do not want to under provision which leads to performance issues nor do you want to over provision and waste money.

I would say the below compute utilization could be improved.

This has always been tricky, but I use PowerShell scripts to really help here via the DMA (Data Migration Assistant) installation. Find the tool from https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15

Once installed, open a CMD and run the following commands against your on-premises workload.

.\SkuRecommendationDataCollectionScript.ps1 -ComputerName sqlserver22 -OutputFilePath c:\localcounters.csv -CollectionTimeInSeconds 120 -DbConnectionString ‘Server=sqlserver22;Initial Catalog=master;Integrated Security=SSPI;’

.\DmaCmd.exe /Action=SkuRecommendation /SkuRecommendationInputDataFilePath=”c:\localcounters.csv” /SkuRecommendationOutputResultsFilePath=”C:\localprices.html” /SkuRecommendationTsvOutputResultsFilePath=”C:\localprices.tsv” /SkuRecommendationPreventPriceRefresh=true

2. Allow Azure Services and resources to access this server setting set to on/off?

I always set this to off. I do not like it ON.

Why? Because I like to control things via vnets (maybe IPs if really needed – it depends on your solution). Nowadays you can use private endpoint connections which allow connections from within a vnet to a private IP.  Sure, you may want to use IP addresses, if you do then I suggest database level firewall rules over server level, especially if you use failover groups.

3. No Active Directory Admin setup

No excuses for not setting this up now that is it fully available and has been for some time.

It is more secure than SQL authentication, allows for password rotation from a single place, and you can use in combination with MFA (Multi factor authentication). I like the below diagram because it shows you how all the components fit and interact with each other, especially between your on-premises AD and Azure AD.

I walk through an example here: https://blobeater.blog/2017/05/22/ad-authentication-and-azure-sql-database/

4.  Forgetting to enable ADS – Advanced Data Security.

Personally, this is one of the most important features I see missed out, I like to enable it for its minimal cost because it allows access to the following features:

• Data discovery and classification.
• Vulnerability assessments.
• Advanced Threat protection.

The data discovery one might not be that useful when compared to the other two which I heavily rely on. With the vulnerability assessments you have the option of automating a scheduled scan, so if something does change over the week at least you will be aware via an email. You never know, someone in your team might have granted an account very high permission set!

When it comes to Advanced threat detection, well it is sophisticated stuff. The aim of this feature is to alert you from potential SQL injection, access from unusual location or data centres, access from unfamiliar principal and brute force SQL credentials attack patterns. I absolutely want to know if these activities are happening.

 

5. No alerts setup – now dynamic thresholds.

There is a new concept to alerting I have been testing out called dynamic thresholds. It is different to conventional alerts because it leverages advanced machine learning (ML) to learn metrics’ historical behaviour, identify patterns and anomalies that indicate possible service issues, it triggers only when the monitored metric doesn’t behave as expected. You can opt to use standard alerting types, but regardless many setups have no configured any form of alerting. Personally, I would like to know if my DTUs are being heavily utilized.

Anything else you do or use?