Moving a Database into Azure: Are We in a Cloud yet?

by | Jun 13, 2018

The idea of moving into Cloud service takes over minds in organisations I work with. On my previous project we had our databases in Azure, and my current customer is starting the migration project next month.
For most of people moving into cloud means everything stays the same, but the physical server instead of being in the server room at the same building is located somewhere else. That’s why many organisations choose Azure virtual machine with good old SQL Server installed there. Apart for obvious advantage for organisation to be able to tell everybody “we are in the Cloud now”, it gives such benefits as quick and easy hardware growth when it’s needed, simplified business continuity solution and no actual change in human resource area. This is called an Infrastructure as a service (IaaS) solution. Developers and other SQL users won’t feel the difference in using local SQL Server and using SQL Server in Azure virtual machine. There is a small difference for database administrators.
However, Microsoft wants us to use Azure SQL database instead, a platform as a service (PaaS) solution. You can guess it from the amount of benefits and freebies you get. Azure SQL Database has flexible scalability and growth on demand; fantastic business continuity options, full audit at all time, query statistics and index advisor. The most important of that, it’s much cheaper than running a virtual machine (think of how much you are saving on Windows licence alone!). But to use it you need to forget what you have been doing for years and start doing it differently. SQL Server is a very smart box. It knows how to deal with the data, but also it could run a job in time, collect statistics, control user access to different sorts of data and did the data encryption. Instead of that Azure gives you a whole world of services which could be added or removed on demand. Instead of having everything in one basket, Microsoft found a use to old Unix philosophy rebranded under the name microservices. The reason for that is SQL Server as an all-in-one-box has reached its limits.
There is too much data these days. One should be skilled in performance tuning for SQL Server to process hundred gigabytes tables. The alternative is to add more disk space and computing power to the server, and it’s pricey. In Azure this could be resolved with the scheduled tier increase and decrease when needed or shared elastic pools of compute resources for servers that have different peak time. This could help in saving money by paying only for resources that are used. (Thanks Brent for your help in putting the cloudy things straight, see his comment below.)
Some data could not be treated within relational database. Big data platforms were developed to capture and store all of data in any format. Users may want to query and analyse this data alongside with the relational data. In Azure you can keep it all in one place, in your private cloud resource group, so it is easier to find a solution for mixing the data from different sources together.
Azure is full of other cool services and a list is growing. You can easily plug a new service and play with it on your data: parallel computing, machine learning and insights analysis; that’s the names I know.

Different data-related services available on Azure portal

Microsoft and other Cloud providers are forming the future right now. It all sounds attractive, but also scary as so many unknowns yet. Many of these services don’t have nice interfaces we used to have for years because they are still in development. The first rule of programming is, if it works don’t touch anything. So, while I don’t think there is an immediate need to rewrite perfectly working solutions in cloud services when there is a new project starting soon, there is absolutely no reason for starting it on the platform that very soon becomes a legacy – start your new project in the cloud.
Data masseuse

  1. Brent Leslie

    Elastic pools are more for enabling groups of services to share resources, not for adding more resources to a single instance. This means, for example, that if one server is operating at peak load for 10 minutes every hour, then is latent for the remaining hour (and this is happening across say 5 instances), an elastic pool enables you to assign all the instances to the pool and pay for only the elastic pool price of the DTU/QPU/compute unit usage. In traditional terms it is like having the same instances on a single grunty server and scheduling their processing to occur at n:10, n:20, n:30, n:40, n:50. In Azure terms they are separate instances just sharing compute. It is a very powerful feature as you get the benefits of separately managed instances each using the same compute pool. However scheduling/usage patters are very important in this architecture. It is very well suited for global offices which come online and go offline at fairly predictable intervals during a 24h period or where you have multiple databases with predictable usage. The total elastic pool usage can be increased or decreased, similar to Tier changes for individual instances, but this changes the available compute for all instances in the pool.
    What I described above is quite a bit different to “adding more resources when needed with the ability to switch it off and save money when the peak hour has finished”. Almost all Azure services support this to a degree using the Tier features, which is different to elastic pooling. You can schedule (automatically to some degree) tier changes so your database can increase its tier level prior to a large run, then decrease its Tier after. This is particularly useful for BI ETL architecture which seems to be more like what you are describing here. For example, say you have a peak load running for an 6 hours every day. If you want to change this to run in 3 hours and to not pay for compute for the rest of the time, you simply schedule a Tier upgrade just before you start the process, then downgrade once it is finished.
    Tier changing is particularly useful with some Azure services, especially VMs which allow you to power down where you only pay for storage plus a bit more rather than paying for 24*7 compute for a resource not fully utilised during this time.

    • Kate Loguteva

      Hi Brent
      Thanks for your great explanation of elastic pools.
      I haven’t specified a database type in my blog, I was trying to speak generally about any type of need. Of course, I was primarily thinking of BI solutions, this is my area of expertise. Probably, I was thinking about Azure Data Warehouse when I was writing the piece you have highlighted, so I’ve got that phrase wrong.
      The problem I have seen at numerous of my customers’ on-premise environments is that development server has lower specifications than production server to save money. I believe this causes more loss than saving; developers wait time for the ETL results increases, also many bugs are missed due to assumption “this will perform better in prod”.
      My idea is that if dev and prod databases share a resource pool, this could be the answer to this problem. Production does the heavy data refresh during the night, while dev is mostly used during the day. Although, in some places development server loads all the data at the same time as prod, in which case, as you have pointed out, tier change is the only appropriate answer.

Submit a Comment

Your email address will not be published. Required fields are marked *