Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Issue with spinning-up SQL Server 2012 running on Windows Server 2012 R2 Datacenter VM? #1325

Closed
siddthota opened this issue May 31, 2018 · 13 comments
Labels

Comments

@siddthota
Copy link

siddthota commented May 31, 2018

Hello,

I've already raised a similar issue and I made more progress on the issue, but I'm not sure how to approach it.

I wanted to spin-up SQL Server 2012 on Windows Server 2012 R2 Datacenter. We do have this as default image on Azure compute.

My Terraform Script looks like this

  1. Resource Group
  2. Key Vault - optional
  3. availability set - optional
  4. Managed Disk
  5. Vnet, Subnet, NSG, Public IP, NIC
  6. Storage Account
  7. VM
  8. VM Extension.
resource "azurerm_resource_group" "test" {
    name = "acctestrg"
    location = "West US"
}

resource "azurerm_virtual_network" "test" {
    name = "acctvn"
    address_space = ["10.0.0.0/16"]
    location = "West US"
    resource_group_name = "${azurerm_resource_group.test.name}"
}

resource "azurerm_subnet" "test" {
    name = "acctsub"
    resource_group_name = "${azurerm_resource_group.test.name}"
    virtual_network_name = "${azurerm_virtual_network.test.name}"
    address_prefix = "10.0.2.0/24"
}

resource "azurerm_network_interface" "test" {
    name = "acctni"
    location = "West US"
    resource_group_name = "${azurerm_resource_group.test.name}"

    ip_configuration {
    	name = "testconfiguration1"
    	subnet_id = "${azurerm_subnet.test.id}"
    	private_ip_address_allocation = "dynamic"
    }
}

resource "azurerm_storage_account" "test" {
    name = "accsa"
    resource_group_name = "${azurerm_resource_group.test.name}"
    location = "westus"
    account_type = "Standard_LRS"

    tags {
        environment = "staging"
    }
}

resource "azurerm_storage_container" "test" {
    name = "vhds"
    resource_group_name = "${azurerm_resource_group.test.name}"
    storage_account_name = "${azurerm_storage_account.test.name}"
    container_access_type = "private"
}

resource "azurerm_virtual_machine" "test" {
    name = "acctvm"
    location = "West US"
    resource_group_name = "${azurerm_resource_group.test.name}"
    network_interface_ids = ["${azurerm_network_interface.test.id}"]
    vm_size = "Standard_A0"

    storage_image_reference {
        publisher = "MicrosoftSQLServer"
        offer = "SQL2012SP4-WS2012R2"
        sku = "Enterprise"
        version = "latest"
    }

   storage_os_disk {
    name          = "${var.vm_os_disk_name}"
    caching       = "${var.vm_os_caching}"
    create_option = "FromImage"
    os_type       = "windows"
    disk_size_gb  = "${var.vm_os_disk_size_gb}"
  }

storage_data_disk {
    name              = "${var.vm_data_disk_name}"
    managed_disk_id   = "${var.vm_data_disk_id}"
    managed_disk_type = "${var.vm_managed_disk_type}"
    create_option     = "Attach"
    disk_size_gb      = "${var.vm_data_disk_size_gb}"
    caching           = "${var.vm_data_caching}"
    lun               = "${var.vm_data_lun}"
  }

    os_profile {
	    computer_name = "hostname"
	    admin_username = "testadmin"
	    admin_password = "Password1234!"
    }

  os_profile_windows_config {
    provision_vm_agent = "${var.vm_provision_vm_agent}"
  }

    tags {
        environment = "staging"
    }
}

resource "azurerm_virtual_machine_extension" "vm_extension" {

 name                       = "SqlIaaSExt"
  location                   = "centralus"
  resource_group_name        = "${azurerm_resource_group.test.name}"
  virtual_machine_name       = "${azurerm_virtual_machine.test.name}"
  publisher                  = "Microsoft.SqlServer.Management"
  type                       = "SqlIaaSAgent"
  type_handler_version       = "1.2"
  auto_upgrade_minor_version = "true"
  settings = <<SETTINGS
  null
  SETTINGS

  protected_settings = <<SETTINGS
  null
  SETTINGS
}

