When creating virtual machines (VMs) using the portals provided by the different cloud providers, you are presented with a list of SQL Server image names. However, when you start automating the creation of VMs, you are usually required to provide the IDs of the images that you want to use. I am going to walk you through how to find the SQL Server images that are available for creating your VMs on Azure, AWS, and Google Cloud Platform (GCP). Let’s get started!
Azure
When creating a VM using the Azure CLI, you must know the publisher, offer, and SKU to create the Unique Resource Name (URN) that will be used in the VM creation process. Let’s look at how to find that information on Azure by first logging in and setting the subscription that you want to use.
az loginaz account <span class="token keyword">set</span> --subscription MySub
Next, we need to retrieve a list of publishers for the East US region.
$ az vm image list-publishers -l eastus<span class="token punctuation">{</span><span class="token string">"id"</span><span class="token keyword">:</span> <span class="token string">"../Providers/Microsoft.Compute/Locations/eastus/Publishers/128technology"</span>,<span class="token string">"location"</span><span class="token keyword">:</span> <span class="token string">"eastus"</span>,<span class="token string">"name"</span><span class="token keyword">:</span> <span class="token string">"128technology"</span>,<span class="token string">"tags"</span><span class="token keyword">:</span> null<span class="token punctuation">}</span>,<span class="token punctuation">{</span><span class="token string">"id"</span><span class="token keyword">:</span> <span class="token string">"../Providers/Microsoft.Compute/Locations/eastus/Publishers/1e"</span>,<span class="token string">"location"</span><span class="token keyword">:</span> <span class="token string">"eastus"</span>,<span class="token string">"name"</span><span class="token keyword">:</span> <span class="token string">"1e"</span>,<span class="token string">"tags"</span><span class="token keyword">:</span> null<span class="token punctuation">}</span><span class="token punctuation">..</span><span class="token punctuation">..</span>more hereThat list is huge! We need to filter the list by Microsoft.
$ az vm image list-publishers -l eastus --query <span class="token string">"[?contains(name, 'Microsoft')]"</span><span class="token punctuation">..</span><span class="token punctuation">..</span>312 results
There are more than 300 results from that query, and it might take some time to find the correct publisher for SQL Server. I will let you in on a secret—the correct publisher is MicrosoftSQLServer. Let’s run the command with that publisher.
$ az vm image list-publishers -l eastus --query <span class="token string">"[?contains(name, 'MicrosoftSQLServer')]"</span><span class="token punctuation">[</span><span class="token punctuation">{</span><span class="token string">"id"</span><span class="token keyword">:</span> <span class="token string">"../Providers/Microsoft.Compute/Locations/eastus/Publishers/MicrosoftSQLServer"</span>,<span class="token string">"location"</span><span class="token keyword">:</span> <span class="token string">"eastus"</span>,<span class="token string">"name"</span><span class="token keyword">:</span> <span class="token string">"MicrosoftSQLServer"</span>,<span class="token string">"tags"</span><span class="token keyword">:</span> null<span class="token punctuation">}</span><span class="token punctuation">]</span>Now that we have identified the publisher, we can start looking at the offers available from that publisher.
$ az vm image list-offers -l eastus --publisher MicrosoftSQLServer -o tableLocation Name---------- -----------------------------eastus SQL2008R2SP3-WS2008R2SP1eastus sql2008r2sp3-ws2008r2sp1-byoleastus SQL2012SP3-WS2012R2<span class="token punctuation">..</span>.truncated results
30 offers are returned. We are primarily interested in the SQL Server 2019 images, so let’s filter the list by sql2019.
$ az vm image list-offers -l eastus --publisher MicrosoftSQLServer --query <span class="token string">"[?contains(name, 'sql2019')]"</span> -o tableName Location------------------- ----------sql2019-rhel8 eastussql2019-sles12sp5 eastussql2019-ubuntu1804 eastussql2019-ws2019 eastussql2019-ws2019-byol eastus
We have narrowed it down to five SQL Server 2019 offers. We will roll with the sql2019-ubuntu1804 offer, which is SQL Server 2019 running on Ubuntu 18.04. Next, we can pull the SKUs that are available for that image.
$ az vm image list-SKUs -l eastus --publisher MicrosoftSQLServer --offer sql2019-ubuntu1804 -o tableLocation Name---------- ----------eastus enterpriseeastus sqldeveastus standard
Finally, we have all the information to get the URN that we need to create our VM.
$ az vm image list --location eastus --publisher MicrosoftSQLServer --offer sql2019-ubuntu1804 --sku standard --all -o tableOffer Sku Urn Version------------------ -------- ---------------------------------------------------------- ----------sql2019-ubuntu1804 standard MicrosoftSQLServer:sql2019-ubuntu1804:standard:15.0.200317 15.0.200317
Now we can create our VM using the URN.
$ az vm create -n mydb -g mydb-dev-eus-rg -l eastus --image MicrosoftSQLServer:sql2019-ubuntu1804:standard:15.0.200317
That’s how to find SQL Server images for Azure to create a VM.
AWS: Amazon Web Services
To create a VM using the AWS CLI, we need the Amazon Machine Image (AMI) ID. We could start by retrieving a list of all images, but the list is very large.
$ aws ec2 describe-images --region us-east-1
We can filter the list by owner but, again, it is very large.
$ aws ec2 describe-images --region us-east-1 --owner amazon
What we need to do is implement an additional filter. We will specify that the name must contain SQL, the platform is windows, and the state is available.
$ aws ec2 describe-images --region us-east-1 --owner amazon --filters <span class="token string">'Name=name,Values=*SQL*'</span> <span class="token string">'Name=state,Values=available'</span> <span class="token string">'Name=platform,Values=windows'</span><span class="token punctuation">..</span>.large result <span class="token keyword">set</span>
This produces a large list to sort through, so we will further narrow it down to only the names of the image by adding a query to return the top five.
$ aws ec2 describe-images --region us-east-1 --owner amazon --filters <span class="token string">'Name=name,Values=*SQL*'</span> <span class="token string">'Name=state,Values=available'</span> <span class="token string">'Name=platform,Values=windows'</span> --query <span class="token string">'reverse(sort_by(Images, &CreationDate))[:5].Name'</span> --output table-----------------------------------------------------------------------<span class="token operator">|</span> DescribeImages <span class="token operator">|</span>+---------------------------------------------------------------------+<span class="token operator">|</span> Windows_Server-2019-Japanese-Full-SQL_2017_Enterprise-2020.03.18 <span class="token operator">|</span><span class="token operator">|</span> Windows_Server-2019-English-Full-SQL_2017_Web-2020.03.18 <span class="token operator">|</span><span class="token operator">|</span> Windows_Server-2019-English-Full-SQL_2017_Standard-2020.03.18 <span class="token operator">|</span><span class="token operator">|</span> Windows_Server-2019-English-Full-SQL_2017_Enterprise-2020.03.18 <span class="token operator">|</span><span class="token operator">|</span> Windows_Server-2019-English-Full-SQL_2016_SP2_Standard-2020.03.18 <span class="token operator">|</span>+---------------------------------------------------------------------+
Now we can see the naming convention used, which will allow us to tweak our name filter to find only SQL Server 2019 versions.
$ aws ec2 describe-images --region us-east-1 --owner amazon --filters <span class="token string">'Name=name,Values=*SQL_2019*'</span> <span class="token string">'Name=state,Values=available'</span> <span class="token string">'Name=platform,Values=windows'</span> --query <span class="token string">'reverse(sort_by(Images, &CreationDate))[:5].Name'</span> --output table---------------------------------------------------------------------<span class="token operator">|</span> DescribeImages <span class="token operator">|</span>+-------------------------------------------------------------------+<span class="token operator">|</span> Windows_Server-2019-English-Full-SQL_2019_Web-2020.03.18 <span class="token operator">|</span><span class="token operator">|</span> Windows_Server-2019-English-Full-SQL_2019_Enterprise-2020.03.18 <span class="token operator">|</span><span class="token operator">|</span> Windows_Server-2019-English-Full-SQL_2019_Express-2020.03.18 <span class="token operator">|</span><span class="token operator">|</span> Windows_Server-2019-English-Full-SQL_2019_Standard-2020.03.18 <span class="token operator">|</span><span class="token operator">|</span> Windows_Server-2016-English-Full-SQL_2019_Standard-2020.03.11 <span class="token operator">|</span>+-------------------------------------------------------------------+
We are going to retrieve the image ID for Windows Server 2019 with SQL Server 2019 Standard Edition by replacing our name filter with the full name from the list above. (Note that I truncated some of this JSON to make it more readable.)
$ aws ec2 describe-images --region us-east-1 --owner amazon --filters <span class="token string">'Name=name,Values=Windows_Server-2016-English-Full-SQL_2019_Standard-2020.03.11'</span><span class="token punctuation">{</span><span class="token string">"Images"</span><span class="token keyword">:</span> <span class="token punctuation">[</span><span class="token punctuation">{</span><span class="token string">"Architecture"</span><span class="token keyword">:</span> <span class="token string">"x86_64"</span>,<span class="token string">"CreationDate"</span><span class="token keyword">:</span> <span class="token string">"2020-03-11T09:23:36.000Z"</span>,<span class="token string">"ImageId"</span><span class="token keyword">:</span> <span class="token string">"ami-021d3aeea293e78a7"</span>,<span class="token string">"ImageLocation"</span><span class="token keyword">:</span> <span class="token string">"amazon/Windows_Server-2016-English-Full-SQL_2019_Standard-2020.03.11"</span>,<span class="token string">"ImageType"</span><span class="token keyword">:</span> <span class="token string">"machine"</span>,<span class="token string">"Public"</span><span class="token keyword">:</span> true,<span class="token string">"OwnerId"</span><span class="token keyword">:</span> <span class="token string">"801119661308"</span>,<span class="token string">"Platform"</span><span class="token keyword">:</span> <span class="token string">"windows"</span>,<span class="token string">"State"</span><span class="token keyword">:</span> <span class="token string">"available"</span>,<span class="token string">"Description"</span><span class="token keyword">:</span> <span class="token string">"Microsoft Windows Server 2016 Full Locale English with SQL Standard 2019 AMI provided by Amazon"</span>,<span class="token string">"ImageOwnerAlias"</span><span class="token keyword">:</span> <span class="token string">"amazon"</span>,<span class="token string">"Name"</span><span class="token keyword">:</span> <span class="token string">"Windows_Server-2016-English-Full-SQL_2019_Standard-2020.03.11"</span>,<span class="token punctuation">}</span><span class="token punctuation">]</span><span class="token punctuation">}</span>The image ID for this image is ami-021d3aeea293e78a7. Once we have the ID, we can create our EC2 instance.
$ aws ec2 run-instances /--image-id ami-021d3aeea293e78a7 /--count 1 /--instance-type m5.large /--key-name MyKeyPair--security-group-ids sg-903004f8 /--subnet-id subnet-6e7f829e
This is one way to find SQL Server images for AWS to create a VM.
Retrieving the image information to create a VM using the GCP SDK has pretty decent ergonomics. We start by setting our zone.
$ gcloud config <span class="token keyword">set</span> compute/zone us-east1-bUpdated property <span class="token punctuation">[</span>compute/zone<span class="token punctuation">]</span>.
Now, we need to list the images that are part of a specific project; the list can be found here. If you notice, there are windows-cloud and windows-sql-cloud project images. We are interested in the windows-sql-cloud project images. We will pass the no-standard-images option so that we only get a list of images that belong to the windows-sql-cloud project.
$ gcloud compute images list --project windows-sql-cloud --no-standard-imagesNAME PROJECT FAMILY DEPRECATED STATUSsql-2012-enterprise-windows-2012-r2-dc-v20200310 windows-sql-cloud sql-ent-2012-win-2012-r2 READYsql-2012-standard-windows-2012-r2-dc-v20200310 windows-sql-cloud sql-std-2012-win-2012-r2 READYsql-2012-web-windows-2012-r2-dc-v20200310 windows-sql-cloud sql-web-2012-win-2012-r2 READY<span class="token punctuation">..</span><span class="token punctuation">..</span>more
There are more than 20 images in that list, so let’s apply a filter for just SQL Server 2019.
$ gcloud compute images list --project windows-sql-cloud --no-standard-images --filter<span class="token operator">=</span><span class="token string">'name:sql-2019*'</span>NAME PROJECT FAMILY DEPRECATED STATUSsql-2019-enterprise-windows-2019-dc-v20200310 windows-sql-cloud sql-ent-2019-win-2019 READYsql-2019-standard-windows-2019-dc-v20200310 windows-sql-cloud sql-std-2019-win-2019 READYsql-2019-web-windows-2019-dc-v20200310 windows-sql-cloud sql-web-2019-win-2019 READY
We are going to grab the image family for the Standard Edition and create our instance. Using the image family guarantees we grab the latest version of the SQL Server image so that we are creating VMs that are up to date.
gcloud compute instances create mydb \--image-project windows-sql-cloud \--image-family sql-std-2019-win-2019 \--machine-type n1-standard-2 \--boot-disk-size 80 \--boot-disk-type pd-ssd
That is how you can find SQL Server images for creating VMs on GCP.
Next Steps
I find myself hunting for these steps every time I need to use a new SQL Server image. I hope you find this useful and that it helps you get started finding SQL Server images so that you can automate more of your workflow.
