{"id":9,"date":"2017-01-04T14:58:54","date_gmt":"2017-01-04T20:58:54","guid":{"rendered":"http:\/\/peterod.azurewebsites.net\/?p=9"},"modified":"2017-01-04T14:58:54","modified_gmt":"2017-01-04T20:58:54","slug":"install-sql-server-onto-an-azure-vm-using-powershell-dsc","status":"publish","type":"post","link":"http:\/\/www.perktime.org\/index.php\/2017\/01\/04\/install-sql-server-onto-an-azure-vm-using-powershell-dsc\/","title":{"rendered":"Install SQL Server onto an Azure VM using PowerShell DSC"},"content":{"rendered":"

The Azure marketplace has quite a few prebuilt virtual machines with SQL Server already in them from versions SQL Server 2008R2 to SQL Server 2016. You can also use the BYOL versions to provide your own SQL Server license if you prefer. However, you may still wish to have more control over the installation process such as the SQL Server instance name, install location, installed features, etc. I\u2019ve created a GitHub project here:
\nhttps:\/\/github.com\/perktime\/InstallSQLServerByDSCForAzure<\/a>
\nThese ARM templates will create a new base Windows VM using an Azure marketplace image, domain join the VM into an existing Windows AD domain and use PowerShell DSC to install SQL Server from Azure Files.
\nThe DSC uses the xSQLServer PowerShell module from here: https:\/\/github.com\/PowerShell\/xSQLServer<\/a> where you will also find additional documentation. Also note that currently not all potential parameters for SQL Server setup are implemented in the ARM template. You could either add them yourself to the ARM templates and SQLInstall.ps1 file or let me know and I might update \"Winking
\nPrerequisites<\/strong>
\nBefore you can use this solution, you will need to create a storage account (or use an existing one) and enable Azure Files for it.
\n1) Go to the Azure portal and create a new storage account:
\n\"image\"<\/a>
\n2) Once the storage account is done creating, you will need to create a file share for it. Click Files and then click \u201c+ File Share\u201d
\n\"image\"<\/a>
\n3) Supply a name and a quota and click Create
\n\"image\"<\/a>
\n4) You will then need to download a version of SQL Server, create a folder for it in Azure Files and copy the SQL Server install files into that folder. You may find it quickest to do this right from an existing Azure VM in the same region to access the Microsoft VLSC site or MSDN or download a copy of SQL Server Developer Edition here: https:\/\/www.microsoft.com\/en-us\/sql-server\/sql-server-editions-developers<\/a>. Note that if you get the ISO, you will need to extract the files out of the ISO as this template currently does not support directly installing from the ISO.
\n5) You will need to take note of the access key for this storage account as well as the Azure Files URL so that you can supply them to the azuredeploy.parameters.json file. If you click \u201cConnect\u201d on the file share, you can see the Azure Files UNC path as well as click the link for the access keys:
\n\"image\"<\/a>
\n6) Next, you will need to copy the SQLinstall.ps1.zip and DeployWindowsVM.json files into your Azure Blob storage account (note: not in Azure Files). Using a tool like Azure Storage Explorer, copy these 2 files into a blob container that has public read access enabled:
\n\"image\"<\/a>
\nUpdating the azuredeploy.parameters.json file<\/strong>
\nThe azuredeploy.parameters.json file has a number of parameters that you will need to update such as:
\nvmName<\/strong>: The computer name of the VM
\nvmSize<\/strong>: The desired Azure VM size and series. It is recommended that you use a series that supports SSD storage such as DS, GS or FS.
\nassetLocation<\/strong>: The location in Azure blob storage where the SQLInstall.ps1.zip and DeployWindowsVM.json are deployed into an Azure blob storage container with public read access.
\nAdminUserName<\/strong>: The local Windows administrator account
\nAdminPassword<\/strong>: The local Windows administrator account password
\nDomainUserName<\/strong>: The domain username that has domain join permissions
\nDomainPassword<\/strong>: The domain user\u2019s password
\nexistingDomainName<\/strong>: The name of the Windows domain you will be joining
\nexistingOUPath (optional)<\/strong>: The OU where you want the computer account placed in Active Directory
\nexistingVirtualNetworkName<\/strong>: The existing Azure virtual network where this VM will be placed
\nexistingVirtualNetworkResourceGroup: <\/strong>The existing Azure virtual network resource group
\nstorageAccountUri: <\/strong>The existing Azure blob storage account for this VM\u2019s disks. Premium storage is recommended for SQL Server
\nbootdiagnosticsstorageAccountUri: <\/strong>The existing Azure blob storage account for boot diagnostics. Must be standard storage
\nwindowsOSVersion: <\/strong>The version of Windows Server to use for the VM. Note that not all versions of SQL Server may be supported on all versions of Windows Server
\nsubnetName: <\/strong>The existing subnet name where this VM will be placed
\nFileShareUserName: <\/strong>The Azure Files username. It should be the same as the first part of the Azure Files UNC path (e.g. if your Azure Files is \\\\peteazurefiles.file.core.windows.net<\/a> then the username would be azurefiles
\nFileSharePassword: <\/strong>The Azure Files access key.
\nInstallDir: <\/strong>The folder where the SQL Server files are located (e.g. sql2016). It is not the full path
\nPackagePath: <\/strong>The path to Azure Files directory where the SQL Server install files are location (e.g. \\\\\\\\peteazurefiles.file.core.windows.net\\\\installs<\/a>\u201d)
\nlocation: <\/strong>The Azure data center location you wish to use
\nSQLAgentUserName: <\/strong>The domain\\username for the SQL Agent account
\nSQLAgentPassword: <\/strong>The password for the SQL Agent account
\nSQLSAAccountPassword: <\/strong>The SQL SA Account password
\nSQLServiceUserName: <\/strong>The domain\\username for the SQLService account
\nSQLServicePassword: <\/strong>The password for the SQLService account
\nFeatures: <\/strong>The installed features for SQL Server (SQLENGINE,FULLTEXT). Note that not all versions of SQL Server support the same features.
\nUpdateSource: <\/strong>This is the location where SQL Server setup searches for product updates. Use \u201cMU\u201d if you want to have SQL Server use Windows Update.
\nUpdateEnabled: <\/strong>This determines if SQL Server should update itself or not. Can be true or false.
\nInstallSharedDir: <\/strong>The installation path for shared SQL Files
\nInstallSharedWOWDir: <\/strong>The installation path for x86 shared SQL files
\nSQLInstanceName: <\/strong>The name of the SQL Instance
\nSQLInstanceDir: <\/strong>The installation path for the SQL instance files
\nSecurityMode: <\/strong>The SQL Security mode (either Windows or SQL). SQL is also known as Mixed Mode
\nSQLSysAdminAccounts: <\/strong>Array of accounts to be made SQL administrators.
\n 
\nTroubleshooting<\/strong>
\nIn the event that your deployment fails and it\u2019s because of an invalid parameter, Azure may not provide a helpful error message in this case and you may see this:
\n\"image\"<\/a>
\nIf this occurs, your best bet is to look at the SQL Server log file (e.g. \u201cC:\\Program Files\\Microsoft SQL Server\\130\\Setup Bootstrap\\Log\\Summary.txt\u201d)<\/p>\n","protected":false},"excerpt":{"rendered":"

The Azure marketplace has quite a few prebuilt virtual machines with SQL Server already in them from versions SQL Server 2008R2 to SQL Server 2016. You can also use the BYOL versions to provide your own SQL Server license if you prefer. However, you may still wish to have more control over the installation process … <\/p>\n

Continue reading “Install SQL Server onto an Azure VM using PowerShell DSC”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/www.perktime.org\/index.php\/wp-json\/wp\/v2\/posts\/9"}],"collection":[{"href":"http:\/\/www.perktime.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.perktime.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.perktime.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.perktime.org\/index.php\/wp-json\/wp\/v2\/comments?post=9"}],"version-history":[{"count":0,"href":"http:\/\/www.perktime.org\/index.php\/wp-json\/wp\/v2\/posts\/9\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.perktime.org\/index.php\/wp-json\/wp\/v2\/media?parent=9"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.perktime.org\/index.php\/wp-json\/wp\/v2\/categories?post=9"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.perktime.org\/index.php\/wp-json\/wp\/v2\/tags?post=9"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}