Those of you who have tried the new version of SQL Server Management Studio might have noticed already that it has a couple of new wizards added. One of those wizards is sitting next to the already existing that has been allowing you to deploy to a Windows Azure SQL Database service (former SQL Azure) and it is called Deploy Database to a Windows Azure Virtual Machine (VM).
You might be wondering why this wizard is here and what it can do. The blog post below tries to provide the answer to this and some of the related questions.
The Path to Windows Azure
We have seen many time that when people are trying a Virtual Machine in the Windows Azure environment they quickly come up with a number of questions. Once the first set of questions is resolved the next wave of questions come up, then the next wave, and a few next waves.
I have tried to compile the list of things you could expect.
- Get access to a Windows Azure.
Logging into a Windows Azure Portal might not be enough to unlock the full power of the Azure. In majority of cases you would need a Management Certificate. There is nothing special about it, except it should be available on the machine you are using, and it should be known to the Windows Azure. This means you should create a certificate somehow. Alternate option is to download Publishing Profile from the Portal. In this case Portal will generate certificate on your behalf, add it to the subscriptions you have access to, and share it with you in the form of XML file.
- Create and configure your VM.
When you have an access to the Azure environment you can start creating other pieces you are needed. The first thing to create is a VM. Don’t forget that you need a VM that:
- Has a SQL Server in it and version of the SQL Server is the same or higher as the version of SQL Server you have currently, otherwise your deployment capabilities will be very limited.
- Has an Azure Drive attached, you will need it to store your data.
- In case VM was created in a Cloud Service it should have endpoints configured properly, otherwise please make sure you have Azure VNet and corresponding VPN.
- You should be able to connect to the SQL Server instance meaning connections should be accepted by the SQL Server and customer should be able to authenticate. In case your machine is not joined to domain this means only SQL authentication is available, so don’t forget to set up a SQL login in advance to the instance configuration.
- Windows firewall should allow connections to the SQL server to allow them to go though.
- Plus many other steps needs to be taken.
- Once your target system is set up you need to focus on copying data from the source system. The best option that gives you the most of the recovery capabilities it to use the Azure Storage as an intermediate location. In this case you either copy your files directly using Backup to URL, or backup them locally and then use Azure SDK to upload them to the storage.
- Once data is in the cloud you need to get it to the VM. Depends on the data size you can either pull it to the VM and restore or just use Restore from URL.
Those steps are describing the things need to happen to end up with your workload in Windows Azure VM. There are multiple small details in this process, same as a lot of things to consider. You can consult this article if you need more information on the subject.
You might have question how the process above is related to the wizard described in this post. This is exactly what Deploy Database to a Windows Azure VM wizard does for you, plus a few additional convenience items.
Let’s see how this looks in the wizard.
The first screen tries to provide you an idea of what information might be useful for you during the use of the wizard.
The Source Settings screen only want you to provide with two pieces of information:
- Connection to the source server and database
- Where to place backup files (file or UNC path). Please note: this path should look the same for the wizard and the Database Engine
Once you told the wizard where to get the data from you might want to connect to Windows Azure. There are 3 different options:
- Manually provide with the management certificate and paste or type a subscription ID
- Import a publishing profile if you have downloaded it already
- Use you Microsoft Account (former Windows Live ID) to sign in to the portal and allow the Wizard to retrieve publishing profile behind the scenes for you
Once all authentication information is in place we connect to a Windows Azure environment, so you can start configuring it. There are a few things you could do:
- Type a new Cloud Service name (or select it from the list if you already have one)
- Type a new VM name (same as before, you can select it from the list if there are some to select from)
- Select the storage account which is in the same region with the VM (if you don’t have any, don’t worry – wizard will create an account for you)
- Press the Settings… button (One of the most important steps). This button behave differently depending on whether this is a new VM or an existing VM, but in all cases the button needs to be pressed to enter the information that is important for the process.
When you type a new VM name and press the Settings… button a new dialog comes up. If you have seen Windows Azure Portal before you should be familiar with most fields in this new dialog.
Please note: we are trying to warn you if we think that the target SQL Server instance might be lower version than the source instance. Sometimes those are hard errors that doesn’t allow you to continue, when we know for sure that the final configuration will not be compatible, while sometimes we can only guess. In either case the suggestion is to do not ignore those warnings:
When you select the VM that has existed before this makes the dialog look differently.
Please note two things here:
- This is one of the first times SQL Server Cloud Adapter comes up on the screen. This is a new component that runs as a service inside a VM (you can run it on the physical machine too) and does all the magic of the VM configuration. We will spend a bit of time on its capabilities and restrictions a little later.
- Since single Cloud Service can contain multiple VMs sharing the same public IP – this means Cloud Adapter Port might be different for those VMs. Also as the previous phrase suggests this is a public port.
Once you chose settings for a new VM or connected to an existing one the Target Database section became active. Please note that Database name field is editable meaning you can change your mind about database name on the target machine.
Now you are just two easy steps away from having your database in the cloud. The first step is to confirm the selection you have made when following the wizard:
And a second step is to wait until process completes. Depends on the database size and your connection speed the time might differ, but wizard will make sure it is done.
And finally you would see the confirmation screen that also contains a link to the detailed log.
This log is important for most of the troubleshooting actions, same as for digging for details.
Deploy Database to a Windows Azure VM wizard has guided you through the deployment process. Now this is the right time to observe what the end result is look like.
- You have Virtual Machine running in Windows Azure
- This VM has SQL Server instance configured
- You database is deployed to the instance
However, a few manual steps might be needed.
- You need to add a logins and give those logins access to the database the wizard has deployed.
- If your application would be connecting to the VM from outside – make sure to configure the SQL Server to allow the connectivity.
The Deploy Database to a Windows Azure VM wizard is in place to simplify the steps needed to have a database in a Windows Azure VM environment. The whole wizard is made around the scenarios when you need your database in the cloud as soon as possible and willing to skip some studying or preparation steps as a tradeoff for the speed of the deployment. The other assumption was that you don’t want to do a deployment manually and prefer SQL Server to do some of this work for you.
The wizard was made as an extremely safe to your data. There is no chance it could delete, overwrite, or even modify your data. All operations it does are read only for the data.
We really hope this new feature will help you to save time during your deployments.