This spins-up windows VM, but not the SQL Server. I feel I need to insert SQL Server authentication details (somewhere but not sure where), but since I'm passing in the SQL2012SP4-WS2012R2, can I expect it to spin-up SQL Server on Windows VM or am I missing anything.

If I'm doing with portal, I get an extra option than spinning up normal VM for SQL Server on VM in which we need to enable authentication and provide login details.

I'm trying to wrap my head around on how I can get this to working.

All I'm expecting is to have SQL Server Configurations option appearing on VM Dashboard.

Thanks!

@siddthota siddthota changed the title How do I spin-up SQL Server 2012 running on Windows Server 2012 R2 Datacenter VM? Issue with spinning-up SQL Server 2012 running on Windows Server 2012 R2 Datacenter VM? Jun 1, 2018
@tombuildsstuff
Copy link
Contributor

hi @siddthota

Thanks for opening this issue :)

Looking at the Terraform Configuration you've posted above, I can't see the VM Extension you're referring too? Attempting to create a Virtual Machine in the Portal with SQL Server pre-installed, I see the following VM Extension is configured (extracted from either the Download template and parameters [pre-provisioning] / Automation Script [post-provisioning] screen):

 {
            "apiVersion": "2015-06-15",
            "type": "Microsoft.Compute/virtualMachines/extensions",
            "name": "[concat(parameters('virtualMachineName'), '/SqlIaasExtension')]",
            "location": "[parameters('location')]",
            "dependsOn": [
                "[concat('Microsoft.Compute/virtualMachines/', parameters('virtualMachineName'))]",
                "[concat('Microsoft.Compute/virtualMachines/', parameters('virtualMachineName'))]"
            ],
            "properties": {
                "type": "SqlIaaSAgent",
                "publisher": "Microsoft.SqlServer.Management",
                "typeHandlerVersion": "1.2",
                "autoUpgradeMinorVersion": "true",
                "settings": {
                    "AutoTelemetrySettings": {
                        "Region": "[parameters('location')]"
                    },
                    "AutoPatchingSettings": {
                        "PatchCategory": "WindowsMandatoryUpdates",
                        "Enable": true,
                        "DayOfWeek": "[parameters('sqlAutopatchingDayOfWeek')]",
                        "MaintenanceWindowStartingHour": "[parameters('sqlAutopatchingStartHour')]",
                        "MaintenanceWindowDuration": "[parameters('sqlAutopatchingWindowDuration')]"
                    },
                    "KeyVaultCredentialSettings": {
                        "Enable": false,
                        "CredentialName": ""
                    },
                    "ServerConfigurationsManagementSettings": {
                        "SQLConnectivityUpdateSettings": {
                            "ConnectivityType": "[parameters('sqlConnectivityType')]",
                            "Port": "[parameters('sqlPortNumber')]"
                        },
                        "SQLWorkloadTypeUpdateSettings": {
                            "SQLWorkloadType": "[parameters('sqlStorageWorkloadType')]"
                        },
                        "SQLStorageUpdateSettings": {
                            "DiskCount": "[parameters('sqlStorageDisksCount')]",
                            "NumberOfColumns": "[parameters('sqlStorageDisksCount')]",
                            "StartingDeviceID": "[parameters('sqlStorageStartingDeviceId')]",
                            "DiskConfigurationType": "[parameters('sqlStorageDisksConfigurationType')]"
                        },
                        "AdditionalFeaturesServerConfigurations": {
                            "IsRServicesEnabled": "[parameters('rServicesEnabled')]"
                        }
                    }
                },
                "protectedSettings": {}
            }
        },

Using this information it should be possible to convert this across to using the azurerm_virtual_machine_extension resource and configure the SQL Server in the same way :)

