How Azure SQL Database makes database management easier?

[JovanPop]

First published on MSDN on Apr 26, 2018
Azure SQL database is a platform as a service SQL Server Database Engine hosted in Azure cloud and managed by Azure. Azure automates many management activities and enables you to focus on development and optimizations. In addition, it enables you to easily configure some advanced management configurations.



There are some critical database management activities that you need to do in order to ensure that your database is running without any issues:

1. Backups


2. High-availability configuration


3. Performance optimization


4. Security


5. Monitoring

Azure SQL database has many of these activities built-in and automated. You can leverage existing mechanism and more easily managed your database.

Automated backups

Having the backups for your database is crucial for your management operations. Backups ensure that you can easily recover if any error happens, some data is accidentally deleted, or if some wrong data is entered in your database.

Azure SQL Database manages backups for you and automatically creates backups of your databases. Full database backups happen weekly, differential database backups generally happen every few hours, and transaction log backups generally happen every 5 - 10 minutes. Backups are kept 7 days in Basic tier, and 35 days in Standard/Premium tiers. New General Purpose/Business Critical tiers have configurable backup retention with 35 days maximum backup retention period, and default value 7 days. Backups are retained even if you delete a database, which allows you to restore accidentally deleted databases. You can also keep database backups for up to 10 years .

Backups allow you to restore a database to a specific point-in-time to the same server that hosts the database. When you restore a database, the service figures out which full, differential, and transaction log backups need to be restored. You can restore a database on a server in different Azure region from the most recent geo-replicated full and differential backups. Geo-restore uses a geo-redundant backup as its source and can be used to recover a database even if the database or datacenter is inaccessible due to an outage.

Backups are encrypted if Transparent Data Encryption is enabled in your database (which is the default setting for new database).

High-availability

Azure SQL database has built-in High availability mechanism that guarantees 99.99% of availability of your databases and you don’t need to configure anything. You have some additional options that you can configure in order to establish additional availability:

1. Active geo-replication : SQL Database allows you to configure up to four readable secondary databases in either the same or globally distributed Azure data centers. For example, if you have a SaaS application with a catalog database that has a high volume of concurrent read-only transactions, use active geo-replication to enable global read scale and remove bottlenecks on the primary that are due to read workloads. Active geo-replication allows for the failover of a single database.


2. Failover groups : SQL Database allows you to enable high availability and load balancing at global scale, including transparent geo-replication and failover of large sets of databases and elastic pools. Failover groups and active geo-replication enables creation of globally distributed SaaS applications with minimal administration overhead leaving all the complex monitoring, routing, and failover orchestration to SQL Database. Failover groups allows for the failover of multiple databases within a single group.


3. Zone-redundant databases : SQL Database allows you to provision Premium or Business Critical (preview) databases or elastic pools across multiple availability zones. Because these databases and elastic pools have multiple redundant replicas for high availability, placing these replicas into multiple availability zones provides higher resilience, including the ability to recover automatically from the datacenter scale failures without data loss. This feature is currently in preview.

Performance tuning

Azure SQL Database has built-in intelligence that constantly monitors the activities in your database and takes corrective actions if the issue might be mitigated. There are two automatic tuning features that can improve your database performance:

• Automatic indexing that analyzes your T-SQL queries identifies the indexes that could be added into the database, apply indexes, and measures workload performance in order to prove that the new index improved performance. If the index didn’t improved performance, it would be dropped.


• Automatic plan correction monitors performance of you query plans and automatically applies some previous good plan if the current plan degrades performances.

Azure SQL Database might automatically apply Automatic tuning corrective actions, or you can keep them in the "advisor mode" where Azure SQL database would just report recommendations and let you review them and apply the ones that you find most useful.

In addition there are many internal built-in Database Engine optimization techniques that enable Database engine to learn from your workload and dynamically adapt query plans.

Monitoring

Azure SQL Database exposes information about the activities that are happening in your database. In addition, it processes information emitted from database and tries to identify problems and to give you more details about the potential solutions. You have the following features that can help you to monitor your database:

• SQL Database Auditing tracks database events and writes them to an audit log in your Azure storage account. Auditing can help you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.


• Query Store is a set of views in Database Engine that enables you to track performance of your database queries and identify performance issues.


• Intelligent insights analyze events related to performance of your queries and give you the root causes of the identified problems.


• Threat detection constantly monitors queries and notifies you if some potential threat is identified (for example SQL Injection attempt)


• Vulnerability assessment scans your database objects and reports security weaknesses in your database.

You can easily access this information using Azure portal or built-in views using T-SQL.

Security

Azure SQL Database has built-in best practices and security configurations that enable you to easily ensure that your data is secure. The following features are available in Azure SQL database:

1. All connections to Azure SQL Database require encryption (SSL/TLS) at all times while data is "in transit" to and from the database.


2. You can control who can access your database using Built-in firewall rules and restrict the access to some IP addresses. You can also put server in your VNET (Azure SQL Database Managed Instance only) or configure Virtual Service endpoints (Azure SQL Database only)


3. With Azure Active Directory (AAD) authentication you can enable your corporate accounts to access database. You can also configure multi-factor authentication (MFA) as an additional security measure.


4. Transparent data encryption transparently encrypts data stored in data/log/backup files, ensuring that nobody can take and read the data stored on disks.


5. Always Encrypted enables you to keep sensitive columns encrypted and to decrypt their values on the client side. Nobody can read sensitive information in database, unless if it has the matching certificate on the client side.

These security policies might help you to keep your data secured and more easily certify your solution from the security perspective.

Conclusion

Azure SQL Database is pre-configured SQL Server Database Engine hosted in Azure cloud that has built-in different management capabilities. In most of the cases you can use default management setting and let Azure manage your database. However, if you need to do some additional management actions or configurations, Azure SQL Database might make your tasks easier.