{
"metadata": {
"kernelspec": {
"name": "powershell",
"display_name": "PowerShell"
},
"language_info": {
"name": "powershell",
"codemirror_mode": "shell",
"mimetype": "text/x-sh",
"file_extension": ".ps1"
}
},
"nbformat_minor": 2,
"nbformat": 4,
"cells": [
{
"cell_type": "markdown",
"source": [
"Create Azure SQL Virtual Machine\n",
"============================================\n",
"\n",
"Description\n",
"-----------\n",
"For more information about other Azure PowerShell options for creating SQL VMs, see the Provisioning guide for SQL Server VMs with Azure PowerShell. See also Quickstart guide to creating a SQL Server VM with Azure PowerShell.\n",
"\n",
"\n",
"Steps of this procedure include:\n",
"1. Connect to Azure subscription\n",
"1. Provision resource group for SQL VM migration\n",
"2. Create a storage account\n",
"3. Configure Network Settings\n",
"3. Provision SQL VM\n",
"4. Configure SQL VM IaaS agent"
],
"metadata": {
"azdata_cell_guid": "e479b550-d6bd-49c5-965a-34a7d1d16412"
}
},
{
"cell_type": "markdown",
"source": [
"#### *Enter all the values in quotes *"
],
"metadata": {
"azdata_cell_guid": "37db2e50-dcde-4dd5-820c-7dc11212f1eb"
}
},
{
"cell_type": "code",
"source": [
"# Resource Group \r\n",
"$ResourceGroupName = \"\" # Name of the resource group to create in the current subscription\r\n",
"$Location = \"\" # see Appendix for a list of location settings\r\n",
"\r\n",
"# Compute\r\n",
"$VMName = \"\" # VM to create\r\n",
"$PublisherName = \"\" # Name of Publisher, Default would be 'MicrosoftSQLServer'\r\n",
"$Version = \"\" # Version of VM, Default would be 'latest'\r\n",
"\r\n",
"# Storage\r\n",
"$StorageAccountName = $ResourceGroupName + \"_storage\"\r\n",
"$StorageSku = \"\" # Choose your storage sku (see appendix)\r\n",
"$StorageName = \"sqlstorage\" + (Get-Random -Minimum 1 -Maximum 100)\r\n",
"\r\n",
"# VM Password\r\n",
"$secureVMPassword = \"\" # Create the password for VM."
],
"metadata": {
"azdata_cell_guid": "b9aff9cc-a3af-41cb-a2a5-35f36b2bcc55",
"tags": []
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Connect to Azure Account"
],
"metadata": {
"azdata_cell_guid": "b06fee5e-355d-47fc-8c1f-41294756cc87"
}
},
{
"cell_type": "code",
"source": [
"Connect-AzAccount"
],
"metadata": {
"azdata_cell_guid": "7dbdc638-d6cd-4ebe-9268-3efc18d3c415"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Get Subscription\r\n",
"Below command will open a _Dialouge Box_ with list of subscriptions. Selecting one of those will set that subscription for rest of the commands."
],
"metadata": {
"azdata_cell_guid": "b58f1048-3e9d-4888-bda0-4d0443a11c97"
}
},
{
"cell_type": "code",
"source": [
"$subscription = Get-AzSubscription | Out-GridView -PassThru\r\n",
"Set-AzContext -SubscriptionName $subscription"
],
"metadata": {
"azdata_cell_guid": "0cc44e68-3810-46f4-b29c-e6ad4321e384"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Create Azure Resource Group"
],
"metadata": {
"azdata_cell_guid": "c17dd324-5c55-484f-8a25-2a5a7e43633e"
}
},
{
"cell_type": "code",
"source": [
"# Create Azure resource group, if necessary\r\n",
"$rg = Get-AzResourceGroup | Where ResourceGroupName -eq $ResourceGroupName\r\n",
"\r\n",
"if (!$rg)\r\n",
"{\r\n",
" # Need to create a new resource group\r\n",
" Write-Output \"Resource Group $ResourceGroupName does not exist. Creating...\"\r\n",
" $rg = New-AzResourceGroup -Name $ResourceGroupName -Location $Location\r\n",
"}"
],
"metadata": {
"azdata_cell_guid": "2c37ef31-5f47-4918-a2b3-05e11aab28da"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Create a storage account\r\n",
"VMs require storage resources for OS, SQL data and logs. Create a new storage account as a place for it."
],
"metadata": {
"azdata_cell_guid": "11b78695-40d3-45d7-8e3c-32d086ddf94a"
}
},