One thing to bear in mind here from my previous experience with VM Extensions is that the settings JSON section is case-sensitive depending on the VM Extension being configured; as such I'd suggest copy-pasting the settings section from above, to ensure the keys are cased correctly.

Would you be able to take a look and see if that solves your issue? :)

Thanks!

@siddthota
Copy link
Author

My bad, sorry I forgot to copy the extension resource/module. I've updated my extensions field above. I'm currently using nothing but just null above/below.

resource "azurerm_virtual_machine_extension" "vm_extension" {

 name                       = "SqlIaaSExt"
  location                   = "centralus"
  resource_group_name        = "${azurerm_resource_group.test.name}"
  virtual_machine_name       = "${azurerm_virtual_machine.test.name}"
  publisher                  = "Microsoft.SqlServer.Management"
  type                       = "SqlIaaSAgent"
  type_handler_version       = "1.2"
  auto_upgrade_minor_version = "true"
  settings = <<SETTINGS
  null
  SETTINGS

  protected_settings = <<SETTINGS
  null
  SETTINGS
}

So I need to copy the settings JSON into my settings field?

I'll give it a try and update it.

Thanks!

@tombuildsstuff
Copy link
Contributor

@siddthota

My bad, sorry I forgot to copy the extension resource/module.

No worries :)

So I need to copy the settings JSON into my settings field? I'll give it a try and update it.

indeed - it's worth noting that if the values are null (e.g. in the case of protected_settings) you can just entirely omit the field and it should be fine 👍

Thanks!

@siddthota
Copy link
Author

siddthota commented Jun 1, 2018

How are you able to reference the SQLServerIASSAgent?

I was seeing this error on my Automation Script on Azure Portal -
Export template operation completed with errors. Some resources were not exported. Please see details for more information. (Code: ExportTemplateCompletedWithErrors) Virtual machine extension 'Microsoft:2ESqlServer:2EManagement-SqlIaaSAgent' is not supported for template export. (Code: ExportVirtualMachineExtensionNotSupported, Target: Microsoft:2ESqlServer:2EManagement-SqlIaaSAgent)

How do I rectify this to look at the Automation script to understand my configurations?

And also, I'm assuming that my extension should look like this?

module "create-vm-extension" {
   name                       = "SqlIaaSExt"
  location                   = "centralus"
  resource_group_name        = "${azurerm_resource_group.test.name}"
  virtual_machine_name       = "${azurerm_virtual_machine.test.name}"
  publisher                  = "Microsoft.SqlServer.Management"
  type                       = "SqlIaaSAgent"
  type_handler_version       = "1.2"
  auto_upgrade_minor_version = "true"
 depends_on = "${azurerm_virtual_machine.test.name}"
 
  settings = <<SETTINGS
  {
    "ServerConfigurationsManagementSettings": {
                        "SQLConnectivityUpdateSettings": {
                            "ConnectivityType": "Public (Internet)",
                            "Port": "3389"
                        },
                        "SQLWorkloadTypeUpdateSettings": {
                            "SQLWorkloadType": "[parameters('sqlStorageWorkloadType')]" //not sure!
                        },
                        "SQLStorageUpdateSettings": {
                            "DiskCount": "[parameters('sqlStorageDisksCount')]", //2 - storage/OS Disks
                            "NumberOfColumns": "[parameters('sqlStorageDisksCount')]", //not sure!
                            "StartingDeviceID": "[parameters('sqlStorageStartingDeviceId')]", //not sure!
                            "DiskConfigurationType": "[parameters('sqlStorageDisksConfigurationType')]" //note sure
                        },
                        "AdditionalFeaturesServerConfigurations": {
                            "IsRServicesEnabled": "[parameters('rServicesEnabled')]" //true?
                        }
                    }
  }
  SETTINGS
}

I mentioned //not sure, and other input values that I'm thinking to enter, but is it mandatory to have these things added to settings to get SQL Server up and running and also I don't see options like enable/disable authentication and user/password or port number etc details in here. Am I missing something?

Sorry, I'm trying to wrap my head around the extensions concept on Terraform for VM.

