We had previously blogged about moving some of our on-premise SQL Server environments which manages critical functions necessary for releasing the SQL Server builds to all our customers. We announced SQL Server 2016 CTP 2.4 which was made available on September 30th, 2015. You can download the preview from the Microsoft TechNet Evaluation Center site. In this post, we will outline how we moved some of our smaller databases to the Azure Virtual Machines using Lift-and-Shift and at the same time we minimized the downtime for all the databases.
Who did we move?
The databases that we are referencing in this post were lesser than 25GB in size. Most of the database servers were running Windows Server 2012 with a standalone SQL Server 2014.
What did we move them to?
We consolidated the standalone SQL Server environments into a single SQL Server 2016 CTP Availability Group running on an Azure Virtual Machine.
While Lift-and-Shift was possible for the scenario that we had, there is some amount of pre-work that needed to be taken care of. Yes, there is always pre-work!
The first task was to baseline the current environments. This allowed us to determine what tier we needed to pick for our virtual machine which would satisfy the CPU and Memory requirements. The second order of the day was to baseline our storage requirements which allowed us to pick the correct storage tier and also configure it appropriately. If you are wondering what SQL specific configurations that we implemented, you can find them listed in the best practices documentation for running SQL Server instances on Azure Virtual Machines.
We are using a D4 Azure Virtual Machine which we are in the process of migrating to DS4 Azure Virtual Machine with Premium IO. We are planning to consolidate additional databases into this environment and based on the recent best practices guidance and our IO & compute needs, we would be moving to a DS4 virtual machine. We setup an Availability Group in the environment which was connected to the on-premise environment using ExpressRoute (details available below).
Post this, we setup an ExpressRoute between our on-premise environment and Azure. This would allow our applications which would reside on on-premises environment to connect to the Azure virtual machines. ExpressRoute is a service that enables you to create private connections between Azure datacenters and infrastructure that’s on your premises or in a colocation environment. ExpressRoute connections do not go over the public Internet, and offer more reliability, faster speeds, lower latencies and higher security than typical connections over the Internet.
TEST, TEST and TEST
The next task was to test the environment that we setup with our on-premise applications. Once we completed these functionality tests, we knew that the setup worked as expected. Next were the performance tests and we did not find any issues on that front. Finally, we had an environment that you see in the network diagram shown below.
The next task on our plate was to minimize downtime when we actually switched over the applications to the Azure virtual machine which we achieved through the following:
We setup a sync between the current environment and the future environment so that we minimized downtime during the cutover period. For this we evaluated our options and went with log shipping where the databases were restored on the SQL Server instances on the Azure virtual machines using the Express Route connection.
The second task was to create a DNS entry which would allow the applications to connect to the SQL Server instances which were hosted on the on-premise servers. During the cutover period, we intended to change the DNS entry to point to the SQL Server instances hosted on the Azure virtual machines.
The architecture after we setup the environment looked like the visio diagram available below.
We sent out all the service updates to everyone that might be impacted. During the actual ZERO hour:
Stopped the applications and their associated services
Synced the log shipping secondary databases with any remaining backups
Removed log shipping and brought the databases online
Setup availability group secondary for the databases that we added to the availability group
Modified the DNS entry to point to the SQL Server instances on the Azure Virtual Machine
Restarted the application service and did the necessary tests to verify that everything was working as expected
And voila…. Migration to Azure was done! The actual end-to-end switchover took less than 60 minutes along with the application testing!
Stay tuned to your blog as we will provide more insider scoops about systems that we are upgrading and migrating. And did I forget to tell you that we are running CTP 2.4 on these systems right now!
Our current architecture diagram is shown below. While we are planning to migrate the applications to Azure as well in the future, we chose this approach as using ExpressRoute and DNS entry enabled us to move our backend database to SQL Server instances on Azure Virtual Machines without having to change the application code.
As always, we are more than happy to hear your feedback… So feel free to leave a comment or reach out to us on Twitter (@mssqltiger).
- Amit Banerjee (@banerjeeamit)
Senior Program Manager
Microsoft Data Platform Group