How Azure SQL Database makes database management easier?

How Azure SQL Database makes database management easier?

April 26th, 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.

Source: Microsoft Blog – SQL Server Storage Engine – How Azure SQL Database makes database management easier?

Azure SQL Database – scalability

April 26th, 2018

Azure SQL Database is a flexible Platform as a service database that can be easily scaled to fit your needs. You can add more compute or storage to satisfy your performance requirements without waiting for new hardware or migrating data to more powerful machines. Azure enables you to change performance characteristics of your database on the fly and assign more resources when needed or release the resources when they are not needed in order to decrease the cost.

Azure SQL Database supports two types of scaling:

  • Vertical scaling where you can scale up or down the database by adding more compute power.
  • Horizontal scaling where you can add more databases and to shard your data into multiple database nodes.

Horizontal and Vertical scaling

You would need to scale up your database if you see performance issues that cannot be solved using classic database optimization techniques such as query changes, indexing, etc., and you need to quickly react to fix performance issues. Vertical scaling is useful if you notice some spikes in your workloads where the current performance level cannot satisfy all requests, so you can easily handle peak workload by adding more resources, and go back to the original state when the resources are not needed anymore.

If you cannot get enough resources even on the highest performance level, you might consider horizontal scaling. In horizontal scaling you can split your data in several databases (shards) and every shard would can be scaled up or down independently.

Scaling up/down

Scaling up refers to a process of adding more resources to the database in order to achieve better performance. You can scale-up your database if you see that your workload is hitting some performance limit (e.g. CPU or IO).

Azure SQL Database enables you to choose how many CPU you want to use, how much storage you need, and to dynamically change these parameters any time. Then you can simply add more CPU power or storage if the current resources cannot handle your workload.

As an example, you can create an Azure SQL Database with 16 cores and 500GB storage, and then increase or decrease these numbers depending on your needs.

Changing CPU or storage in instance can be done via Azure portal using simple slider:

Any change that you made will be almost instant. There is no additional provisioning, attaching disks, etc. Database will be reconfigured using the values that you set in the slider, and Azure SQL Database will immediately get more resources.

There is another model called DTU-based model where you put your database in pre-defined performance class (for example, 100DTU, 200DTU, etc.) Every DTU class is a resource bounding box where you are getting maximum number of CPU, Memory, IO Read and Write operations for that class:

The ratio of CPU/memory/read/writes is calibrated based on Azure SQL Database benchmarks and optimized for generic workloads. Azure SQL Database guarantees that you will always have the amount of resources defined in the DTU class. As long as none of the dimensions in the DTU bounding box reach the limit, your workload can be executed on the selected DTU class without need to scale-up the database. If you are hitting some limit, you would need to go to the higher class.

Another way for scaling up/down is changing service tier from Standard/General Purpose to Premium/Business Critical. In Standard/General Purpose you are getting classic infrastructure with data stored on Azure premium disks, while in Premium/Business Critical you are getting data on local SSD. Switching between these tiers is usually long-term decision that is made if you determine that standard architecture cannot satisfy your latency requirements.

Read scale-out

If your workload is hitting the limit of available resources, and you cannot scale-up database to fix the issues, another option would be to redirect part of your workload to another database node.

Databases in the Premium (DTU-based model) or in the Business Critical (vCore-based model) have several replicas to support the high-availability. Usually these replicas just collect data from primary node, apply changes, and activate if primary node fails.

The Read Scale-Out feature allows you to use the capacity of the read-only replicas for read-only queries. This way the read-only workload will be isolated from the main read-write workload and will not affect its performance. The feature is intended for the applications that include logically separated read-only workloads, such as analytics, and therefore could gain performance benefits using this additional capacity at no extra cost.

The main benefits of Read Scale-Out are:

  1. Primary database node will not spend resources on the read-only/analytic queries because they are not sent to the primary node anymore. Saved resources might be used to improve the performance of writable workload.
  2. You can use resources on secondary nodes to handle heavy reports and analytical queries.

Redirecting read-only/analytical queries to secondary replicas is easy. Once you enable read scale-out you can add ApplicationIntent=ReadOnly; property into the connection string for your analytic queries and these queries will be sent to the secondary replica.

Note that data stored in secondary replica might not be identical to the data in primary database. For the performance reasons, process of moving changes from primary to secondary nodes is asynchronous so you might net see the latest data in secondary node. Use secondary read-only replicas if you are planning to run some analytic reports that don’t need precise/exact latest data (e.g. monthly/weekly reports).

Global Scale-out/Sharding

Sharding is approach where you split your data into multiple database nodes. This way you can scale-out your data into several database shards. Every database shard is an independent database where you can add or remove resources as needed. There is also a Elastic Database split-merge tool that enables you to move data between shards and reorganize your data distribution.

Sharding might be useful architecture choice if you have geo-distributed application where every application should access part of data in the region. Every application may access only the shard associated to that region without affecting other shards.

Another scenario where global sharding might be useful is load balancing. You can have a large number of geo-distributed clients that insert data in their own dedicated shards.

You can also use sharding if you cannot get good performance even in the highest performance tiers, or if your data cannot fit into a single 4TB database.

Azure SQL Database enables you to create, manage, and use sharded data using the following libraries:

  1. Elastic Database client library: The client library is a feature that allows you to create and maintain sharded databases.
  2. Elastic Database split-merge tool that moves data between sharded databases. This is useful for moving data from a multi-tenant database to a single-tenant database (or vice-versa).
  3. Elastic Database query(preview): Enables you to run a Transact-SQL query in SQL database that spans multiple databases.
  4. Linked servers that are available in Azure SQL Managed Instance where you can create a link to remote shard and execute remote queries.
  5. Elastic client transactions that allow you to run transactions that span several databases in Azure SQL Database. Elastic database transactions are available for .NET applications using ADO .NET.
  6. Elastic Database jobs(preview): Use jobs to manage large numbers of Azure SQL databases. Easily perform administrative operations such as schema changes, credentials management, reference data updates, performance data collection, or tenant (customer) telemetry collection using jobs.

Using these tools, you can create as many as needed shards and run the queries on one or multiple shards.

Conclusion

Azure SQL database is scalable database platform as a service that enables you to easily increase or remove resources in your database, offload queries to secondary nodes or implement full sharding solution. You have a variety of options to design scalable architecture where the database will adapt your application workload.

Source: Microsoft Blog – SQL Server Storage Engine – Azure SQL Database – scalability

We provide excellence in Database Administration

Let's work together