Thanks for the help.

@siddthota
Copy link
Author

@tombuildsstuff
Hi, Just wondering if I need to pass in anything else or can you help me out with what are the parameters I need to pass in?

With my above config, I can just spin-up VM, but not SQL Server. I'm not able to view my Template for SQL Server on Azure Portal which is making things tough for me.

Thanks!

@tombuildsstuff
Copy link
Contributor

@siddthota (sorry for the delayed response, I've been AFK)

With my above config, I can just spin-up VM, but not SQL Server. I'm not able to view my Template for SQL Server on Azure Portal which is making things tough for me.

If you provision a VM with this extension (e.g. via the Portal) it should be possible to see this information both in the Azure CLI or the Azure Resource Explorer - by drilling down into Subscriptions -> [name] -> Resource Groups -> [name] -> VirtualMachines -> [name] -> Extensions -> [name] and see the response in the JSON.

Thanks!

@siddthota
Copy link
Author

Yeah, I've requested MS support about it and they helped me with that.

But, I'm still trying to figure out how to spin the SQL Server on VM.

@tombuildsstuff
Copy link
Contributor

@siddthota the Virtual Machine extension is what configures the SQL Server on the Virtual Machine. Would you be able to show the Virtual Machine Extension you're trying to use to configure this; or the output from the Azure CLI / Resource Explorer as documented in this comment - then we can try and help further :)

Thanks!

@siddthota
Copy link
Author

siddthota commented Jun 20, 2018

Hi Tom,

I'm not sure what exactly needs to be configured, as there isn't much documentation about converting VM extensions from Template -> Terraform script. This is how my VM extension for SQL Server looks like.

module "create-vm-extension" {
   name                       = "SqlIaaSExt"
  location                   = "centralus"
  resource_group_name        = "${azurerm_resource_group.test.name}"
  virtual_machine_name       = "${azurerm_virtual_machine.test.name}"
  publisher                  = "Microsoft.SqlServer.Management"
  type                       = "SqlIaaSAgent"
  type_handler_version       = "1.2"
  auto_upgrade_minor_version = "true"
 depends_on = "${azurerm_virtual_machine.test.name}"
 
  settings = <<SETTINGS
  {
    "ServerConfigurationsManagementSettings": {
                        "SQLConnectivityUpdateSettings": {
                            "ConnectivityType": "Public (Internet)",
                            "Port": "3389"
                        },
                        "SQLWorkloadTypeUpdateSettings": {
                            "SQLWorkloadType": "[parameters('sqlStorageWorkloadType')]" //not sure!
                        },
                        "SQLStorageUpdateSettings": {
                            "DiskCount": "[parameters('sqlStorageDisksCount')]", //2 - storage/OS Disks
                            "NumberOfColumns": "[parameters('sqlStorageDisksCount')]", //not sure!
                            "StartingDeviceID": "[parameters('sqlStorageStartingDeviceId')]", //not sure!
                            "DiskConfigurationType": "[parameters('sqlStorageDisksConfigurationType')]" //note sure
                        },
                        "AdditionalFeaturesServerConfigurations": {
                            "IsRServicesEnabled": "[parameters('rServicesEnabled')]" //true?
                        }
                    }
  }
  SETTINGS
}

@tombuildsstuff
Copy link
Contributor

hi @siddthota

This information can be found on the Parameters tab next to the generated ARM Template in the Portal - or via the Azure Resource Explorer above:

screen shot 2018-06-20 at 16 56 04

This information can then be used to populate the VM Extension, for instance:

resource "azurerm_resource_group" "test" {
  # ...
}
resource "azurerm_virtual_machine" "test" {
  # ...
}

resource "azurerm_virtual_machine_extension" "test" {
  name                 = "SqlIaasExtension"
  location             = "${azurerm_resource_group.test.name}"
  resource_group_name  = "${azurerm_resource_group.test.name}"
  virtual_machine_name = "${azurerm_virtual_machine.test.name}"
  publisher            = "Microsoft.SqlServer.Management"
  type                 = "SqlIaaSAgent"
  type_handler_version = "1.2"

  parameters = [
    Location = "${var.location}"
    StorageDisksCount = 2
    AutoPatchingDayOfWeek = "Sunday"
    AutoPatchingStartHour = 2
    AutoPatchingWindowDuration = 60
  ]

  settings = <<SETTINGS
  {
    "AutoTelemetrySettings": {
      "Region": "[parameters('Location')]"
    },
    "AutoPatchingSettings": {
      "PatchCategory": "WindowsMandatoryUpdates",
      "Enable": true,
      "DayOfWeek": "[parameters('AutoPatchingDayOfWeek')]",
      "MaintenanceWindowStartingHour": "[parameters('AutoPatchingStartHour')]",
      "MaintenanceWindowDuration": "[parameters('AutoPatchingWindowDuration')]"
    },
    "KeyVaultCredentialSettings": {
      "Enable": false,
      "CredentialName": ""
    },
    "ServerConfigurationsManagementSettings": {
      "SQLConnectivityUpdateSettings": {
          "ConnectivityType": "Private",
          "Port": "1433"
      },
      "SQLWorkloadTypeUpdateSettings": {
          "SQLWorkloadType": "GENERAL"
      },
      "SQLStorageUpdateSettings": {
          "DiskCount": "[parameters('StorageDisksCount')]",
          "NumberOfColumns": "[parameters('StorageDisksCount')]",
          "StartingDeviceID": "2",
          "DiskConfigurationType": "NEW"
      },
      "AdditionalFeaturesServerConfigurations": {
          "IsRServicesEnabled": "false"
      }
    }
  }
SETTINGS
}

Would you be able to take a look and see if that solves your issue here? Since this is a configuration question rather than a bug in Terraform, I'm going to close this issue for the moment (but we'll continue replying) :)

