Question on collation of Azure SQL database

anil kumar 1,641 Reputation points
2021-01-29T05:29:40.8+00:00

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!!

Azure SQL Database
Azure Policy
Azure Policy
An Azure service that is used to implement corporate governance and standards at scale for Azure resources.
803 questions
0 comments No comments
{count} votes

Accepted answer
  1. Stanislav Zhelyazkov 21,506 Reputation points MVP
    2021-01-29T08:13:16.053+00:00

    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. tbgangav-MSFT 10,386 Reputation points
    2021-01-29T08:23:55.483+00:00

    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.

    61768-image.png

    1 person found this answer helpful.