The client has a business partner who manages a particular application and its database for them. The client was relying on periodic data extracts for business intelligence and operational data. This was a real drag on business operations in an era when everyone expects near real-time information.
We can replicate the data from the partners database, except the partner has SQL Server Enterprise edition and you can’t replicate from Enterprise to Standard edition if you want to be able to report off the replica. And Enterprise edition was going to cost us at least £20,000 just for a lowly two core server plus a VM to run it on so probably more like £30,000 by the time we’re done. The client didn’t have that kind of capex money lying around so we were tasked with finding a way to do it on a shoestring.
The Solution – Azure to the rescue
The essential problem was the cost of SQL Server Enterprise edition licensing, so we looked at other options for the client. We hit upon the idea of using an Azure VM because we didn’t need the VM running all the time and we figured it might save a lot of money if we only needed it up a few hours a day.
So we did some calculations using the Azure pricing calculator at https://azure.microsoft.com/en-gb/pricing/calculator/ and worked out that we could get 100 hours a month for about £250 a month. That includes a VM with SQL Enterprise licensing, a private IP address and and a terabyte of premium managed disk for the databases. That gives us about four hours of up time a day in the week and and a couple of hours a day at the weekend.
Next job was to get it set up and accessible from the client’s network. We use an ARM template that we developed to set up the VM in its own secure virtual network. The details of that are beyond the scope of this case study, but suffice to say you can create a very secure environment for your VM with traffic locked down to and from only the IP addresses you want. Once we had the private IP and DNS setup we were able to provide these to the clients on premise network team so they could configure their firewall appropriately to be able to connect to the VM.
The final piece of the puzzle was to configure log shipping along with some secure FTP to manage the log file synchronisation. For the SFTP we chose WinSCP because of it’s excellent documentation and easy scripting, which meant we could call it directly from the log shipping agent job.
The Architecture diagram.
We started off with the VM being stopped and started by Azure Run Jobs – essentially PowerShell scripts running as scheduled jobs and configured in the Azure Portal. Then we’d have an on premise job that started up about half an hour after the VM and ran an ETL to pull down the latest data into the on premise operational data store. Whilst this worked most of the time, we didn’t have much control if anything went wrong, so back to the drawing board for a better solution.
In the end we hit upon controlling everything via the ETL. We switched off the Azure Runbook jobs and used SSIS instead to orchestrate the job. In the end it was like this:
- Start up the VM
- Wait five minutes for SQL Agent to start up
- Kick of the Agent job to fetch the latest logs from the partner’s SFTP and run the log shipping restore.
- Pull down the latest data
- Turn off the VM
- Run the post processes necessary to push the new data into the places it was needed
We had to put a bunch of code in to retry various steps if needed and to provide error reporting, but in the end we had a job that ran in about 20 minutes end to end, meaning we could comfortably have around half a dozen synchronisations a day, giving operational staff a much more up to date view of their customer base, improving customer interactions and outcomes.
So not only has it saved a bunch of money but it has improved day to day operations in a
How Much Did it Cost
The VM was switched on a bit longer during the development period so it was up to about £500 for the first month. Ongoing costs have come down to around the original estimate of £250 / Month plus another £12 a month to give us the security of daily snapshots of the VM. We’ve been able to test the restore of that as a disaster recovery exercise and had another replica up and running within two hours.
Azure VMs are a great solution where you need SQL Server in the cloud, but you don’t need it running 24/7. The solution is extremely robust and the security very strong – as long as you know what to do and do it properly.
If this is something that would work for you, then get in touch.