Thanks!

@siddthota
Copy link
Author

All Done... Thank you so much Tom.. You rock \m/

screen shot 2018-06-20 at 1 48 27 pm

@bfleming-ciena
Copy link

bfleming-ciena commented Sep 2, 2018

Got this working.

  1. You must enable provision_vm_agent under the os_profile_windows_config.

Then this will work as expected.

resource "azurerm_virtual_machine_extension" "test" {
  name                 = "SqlIaasExtension"
  location             = "${local.workspace["location"]}"
  resource_group_name  = "${element("${local.workspace_lists["it_apps_resource_groups"]}", var.SQL_INDEX)}"
  virtual_machine_name = "${module.AZSQLSVR.vm_hostname[0]}"
  publisher            = "Microsoft.SqlServer.Management"
  type                 = "SqlIaaSAgent"
  type_handler_version = "1.2"

  settings = <<SETTINGS
  {
    "AutoTelemetrySettings": {
      "Region": "westus"
    },
    "AutoPatchingSettings": {
      "PatchCategory": "WindowsMandatoryUpdates",
      "Enable": true,
      "DayOfWeek": "Sunday",
      "MaintenanceWindowStartingHour": "2",
      "MaintenanceWindowDuration": "60"
    },
    "KeyVaultCredentialSettings": {
      "Enable": false,
      "CredentialName": ""
    },
    "ServerConfigurationsManagementSettings": {
      "SQLConnectivityUpdateSettings": {
          "ConnectivityType": "Private",
          "Port": "1433"
      },
      "SQLWorkloadTypeUpdateSettings": {
          "SQLWorkloadType": "GENERAL"
      },
      "AdditionalFeaturesServerConfigurations": {
          "IsRServicesEnabled": "false"
      }
    }
  }
SETTINGS

  tags = {
    terraform = "true"

    Environment = "${terraform.workspace}"

    Service = "SQL"
  }

}

@ghost
Copy link

ghost commented Mar 30, 2020

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.

If you feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 [email protected]. Thanks!

@ghost ghost locked and limited conversation to collaborators Mar 30, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
Projects
None yet
Development

No branches or pull requests

3 participants