In reference to my previous post , I am going to talk about how backups will be managed in Azure. This is one of the activities which every DBA does almost every day or week. Just to let you know , there is a change in the backups of azure databases. You can no longer take DB/log backups for SQL Azure database(PaaS).
Before you read forward, just be sure of what’s SQL on Azure VMs (IaaS) and SQL azure databases(PaaS). Please check this link : – https://azure.microsoft.com/en-us/documentation/articles/data-management-azure-sql-database-and-sql-server-IaaS/.
Generally, if you want to move the SQL database from test/development to production environment – you take SQL database full backup and restore it. But, this will change with SQL Azure database. In SQL Azure DBs , we have options
1. Bacpac – It consists of schema and data. If you want to move your database from on-premise SQL server to SQL Azure database, then you will be create a Bacpac file of your database. Move this Bacpac file to Azure storage and restore it as a Azure DB. Currently, there is no option of traditional backups for Azure DB.
2. Dacpac -> it consists of schema only. if you want to create database on Azure without data then this option is used.
For information on Bacpac and Dacpac , please check: –
Sometime, DB professionals use DB migration wizard to move the databases from on-premise to SQL Azure.
3. Copy database -> This option is used , if you want to move your existing azure database into some other location in Azure itself. It’s an asynchronous operation and connection to azure is not required after it started. For more information on Copy database , please check –
As above three topics are discussed in detail on the web already therefore, refered the links for deep understanding.
Q:Now, you got how backups in Azure work but at the same time, how will you manage if there is any accidental deletion of the data. In other words, how will you do point in time recovery.
A: You will be glad to know that, SQL Azure automatically takes full, differential and t-log backups in the background. By default one full backup every week , differential backup everyday and t-log backup every 5 minutes is taken. Depending on the tier of your database , the retention time will be set e.g. if you have database deployed in Premium tier then 35 days is the retention time. Even you can restore the deleted database within 35 days/retention time.
Moreover, you will get backup storage by default equivalent to the double of max database size of you performance tier e.g. for premium it’s 500 GB which means you will get 1000 GB of backup storage. if you want increase the size of backup storage, you will need to pay extra money for that (Contact Azure support for that).
For more information , please check – https://msdn.microsoft.com/en-us/library/ff951631.aspx
Backup and Restore for Azure database – https://msdn.microsoft.com/en-us/library/azure/jj650016.aspx
Please note – For Web and Business edition you still need to manage backups (Bacpac and database copy) yourself for point in time recovery.
let’s see how point in time recovery works in Azure database:
Once you select the time and click on confirm , you will see:
Q: How will HADR function for SQL Azure databases.
A: As, there is no DB/t-log backups so, features like AlwaysON/mirroring/log shipping etc. doesn’t exist in Azure DB world for now.
I will write my next post on HADR for SQL Azure databases and how it’s different from on-premise SQL server HADR.
The intent of this series is to pick some of the features which have changed and will change your way of working with databases.
The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.