Created
May 15, 2018 12:45
-
-
Save davidobrien1985/3b6a438150f015a5d9ff5784a0ec64ba to your computer and use it in GitHub Desktop.
Azure SQL ARM template
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#", | |
| "contentVersion": "1.0.0.0", | |
| "parameters": { | |
| "administratorLogin": { | |
| "type": "string" | |
| }, | |
| "sqlPassword": { | |
| "type": "securestring" | |
| }, | |
| "collation": { | |
| "type": "string" | |
| }, | |
| "databaseName": { | |
| "type": "string" | |
| }, | |
| "tier": { | |
| "type": "string" | |
| }, | |
| "skuName": { | |
| "type": "string" | |
| }, | |
| "locationPrimary": { | |
| "type": "string" | |
| }, | |
| "locationSecondary": { | |
| "type": "string" | |
| }, | |
| "maxSizeBytes": { | |
| "type": "int" | |
| }, | |
| "serverNamePrimary": { | |
| "type": "string" | |
| }, | |
| "serverNameSecondary": { | |
| "type": "string" | |
| }, | |
| "zoneRedundant": { | |
| "type": "bool", | |
| "defaultValue": false | |
| }, | |
| "licenseType": { | |
| "type": "string", | |
| "defaultValue": "" | |
| }, | |
| "storageAccountNameprefix": { | |
| "type": "string", | |
| "defaultValue": "storagesqlaudit" | |
| }, | |
| "storageType": { | |
| "type": "string", | |
| "defaultValue": "Standard_GRS", | |
| "allowedValues": [ | |
| "Standard_LRS", | |
| "Standard_ZRS", | |
| "Standard_GRS", | |
| "Standard_RAGRS", | |
| "Premium_LRS" | |
| ] | |
| }, | |
| "sqlAdminAadGroupId": { | |
| "type": "securestring" | |
| }, | |
| "sqlAdminAadGroup": { | |
| "type": "string" | |
| }, | |
| "aadTenantId": { | |
| "type": "securestring" | |
| } | |
| }, | |
| "variables": { | |
| "storageAccountName": "[take(concat(parameters('storageAccountNameprefix'), uniqueString(resourceGroup().location)),24)]" | |
| }, | |
| "resources": [ | |
| { | |
| "apiVersion": "2015-05-01-preview", | |
| "type": "Microsoft.Sql/servers", | |
| "location": "[parameters('locationPrimary')]", | |
| "name": "[parameters('serverNamePrimary')]", | |
| "properties": { | |
| "administratorLogin": "[parameters('administratorLogin')]", | |
| "administratorLoginPassword": "[parameters('sqlPassword')]" , | |
| "version": "12.0" | |
| }, | |
| "resources": [ | |
| { | |
| "apiVersion": "2017-10-01-preview", | |
| "type": "Microsoft.Sql/servers/databases", | |
| "dependsOn": [ | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNamePrimary'))]" | |
| ], | |
| "location": "[parameters('locationPrimary')]", | |
| "name": "[concat(parameters('serverNamePrimary'), '/', parameters('databaseName'))]", | |
| "properties": { | |
| "collation": "[parameters('collation')]", | |
| "maxSizeBytes": "[parameters('maxSizeBytes')]", | |
| "zoneRedundant": "[parameters('zoneRedundant')]", | |
| "licenseType": "[parameters('licenseType')]" | |
| }, | |
| "sku": { | |
| "name": "[parameters('skuName')]", | |
| "tier": "[parameters('tier')]" | |
| }, | |
| "resources": [ | |
| { | |
| "name": "default", | |
| "type": "backupLongTermRetentionPolicies", | |
| "dependsOn": [ | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNamePrimary'), '/', 'databases', '/', parameters('databaseName'))]" | |
| ], | |
| "apiVersion": "2017-03-01-preview", | |
| "properties": { | |
| "weeklyRetention": "P1W", | |
| "monthlyRetention": "P4W", | |
| "yearlyRetention": "P520W", | |
| "weekOfYear": 1 | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "apiVersion": "2014-04-01-preview", | |
| "dependsOn": [ | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNamePrimary'))]" | |
| ], | |
| "location": "[parameters('locationPrimary')]", | |
| "name": "AllowAllWindowsAzureIps", | |
| "properties": { | |
| "endIpAddress": "0.0.0.0", | |
| "startIpAddress": "0.0.0.0" | |
| }, | |
| "type": "firewallrules" | |
| }, | |
| { | |
| "apiVersion": "2015-05-01-preview", | |
| "type": "auditingSettings", | |
| "name": "Default", | |
| "location": "[parameters('locationPrimary')]", | |
| "dependsOn": [ | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNamePrimary'))]", | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNamePrimary'), '/databases/',parameters('databaseName'))]", | |
| "[concat('Microsoft.Storage/storageAccounts/',variables('storageAccountName'))]" | |
| ], | |
| "properties": { | |
| "State": "Enabled", | |
| "storageEndpoint": "[concat('https://',variables('storageAccountName'),'.blob.core.windows.net')]", | |
| "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]", | |
| "retentionDays": 0, | |
| "auditActionsAndGroups": [ "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "DATABASE_LOGOUT_GROUP", "USER_CHANGE_PASSWORD_GROUP" ], | |
| "storageAccountSubscriptionId": "[subscription().subscriptionId]", | |
| "isStorageSecondaryKeyInUse": false | |
| } | |
| }, | |
| { | |
| "name": "ActiveDirectory", | |
| "type": "administrators", | |
| "apiVersion": "2014-04-01-Preview", | |
| "dependsOn": [ | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNamePrimary'))]", | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNamePrimary'), '/databases/',parameters('databaseName'))]" | |
| ], | |
| "location": "[parameters('locationPrimary')]", | |
| "properties": { | |
| "administratorType": "ActiveDirectory", | |
| "login": "[parameters('sqlAdminAadGroup')]", | |
| "sid": "[parameters('sqlAdminAadGroupId')]", | |
| "tenantId": "[parameters('aadTenantId')]" | |
| } | |
| } | |
| ], | |
| "tags": { | |
| "application": "sql", | |
| "owner": "david@davidobrienconsulting.com", | |
| "environment": "dev", | |
| "costcentre": "devops" | |
| } | |
| }, | |
| { | |
| "apiVersion": "2015-05-01-preview", | |
| "type": "Microsoft.Sql/servers", | |
| "location": "[parameters('locationSecondary')]", | |
| "name": "[parameters('serverNameSecondary')]", | |
| "properties": { | |
| "administratorLogin": "[parameters('administratorLogin')]", | |
| "administratorLoginPassword": "[parameters('sqlPassword')]", | |
| "version": "12.0" | |
| }, | |
| "resources": [ | |
| { | |
| "type": "Microsoft.Sql/servers/databases", | |
| "dependsOn": [ | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNameSecondary'))]", | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNamePrimary'), '/databases/',parameters('databaseName'))]" | |
| ], | |
| "sku": { | |
| "name": "[parameters('skuName')]", | |
| "tier": "[parameters('tier')]" | |
| }, | |
| "name": "[concat(parameters('serverNameSecondary'), '/', parameters('databaseName'))]", | |
| "apiVersion": "2017-10-01-preview", | |
| "location": "[parameters('locationSecondary')]", | |
| "properties": { | |
| "createMode": "OnlineSecondary", | |
| "sourceDatabaseId": "[resourceId('Microsoft.Sql/servers/databases', parameters('serverNamePrimary'), parameters('databaseName'))]", | |
| "zoneRedundant": "[parameters('zoneRedundant')]", | |
| "licenseType": "[parameters('licenseType')]" | |
| }, | |
| "resources": [ | |
| { | |
| "name": "default", | |
| "type": "backupLongTermRetentionPolicies", | |
| "dependsOn": [ | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNameSecondary'), '/', 'databases', '/', parameters('databaseName'))]" | |
| ], | |
| "apiVersion": "2017-03-01-preview", | |
| "properties": { | |
| "weeklyRetention": "P1W", | |
| "monthlyRetention": "P4W", | |
| "yearlyRetention": "P520W", | |
| "weekOfYear": 1 | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "apiVersion": "2015-05-01-preview", | |
| "type": "auditingSettings", | |
| "name": "Default", | |
| "location": "[parameters('locationSecondary')]", | |
| "dependsOn": [ | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNameSecondary'))]", | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNameSecondary'), '/databases/',parameters('databaseName'))]", | |
| "[concat('Microsoft.Storage/storageAccounts/',variables('storageAccountName'))]" | |
| ], | |
| "properties": { | |
| "State": "Enabled", | |
| "storageEndpoint": "[concat('https://',variables('storageAccountName'),'.blob.core.windows.net')]", | |
| "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]", | |
| "retentionDays": 0, | |
| "auditActionsAndGroups": [ "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "DATABASE_LOGOUT_GROUP", "USER_CHANGE_PASSWORD_GROUP" ], | |
| "storageAccountSubscriptionId": "[subscription().subscriptionId]", | |
| "isStorageSecondaryKeyInUse": false | |
| } | |
| }, | |
| { | |
| "apiVersion": "2014-04-01-preview", | |
| "dependsOn": [ | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNameSecondary'))]" | |
| ], | |
| "location": "[parameters('locationPrimary')]", | |
| "name": "AllowAllWindowsAzureIps", | |
| "properties": { | |
| "endIpAddress": "0.0.0.0", | |
| "startIpAddress": "0.0.0.0" | |
| }, | |
| "type": "firewallrules" | |
| }, | |
| { | |
| "name": "ActiveDirectory", | |
| "type": "administrators", | |
| "apiVersion": "2014-04-01-Preview", | |
| "dependsOn": [ | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNameSecondary'))]", | |
| "[concat('Microsoft.Sql/servers/', parameters('serverNameSecondary'), '/databases/',parameters('databaseName'))]" | |
| ], | |
| "location": "[parameters('locationPrimary')]", | |
| "properties": { | |
| "administratorType": "ActiveDirectory", | |
| "login": "[parameters('sqlAdminAadGroup')]", | |
| "sid": "[parameters('sqlAdminAadGroupId')]", | |
| "tenantId": "[parameters('aadTenantId')]" | |
| } | |
| } | |
| ], | |
| "tags": { | |
| "application": "sql", | |
| "owner": "david@davidobrienconsulting.com", | |
| "environment": "dev", | |
| "costcentre": "devops" | |
| } | |
| }, | |
| { | |
| "name": "[variables('storageAccountName')]", | |
| "type": "Microsoft.Storage/storageAccounts", | |
| "location": "[resourceGroup().location]", | |
| "apiVersion": "2015-06-15", | |
| "dependsOn": [ "[concat('Microsoft.Sql/servers/', parameters('serverNamePrimary'))]", "[concat('Microsoft.Sql/servers/', parameters('serverNameSecondary'))]" ], | |
| "properties": { | |
| "accountType": "[parameters('storageType')]" | |
| }, | |
| "tags": { | |
| "application": "sql", | |
| "owner": "david@davidobrienconsulting.com", | |
| "environment": "dev", | |
| "costcentre": "devops" | |
| } | |
| } | |
| ], | |
| "outputs": {} | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment