SQL Server in Microsoft Azure Cloud: PaaS vs IaaS
The path to hosting a SQL Server in any cloud, be it Microsoft or another, must begin with careful planning. Architectural decisions made at an early stage of a project may in a completely unexpected way affect the future of a project, and this may not always be obvious-solvable problems. Planning also includes deciding whether the SQL Server should be placed in the IaaS model (virtual machine) or in the PaaS model (as SQL Azure DB). The difference between the models lies in both the technological plan and the price, for example the levels of service agreements (SLA) of both models, the influence of latency both at an early stage, and at the stage of project publication, 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 about where such a difference can arise, where problems can arise in PaaS, which can be not met in IaaS, and architecture these solutions, and what Azure SQL DB Premium is and when to use it.
Table of contents:
Choose between IaaS and Azure SQL DB
IaaS Way: general description and architecture
PaaS Way: general description, architecture and possible problems
PaaS Way: Security
Azure SQL DB SLA - what's the difference from a SQL Server SLA in a virtual machine?
PaaS Way: High Availabiliy
How to choose Azure SQL DB usage mode? Premium level - guaranteed high performance of SQL Server as a service
Choose between IaaS and Azure SQL DB ( PaaS )
')
In any data management solution using the SQL Server platform, there are three options for hosting this platform:
Locally, in its own infrastructure
On the Azure virtual machine (IaaS)
As a service (Azure SQL DB)
Placement of a local subject is not considered, since it is circumvented up and down. Considering the other two options, we will start right away from the decision tree, after which we will begin to delve into the details.
The first step depends on whether a completely new solution is being developed or an existing one is being migrated. As is often said, it is easier to rewrite it rather than deal with the migration of an existing one, and here it can be the first architectural solution. If we have a new project, and it does not yet have a backend on data management, then we can immediately go in the direction of PaaS - this approach will allow us to save a lot of time for managing and deploying infrastructure.
In the case of migration, there is a fork in front of us - since Azure SQL DB in terms of functionality is very different from a full-fledged SQL Server, we need to analyze our code base and database and see if the changes are necessary and how critical they are. This is a very important fork - if we miss a data type that is not supported by Azure SQL DB, then we will lose a lot of time and effort. With databases, sqlazuremw.codeplex.com can help us , which can analyze the database and say that it is not supported from it in Azure SQL DB. And, if at this step there is a need to make major 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 is broken and we need to use a local SQL Server or install it on a virtual machine (IaaS).
If we are developing a new solution or have the opportunity to make non-critical changes to the project being migrated, then the next question we face is the limit on the size 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 do not break through this ceiling, then we can safely use Azure SQL DB. If we understand that someday there may be a situation in which we will have 501 GB, proceed to the next step.
Is it possible to redesign the architecture or implement sharding on its own? There are situations and projects when this can not 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 falls on the client’s shoulders. If we are ready to take on this, then nothing prevents us from using Azure SQL DB. If not, we are again logging on to SQL Server on a virtual machine or locally.
Let's look at the differences and architecture of both solutions.
IaaS way
The method of placing a SQL Server in a virtual machine is not fundamentally different from installing it locally, but the devil is in the small things and the small things related to a complex discipline - optimization. Thesis:
On a virtual machine, we can put a full-fledged SQL Server . The word "full" is important here, since there are functional differences between SQL Server and Azure SQL DB, despite common parents;
The virtual machine image lies on Microsoft Azure Storage , which imposes optimization constraints - in order to improve disk performance, you need, for example, to merge them into a raid. A virtual machine in Microsoft Azure does not have disks by default, which can be combined into a raid - when it is created, it receives two disks, and one disk (D :) is temporary, as it is located on the server disk on which the machine is currently running, and the second (C :) is placed as a blob on Microsoft Azure Storage, and all I / O requests go through the caching layer. For this reason, C: performance will always be less than D :, but D: will be cleared during every system operation on 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 many more) to the machine.
In Microsoft Azure, ready-made optimized images of SQL Server (2008 R2 - 2014) are already available, which can be used with both a separately paid license and having brought your license (under License Mobility & Software Assurance).
Standardized iron. In any cloud, you get standardized equipment, and you need to understand that it most often does not have a highly tuned performance.
PaaS way
The paaS approach is strikingly different from IaaS by the presence of a larger number of developer abstractions from infrastructure, constraints, and architectural delights. At the very bottom, at the level of infrastructure and equipment, this is, of course, the same standardized equipment, so if you use the Shared-mode of Azure SQL DB, then in the presence of disturbed neighbors, performance can subside in a completely unexpected way. There is also a set of Soft / Hard restrictions, which is done with an understandable purpose - if we administer the SQL Server on the virtual machine, and we deal with performance and architecture problems, in general, again, then PaaS is fully managed by the vendor, and you need to do so so that the solution is highly scalable and fault tolerant, and restless users with a high seasonal load do not put anything infrastructural.
At the same time, as mentioned above, sharding and other solutions must be made by the developer - there are no embedded 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 you need to understand is that when we create an Azure SQL DB server, in fact, there are three servers that, in general, are not servers at all - these are replicas, the request from the client to which passes through the TDS entry point. If we compare it with a traditional solution, then in the case of its IP server we lead 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 provided by our Azure SQL DB — deploying resources, billing consumed resources, query routers, and more.
Next, the query is routed to the platform layer on which the infrastructure services are running — something that provides the immediate functionality of SQL Server as a service. This is the last layer of abstraction above the infrastructure, below - only the equipment.
As you can see from the picture, the architecture of the Azure SQL DB solution is much more complicated than hosting a SQL Server in a virtual machine. Load balancing and replication also work on each layer, and it all works automatically without human intervention.
With this architecture, the approach to solving problems arises in some way from what we are used to. There are fewer tools for diagnostics; therefore, it is important to understand why a particular problem occurs. If we talk about the most common problems, the causes of their occurrence can be placed on the layers of Azure SQL DB. The first level of problems arises already at the level of TDS communications. Here we have a greater latency than it is in local deployment, and various kinds of fluctuations - mostly occurring even before entering the Azure SQL DB infrastructure, that is, on the network backbones that are not related to Microsoft. This point is important, since the request can return with a timeout, and this needs to be covered by the development and planning of the retry logic.
When the request has already arrived at the infrastructure, it is checked by internal means, and the login passes through one gateway along with other requests for this equipment. A connection opens further, however, a timeout may also occur here if the operation is not performed on time - and this should also be decided by the retry logic.
While Azure SQL DB is running, there is a constant likelihood of problems at the platform layer level - just here all the limitations imposed by the vendor on Azure SQL DB are valid, and, if they are exceeded, the corresponding error will be returned to the client. We cannot do anything here, the only option is to handle these errors on the client side (the list of errors is msdn.microsoft.com/en-us/library/ff394106.aspx ).
Summarizing the use of Azure SQL DB, we need to 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 with us locally, we must understand that we will have constant fluctuations of the network backbones, and implement the appropriate handling of these fluctuations and situations with the occurrence of timeout errors. A good option seems to be placing the client in the cloud as close to Azure SQL DB as possible.
In Azure SQL DB there are restrictions that are not just introduced. If we want to work comfortably, we should not take as much as they give, but take as much as we need, and take into account that the connection pool is not rubber.
Features multi-tenancy. Nowhere will they give us performance guarantees, where resources are shared between customers. Here, in the case of Azure SQL DB, a Premium mode occurs in which we are given reserved resources that we can freely use and be sure that no one will take them away if they are left unused.
We looked at how the architecture of our PaaS solution differs from the IaaS solution. As already mentioned, the PaaS architecture is more technologically sophisticated, since the client should be provided with fault tolerance, rapid deployment and much more in automatic mode, and at the same time it should be provided for a large number of clients. However, with the increasing complexity of the architecture, there are also more complex troubleshooting problems, limitations and differences with standard SQL Server.
Let's now look at another important aspect - the security of Azure SQL DB. About 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 set of differences from the local SQL Server, and the most significant ones concern security issues. Given the implementation features of any software product that is provided as a service (that is, in fact, 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 only encrypted communications are allowed. In SQL Server 2008, a new tool was introduced: transparent data encryption (TDE), which allows you to fully encrypt data with minimal effort. However, at the moment, SQL Azure Databases does not support encryption at the database level. It should be noted that currently Azure SQL DB is available only through TCP connections and only through port 1433. Therefore, you need to consider the ADO.NET encryption capabilities and certificates. And, for example, the properties of the connection Encrypt = True and TrustServerCertificate = False will protect the transmitted data and help prevent man-in-the-middle attacks. The second means of protecting Azure SQL DB, and, in general, the main one is the Azure SQL DB firewall, which initially blocks all access to the server. Attempts to connect to the appropriate settings will fail. To get started with the Azure SQL DB server, you need to log in to the Azure SQL DB portal and define the firewall settings for accessing 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, managing user accounts 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 the roles created in each database and associated with user logins.
Microsoft SQL Server has standard server-level roles such as serveradmin, securityadmin and dbcreator. There are no such roles in SQL Azure Databases. Instead, SQL Azure Databases has the role 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 is via the Tabular Data Stream (TDS) application-level protocol, which is protected by SSL, via TCP / 1433. Using SSL is optional for Microsoft SQL Server and optional for Azure SQL DB.
In SQL Server, IP-based access control must be performed 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 until the clients of the computers that have access rights are defined. The firewall gives 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 functionality of Transparent Data Encryption (TDE). TDE is not supported in Azure SQL DB.
Azure SQL DB has built-in DDOS protection. There is none in SQL Server.
Azure SQL DB is automatically patched and updated without downtime. SQL Server needs to be updated, and this can lead to downtime.
So, there are differences in security, and they should be foreseen. Sometimes a blocker is the absence of TDE, sometimes it is not as such - it all depends on the decision. And the next block I propose to look at the differences in SLA.
Azure SQL DB SLA - what's the difference from a SQL Server SLA in a virtual machine?
SLA is an indispensable part of any commercial deployment. Azure SQL DB SLA is more granular than a SQL Server SLA on a virtual machine, from the standpoint 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, which are spent on servicing the service. About customer service warn in advance.
PaaS Way: High Availability
Azure SQL DB is initially laid out under High Availability, and all components of the architecture are fault tolerant. It is important to understand that the standard for SQL Servr concept of HA to Azure SQL DB is not applicable (again due to architectural differences). Inside Azure SQL DB, there are mechanisms that replicate all the records in three replicas across different servers, which ensures 99.9% SLA. In this case, 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 with, for example, Data Sync.
How to choose Azure SQL DB mode?
Starting from April 2014, Azure SQL DB modes of operation are changing - the old Web / Business modes are canceled, the main focus of which was concentrated around the database size, while the new Basic / Standard / Premium modes are focused entirely on performance guarantees. The figure shows a simple decision tree on which of the new modes in which conditions to use. It is necessary, however, to understand that, despite the provision of a certain level of performance with Basic / Standard modes, the Premium mode differs in elevated values of all indicators - starting from the fact that the base in the Premium mode can be up to 500 GB and ending with the fact that the iron resources are much more than in Basic / Standard, therefore, if you plan to deploy a solution in Azure SQL DB that is very resource intensive and, above all, to peaks of use, using the Premium mode is much more preferable.
Separate consideration requires Premium mode. , , , . , , Azure SQL Database Benchmark
( , OLTP. Azure SQL DB Benchmark Database Throughput Units (DTU). DTU CPU, R/W. , DTU , . Azure SQL DB Benchmark Transaction Rate, , (basic — , standard — , premium ), , .
Premium. , Basic, 1 DTU, Premium P3 800 DTU, , , 800 , .
Azure SQL DB — msdn.microsoft.com/en-US/library/azure/dn741340.aspx
Summary
SQL Azure Databases – SQL Server PaaS. , , SQL Server ( , ), SQLAD , , , . , (IaaS PaaS) , , , , .
In this article, we will try to understand not only what technological differences exist between IaaS and PaaS in Microsoft Azure, but also about where such a difference can arise, where problems can arise in PaaS, which can be not met in IaaS, and architecture these solutions, and what Azure SQL DB Premium is and when to use it.
Table of contents:
Choose between IaaS and Azure SQL DB
IaaS Way: general description and architecture
PaaS Way: general description, architecture and possible problems
PaaS Way: Security
Azure SQL DB SLA - what's the difference from a SQL Server SLA in a virtual machine?
PaaS Way: High Availabiliy
How to choose Azure SQL DB usage mode? Premium level - guaranteed high performance of SQL Server as a service
Choose between IaaS and Azure SQL DB ( PaaS )
')
In any data management solution using the SQL Server platform, there are three options for hosting this platform:
Locally, in its own infrastructure
On the Azure virtual machine (IaaS)
As a service (Azure SQL DB)
Placement of a local subject is not considered, since it is circumvented up and down. Considering the other two options, we will start right away from the decision tree, after which we will begin to delve into the details.
The first step depends on whether a completely new solution is being developed or an existing one is being migrated. As is often said, it is easier to rewrite it rather than deal with the migration of an existing one, and here it can be the first architectural solution. If we have a new project, and it does not yet have a backend on data management, then we can immediately go in the direction of PaaS - this approach will allow us to save a lot of time for managing and deploying infrastructure.
In the case of migration, there is a fork in front of us - since Azure SQL DB in terms of functionality is very different from a full-fledged SQL Server, we need to analyze our code base and database and see if the changes are necessary and how critical they are. This is a very important fork - if we miss a data type that is not supported by Azure SQL DB, then we will lose a lot of time and effort. With databases, sqlazuremw.codeplex.com can help us , which can analyze the database and say that it is not supported from it in Azure SQL DB. And, if at this step there is a need to make major 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 is broken and we need to use a local SQL Server or install it on a virtual machine (IaaS).
If we are developing a new solution or have the opportunity to make non-critical changes to the project being migrated, then the next question we face is the limit on the size 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 do not break through this ceiling, then we can safely use Azure SQL DB. If we understand that someday there may be a situation in which we will have 501 GB, proceed to the next step.
Is it possible to redesign the architecture or implement sharding on its own? There are situations and projects when this can not 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 falls on the client’s shoulders. If we are ready to take on this, then nothing prevents us from using Azure SQL DB. If not, we are again logging on to SQL Server on a virtual machine or locally.
Let's look at the differences and architecture of both solutions.
IaaS way
The method of placing a SQL Server in a virtual machine is not fundamentally different from installing it locally, but the devil is in the small things and the small things related to a complex discipline - optimization. Thesis:
On a virtual machine, we can put a full-fledged SQL Server . The word "full" is important here, since there are functional differences between SQL Server and Azure SQL DB, despite common parents;
The virtual machine image lies on Microsoft Azure Storage , which imposes optimization constraints - in order to improve disk performance, you need, for example, to merge them into a raid. A virtual machine in Microsoft Azure does not have disks by default, which can be combined into a raid - when it is created, it receives two disks, and one disk (D :) is temporary, as it is located on the server disk on which the machine is currently running, and the second (C :) is placed as a blob on Microsoft Azure Storage, and all I / O requests go through the caching layer. For this reason, C: performance will always be less than D :, but D: will be cleared during every system operation on 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 many more) to the machine.
In Microsoft Azure, ready-made optimized images of SQL Server (2008 R2 - 2014) are already available, which can be used with both a separately paid license and having brought your license (under License Mobility & Software Assurance).
Standardized iron. In any cloud, you get standardized equipment, and you need to understand that it most often does not have a highly tuned performance.
PaaS way
The paaS approach is strikingly different from IaaS by the presence of a larger number of developer abstractions from infrastructure, constraints, and architectural delights. At the very bottom, at the level of infrastructure and equipment, this is, of course, the same standardized equipment, so if you use the Shared-mode of Azure SQL DB, then in the presence of disturbed neighbors, performance can subside in a completely unexpected way. There is also a set of Soft / Hard restrictions, which is done with an understandable purpose - if we administer the SQL Server on the virtual machine, and we deal with performance and architecture problems, in general, again, then PaaS is fully managed by the vendor, and you need to do so so that the solution is highly scalable and fault tolerant, and restless users with a high seasonal load do not put anything infrastructural.
At the same time, as mentioned above, sharding and other solutions must be made by the developer - there are no embedded 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 you need to understand is that when we create an Azure SQL DB server, in fact, there are three servers that, in general, are not servers at all - these are replicas, the request from the client to which passes through the TDS entry point. If we compare it with a traditional solution, then in the case of its IP server we lead 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 provided by our Azure SQL DB — deploying resources, billing consumed resources, query routers, and more.
Next, the query is routed to the platform layer on which the infrastructure services are running — something that provides the immediate functionality of SQL Server as a service. This is the last layer of abstraction above the infrastructure, below - only the equipment.
As you can see from the picture, the architecture of the Azure SQL DB solution is much more complicated than hosting a SQL Server in a virtual machine. Load balancing and replication also work on each layer, and it all works automatically without human intervention.
With this architecture, the approach to solving problems arises in some way from what we are used to. There are fewer tools for diagnostics; therefore, it is important to understand why a particular problem occurs. If we talk about the most common problems, the causes of their occurrence can be placed on the layers of Azure SQL DB. The first level of problems arises already at the level of TDS communications. Here we have a greater latency than it is in local deployment, and various kinds of fluctuations - mostly occurring even before entering the Azure SQL DB infrastructure, that is, on the network backbones that are not related to Microsoft. This point is important, since the request can return with a timeout, and this needs to be covered by the development and planning of the retry logic.
When the request has already arrived at the infrastructure, it is checked by internal means, and the login passes through one gateway along with other requests for this equipment. A connection opens further, however, a timeout may also occur here if the operation is not performed on time - and this should also be decided by the retry logic.
While Azure SQL DB is running, there is a constant likelihood of problems at the platform layer level - just here all the limitations imposed by the vendor on Azure SQL DB are valid, and, if they are exceeded, the corresponding error will be returned to the client. We cannot do anything here, the only option is to handle these errors on the client side (the list of errors is msdn.microsoft.com/en-us/library/ff394106.aspx ).
Summarizing the use of Azure SQL DB, we need to 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 with us locally, we must understand that we will have constant fluctuations of the network backbones, and implement the appropriate handling of these fluctuations and situations with the occurrence of timeout errors. A good option seems to be placing the client in the cloud as close to Azure SQL DB as possible.
In Azure SQL DB there are restrictions that are not just introduced. If we want to work comfortably, we should not take as much as they give, but take as much as we need, and take into account that the connection pool is not rubber.
Features multi-tenancy. Nowhere will they give us performance guarantees, where resources are shared between customers. Here, in the case of Azure SQL DB, a Premium mode occurs in which we are given reserved resources that we can freely use and be sure that no one will take them away if they are left unused.
We looked at how the architecture of our PaaS solution differs from the IaaS solution. As already mentioned, the PaaS architecture is more technologically sophisticated, since the client should be provided with fault tolerance, rapid deployment and much more in automatic mode, and at the same time it should be provided for a large number of clients. However, with the increasing complexity of the architecture, there are also more complex troubleshooting problems, limitations and differences with standard SQL Server.
Let's now look at another important aspect - the security of Azure SQL DB. About 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 set of differences from the local SQL Server, and the most significant ones concern security issues. Given the implementation features of any software product that is provided as a service (that is, in fact, 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 only encrypted communications are allowed. In SQL Server 2008, a new tool was introduced: transparent data encryption (TDE), which allows you to fully encrypt data with minimal effort. However, at the moment, SQL Azure Databases does not support encryption at the database level. It should be noted that currently Azure SQL DB is available only through TCP connections and only through port 1433. Therefore, you need to consider the ADO.NET encryption capabilities and certificates. And, for example, the properties of the connection Encrypt = True and TrustServerCertificate = False will protect the transmitted data and help prevent man-in-the-middle attacks. The second means of protecting Azure SQL DB, and, in general, the main one is the Azure SQL DB firewall, which initially blocks all access to the server. Attempts to connect to the appropriate settings will fail. To get started with the Azure SQL DB server, you need to log in to the Azure SQL DB portal and define the firewall settings for accessing 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, managing user accounts 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 the roles created in each database and associated with user logins.
Microsoft SQL Server has standard server-level roles such as serveradmin, securityadmin and dbcreator. There are no such roles in SQL Azure Databases. Instead, SQL Azure Databases has the role 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 is via the Tabular Data Stream (TDS) application-level protocol, which is protected by SSL, via TCP / 1433. Using SSL is optional for Microsoft SQL Server and optional for Azure SQL DB.
In SQL Server, IP-based access control must be performed 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 until the clients of the computers that have access rights are defined. The firewall gives 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 functionality of Transparent Data Encryption (TDE). TDE is not supported in Azure SQL DB.
Azure SQL DB has built-in DDOS protection. There is none in SQL Server.
Azure SQL DB is automatically patched and updated without downtime. SQL Server needs to be updated, and this can lead to downtime.
So, there are differences in security, and they should be foreseen. Sometimes a blocker is the absence of TDE, sometimes it is not as such - it all depends on the decision. And the next block I propose to look at the differences in SLA.
Azure SQL DB SLA - what's the difference from a SQL Server SLA in a virtual machine?
SLA is an indispensable part of any commercial deployment. Azure SQL DB SLA is more granular than a SQL Server SLA on a virtual machine, from the standpoint 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, which are spent on servicing the service. About customer service warn in advance.
PaaS Way: High Availability
Azure SQL DB is initially laid out under High Availability, and all components of the architecture are fault tolerant. It is important to understand that the standard for SQL Servr concept of HA to Azure SQL DB is not applicable (again due to architectural differences). Inside Azure SQL DB, there are mechanisms that replicate all the records in three replicas across different servers, which ensures 99.9% SLA. In this case, 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 with, for example, Data Sync.
How to choose Azure SQL DB mode?
Starting from April 2014, Azure SQL DB modes of operation are changing - the old Web / Business modes are canceled, the main focus of which was concentrated around the database size, while the new Basic / Standard / Premium modes are focused entirely on performance guarantees. The figure shows a simple decision tree on which of the new modes in which conditions to use. It is necessary, however, to understand that, despite the provision of a certain level of performance with Basic / Standard modes, the Premium mode differs in elevated values of all indicators - starting from the fact that the base in the Premium mode can be up to 500 GB and ending with the fact that the iron resources are much more than in Basic / Standard, therefore, if you plan to deploy a solution in Azure SQL DB that is very resource intensive and, above all, to peaks of use, using the Premium mode is much more preferable.
Separate consideration requires Premium mode. , , , . , , Azure SQL Database Benchmark
( , OLTP. Azure SQL DB Benchmark Database Throughput Units (DTU). DTU CPU, R/W. , DTU , . Azure SQL DB Benchmark Transaction Rate, , (basic — , standard — , premium ), , .
Premium. , Basic, 1 DTU, Premium P3 800 DTU, , , 800 , .
Azure SQL DB — msdn.microsoft.com/en-US/library/azure/dn741340.aspx
Summary
SQL Azure Databases – SQL Server PaaS. , , SQL Server ( , ), SQLAD , , , . , (IaaS PaaS) , , , , .
Source: https://habr.com/ru/post/221219/
All Articles