December 02, 2022 Blog

A comprehensive tour of Azure SQL and it’s main components

closeup-of-computer-display-of-software-developer-2022-01-18-15-36-26-utc

Azure SQL databases are always up-to-date and here’s a Beginner’s Guide taking you through the most components, offerings and also the essentials you would like to understand.

Azure SQL could be a fully managed database within the Cloud, which concludes to – no have to be sure of the OS & no must accommodate any hardware, both of which are managed by Microsoft Azure. you’ve got a server name, database name which allows you to attach and that’s all you wish.

In case you’re searching for a full instance of SQL server then you’ll go along with IaaS (infrastructure as a service). Azure SQL database could be a managed service which allows your focus to be database only.

What exactly does one pay for?

As you initiate the service, the primary thing you buy is that the size of your database. The service has three tiers: Basic, standard and Premium, which are further divided into S0-S1-S2-S3 and P1-P2-P3 for normal and premium respectively.

Each tier mentioned above provides a unique set of recovery, DR and performance characteristics, which is strictly what you’re paying for.

The fundamental characteristics of every tier:

Basic Standard Premium
Size limit 2 GB 250 GB 500 GB
7 Days 14 Days 35 Days
DR Geo-restore to another Azure region Geo-restore, Standard Geo-replication Geo-restore, standard or active Geo-replication
Auditing Allowed Allowed Allowed
Performance 5 DTU 10-20-50-100 DTU 100-200-800 DTU
Suggested for Targeted for small size databases This tier is targeted for little to medium size OLTP databases with up to some hundred users This tier is targeted for giant databases and might sustain thousands of transactions per second.

Also, with the most recent release (V12, in preview right now) it’s also the tier targeted to form Datamarts within the cloud. the explanation for that’s that on this latest release it’ll support Partitioning, Clustered Columnstore Indexes further as execution plans with Parallelism.

You can also visit the Azure pricing page for up thus far pricing information. http://azure.microsoft.com/en-us/pricing/details/sql-database/

Let’s take a steep into the DR strategies offered and what do they mean:

Geo-Restore

Ge-restore is offered altogether tiers and might be defined because the capability of Restoring your database in the other Azure region. But, it’s important to stay in mind while planning a DR strategy, is that a Geo-restore copy can have upto 24 hrs of information loss.

Standard Geo-Replication

Standard Geo-replication is obtainable for traditional and Premium only. It keeps an offline warm standby copy of your database on another Azure region. This copy has an RTO (Recovery Time Objective) of two hours or less and an RPO (Recovery Point Objective) of half-hour or less. Because it’s an offline copy, the database is charged at 50% of the conventional rate. Also, you can’t failover and failback at will, within the event of an event Microsoft will enable the failover capability. At any point in time you’ll be able to break the replication and use the secondary copy if you would like but you’ll need to re-initialize it afterward.

Active Geo-Replication

This is the foremost advanced DR strategy for Azure SQL databases and is offered just for Premium. Active Geo-Replication will keep a web copy that’s also read-only in another Azure region. this suggests that you simply can actually use this copy of region-based scale out. So you’ll be able to see, Active Geo-Replicatio isn’t only a DR tool but also a performance scalability tool! This copy has an RTO (Recovery Time Objective) of 1 hour or less and an RPO (Recovery Point Objective) of 5 minutes or less (a big difference to plain Geo-Replication).

Because the secondary is additionally read-only, it’s charged at the identical rate as the other database and as of this moment you’ll create up to 4 of those secondaries. Unlike Standard Geo-Replication, the Active one allows failover and failback initiated by the client.

Now that we’re finished the DR differences, let’s move to the DTU concept that we mentioned before.

DTU stands for a Database Throughput Unit. It’s an abstraction of the CPU, Memory and IO power of the database and is employed as a unit of comparison of performance between the various service tiers. Basically, the Microsoft engineers want to abstract the hardware faraway from the database as a service and thus came up with this measurement to point out one number that represents your database performance.

In conclusion, we hope this guide can offer you an overview of the capabilities of the Azure SQL Database and provides an outline of what capabilities to expect.

Once you’re able to try all this out, get in touch with our experts for guidance.