locked
Problem with SQL Virtual Machine deployment - timeout after 1:30h RRS feed

  • Question

  • Hello, 

    I am trying to automatise deployment of SQL Virtual Machine in some existing resource group in West Europe. I have prepared some template and trying deploy vm from this template. I am using PowerShell and invoke command like: 

    Template and Param file is just taken from SQL VM creation via portal.

    Scenario is always the same. Disk, NetworkInterface and Virtual Machine is deployed successfully, but if it comes to SqlVirtualMachine then it stuck in status like: New-AzResourceGroupDeployment

    STATUS: Created, PROVISIONING STATE: Running

    and got timeout after ~1:30h

    At the end I have online VirtualMachine and SqlVirtualMachine is in offline state with information like: 
    The SQL virtual machine resource is not in a valid state for management. The cause could be the SQL virtual machine is in bad states, the underlying virtual machine state is invalid (Example: stopped, deallocated or failed) or the virtual machine was not found. Please make sure the SQL virtual machine and the underlying virtual machine are healthy.



    And the most strange for me is fact, that sometimes deployment finish successfully. This seems to be rare and quite random.

    I did some tests and when deployment stuck, then I stopped it and removed SqlVirtualMachine resource. After this I modified template and left only part for SqlVirtualMachine and ran script one more time. In a result I got successful deployment of SqlVirtualMachine. Result is not always the same, but still more often it is successful than full deployment at once. 

    So I am looking for solution to make deployment stable. 

    In my scripts I also deploy many different VMs (with standard Ubuntu or Windows 2016 server images) and many other type of resources and they are working fine every time. 

    I am also curios about role of sqlStorageDeploymentToken field in template. It is given as a parameter but it is not used at all in template. As ready downloaded template had this field then I left it as it was but not sure about its purpose. 

    My params file

    {
        "$schema": "......",
        "contentVersion": "1.0.0.0",
        "parameters": {
            "subnetName": {
                "value": "data-subnet"
            },
            "osDiskType": {
                "value": "Premium_LRS"
            },
            "dataDisks": {
                "value": [
                    {
                        "lun": 0,
                        "createOption": "attach",
                        "caching": "ReadOnly",
                        "writeAcceleratorEnabled": false,
                        "id": null,
                        "name": "sql-test-wizard_DataDisk_0",
                        "storageAccountType": null,
                        "diskSizeGB": null
                    }
                ]
            },
            "dataDiskResources": {
                "value": [
                    {
                        "name": "sql-test-wizard_DataDisk_0",
                        "sku": "Premium_LRS",
                        "properties": {
                            "diskSizeGB": 1024,
                            "creationData": {
                                "createOption": "empty"
                            }
                        }
                    }
                ]
            },
            "virtualMachineSize": {
                "value": "Standard_DS2_v2"
            }, 
            "sqlConnectivityType": {
                "value": "Private"
            },
            "sqlPortNumber": {
                "value": 1433
            },
            "sqlStorageDisksCount": {
                "value": 1
            },
            "sqlStorageWorkloadType": {
                "value": "General"
            },
            "sqlStorageDisksConfigurationType": {
                "value": "NEW"
            },
            "sqlStorageStartingDeviceId": {
                "value": 2
            },
            "sqlStorageDeploymentToken": {
                "value": 39238
            },
            "dataPath": {
                "value": "some path to F drive"
            },
            "dataDisksLUNs": {
                "value": [
                    0
                ]
            },
            "logPath": {
                "value": "some path to F drive"
            },
            "logDisksLUNs": {
                "value": [
                    0
                ]
            },
            "tempDbPath": {
                "value": "some path to F drive"
            },
            "tempDisksLUNs": {
                "value": [
                    0
                ]
            },
            "rServicesEnabled": {
                "value": "false"
            }
        }
    }

    My template file: 

    {
        "$schema": "...",
        "contentVersion": "1.0.0.0",
        "parameters": {
            "TagsObject": {
                "type": "Object"
            },
            "PrivateIPAddress": {
                "type": "string"
            },
            "networkInterfaceName": {
                "type": "string"
            },
            "vnetName": {
                "type": "string" 
            },
            "subnetName": {
                "type": "string"
            },
            "virtualMachineName": {
                "type": "string"
            },
            "virtualMachineNameShort": {
                "type": "string"
            },
            "osDiskType": {
                "type": "string"
            },
            "dataDisks": {
                "type": "array"
            },
            "dataDiskResources": {
                "type": "array"
            },
            "virtualMachineSize": {
                "type": "string"
            },
            "adminUsername": {
                "type": "string"
            },
            "adminPassword": {
                "type": "secureString"
            },
            "sqlVirtualMachineName": {
                "defaultValue": "[parameters('virtualMachineNameShort')]",
                "type": "string"
            },
            "sqlConnectivityType": {
                "type": "string"
            },
            "sqlPortNumber": {
                "type": "int"
            },
            "sqlStorageDisksCount": {
                "type": "int"
            },
            "sqlStorageWorkloadType": {
                "type": "string"
            },
            "sqlStorageDisksConfigurationType": {
                "type": "string"
            },
            "sqlStorageStartingDeviceId": {
                "type": "int"
            },
            "sqlStorageDeploymentToken": {
                "type": "int"
            },
            "dataPath": {
                "type": "string"
            },
            "dataDisksLUNs": {
                "type": "array"
            },
            "logPath": {
                "type": "string"
            },
            "logDisksLUNs": {
                "type": "array"
            },
            "tempDbPath": {
                "type": "string"
            },
            "tempDisksLUNs": {
                "type": "array"
            },
            "rServicesEnabled": {
                "type": "string"
            },
            "DataDiskName": {
                "type": "string"   
            }
        },
        "variables": {
            "vnetId": "[concat(resourceGroup().id, '/providers/Microsoft.Network/virtualNetworks/', parameters('vnetName'))]",
            "subnetRef": "[concat(variables('vnetId'), '/subnets/', parameters('subnetName'))]"
        },
        "resources": [
            {
                "name": "[parameters('networkInterfaceName')]",
                "type": "Microsoft.Network/networkInterfaces",
                "apiVersion": "2019-07-01",
                "location": "[resourceGroup().location]",
                "dependsOn": [],
                "tags": "[parameters('TagsObject')]",
                "properties": {
                    "ipConfigurations": [
                        {
                            "name": "ipconfig1",
                            "properties": {
                                "subnet": {
                                    "id": "[variables('subnetRef')]"
                                },
                                "privateIPAddress": "[parameters('PrivateIPAddress')]",
                                "privateIPAllocationMethod": "Static"
                            }
                        }
                    ]
                }
            },
            {
                "type": "Microsoft.Compute/disks",
                "apiVersion": "2019-07-01",
                "name": "[parameters('DataDiskName')]",
                "location": "[resourceGroup().location]",
                "tags": "[parameters('TagsObject')]",
                "sku": {
                    "name": "[parameters('dataDiskResources')[copyIndex()].sku]"
                },
                "properties": "[parameters('dataDiskResources')[copyIndex()].properties]",
                "copy": {
                    "name": "managedDiskResources",
                    "count": "[length(parameters('dataDiskResources'))]"
                }
            },
            {
                "type": "Microsoft.Compute/virtualMachines",
                "apiVersion": "2019-07-01",
                "name": "[parameters('virtualMachineNameShort')]",
                "location": "[resourceGroup().location]",
                "dependsOn": [
                    "managedDiskResources",
                    "[concat('Microsoft.Network/networkInterfaces/', parameters('networkInterfaceName'))]"
                ],
                "tags": "[parameters('TagsObject')]",
                "properties": {
                    "hardwareProfile": {
                        "vmSize": "[parameters('virtualMachineSize')]"
                    },
                    "storageProfile": {
                        "osDisk": {
                            "createOption": "fromImage",
                            "managedDisk": {
                                "storageAccountType": "[parameters('osDiskType')]"
                            }
                        },
                        "imageReference": {
                            "publisher": "MicrosoftSQLServer",
                            "offer": "SQL2016SP2-WS2016",
                            "sku": "SQLDEV",
                            "version": "latest"
                        },
                        "copy": [
                            {
                                "name": "dataDisks",
                                "count": "[length(parameters('dataDisks'))]",
                                "input": {
                                    "lun": "[parameters('dataDisks')[copyIndex('dataDisks')].lun]",
                                    "createOption": "[parameters('dataDisks')[copyIndex('dataDisks')].createOption]",
                                    "caching": "[parameters('dataDisks')[copyIndex('dataDisks')].caching]",
                                    "writeAcceleratorEnabled": "[parameters('dataDisks')[copyIndex('dataDisks')].writeAcceleratorEnabled]",
                                    "diskSizeGB": "[parameters('dataDisks')[copyIndex('dataDisks')].diskSizeGB]",
                                    "managedDisk": {
                                        "id": "[coalesce(parameters('dataDisks')[copyIndex('dataDisks')].id, if(equals(parameters('DataDiskName'), json('null')), json('null'), resourceId('Microsoft.Compute/disks', parameters('DataDiskName'))))]",
                                        "storageAccountType": "[parameters('dataDisks')[copyIndex('dataDisks')].storageAccountType]"
                                    }
                                }
                            }
                        ]
                    },
                    "networkProfile": {
                        "networkInterfaces": [
                            {
                                "id": "[resourceId('Microsoft.Network/networkInterfaces', parameters('networkInterfaceName'))]"
                            }
                        ]
                    },
                    "osProfile": {
                        "computerName": "[parameters('virtualMachineNameShort')]",
                        "adminUsername": "[parameters('adminUsername')]",
                        "adminPassword": "[parameters('adminPassword')]",
                        "windowsConfiguration": {
                            "enableAutomaticUpdates": true,
                            "provisionVmAgent": true
                        }
                    }
                }
            },
            {
                "type": "Microsoft.SqlVirtualMachine/SqlVirtualMachines",
                "apiVersion": "2017-03-01-preview",
                "name": "[parameters('sqlVirtualMachineName')]",
                "location": "[resourceGroup().location]",
                "dependsOn": [
                    "[resourceId('Microsoft.Compute/virtualMachines', parameters('sqlVirtualMachineName'))]"
                ],
                "properties": {
                    "virtualMachineResourceId": "[resourceId('Microsoft.Compute/virtualMachines', parameters('sqlVirtualMachineName'))]",
                    "sqlManagement": "Full",
                    "SqlServerLicenseType": "PAYG",
                    "AutoPatchingSettings": {
                        "Enable": false
                    },
                    "KeyVaultCredentialSettings": {
                        "Enable": false,
                        "CredentialName": ""
                    },
                    "StorageConfigurationSettings": {
                        "DiskConfigurationType": "[parameters('sqlStorageDisksConfigurationType')]",
                        "StorageWorkloadType": "[parameters('sqlStorageWorkloadType')]",
                        "SQLDataSettings": {
                            "LUNs": "[parameters('dataDisksLUNs')]",
                            "DefaultFilePath": "[parameters('dataPath')]"
                        },
                        "SQLLogSettings": {
                            "LUNs": "[parameters('logDisksLUNs')]",
                            "DefaultFilePath": "[parameters('logPath')]"
                        },
                        "SQLTempDbSettings": {
                            "DefaultFilePath": "[parameters('tempDbPath')]",
                            "LUNs": "[parameters('tempDisksLUNs')]"
                        }
                    },
                    "ServerConfigurationsManagementSettings": {
                        "SQLConnectivityUpdateSettings": {
                            "ConnectivityType": "[parameters('sqlConnectivityType')]",
                            "Port": "[parameters('sqlPortNumber')]",
                            "SQLAuthUpdateUserName": "",
                            "SQLAuthUpdatePassword": ""
                        },
                        "AdditionalFeaturesServerConfigurations": {
                            "IsRServicesEnabled": "[parameters('rServicesEnabled')]"
                        }
                    }
                },
                "tags": "[parameters('TagsObject')]"
            }
        ],
        "outputs": {
            "adminUsername": {
                "type": "String",
                "value": "[parameters('adminUsername')]"
            }
        }
    }


    • Edited by GrzegorzCzm Wednesday, November 20, 2019 3:05 PM added template content
    Wednesday, November 20, 2019 3:03 PM

