ARM Template - Azure SQL Database Diagnostics & Log Analytics Workspace

Question

Using an ARM Template I want to enable diagnostics settings for my Azure SQL Database and have that data stored in a Log Analytics workspace I created. I want to enable the errors, timeouts, blocks and wait statistics logs and the basic metric option. I was able to get this working with a web app so I took a similar approach but my template fails and throws errors saying the metrics or diagnostic categories I reference don't exist or not supported. Here's some of the resources section my template:

I'm not sure if additional settings need to be enabled or referenced in the template for this to work but I would appreciate any help, thanks!

          "resources": [
                {
                    "type": "databases",
                    "apiVersion": "2019-06-01-preview",
                    "name": "[parameters('sqlDatabase')]",
                    "location": "[parameters('location')]",
                    "tags": {},
                    "dependsOn": [
                        "[parameters('sqlServer')]"
                    ],
                    "sku": {
                        "name": "GP_Gen5_4",
                        "tier": "GeneralPurpose"
                    },
                    "properties": {
                        "startIpAddress": "0.0.0.0",
                        "endIpAddress": "0.0.0.0"
                    }
                },

                {
                    "type": "providers/diagnosticSettings",
                    "name": "[concat('Microsoft.Insights/', parameters('diagnostics-name'))]",
                    "dependsOn": [
                        "[resourceId('Microsoft.Sql/servers', parameters('sqlServer'))]",
                        "[resourceId('Microsoft.Sql/servers/databases', parameters('sqlServer'), parameters('sqlDatabase'))]"
                    ],

                    "apiVersion": "2017-05-01-preview",
                    "properties": {
                        "name": "[parameters('diagnostics-name')]",
                        "workspaceId": "[concat('subscriptions/', subscription().subscriptionId, '/resourceGroups/', parameters('loganalytics-rg'), '/providers/Microsoft.OperationalInsights/workspaces/', parameters('workspacename'))]",
                        "logs": [
                            {
                                "category": "Errors",
                                "enabled": "true",
                                "retentionPolicy": {
                                    "enabled": "true",
                                    "days": 7
                                }
                            },
                            {
                                "category": "DatabaseWaitStatistics",
                                "enabled": "true",
                                "retentionPolicy": {
                                    "enabled": "true",
                                    "days": 7
                                }
                            },
                            {
                                "category": "Timeouts",
                                "enabled": "true",
                                "retentionPolicy": {
                                    "enabled": "true",
                                    "days": 7
                                }
                            },
                            {
                                "category": "Blocks",
                                "enabled": "true",
                                "retentionPolicy": {
                                    "enabled": "true",
                                    "days": 7
                                }
                            }

                        ]
                    }
                }
            ]
        }
    ]
}

Answer 1

After making the changes below to my ARM template I was able to deploy an azure sql server and database with diagnostics enabled on the DB which directs the diagnostic logs to an existing Log Analytics workspace:

"resources": [
        {
            "type": "Microsoft.Sql/servers",
            "apiVersion": "2019-06-01-preview",
            "name": "[parameters('sqlServer')]",
            "location": "[parameters('location')]",
            "properties": {
                "administratorLogin": "[parameters('adminLogin')]",
                "administratorLoginPassword": "[parameters('adminPassword')]",
                "version": "12.0"
            },

            "resources": [
                {
                    "type": "databases",
                    "apiVersion": "2019-06-01-preview",
                    "name": "[parameters('sqlDatabase')]",
                    "location": "[parameters('location')]",
                    "tags": {},
                    "dependsOn": [
                        "[parameters('sqlServer')]"
                    ],
                    "sku": {
                        "name": "GP_Gen5_4",
                        "tier": "GeneralPurpose"
                    },
                    "properties": {
                        "startIpAddress": "0.0.0.0",
                        "endIpAddress": "0.0.0.0"
                    }
                },

                {
                    "type": "microsoft.sql/servers/databases/providers/diagnosticSettings",
                    "name": "[concat(parameters('sqlServer'),'/',parameters('sqlDatabase'),'/microsoft.insights/', parameters('diagnostics-name'))]",
                    "dependsOn": [
                        "[parameters('sqlServer')]",
                        "[parameters('sqlDatabase')]"
                    ],

                    "apiVersion": "2017-05-01-preview",
                    "properties": {
                        
                        "workspaceId": "[concat('subscriptions/',subscription().subscriptionId,'/resourceGroups/',parameters('loganalytics-rg'),'/providers/Microsoft.OperationalInsights/workspaces/',parameters('workspacename'))]",
                        "logs": [
                            {
                                "category": "Errors",
                                "enabled": true
                            },

                            {
                                "category": "Timeouts",
                                "enabled": true
                            },

                            {
                                "category": "Blocks",
                                "enabled": true
                            },

                            {
                                "category": "Deadlocks",
                                "enabled": true
                            },

                            {
                                "category": "SQLInsights",
                                "enabled": true
                            },

                            {
                                "category": "DatabaseWaitStatistics",
                                "enabled": true
                            }
                        ],
                        "metrics":[
                            {
                                "category": "Basic",
                                "enabled": true
                            },
                            {
                                "category": "InstanceAndAppAdvanced",
                                "enabled": true
                            }
                        ]
                    }
                }
            ]
        }
    ]
}