Image Source : Microsoft
An Azure SQL Database logical server acts as a central administrative point for multiple databases. It resides within an azure subscription but can be moved to different subscription with all contained resources. Server level principle logins to manage all the databases. Connections are possible via SQL Authentication and Azure Active Directory Authentication. SQL Database firewall will help to restrict the possible connections with the specified address. Collation Changes are not recommended after the database creation. An Azure SQL Database with its own set of resources (DTUs) or Elastic Pool (eDTUs).
Key Advantages are
- Dynamically Scalable with no downtime
- Built in business continuity
- Data protection (TDE) and Near Zero Administration
- Built in performance monitoring and alerting
- Point in time restore and Long term retention available up to 10 years
Different Types of Service Tiers are
- Basic (2GB, 7 Days Backup retention policy: Elastic Pool – 400 databases per pool with maximum storage of 117GB / Pool)
- Standard (250GB, 35 Days Backup retention policy: Elastic Pool – 400 databases per pool with maximum storage of 1200GB / Pool)
- Premium (up to 1TB, 35 Days Backup retention policy: Elastic Pool – 50 databases per pool with maximum storage of 750GB / Pool)
What to Know, before switching between the service tiers
Upgrading a Geo Replication database, make sure the secondary is first upgraded to the target performance tier. Now you can take the advantage of your point in time restore and retention policy because of longer period.
Downgrading a Geo replicated database, break replication status then downgrade to desired performance level and reenable the replication. Be aware your point in time restore and retention policy might be smaller.
Migration of Database to Azure SQL DB
Possibility with Downtime, you can use DMA to assess the database, apply the correction scripts, export / import the bacpac to Azure SQL Database.
Without Downtime, use Transactional replication (Setup Distribution, Create Publication, Create Subscription)
You can use Active Geo-Replication feature to create a copy of Azure SQL database. Recommend using the database firewall for Geo-replicated databases so that we have the same rules applied on both the primary and secondary databases.
SQL Database Backups and Restore
SQL Database automatically creates a database backups and uses Azure read-access geo-redundant storage (RA-GRS) to provide geo-redundancy
SQL Database provides up to 200% of your maximum provisioned database storage as backup storage at no additional cost
Different types Full, differential and transaction log backups
LTBR – Long Term Backup Retention feature enables you to store your azure SQL database in an azure recovery services vault for up to 10 years. 1000 databases per vault. Recommended to have separate vault for each database server.
Enabling long-term backup retention
- Create azure recovery services (In same subscription, same region and same resource group as your sql database server)
- Register the server to vault
- Create azure recovery services protection policy
- Apply the retention policy to the database that require the LTBR
To restore long-term backups
- List the vault where the backup is stored
- List the container that is mapped to your logical server
- List the data source that is mapped to the database
- List the recovery points available to restore
- Restore from the recovery point on the target server in the subscription
Database recovery using backups
- Point in Time Restore, can be used as Database replacements or for Data recovery scenarios.
- Geo-Restore is the default recovery option- For Basic databases with maximum size of 2 GB Geo-Restore, provides a reasonable DR solution with an ERT of 12 hours.
Active Geo-Replication offers a much lower RPO and ERT as it only requires you initiate a failover to a continuously replicated secondary
- Used during Database Migration
- Application upgrades
SQL Migration Tool to recover a single table using the Point-in Time Restore
Note: In Azure storage, the term replication refers to copying files from one location to another. SQL’s database replication refers to keeping to multiple secondary databases synchronized with a primary database.