All replies

  • Just to confirm, are you hardcoding any variables? Or are you entering them manually every time you run the template? 
    Wednesday, November 20, 2019 6:59 PM
  • Variables in params files are hardcoded there and taken from this file. Rest of variables like:

    • adminUsername,
    • adminPassword,
    • TagsObject,
    • virtualMachineName
    • networkInterfaceName
    • PrivateIPAddress
    • virtualMachineNameShort
    • vnetName
    • DataDiskName

    are taken from some general project specific config.file or names are just generated base on config file. So when I run script twice time without changes in configs then I get exactly the same params as a input for template and in a result always below resources are created

    • aid-acl-d-sql  - Virtual machine
    • aid-acl-d-sql  -  SQL virtual machine
    • aid-acl-d-sql_OsDisk_1_5b092515ca3047759495a5c1a1b83d83  - Disk
    • aid**-acloud-dev-sql-dd  - Disk
    • aid**-acloud-dev-sql-ni - Network interface

    where only one disk has name which is not same every-time as Azure add there some posfix



    • Edited by GrzegorzCzm Thursday, November 21, 2019 7:04 AM
    Thursday, November 21, 2019 7:03 AM
  • Thanks for that. I reviewed all you are doing and the process you are taking and attempted to repo. However every time I deploy my template it appears to succeed. I am not getting any timeouts or errors. I also just took the template from the portal. There might be something simple we are missing but it is hard to tell without any of the backend logs. 

    I would suggest opening a technical support ticket to have our SQL engineers review the backend logs to see if they can track down the issue. If you don't have the ability to open a technical support ticket you can email me at AzCommunity@microsoft.com and provide me with your SubscriptionID and link to this issue. I can then assist you in getting that ticket open. 

    Thursday, November 21, 2019 9:09 PM