This article is the second in a series that provides a high level overview of some of the major differences you can expect between the AWS RDS SQL Server service and the Azure SQL Database service. Both offerings are considered Platform as a Service (PaaS) implementations, which by nature offer certain limitations that we will cover. This article does not review the Infrastructure as a Service (IaaS) implementation of SQL Server. You can
view the first article here, in which I covered differences in architecture, availability, connectivity, licensing and major SQL features. This article focuses on backup and security differences specifically.
Backup
As expected the ability to backup user databases is a critical component of any database service.
The first thing to note is that Azure SQL Database managed instances offer support for the BACKUP operation, but only using the full database COPY_ONLY backup option. This means that differential backups and log backups are not supported. The BACKUP operation is not supported for Single databases in Azure.
In addition to providing limited access to the BACKUP command, the Azure platform provides automated backups (with point-in-time restores), with weekly full backups, differential backups every 12 hours, and transaction log backups every 5-10 minutes. Backup files are kept between 7 and 35 days depending of the service level selected. Additional options are available for storing backups for up to 10 years (for Single Databases and Elastic Pools).
On AWS RDS SQL Server, Amazon will also backup your database daily (automatic snapshot), with a 5 minute log backup increment; however this automated backup mechanism works on the entire instance (not individual databases). The Backup retention for AWS RDS is also 35 days. AWS RDS also provides support for manual DB snapshots. Like automated backups, a DB snapshot creates a backup of an entire SQL Server instance, not just a database. Manual DB snapshots do not have a retention policy; however you are limited to 100 manual snapshots by default. Finally AWS RDS also provides support for the BACKUP command for full backups of individual databases. This native BACKUP option allows you to backup your RDS SQL Server databases to S3 buckets that you can then restore to a local SQL Server. The BACKUP operation will not work at the same time a snapshot is taking place.
Depending on the snapshot (automated or copied), it can be shared, copied, or both. Automated backups create snapshots that are not directly accessible; however they can be copied. The process of copying an automated backup creates the equivalent of a manual snapshot. Manual snapshots can both be copied and shared with other AWS accounts.
(*) Backup files from a Managed Instance can only be restored to an Azure Managed Instance
(**) Azure doesn’t offer a built-in method to recover an entire server; instead see this
TechNet article.
| |
Azure SQL Database
|
AWS RDS SQL Server
|
|
Single DB /
Elastic Pools
|
Managed
|
|
BACKUP Command
|
No
|
Yes
|
Yes
|
|
RESTORE Command
|
No
|
Yes (*)
|
Yes
|
|
AUTOMATED BACKUPS
|
|
Automated Backups
|
Yes
|
Yes
|
Yes
|
|
Retention Period
|
7-35 days
|
7-35 days
|
Up to 35 days
|
|
Recovery Point Objective
|
5 minutes
|
5 minutes
|
5 minutes
|
|
Long-Term Retention
|
Up to 10yrs
|
No
|
No
|
|
RECOVERY OPTIONS
|
|
Database Recovery
|
Yes
|
Yes
|
No
|
|
Instance Recovery
|
No
|
No (**)
|
Yes (Snapshots)
|
Security
Database service security is a vast topic, including server-level security, database-level security & isolation, service-level security, data masking and encryption. In this article we will look at a specific set of security features: firewall, auditing, encryption, data masking and authentication options.
Firewall
Both Amazon RDS SQL Server and Azure SQL Database offer a connectivity firewall. The Azure Firewall provides TCP/IP filtering at the service and the database level, for public connections and from Virtual Network connections (from an Azure Data Center) for Single Databases. The Managed Instance offering does not offer a firewall since this database service cannot be exposed to the Internet; however you can place the Managed Instance in a VNet with a public service endpoint, which gives you complete control over connectivity rules through the use of Network Security Groups and allows you to establish connection over VPN or point-to-site connections. The Azure SQL Database firewall implements a dual layer firewall: database and server level. Database-level firewall rules are inspected first; if there are no rules defined allowing connection to the requested database, server-level rules are then inspected. Implementing database-level rules makes the database more portable; when you backup and restore the database in another data center, the rules are automatically applied. As a result, it is recommended to implement database-level rules whenever possible.
In AWS, RDS access is controlled through the use of security groups. A security group must first be created (limited to 20 rules per security group), and a database is then assigned to a security group to grant access. Both Amazon RDS and Azure SQL Database use a secured by default model, requiring administrative action before a database can be accessed. This is a different implementation than the Azure platform; in Amazon administrators need to build security groups first which can be reused across databases, while Azure requires each database (or server) to have a specific set of rules unique to each.
Encryption
Encryption in transit is required for the Azure SQL Database service, while it is an optional configuration for AWS RDS. Although SSL encryption in optional for RDS SQL Server, you have the ability to force connections to use SSL.
Regarding encryption at rest, Amazon supports the option to enable Transparent Data Encryption (TDE) for any RDS SQL Server Enterprise Edition versions (since only this edition allows the use of the TDE feature); however it is optional and must be configured by an administrator. By contrast, the Azure SQL Database always encrypts data at rest. Both cloud vendors provide the ability to use a service-generated encryption certificate or allow customers to provide their own through the KMS (Amazon) or the Azure Key Vault (Azure) service.
We could argue that by not having to enforce encryption both at the transport and at the disk level, the AWS RDS SQL Server platform offers more flexibility since it allows customers to choose whether to use encryption or not; after all some environments do not have sensitive data and not using encryption certainly speeds things up. On the other hand, we could also argue the opposite, by considering that cloud services must demonstrate trust by design, in which case encryption is no longer considered a feature, but a basic foundation for safe computing. For organizations that require strong encryption, know that the AWS RDS SQL Server can provide an equal amount of data encryption as long as you choose the Enterprise Edition of SQL Server, configure SSL at the transport level, and build additional automation that your DevOps team will be responsible for to deploy and manage certificates for TDE which is not required for Azure since TDE is enabled by default using certificates created by the SQL Database service.
Authentication
From an authentication standpoint, both services allow database-logins. The ‘sa’ account is not available in Azure SQL Database; it is available in AWS RDS SQL Server but it doesn’t have sysadmin rights. The Azure database service creates a Server Administrator account which allows you to login to the master database. In addition the Azure database service allows you to specify an existing Active Directory administrative account (or security group) as the database administrator. Just like the AWS RDS service, the administrative accounts in Azure SQL Database do not have sysadmin rights.
In addition, both allow Active Directory integration with certain limitations. The Azure SQL Database service only works with the Azure Directory Service, while the AWS RDS SQL Server service requires a service called Microsoft Active Directory (MAD). The difference between the two implementations is that the Azure Directory Service is a customer managed directory service while MAD is an AWS-managed Domain Controller.
Auditing
From an auditing standpoint, both services offer the default out-of-the-box SQL Server auditing capabilities. The Azure SQL Database implementation offers a service-level audit log and a database log that can be stored in a storage account, Log Analytics or forwarded to an Event Hub. There are minor differences between the Single DB and the Managed Instance service; the Managed Instance offering tracks audits at the server level (all databases), while the Single Database offering audits at the database level. The AWS RDS SQL Server service also offers multiple options for auditing databases including the CloudTrail Logs (which monitors service level activity) and the built-in database logs that can be downloaded. Both services allow you to use the built-in SQL Server function to access the SQL Server audit logs: sys.fn_get_audit_file.
Advanced Threat Protection
Advanced Threat Protection is a security layer that provides the ability to detect and respond to potential threats as they occur, such as SQL injection, access from unusual locations, brute force SQL credentials and more. Although the AWS RDS service offers advanced infrastructure security, it does not offer an advanced threat protection capability at this time.
Data Masking
Data Masking is a feature that is only available on the Azure SQL Database service and helps prevent unauthorized data access. It allows the dynamic masking of data returned by the database depending on the user making the request (administrators are always able to see the data unmasked). Some of the capabilities of the data masking feature includes credit card masking, email masking, random number generation and custom text.
Here is a summary of the security features of both platforms.
| |
Azure SQL Database
|
AWS RDS SQL Server
|
|
Single DB /
Elastic Pools
|
Managed
|
|
CONNECTIVITY
|
|
Firewall Rules
|
Yes
|
No
|
Yes
|
|
Firewall Rule Portability
|
Yes
|
No
|
No
|
|
Public Internet Access
|
Possible
|
No
|
Possible
|
|
VPN Connectivity
|
Yes
|
Yes
|
Yes
|
|
ENCRYPTION
|
|
SSL Encryption
|
Yes (required)
|
Yes (required)
|
Yes (optional)
|
|
TDE
|
Yes (required)
|
Yes (required)
|
Yes (optional)
|
|
User-Defined Certificates
|
Yes (Key Vault)
|
Yes (Key Vault)
|
Yes (KMS)
|
|
AUTHENTICATION
|
|
Database logins
|
Yes
|
Yes
|
Yes
|
|
AD Login
|
Yes
|
Yes
|
Yes
|
|
OTHER
|
|
Data Masking
|
Yes
|
Yes
|
No
|
|
Auditing
|
Yes
|
Yes
|
Yes
|
|
Advanced Threat Protection
|
Yes
|
Yes
|
No
|
Conclusion
From a data protection standpoint, both services offer a solid backup/restore capability where Azure provides a long-term storage option, and AWS RDS offers an instance-level backup/restore option. On the security front, both services seem to offer similar basic capabilities. However the Azure SQL Database service offers advanced options that are not part of the database engine itself, such as Data Masking and Advanced Threat Protection. In addition the Azure service forces encryption in transit and at rest, while the AWS service makes those an option that must be configured separately.