This is the fourth post for the mini-series entitled “Hosting SQL Server in Window Azure IaaS Fundamentals”. In this post I will teach you about uploading a VHD to Windows Azure that already has SQL Server (and possibly other supporting apps) installed on it. The reason you would want to take this approach is if you want to use SQL Server as part of an existing VM configuration you are running in your on premises virtual environment that may contain other supporting or reliant apps for SQL Server, and you want to move it as-is into Azure. To create an Azure VM and rebuild this environment manually to replicate the on premises SQL Server configuration may be very time consuming and error prone. With this option you will forklift the existing VM environment found on the VM into the Cloud. This minimizes your configuration time and keeps a consistent environment from on premises to the Cloud. However, this process of getting SQL Server into Azure is the most complex and time consuming so it correspondingly takes longer to get SQL Server up and running in the IaaS environment. If expediency of getting SQL Server running in an Azure IaaS VM is important, or if you are not trying to replicate an on premises SQL Server installation exactly in Azure, this option is probably not for you.
We will also look at how to create and install a management certificate into Azure. Once that is in place we can use the CSUpload utility or Azure PowerShell cmdlets to upload our VHD that contains SQL Server into Azure Blob storage. After that is complete we can then create an Azure image or disk from that VHD. This in turn allows us to create our own custom VM installation of SQL Server running in the Azure IaaS Cloud.
Loading a VHD into Azure
By choosing this option to get SQL Server into an Azure IaaS Cloud you will not use any of the stock pre-loaded SQL Server Gallery images to create a SQL Server VM. Instead you will use a custom Azure Disk Image or Disk to create the SQL Server VM. Recall we discussed the differences between Azure disks and images in a previous post. Be careful that when you load your VHD into Azure that it is of fixed format – not dynamic, or it may expand beyond the 127GB size during the upload process. Also, I just want to mention again that with any SQL Server IaaS installation it is your job to manage updates to SQL Server and the VM on your own.
Install Management Cert in Azure Portal
This is a three-step process where we will first create a self-signed certificate for testing. You would probably want to get it signed by a Certificate Authority in real-world deployments. You can then export it from the local certificate store using the Certificate Manager console. You can then upload the certificate into Azure using CSUpload or the Azure portal. Once the certificate is loaded you can then invoke PowerShell cmdlets or CSUpload to upload the VHD to Azure.
1. Create self-signed management certificates, open a Visual Studio command prompt as an administrator, and then run the following command. The Makecert command stores the new certificate in the default Personal certificate store. You will then need to export it from the personal store.
makecert -sky exchange -r -n “CN=mysqldemocert” -pe -a sha1 -len 2048 -ss My “mysqldemocert.cer”
2. Export your X.509 v3 certificate
- Start / run / certmgr.msc
- Navigate the tree hierarchy until you find the certificate you just created.
the left pane right click on the certificate and select / all tasks / export
- Select “No, do not export the private key”
- Click on next until you are asked for the Export path. Enter the desired export path.
- Follow the prompts and finish the export.
3. Upload the certificate to azure using the portal. Note you can also do it with the CSUpload tool if you desire to do it programmatically.
Upload a VHD from on Premises to Azure Blob Storage using CSUpload
Once the certificate is loaded to Azure, you could use its thumbprint as a parameter to the CSUpload utility and PowerShell cmdlets to upload the VHD to Azure Blob storage. CSUpload is older technology than the Powershell cmdlet to upload the VHD but I will for sure include it since some folks don’t want to mess with Powershell. So we will look at both in this module and you can choose which one you want to use based upon preference. Note that if using PowerShell ISO once you import your publishsettings file you don’t need to specify a management certificate as the tool manages that for you for that session.
First let’s upload a VHD using CSUpload. Before we do any uploading we want to, as a best practice, create a named storage account for our VHDs. We will call this storage account sqluploadvms, all lowercase as required by the portal. CSUpload requires you to include the thumbprint of the management certificate you uploaded as a parameter. Here’s where you get other parameters from Azure for CSUpload.
- Storage URL
- Subscription ID
- Cert Thumbprint (Column in cert page)
You can find the CSUpload tool in the C:\Program Files\Microsoft SDKs\Windows Azure\.NET SDK\2012-10\bin\ folder. Run it from a command prompt as Administrator.
csupload Set-Connection “SubscriptionID= 04786d34-85b6-49c5-a3e3-564d625e1aa1;CertificateThumbprint= EA2D7C84D99127E5294B5A5151B7C7D886462DC3;ServiceManagementEndpoint=https://management.core.windows.net”
csupload Add-PersistentVMImage -Destination “http://mysqlmikevms.blob.core.windows.net/vhds/Azure-SQLServer.vhd” -Label Win2008R2.vhd -LiteralPath C:\Temp\Win2008R2.vhd -OS Windows
Upload a VHD to Azure Blob Storage Using PowerShell
The other option, and as a best practice is typically the preferred option since you don’t have to mess with creating an uploading a mgmt certitficate using PowerShell ISO, it to use PowerShell to upload the VHD. PowerShell API offers more options as well over CSUpload. We don’t need to explicitly upload a management certificate for certain tools like Visual Studio or PowerShell ISE. When you access the publishsettings file from either of them it will create a mgmt certificate for that connection and install in the Azure portal. Note that if you are running a PS1 PowerShell scripts from the normal Powershell comand prompt you need to reference the thumbprint of the certificate within the scripts itself.
Next let’s upload a VHD using PowerShell. Open a Windows Azure PowerShell ISE window. As a best practice when you are interactively using PowerShell this is better than using the normal Azure PowerShell prompt due to the ability to help you develop the call interactively. When you download the publishsettings file it contains information and a certificate for your Windows Azure subscription. This means you do not need to use the certificate thumbprint explicitly in these calls. Import the file set the current subscription and storage account, then being the upload of the VHD to blob storage.
//Causes you to log into azure portal. Download to c:\temp and rename //for simplicity file.publishsettings.
If we now look at the at the Azure portal we can see there is a new certificate that has been uploaded and created for the PowerShell ISO tool. We will now import the publishing settings file which contains an encoded version of the management certificate we just created. It serves as your credentials to administer your subscriptions and related services. Store this file in a secure location or delete it after you use it. When we import the publishsettings file that cert will be used for all powershell calls using this connection.
//Imports the file and chooses a default subscription. If not what you //need to change it in next command.
//Choose current subscription and storage account to copy the VHD to.
Select-AzureSubscription “Windows Azure MSDN – Visual Studio Professional”
//Begin the upload
Add-AzureVhd -Destination “http://mysqlmikevms.blob.core.windows.net/vhds/sqlserverintdisk.vhd” -LocalFilePath “C:\temp\Win2008R2.vhd”.
Once we get the VHD uploaded to Azure we need to the create an Azure Disk or Image from that VHD. What we will create depends upon if the OS on that disk has run sysprep for an image or not for a disk.
Once you get the VHD that contains SQL Server uploaded to blob storage you can then create a SQL Server image from a VHD, or you can create a SQL Server disk from a VHD in one of these two ways:
Creating an Azure SQL Server Image
- Virtual Machines/Images/Create
- Find a VHD that has been syspred and create a SQL Server image
Creating an Azure SQL Server Disk
- Virtual Machines/Disks/Create
- Find a VHD that has been NOT BEEN syspred and create a SQL Server image
- Click The VHD contains an operating system of it does and this disk you will run the SQL Server from. If just a SQL Server data disk (no OS) don’t check this.
Once we create an Azure Image or Disk, we can them create a VM from this in the gallery.
This post is one of the most involved so far. We discussed how to create, export, and upload a self-signed certificate to Azure if you are uploading a VHD using CSUpload or a PS1 file in which you would need to specify the cert thumbprint in the PS1 file. Once that is complete you upload your VHD to blob storage using powershell cmdlets or the CSUpload utility. From there you create an Azure image or disk from the VHD (again depending upon if it has been sysprep’d or not) then create an Azure VM or Disk from that VHD. You can then start your VM and your custom installation of SQL Server is up and running in Azure IaaS.