
{
"cell_type": "code",
"source": [
"$StorageAccount = Get-AzStorageAccount | Where StorageAccountName -eq $StorageAccountName\r\n",
"\r\n",
"if (!$StorageAccount)\r\n",
"{\r\n",
" Write-Output \"Storage Account $StorageName does not exist. Creating...\"\r\n",
" $StorageAccount = New-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $StorageName -SkuName $StorageSku -Kind \"Storage\" -Location $Location\r\n",
"}"
],
"metadata": {
"azdata_cell_guid": "f992bf91-a84a-40c2-813b-cb778907370d",
"tags": []
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Configure network settings\r\n",
"Create a virtual network, subnet, and a public IP address. These resources are used to provide network connectivity to the virtual machine and connect it to the internet."
],
"metadata": {
"azdata_cell_guid": "202634eb-7edf-4ff4-8486-fffbda45dbc8"
}
},
{
"cell_type": "code",
"source": [
"$SubnetName = $ResourceGroupName + \"subnet\"\r\n",
"$VnetName = $ResourceGroupName + \"vnet\"\r\n",
"$PipName = $ResourceGroupName + $(Get-Random)\r\n",
"\r\n",
"# Create a subnet configuration\r\n",
"$SubnetConfig = New-AzVirtualNetworkSubnetConfig -Name $SubnetName -AddressPrefix 192.168.1.0/24\r\n",
"\r\n",
"# Create a virtual network\r\n",
"$Vnet = New-AzVirtualNetwork -ResourceGroupName $ResourceGroupName -Location $Location `\r\n",
" -Name $VnetName -AddressPrefix 192.168.0.0/16 -Subnet $SubnetConfig\r\n",
"\r\n",
"# Create a public IP address and specify a DNS name\r\n",
"$Pip = New-AzPublicIpAddress -ResourceGroupName $ResourceGroupName -Location $Location `\r\n",
" -AllocationMethod Static -IdleTimeoutInMinutes 4 -Name $PipName"
],
"metadata": {
"azdata_cell_guid": "af88cdae-1a62-4990-9231-094481c9337d"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"2. Create a network security group. Configure rules to allow remote desktop (RDP) and SQL Server connections."
],
"metadata": {
"azdata_cell_guid": "3b25e16e-b150-4a2e-80dc-66f2d18b43fb"
}
},
{
"cell_type": "code",
"source": [
"# Rule to allow remote desktop (RDP)\r\n",
"$NsgRuleRDP = New-AzNetworkSecurityRuleConfig -Name \"RDPRule\" -Protocol Tcp `\r\n",
" -Direction Inbound -Priority 1000 -SourceAddressPrefix * -SourcePortRange * `\r\n",
" -DestinationAddressPrefix * -DestinationPortRange 3389 -Access Allow\r\n",
"\r\n",
"#Rule to allow SQL Server connections on port 1433\r\n",
"$NsgRuleSQL = New-AzNetworkSecurityRuleConfig -Name \"MSSQLRule\" -Protocol Tcp `\r\n",
" -Direction Inbound -Priority 1001 -SourceAddressPrefix * -SourcePortRange * `\r\n",
" -DestinationAddressPrefix * -DestinationPortRange 1433 -Access Allow\r\n",
"\r\n",
"# Create the network security group\r\n",
"$NsgName = $ResourceGroupName + \"nsg\"\r\n",
"$Nsg = New-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroupName `\r\n",
" -Location $Location -Name $NsgName `\r\n",
" -SecurityRules $NsgRuleRDP,$NsgRuleSQL"
],
"metadata": {
"azdata_cell_guid": "debe940d-0d0f-4540-be5b-4d6495d338e1"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"3. Create the network interface."
],
"metadata": {
"azdata_cell_guid": "d44de03c-d4f2-48ef-8a60-507069d6c08e"
}
},