Question on collation of Azure SQL database

Question

Hello,

I am new to Azure, please bear with me if my question seems silly.

We know changing database collation for Azure SQL database is very tricky and we use a specific collation (Latin1_General_100_CS_AS_KS_WS_SC) for our databases.

My question is can I create a policy which checks for collation before Azure SQL database gets created and fails the validation if collation is not Latin1_General_100_CS_AS_KS_WS_SC?

To put differently can we create policies to monitor and respond to specific database properties like compatibility level, etc. for Azure SQL databases?

Appreciate your insightful response. Thank you!!

Answer 1

Hi,
Yes, you can audit the value for collation via Azure Policy. The Azure Policy alias for that property is Microsoft.Sql/servers/databases/collation
I would assume you know how to use Azure Policy and what are Azure Policy aliases. You can set your policy with deny effect to avoid anyone creating database without setting the correct collation. I do not think you can audit compatibility level as I believe this is not a setting you set when you create Azure SQL database.

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

Answer 2

Hi @anil kumar ,

This policy would deny deploying databases with collations not listed as part of the allowed list.

Here and here are other SQL related policies which might help you.

Just FYI, you can get the source of above mentioned links from resources section of Azure policy documentation.