{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "credentials_ElasticDbJob_SqlUsername": {
      "type": "SecureString"
    },
    "credentials_CentralDataLoad_SqlUsername":{
      "type": "SecureString"
    },
    "credentials_ElasticDbJob_SqlUsername_password": {
      "type": "SecureString"
    },
    "credentials_CentralDataLoad_password": {
      "type": "SecureString"
    },
     "server_ControlServerName": {
      "type": "String"
    },
     "servers_ControlServer_externalid": {
      "defaultValue": "",
      "type": "String"
    },
    "credentials_JobAgentName": {
      "type": "String"
    },
    "resourceGroupName": {
      "type": "string"
    },
    "tagValues": {
      "type": "object",
      "defaultValue": ""
    },
    "StepCreateProcedure": {
      "type": "string"
    },
    "StepCreateTable": {
      "type": "string"
    },
    "StepCommandExecuteProcedure": {
      "type": "string"
    },
    "JobName1": {
      "type": "string"
    },
    "JobName2": {
      "type": "string"
    },
    "TargetGroup_IndexMaintenanceGroup":{
      "type": "string"
    },
    "TargetGroup_IndexMaintenanceGroupVCore":{
      "type": "string"
    },
    "SQL_CollectCommandLogData":{
      "type": "string"
    },
    "SQL_RebuildStatistics":{
      "type": "string"
    },
     "DatabaseName_CommandLog":{
       "type": "string"
     },
     "subscription_id":{
       "type":"string"
     }
  },
  "variables": {
    "vJobName1":"[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/', parameters('JobName1'))]",
    "vJobName2":"[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/', parameters('JobName2'))]",
    "vTargetGroup_IndexMaintenanceGroup": "[resourceId('Microsoft.Sql/servers/jobAgents/targetGroups', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), parameters('TargetGroup_IndexMaintenanceGroup'))]",
    "vTargetGroup_IndexMaintenanceGroupVCore": "[resourceId('Microsoft.Sql/servers/jobAgents/targetGroups', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), parameters('TargetGroup_IndexMaintenanceGroupVCore'))]",
    "vcredential_ElasticDbJob_SqlUsername": "[resourceId('Microsoft.Sql/servers/jobAgents/credentials', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), parameters('credentials_ElasticDbJob_SqlUsername'))]",
    "vcredentials_CentralDataLoad_SqlUsername": "[resourceId('Microsoft.Sql/servers/jobAgents/credentials', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), parameters('credentials_CentralDataLoad_SqlUsername'))]"
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers/jobAgents",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'))]",
      "location": "centralus",
      "tags": "[parameters('tagValues')]",
      "sku": {
        "name": "Agent",
        "capacity": 100
      },
      "properties": {
        "databaseId": "[concat(parameters('servers_ControlServer_externalid'), '/databases/sqldbElasticDBJobs')]"
      }
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/credentials",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/', parameters('credentials_ElasticDbJob_SqlUsername'))]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'))]"
      ],
      "properties": {
        "username": "[parameters('credentials_ElasticDbJob_SqlUsername')]",
        "password": "[parameters('credentials_ElasticDbJob_SqlUsername_password')]"
      }
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/credentials",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/CentralDataLoad')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'))]"
      ],
      "properties": {
        "password": "[parameters('credentials_ElasticDbJob_SqlUsername_password')]",
        "username": "[parameters('credentials_ElasticDbJob_SqlUsername')]"
      }
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs",
      "apiVersion": "2017-03-01-preview",
      "name": "[variables('vJobName1')]",
      "dependsOn": [
      ],
      "properties": {
        "description": "Weekly job to rebuild indexes and Update Statistics as needed.",
        "schedule": {
          "startTime": "2021-03-07T08:00:00.00Z",
          "endTime": "9999-12-31T11:59:59Z",
          "type": "Recurring",
          "enabled": true,
          "interval": "P1W"
        }
      },
      "Tags": "[parameters('tagValues')]"
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs",
      "apiVersion": "2017-03-01-preview",
      "name": "[variables('vJobName2')]",
      "dependsOn": [
        ],
      "properties": {
        "description": "Indexes and updates Stats on Serverless databases",
        "schedule": {
          "startTime": "2021-03-07T08:00:00.00Z",
          "endTime": "9999-12-31T11:59:59Z",
          "type": "Recurring",
          "enabled": true,
          "interval": "P1W"
        }
      },
      "Tags": "[parameters('tagValues')]"
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs/steps",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/IndexMaintenanceJob/Deploy CommandLog')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJob')]"      ],
      "properties": {
        "targetGroup": "[variables('vTargetGroup_IndexMaintenanceGroup')]",
        "credential": "[variables('vcredential_ElasticDbJob_SqlUsername')]",
        "action": {
          "type": "TSql",
          "source": "Inline",
          "value": "[parameters('StepCreateTable')]"
        },
        "executionOptions": {
          "timeoutSeconds": 43200,
          "retryAttempts": 2,
          "initialRetryIntervalSeconds": 5,
          "maximumRetryIntervalSeconds": 120,
          "retryIntervalBackoffMultiplier": 2
        }
      }
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs/steps",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/IndexMaintenanceJobVCore/Deploy CommandLog')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJobVCore')]"
         ],
      "properties": {
        "targetGroup": "[variables('vTargetGroup_IndexMaintenanceGroupVCore')]",
        "credential": "[variables('vcredential_ElasticDbJob_SqlUsername')]",
        "action": {
          "type": "TSql",
          "source": "Inline",
          "value": "[parameters('StepCreateTable')]"
        },
        "executionOptions": {
          "timeoutSeconds": 43200,
          "retryAttempts": 2,
          "initialRetryIntervalSeconds": 5,
          "maximumRetryIntervalSeconds": 120,
          "retryIntervalBackoffMultiplier": 2
        }
      }
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs/steps",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/IndexMaintenanceJobVCore/Deploy CommandExecute procedure')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJobVCore')]",
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs/steps', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJobVCore', 'Deploy CommandLog')]"
      ],
      "properties": {
         "targetGroup": "[variables('vTargetGroup_IndexMaintenanceGroupVCore')]",
        "credential": "[variables('vcredential_ElasticDbJob_SqlUsername')]",
        "action": {
          "type": "TSql",
          "source": "Inline",
          "value": "[parameters('StepCommandExecuteProcedure')]"
        },
        "executionOptions": {
          "timeoutSeconds": 43200,
          "retryAttempts": 2,
          "initialRetryIntervalSeconds": 5,
          "maximumRetryIntervalSeconds": 120,
          "retryIntervalBackoffMultiplier": 2
        }
      }
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs/steps",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/IndexMaintenanceJob/Deploy CommandExecute procedure')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJob')]",
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs/steps', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJob', 'Deploy CommandLog')]"
        ],
      "properties": {
       "targetGroup": "[variables('vTargetGroup_IndexMaintenanceGroup')]",
        "credential": "[variables('vcredential_ElasticDbJob_SqlUsername')]",
        "action": {
          "type": "TSql",
          "source": "Inline",
          "value": "[parameters('StepCommandExecuteProcedure')]"
        },
        "executionOptions": {
          "timeoutSeconds": 43200,
          "retryAttempts": 2,
          "initialRetryIntervalSeconds": 5,
          "maximumRetryIntervalSeconds": 120,
          "retryIntervalBackoffMultiplier": 2
        }
      }
    }    ,
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs/steps",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/IndexMaintenanceJob/Deploy IndexOptimize')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJob')]",
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs/steps', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJob', 'Deploy CommandExecute procedure')]" 
       ],
      "properties": {
        "targetGroup": "[variables('vTargetGroup_IndexMaintenanceGroup')]",
        "credential": "[variables('vcredential_ElasticDbJob_SqlUsername')]",
        "action": {
          "type": "TSql",
          "source": "Inline",
          "value": "[parameters('StepCreateProcedure')]"
        },
        "executionOptions": {
          "timeoutSeconds": 43200,
          "retryAttempts": 2,
          "initialRetryIntervalSeconds": 5,
          "maximumRetryIntervalSeconds": 120,
          "retryIntervalBackoffMultiplier": 2
        }
      }
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs/steps",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/IndexMaintenanceJobVCore/Deploy IndexOptimize')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJobVCore')]",
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs/steps', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJobVCore','Deploy CommandExecute procedure')]"
       ],
      "properties": {
        "targetGroup": "[variables('vTargetGroup_IndexMaintenanceGroupVCore')]",
        "credential": "[variables('vcredential_ElasticDbJob_SqlUsername')]",
        "action": {
          "type": "TSql",
          "source": "Inline",
          "value": "[parameters('StepCreateProcedure')]"
      },
        "executionOptions": {
          "timeoutSeconds": 43200,
          "retryAttempts": 2,
          "initialRetryIntervalSeconds": 5,
          "maximumRetryIntervalSeconds": 120,
          "retryIntervalBackoffMultiplier": 2
        }
      }
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs/steps",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'),'/IndexMaintenanceJob/Defrag Indexes')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJob')]",
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs/steps', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJob', 'Deploy IndexOptimize')]" 
      ],
      "properties": {
        "targetGroup": "[variables('vTargetGroup_IndexMaintenanceGroup')]",
        "credential": "[variables('vcredential_ElasticDbJob_SqlUsername')]",
        "action": {
          "type": "TSql",
          "source": "Inline",
          "value": "DECLARE @DbName SYSNAME = DB_NAME();\r\n\r\nEXECUTE dbo.IndexOptimize @Databases = @DbName,\r\n                                  @LogToTable = 'Y';"
        },
        "executionOptions": {
          "timeoutSeconds": 43200,
          "retryAttempts": 2,
          "initialRetryIntervalSeconds": 5,
          "maximumRetryIntervalSeconds": 120,
          "retryIntervalBackoffMultiplier": 2
        }
      }
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs/steps",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'),'/IndexMaintenanceJobVCore/Defrag Indexes')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJobVCore')]",
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs/steps', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJobVCore', 'Deploy IndexOptimize')]" 
      ],
      "properties": {
        "targetGroup": "[variables('vTargetGroup_IndexMaintenanceGroupVCore')]",
        "credential": "[variables('vcredential_ElasticDbJob_SqlUsername')]",
        "action": {
          "type": "TSql",
          "source": "Inline",
          "value": "DECLARE @DbName SYSNAME = DB_NAME();\r\n\r\nEXECUTE dbo.IndexOptimize @Databases = @DbName,\r\n                                  @LogToTable = 'Y';"
        },
        "executionOptions": {
          "timeoutSeconds": 43200,
          "retryAttempts": 2,
          "initialRetryIntervalSeconds": 5,
          "maximumRetryIntervalSeconds": 120,
          "retryIntervalBackoffMultiplier": 2
        }
      }
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs/steps",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/IndexMaintenanceJob/Rebuild Statistics')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJob')]",
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs/steps', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJob', 'Defrag Indexes')]"
      ], 
      "properties": {
        "targetGroup": "[variables('vTargetGroup_IndexMaintenanceGroup')]",
        "credential": "[variables('vcredential_ElasticDbJob_SqlUsername')]",
        "action": {
          "type": "TSql",
          "source": "Inline",
          "value": "[parameters('SQL_RebuildStatistics')]"
        },
        "executionOptions": {
          "timeoutSeconds": 43200,
          "retryAttempts": 2,
          "initialRetryIntervalSeconds": 5,
          "maximumRetryIntervalSeconds": 120,
          "retryIntervalBackoffMultiplier": 2
        }
      }
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs/steps",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/IndexMaintenanceJobVCore/Rebuild Statistics')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJobVCore')]",
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs/steps', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJobVCore', 'Defrag Indexes')]" 
      ],
      "properties": {
       "targetGroup": "[variables('vTargetGroup_IndexMaintenanceGroupVCore')]",
        "credential": "[variables('vcredential_ElasticDbJob_SqlUsername')]",
        "action": {
          "type": "TSql",
          "source": "Inline",
          "value": "[parameters('SQL_RebuildStatistics')]"
        },
        "executionOptions": {
          "timeoutSeconds": 43200,
          "retryAttempts": 2,
          "initialRetryIntervalSeconds": 5,
          "maximumRetryIntervalSeconds": 120,
          "retryIntervalBackoffMultiplier": 2
        }
      }
    },
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs/steps",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/IndexMaintenanceJob/CollectCommandLogData')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJob')]",
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs/steps', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJob', 'Rebuild Statistics')]"
      ],
      "properties": {
        "targetGroup": "[variables('vTargetGroup_IndexMaintenanceGroup')]",
        "credential": "[variables('vcredential_ElasticDbJob_SqlUsername')]",
        "action": {
          "type": "TSql",
          "source": "Inline",
          "value": "[parameters('SQL_CollectCommandLogData')]"
        },
        "output": {
          "type": "SqlDatabase",
          "serverName": "[parameters('server_ControlServerName')]",
          "databaseName": "[parameters('DatabaseName_CommandLog')]",
          "schemaName": "dbo",
          "tableName": "CentralCommandLog",
          "credential": "[resourceId('Microsoft.Sql/servers/jobAgents/credentials', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), parameters('credentials_CentralDataLoad_SqlUsername'))]"
        },
        "executionOptions": {
          "timeoutSeconds": 43200,
          "retryAttempts": 2,
          "initialRetryIntervalSeconds": 5,
          "maximumRetryIntervalSeconds": 120,
          "retryIntervalBackoffMultiplier": 2
        }
      }
    },    
    {
      "type": "Microsoft.Sql/servers/jobAgents/jobs/steps",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('server_ControlServerName'), '/', parameters('credentials_JobAgentName'), '/IndexMaintenanceJobVCore/CollectCommandLogData')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJobVCore')]",
        "[resourceId('Microsoft.Sql/servers/jobAgents/jobs/steps', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), 'IndexMaintenanceJobVCore', 'Rebuild Statistics')]"
      ],
      "properties": {
        "targetGroup": "[variables('vTargetGroup_IndexMaintenanceGroupVCore')]",
        "credential": "[variables('vcredential_ElasticDbJob_SqlUsername')]",
        "action": {
          "type": "TSql",
          "source": "Inline",
          "value": "[parameters('SQL_CollectCommandLogData')]"
        },
        "output": {
          "type": "SqlDatabase",
           "serverName": "[parameters('server_ControlServerName')]",
          "databaseName": "[parameters('DatabaseName_CommandLog')]",
          "schemaName": "dbo",
          "tableName": "CentralCommandLog",
          "credential": "[resourceId('Microsoft.Sql/servers/jobAgents/credentials', parameters('server_ControlServerName'), parameters('credentials_JobAgentName'), parameters('credentials_CentralDataLoad_SqlUsername'))]"
        },
        "executionOptions": {
          "timeoutSeconds": 43200,
          "retryAttempts": 2,
          "initialRetryIntervalSeconds": 5,
          "maximumRetryIntervalSeconds": 120,
          "retryIntervalBackoffMultiplier": 2
        }
      }
    }
  ]
}