
{
"cell_type": "code",
"source": [
"$InterfaceName = $ResourceGroupName + \"int\"\r\n",
"$Interface = New-AzNetworkInterface -Name $InterfaceName `\r\n",
" -ResourceGroupName $ResourceGroupName -Location $Location `\r\n",
" -SubnetId $VNet.Subnets[0].Id -PublicIpAddressId $Pip.Id `\r\n",
" -NetworkSecurityGroupId $Nsg.Id"
],
"metadata": {
"azdata_cell_guid": "6dbb3ea0-b52f-4ed2-bd24-59096d134e88"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Create the SQL VM\r\n",
"1. Define your credentials to sign in to the VM. The username is \"azureadmin\". Make sure you change before running the command."
],
"metadata": {
"azdata_cell_guid": "b25dca9e-269b-45db-8cdf-efa53e2213d2"
}
},
{
"cell_type": "code",
"source": [
"# Define a credential object\r\n",
"$SecurePassword = ConvertTo-SecureString $secureVMPassword `\r\n",
" -AsPlainText -Force\r\n",
"$Cred = New-Object System.Management.Automation.PSCredential (\"azureadmin\", $securePassword)"
],
"metadata": {
"azdata_cell_guid": "40d0c992-860a-4b83-8104-16ec7e6e7983"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Appendix: Azure SQL VM Offers\r\n",
"Run the following command to get updated list of offers for Microsoft SQL Server in your location. These settings can be used for the OfferName associated with this migration. Configure it for your purposes.\r\n",
"\r\n",
"Note that the SQL Version is first then appended with an operating system version. E.g.: \"WS2019\" means Windows Server 2019. Along with various versions of Windows Servers, there are also enterprise Linux versions such as RedHat Enterprise, Suse Enterprise, and Ubuntu. Some versions are BYOL (Bring Your Own License) aka Hybrid Benefit."
],
"metadata": {
"azdata_cell_guid": "ce2daf93-7c20-4073-babc-53c9187e5691"
}
},
{
"cell_type": "code",
"source": [
"Get-AzVMImageOffer -Location $Location -Publisher $PublisherName | Select Offer"
],
"metadata": {
"azdata_cell_guid": "e5808cbf-e6c6-4abe-8e1b-3c282bc7667c"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"2. Create a virtual machine configuration object and then create the VM. The following command creates a SQL Server 2017 Developer Edition VM on Windows Server 2016."
],
"metadata": {
"azdata_cell_guid": "2038172f-3f29-499c-ad68-88a1d96ead1f"
}
},
{
"cell_type": "code",
"source": [
"# Create a virtual machine configuration\r\n",
"\r\n",
"$VMConfig = New-AzVMConfig -VMName $VMName -VMSize Standard_DS13_V2 |\r\n",
" Set-AzVMOperatingSystem -Windows -ComputerName $VMName -Credential $Cred -ProvisionVMAgent -EnableAutoUpdate |\r\n",
" Set-AzVMSourceImage -PublisherName \"MicrosoftSQLServer\" -Offer \"SQL2017-WS2016\" -Skus \"SQLDEV\" -Version \"latest\" |\r\n",
" Add-AzVMNetworkInterface -Id $Interface.Id\r\n",
"\r\n",
""
],
"metadata": {
"azdata_cell_guid": "6625d7b2-6c97-432e-b5f5-be4ca93017ae"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Create Virtual Machine"
],
"metadata": {
"azdata_cell_guid": "c42ec570-331a-46ea-b358-b05e47320967"
}
},
{
"cell_type": "code",
"source": [
"# Create the VM\r\n",
"New-AzVM -ResourceGroupName $ResourceGroupName -Location $Location -VM $VMConfig"
],
"metadata": {
"azdata_cell_guid": "05fa1f3d-94e1-480f-ad20-d3006bafc6ac"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Install the SQL IaaS Agent\r\n",
"To get portal integration and SQL VM features, you must install the SQL Server IaaS Agent Extension. To install the agent on the new VM, run the following command after the VM is created."
],
"metadata": {
"azdata_cell_guid": "ef07b3d1-3e2d-45f0-b9d3-fb00be2a7da9"
}
},
{
"cell_type": "code",
"source": [
"Set-AzVMSqlServerExtension -ResourceGroupName $ResourceGroupName -VMName $VMName -name \"SQLIaasExtension\" -version \"2.0\" -Location $Location"
],
"metadata": {
"azdata_cell_guid": "bb3b5436-c34b-44b3-b631-ea60c9dcf16a"
},
"outputs": [],
"execution_count": null
}
]
}