AzureEnterprise TechSQLHow to Backup your Azure SQL Database June 25, 20169220 views0Share By IG ShareI have already spoken about High Availability for SQL Server deployed in an Azure VM. For SQL Server deployments in Azure VM’s (IAAS), you are entirely responsible for its availability and business continuity planning. However, in case of Azure platform as a service (PAAS), Microsoft provides cloud a lot of these features as a part of the Azure SQL PAAS package. In this post, I am going to talk about how to backup your Azure SQL Database.Note: If you buy something from our links, we might earn a commission. See our disclosure statement.How to Backup your Azure SQL DatabaseMicrosoft SQL Database on Azure provides you recovery options for both planned and unplanned outages. SQL Database on Azure PAAS has backed up automatically, and you are not responsible for backing it up yourselves.Azure SQL Database is backed up AutomaticallyThe business continuity features of SQL Database as-a-service is broadly categorized into three buckets:Point in Time RestorePoint in time restoration process is similar to T-Log (Transaction Log) backup restoration process. You can restore your data to a point in time, based on the backup that is available. The backup retention period is not user-driven. Instead, it is dependent upon the service tier that you are in.Note: You can only restore a Azure SQL Database with a different nameSee image below:Geo – RestoreSupported on all service tiers, i.e., Basic, Standard and Premium, the Geo – Restore feature allows you to restore your databases from a different region, other than the primary region, thereby providing site resiliency. This feature is exactly like the Point-in-time restore feature with one minor difference. In this case, the database is restored from a backup chain stored in a geographically-replicated Azure blob storage (RA – GRS). The Azure service with maintaining a backup chain with a frequency of a full weekly backup, multiple times a day-differential backup, and T-Log (transaction logs) saved every 5 minutes.Please note that there can be a delay of up-to 1 hour between backups across the replicated Blobs in RA-GRS. RPO = Up to 1 hourThe Geo-restoration time depends upon several factors like network bandwidth utilization, database size, the number of T-Log’s, etc. The time taken is determined by the service and is not user controlled. Source: https://azure.microsoft.com/en-gb/documentation/articles/sql-database-geo-restore/Active – Geo-ReplicationMicrosoft uses the SQL Server Always on availability groups behind the scenes to provide you this feature. Unlike the On-Premises or the IAAS setup, in this case, the user is only responsible for selecting the region they want the secondary server to be. With just a few clicks, you can have an active secondary. You can have up to 4 active secondaries, and they can be either Readable or Non-readable.Here are the key points to summarize this offering:Up-to 4 readable secondary replicas.Asynchronous replication only. The secondary replicas will have data lags.Only committed transactions are replicated over to the secondaries.Automatic fail-over is provided out of the box.You can also manually failover using Power-shell scripts or using the Azure management (new) portal.All Databases are transactionally consistent.All secondary non-readable databases would be migrated to a readable secondaries post-April 2017.DDL transactions (Schema-changes) will take more time to be committed on the secondary server(s).You can have a mix of service tiers with Primary and secondaries.Your application can off-load read-only queries to the secondary server.The decision to go for Geo – Replication will depend on how much mission critical your application is.Additional cost needs to be taken into consideration. As previously mentioned, based on the service tier, the backup retention period varies. See below: Source: https://azure.microsoft.com/en-gb/documentation/articles/sql-database-business-continuityAzure SQL Database Copy OptionThis allows you to create a copy-only database backup, that can be imported to create a different Azure SQL Database with a different name. See image below:Azure SQL Database Export OptionJust like Copy-Only backups, you can also export your database directly to a Blob storage container. See picture below:To summarize, Azure SQL Database provides a comprehensive set of options to its users for business continuity. To learn more about these features visit the official Microsoft site for detailed documentation:Cloud business continuity and database disaster recovery with Azure SQL DatabaseSQL Database Active Geo-Replication Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Enterprise TechList of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By IGMay 25, 2021
AzureBest Industrial IoT Routers & Gateways For AWS and Azure IoT ServicesIn this article, we are going to list out the Best Industrial IoT Routers for professional ...
AzureDownload Backup and Restore a Azure SQL Database Locally for TestingAzure SQL Database is Microsoft’s Database as a service on the cloud. While your production ...
AzureStep by Step Deployment for SQL Analysis Services PAAS on Azure CloudSQL Server Analysis Services (Both Multi-Dimensional and Tabular) only supports Windows Authentication. However, Analysis Services ...
AzureMove & Load Data to Azure SQL DataWarehouse using Data Platform StudioThere are multiple ways to populate data into Azure SQL DataWarehouse PAAS (Platform as a ...