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 login
az account set --subscription MySub
Next, we need to retrieve a list of publishers for the East US region.
$ az vm image list-publishers -l eastus
{
"id": "../Providers/Microsoft.Compute/Locations/eastus/Publishers/128technology",
"location": "eastus",
"name": "128technology",
"tags": null
},
{
"id": "../Providers/Microsoft.Compute/Locations/eastus/Publishers/1e",
"location": "eastus",
"name": "1e",
"tags": null
}
....more here
That list is huge! We need to filter the list by Microsoft.
$ az vm image list-publishers -l eastus --query "[?contains(name, 'Microsoft')]"
....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 "[?contains(name, 'MicrosoftSQLServer')]"
[
{
"id": "../Providers/Microsoft.Compute/Locations/eastus/Publishers/MicrosoftSQLServer",
"location": "eastus",
"name": "MicrosoftSQLServer",
"tags": null
}
]
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 table
Location Name
---------- -----------------------------
eastus SQL2008R2SP3-WS2008R2SP1
eastus sql2008r2sp3-ws2008r2sp1-byol
eastus SQL2012SP3-WS2012R2
...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 "[?contains(name, 'sql2019')]" -o table
Name Location
------------------- ----------
sql2019-rhel8 eastus
sql2019-sles12sp5 eastus
sql2019-ubuntu1804 eastus
sql2019-ws2019 eastus
sql2019-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 table
Location Name
---------- ----------
eastus enterprise
eastus sqldev
eastus 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 table
Offer 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 'Name=name,Values=*SQL*' 'Name=state,Values=available' 'Name=platform,Values=windows'
...large result set
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 'Name=name,Values=*SQL*' 'Name=state,Values=available' 'Name=platform,Values=windows' --query 'reverse(sort_by(Images, &CreationDate))[:5].Name' --output table
-----------------------------------------------------------------------
| DescribeImages |
+---------------------------------------------------------------------+
| Windows_Server-2019-Japanese-Full-SQL_2017_Enterprise-2020.03.18 |
| Windows_Server-2019-English-Full-SQL_2017_Web-2020.03.18 |
| Windows_Server-2019-English-Full-SQL_2017_Standard-2020.03.18 |
| Windows_Server-2019-English-Full-SQL_2017_Enterprise-2020.03.18 |
| Windows_Server-2019-English-Full-SQL_2016_SP2_Standard-2020.03.18 |
+---------------------------------------------------------------------+
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 'Name=name,Values=*SQL_2019*' 'Name=state,Values=available' 'Name=platform,Values=windows' --query 'reverse(sort_by(Images, &CreationDate))[:5].Name' --output table
---------------------------------------------------------------------
| DescribeImages |
+-------------------------------------------------------------------+
| Windows_Server-2019-English-Full-SQL_2019_Web-2020.03.18 |
| Windows_Server-2019-English-Full-SQL_2019_Enterprise-2020.03.18 |
| Windows_Server-2019-English-Full-SQL_2019_Express-2020.03.18 |
| Windows_Server-2019-English-Full-SQL_2019_Standard-2020.03.18 |
| Windows_Server-2016-English-Full-SQL_2019_Standard-2020.03.11 |
+-------------------------------------------------------------------+
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 'Name=name,Values=Windows_Server-2016-English-Full-SQL_2019_Standard-2020.03.11'
{
"Images": [
{
"Architecture": "x86_64",
"CreationDate": "2020-03-11T09:23:36.000Z",
"ImageId": "ami-021d3aeea293e78a7",
"ImageLocation": "amazon/Windows_Server-2016-English-Full-SQL_2019_Standard-2020.03.11",
"ImageType": "machine",
"Public": true,
"OwnerId": "801119661308",
"Platform": "windows",
"State": "available",
"Description": "Microsoft Windows Server 2016 Full Locale English with SQL Standard 2019 AMI provided by Amazon",
"ImageOwnerAlias": "amazon",
"Name": "Windows_Server-2016-English-Full-SQL_2019_Standard-2020.03.11",
}
]
}
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.
Google Cloud Platform (GCP)
Retrieving the image information to create a VM using the GCP SDK has pretty decent ergonomics. We start by setting our zone.
$ gcloud config set compute/zone us-east1-b
Updated property [compute/zone].
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-images
NAME PROJECT FAMILY DEPRECATED STATUS
sql-2012-enterprise-windows-2012-r2-dc-v20200310 windows-sql-cloud sql-ent-2012-win-2012-r2 READY
sql-2012-standard-windows-2012-r2-dc-v20200310 windows-sql-cloud sql-std-2012-win-2012-r2 READY
sql-2012-web-windows-2012-r2-dc-v20200310 windows-sql-cloud sql-web-2012-win-2012-r2 READY
....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='name:sql-2019*'
NAME PROJECT FAMILY DEPRECATED STATUS
sql-2019-enterprise-windows-2019-dc-v20200310 windows-sql-cloud sql-ent-2019-win-2019 READY
sql-2019-standard-windows-2019-dc-v20200310 windows-sql-cloud sql-std-2019-win-2019 READY
sql-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.
Jamie (@phillipsj73) is a Senior Cloud Engineer at SentryOne and working remotely in East Tennessee. He has been working with .NET since 2007 after discovering .NET development in graduate school. His Geology degree has given him an appreciation for large systems and processes which has created keen interest in Solutions Architecture, DevOps, and the Cloud. During the day he work on Windows, but at night he is all about Linux.
