SQL Server in Microsoft Azure Cloud: PaaS vs IaaS

    The path to hosting SQL Server in any cloud, be it Microsoft or another, must begin with careful planning. Architectural decisions made at an early stage of the project can completely unexpectedly affect the future of the project, and these may not always be obvious-solved problems. Planning also includes a decision about which model SQL Server should be hosted in - in the IaaS model (on the virtual machine) or in the PaaS model (like SQL Azure DB). The difference between the models lies both in the features of the technological plan and in the price, for example, service agreement levels (SLAs) of both models, the influence of latency both at an early stage and at the stage of a project's release, automation, and much more.
     
    In this article, we will try to understand not only what technological differences exist between IaaS and PaaS in Microsoft Azure, but also where this difference can come from, where problems can arise in PaaS, which may not be found in IaaS, and architecture these solutions, as well as what Azure SQL DB Premium is and when to use it.
     
    Table of contents:
    Choosing between IaaS and Azure SQL DB
    IaaS Way: general description and architecture
    PaaS Way: general description, architecture and possible problems
    PaaS Way:
    Azure SQL DB SLA security - what is the difference from SQL Server SLA in a virtual machine?
    PaaS Way: High Availabiliy
    How to choose Azure SQL DB usage mode? Premium level - a guarantee of high performance of SQL Server as a service.
     
    Choose between IaaS and Azure SQL DB ( PaaS ).
     
    In any solution that manages data using the SQL Server platform, there are three options for hosting this platform:
    Locally, in your own infrastructure
    On an Azure virtual machine (IaaS )
    As a service (Azure SQL DB)
     
    Locating a local is not a consideration, as it is circumvented. Consideration of the other two options, we will begin immediately with the decision tree, and then begin to delve into the details.

     
    The first step depends on whether you are developing a completely new solution or migrating an existing one. As they often say, it’s easier to rewrite again than to deal with the migration of the existing one, and here this may be the first architectural solution. If we have a new project, and it does not yet have a backend for data management, then we can immediately go towards PaaS - this approach will allow us to save a lot of time managing and deploying the infrastructure.
     
    In the case of migration, there is a fork in front of us - since Azure SQL DB is very different in functionality from a full-fledged SQL Server, we must analyze our code base and database and see if changes are needed and how critical they are. This is a very important fork - if we miss the data type unsupported by Azure SQL DB, then we will lose a lot of time and effort. With databases we can helpsqlazuremw.codeplex.com , which can analyze the database and say that it is not supported in Azure SQL DB. And, if at this step there is a need to make serious changes to our solution, or there will be functionality that is not supported in Azure SQL DB, but we need it, then the decision tree breaks off and we need to use a local SQL Server or put it on a virtual machine (IaaS).
     
    If we are developing a new solution or are able to make non-critical changes to the migrated project, the next question that arises before us is the size limit of the database in Azure SQL DB, which is 500 GB. Is this enough for us? What will be the projected growth? If we understand that we will not break through this ceiling, we can safely use Azure SQL DB. If we understand that someday a situation may arise in which we will have 501 GB, go to the next step.
     
    Is it possible to redesign architecture or implement sharding on our own? There are situations and projects when this cannot be done - for various reasons, from legacy code to problems with time. Azure SQL DB does not have a built-in sharding mechanism, so its implementation rests with the client. If we are ready to tackle this, then nothing prevents us from using Azure SQL DB. If not, we will again exit to SQL Server in a virtual machine or locally.
     
    Let's look at the differences and architecture of both solutions.
     
    IaaS Way
     
    The method of placing SQL Server in a virtual machine does not fundamentally differ from installing it locally, but the devil is in the little things, and the little things relating to a complex discipline - optimization. Thesis:
     
    We can put on a virtual machinefull SQL Server . The word "full" is important here, because there are functional differences between SQL Server and Azure SQL DB, despite common parents;
    The virtual machine image is on Microsoft Azure Storage, which imposes limitations of an optimizing nature - in order to increase disk performance, you need, for example, to combine them into a raid. By default, a virtual machine in Microsoft Azure does not have disks that can be combined into a raid - when it is created, it receives two disks, and one disk (D :) is temporary, because it is located on the disk of the server on which the machine is currently running, and the second (C :) is hosted as a blob on Microsoft Azure Storage, and all I / O requests go through the caching layer. For this reason, the performance of C: will always be less than D :, however, D: will be cleared during each system operation from the virtual machine (due to transfer to other physical resources). To create a raid, you need to create and connect empty data disks (on the portal, Powershell and much more) to the machine.
     

     
    Ready-made optimized SQL Server images (2008 R2 - 2014) are already available in Microsoft Azure , which can be used both with a separately paid license and by bringing your license (under License Mobility & Software Assurance).
    Standardized Iron. In any cloud, you get standardized equipment, and you need to understand that it often does not have high tuned performance.
     
    PaaS Way
     
    The PaaS approach is very different from IaaS in that there are more developer abstractions from infrastructure, restrictions, and architectural delights. At the very bottom, at the level of infrastructure and equipment, this, of course, is the same standardized equipment, so if you use the Shared mode of Azure SQL DB, then in the presence of restless neighbors, performance can sink in a completely unexpected way. There is also a set of Soft / Hard restrictions, which is done for an understandable purpose - if we administer SQL Server on a virtual machine, and we deal with performance and architecture problems, on the whole, again, then PaaS is completely controlled by the vendor, and you need to do so, so that the solution is highly scalable and resilient,
     
    At the same time, as mentioned above, sharding and other decisions must be made by the developer - there are no built-in tools in Azure SQL DB yet.
     
    In order to better understand what Azure SQL DB is and how it works, consider its architecture.
     
    The first thing to understand - when we create the Azure SQL DB server, in fact, three servers are created, which, in general, are not servers at all - these are replicas, the request from the client to which passes through the TDS entry point. Compared with the traditional solution, in the case of its IP server, it leads to the server, in the case of Azure SQL DB, IP leads to the infrastructure.
     

     
    The request is sent via TDS to the service layer. The service layer is the gateway between the client and the platform layer, and here are the high-level services of our Azure SQL DB - deployment of resources, billing of consumed resources, query router, and much more.
     
     

     
    Further, the request is routed to the platform layer, on which infrastructure services are running - something that provides the immediate functionality of SQL Server as a service. This is the last layer of abstraction over infrastructure, below is just equipment.
    As you can see from the picture, the architecture of the Azure SQL DB solution is much more complicated than hosting SQL Server in a virtual machine. Load balancing and replication also work on each layer, and all this works automatically without human intervention.
    With such an architecture, the approach to solving emerging problems is somewhat different from what we are already used to. There are fewer tools for diagnostics, so it is important to understand what causes this or that problem. If we talk about the most common problems, then the causes of their occurrence can be placed on Azure SQL DB layers. The first level of problems arises already at the level of TDS communications. Here we have more latency than it is in local deployment, and various kinds of fluctuations - mainly arising before entering the Azure SQL DB infrastructure, that is, on network highways that are not related to Microsoft. This point is important, since the request may return with a timeout, and this must be covered by the development and planning of retry logic.
     

     
    When the request has already arrived at the infrastructure, it is checked internally and the login goes through one gateway along with other requests for this equipment. Then the connection opens, however, a timeout may also occur here if the operation is not completed on time - and this should also be solved by retry logic.
    During the work of Azure SQL DB itself, there is a constant probability of problems at the platform layer level - this is where all the restrictions imposed by the vendor on Azure SQL DB apply, and if they are exceeded, the corresponding error will be returned to the client. Here we can not do anything, the only option is to handle these errors on the client side (list of errors -msdn.microsoft.com/en-us/library/ff394106.aspx ).
    Summarizing the use of Azure SQL DB, we must understand and handle the following situations:
     
    The client should not be far from Azure SQL DB in terms of geography. If the client is located locally with us, we must understand that we will have constant fluctuations in the network backbones, and implement appropriate processing for these fluctuations and situations with timeout errors. A good option is to place the client in the cloud as close as possible to Azure SQL DB.
    Azure SQL DB has limitations that are introduced for a reason. If we want to work comfortably, we should not take how much they give, but take how much we need, and take into account that the connection pool is not rubber.
    Features of multi-tenancy. Nowhere will we be given performance guarantees where resources are shared between customers. Here, in the case of Azure SQL DB, the Premium mode arises, in which we are given reserved resources that we can freely use and be sure that no one will pick them up if they stand unused.
     
    We looked at how the architecture of our PaaS solution differs from the IaaS solution. As already mentioned, the PaaS architecture is more complex technologically, since the client must be provided with fault tolerance, fast deployment and much more in the automatic mode, and at the same time it must be laid on a large number of clients. However, with the complexity of the architecture, more complex diagnostics of problems, limitations and differences with standard SQL Server also come.
    Let's now take a look at another important aspect - Azure SQL DB security. About the security of virtual machines, and, therefore, SQL Server in them, it is written here:blogs.msdn.com/b/albe/archive/2014/04/21/azure.aspx
     
    PaaS Way : Security
     
    Azure SQL DB has a number of differences from the local SQL Server, and the most significant ones relate to security issues. Given the features of the implementation of any software product that is provided as a service (that is, in fact, it provides a serious level of abstraction from a large number of infrastructure tasks), it is necessary to clearly understand how SQL Azure works in order to have an idea of ​​why this or that functionality can not yet supported.
     
    As already written, SQL Azure Databases uses the standard SQL Server Tabular Data Stream (TDS) protocol, but exclusively encrypted communications are allowed. SQL Server 2008 introduced a new feature: transparent data encryption (TDE), which allows you to fully encrypt data with minimal effort. However, at this time, SQL Azure Databases does not support database-level encryption. It should be noted that at present, Azure SQL DB is available only through TCP connections and only through port 1433. Therefore, it is necessary to take into account ADO.NET encryption capabilities and certificates. And, for example, the properties of the Encrypt = True and TrustServerCertificate = False connection will protect the transmitted data and help prevent man-in-the-middle attacks. Azure SQL DB’s second safeguard, and, in general, the main thing is the Azure SQL DB firewall, which initially blocks all access to the server. Attempts to connect to the appropriate settings will fail. To start working with the Azure SQL DB server, you need to go to the Azure SQL DB portal and determine the firewall settings for access to your server. Azure SQL Firewall can be managed through the Azure SQL DB portal or directly in the main database using stored procedures such as sp_set_firewall_rule and sp_delete_firewall_rule.
     
    As with any SQL Server implementation, user account management is another aspect that needs to be tightly controlled.
    There is a list of differences between SQL Server and SQL Azure Databases in the security context:
    Microsoft SQL Server supports Windows Integrated authentication using access parameters from Active Directory; SQL Azure Databases only supports SQL Server Authentication.
    Microsoft SQL Server and SQL Azure Databases use the same authorization model based on users and roles created in each database and associated with user logins.
    Microsoft SQL Server has standard server-level roles such as serveradmin, securityadmin, and dbcreator. SQL Azure Databases does not have these roles. Instead, SQL Azure Databases has the roles of loginmanager (creating logins) and dbmanager (creating and managing databases). These roles can be associated with users in the master database.
    Access to SQL Server and Azure SQL DB takes place via the application layer protocol Tabular Data Stream (TDS), which is protected by SSL, through the TCP / 1433 port. Using SSL is optional for Microsoft SQL Server and required for Azure SQL DB.
    In SQL Server, IP-based access control must be done at the host or network level using firewalls. Azure SQL DB has a built-in firewall that restricts all access to the Azure SQL DB server to determine which clients are authorized computers. The firewall issues access based on the IP address of each request.
    SQL Server provides real-time encryption of all stored data at the page level using the Transparent Data Encryption (TDE) functionality. TDE is not supported in Azure SQL DB.
    Azure SQL DB has built-in DDOS protection. In SQL Server, there is none.
    Azure SQL DB automatically patches and updates withoutdowntime. SQL Server needs to be updated, and this can lead to downtime.
     

     
    So, there are security differences, and they need to be addressed. Sometimes the blocker is the lack of TDE, sometimes it is not - it all depends on the decision. And the next block I propose to look at the differences in SLA.
     
    Azure SQL DB SLA - what is the difference from SQL Server SLA in a virtual machine?
     
    SLA is an essential part of any commercial deployment. An Azure SQL DB SLA is more granular than a SQL Server SLA in a virtual machine, in that the SLA is provided directly to the Azure SQL DB service, and not to the underlying infrastructure and virtual machine (but not SQL Server) in the case of IaaS. This is fundamentally important. The SLA on Azure SQL DB is 99.9%, which means 10 hours of downtime per calendar year that go to service the service. Customer service is warned in advance.
     
    PaaS Way: High Availability
     
    Azure SQL DB is initially built under High Availability, and all architecture components are fault tolerant. It’s important to understand that the HA standard concepts for SQL Servr are not applicable to Azure SQL DB (again due to architectural differences). Inside Azure SQL DB there are mechanisms that replicate all records in three replicas to different servers, which provides 99.9% SLA. At the same time, one replica is primary — a write operation is performed on it and, if it is confirmed, everything is replicated synchronously to secondary replicas.
     
    If we want to replicate Azure SQL DB on our own, no one bothers to do this using, for example, Data Sync.
     

     
     
    How to choose the mode of operation of Azure SQL DB?
     
    Starting in April 2014, the Azure SQL DB operating modes changed - the old Web / Business modes were canceled, the main focus of which was concentrated on the size of the database, while the new ones, Basic / Standard / Premium, were completely focused on providing performance guarantees. The figure shows a simple decision tree about which of the new modes to use under what conditions. However, you need to understand that, despite the provision of a certain level of performance by Basic / Standard modes, the Premium mode is characterized by increased values ​​of all indicators - starting from the fact that the base in Premium mode can be up to 500 GB and ending with a lot of guaranteed iron resources more than in Basic / Standard, so if you plan to host a solution in Azure SQL DB that is very resource intensive and,
     
     
     

     
    A separate review requires Premium mode. It is intended, as already mentioned, for projects that require a guaranteed high degree of productivity. In order to evaluate this performance, a special test Azure SQL Database Benchmark was developed
    (doing calculations of the values ​​used in various OLTP tasks. The resources and power of each mode and performance level in Azure SQL DB Benchmark is expressed in Database Throughput Units (DTU). DTU provides a way to measure the relative performance level based on CPU, memory and R / W operations Thus, doubling the DTU practically means doubling the power of the resource allocated for the base.The Azure SQL DB Benchmark test also takes into account the Transaction Rate, which is expressed in transactions per unit, while reducing the time for more powerful modes (basic - by an hour, standard - n and minutes, and premium allows you to perform a large number of parallel transactions per second), and predictability, or a guarantee on the response time.
    In this test, the most powerful mode is Premium. So, from the simplest Basic mode, which has 1 DTU, Premium P3 level has 800 DTU, that is, in fact, is 800 times more powerful, while the performance guarantee is also increased.
     
    You can read more about the new operating modes of Azure SQL DB on the official page -msdn.microsoft.com/en-US/library/azure/dn741340.aspx SQL Azure Databases
     
    Summary
     
    - SQL Server in PaaS mode. With its limitations, with its functions that are not available in SQL Server (mainly related to fault tolerance and automation of tasks such as load balancing), SQLAD can be a tool that will be used instead of local deployment in order to abstract from infrastructure tasks, but it is important and necessary understand that the existing restrictions should seriously affect the processes of discussion and planning of the developed project. It is impossible to say unequivocally which approach (IaaS or PaaS) will be better now and then, but the data and thoughts presented in the article can help in this matter.
     

    Also popular now: