Some queries

Question

I am very new to Azure SQL database  and I have some query 

1. it's something different when running sp_who2 in Azure DB. There are some background process from different server :

DmvCollector                                                                                                                   
BackupService                                                                                                                  
NodeAgent                                                                                                                      
MetricsDownloader                                                                                                              
MetricsDownloader                                                                                                              
VaService                                                                                                                      
TdService  
 what are they ?

2. Can choose specific full database backup to restore ? or just point-in-time recovery ?

3. Can point-in-time recovery to the same db name ?

4. what's the procedure to restore if the DB is deleted ?

5. how to charge license for Active Geo-replication ? and what's the RPO? I know it's always-on in backend with async mode.

6. How to detect failover for geo-restore and  Active Geo-replication?

7. Can I change the timezone for the azure database ? when I type select getdate() it's different timezone. I am wondering when migrate the db from premise to azure db. the time will get all wrong!!!

Answer 1

Hello,

Azure SQL runs some internal processes to provide features automatic tuning, automated backups, etc.

Database restores always create a new database as result but yes you have available point-in-time restores on Azure SQL Database.

https://azure.microsoft.com/en-us/blog/azure-sql-database-point-in-time-restore/

Automated backups made by Azure services create full, differential and transaction log backups. Those automated backups are retained for up to 35 days. If you want backups retained for more than 35 days, then you can configure long term backups.

Below is the procedure to restore a deleted database.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-recovery-using-backups#deleted-database-restore

Information about RPO can be found on the following article:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-business-continuity#sql-database-features-that-you-can-use-to-provide-business-continuity

You need to examine Activity Log to keep track of failovers.

All azure services use UTC time zone, but below article shows you how to deal with that:

https://blogs.msdn.microsoft.com/azuresqldbsupport/2016/07/27/lesson-learned-4-modifying-the-default-time-zone-for-your-local-time-zone/

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

I have difficulty on setting up the long-term backups.

are there any updated procedure ? I found in the Microsoft, seems the screen is not sync to what my portal has

And I would like to clarify following:

For automated backups: for example 35 days, will azure keep 35 days full backup for me ? or 35 days differential backup ? or these automated backup are for point in time recovery purpose????

and versus long-term backup, I can choose specific day full backup to restore.

Answer 3

Hello,

Azure services are always evolving, and documentation goes obsolete quickly. I have seen some of my Azure articles going obsolete in a time span of months.

If you can share with me the URL of the Microsoft documentation you were following this may allow me to contact the documentation team.

About the question related automated backups, Azure SQL Database uses SQL Server technology to create full, differential, and transaction log backups for the purposes of Point-in-time restore (PITR). The transaction log backups generally occur every 5 - 10 minutes and differential backups generally occur every 12 hours, with the frequency based on the performance level and amount of database activity. Transaction log backups, with full and differential 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.

Yes you can choose a day to restore the database as it was on that day. A new database will be created with the restore.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 4

So my question here.

"Yes you can choose a day to restore the database as it was on that day. A new database will be created with the restore" < how to do it instead of point in time.

How to manually do geo-restore. ?

How to configure Recovery Services vault so that I can use Long-time retention for Azure SQL backup ?

And why my "available backup" is empty ?

Answer 5

Hello,

You can only restore automated backups to a point-in-time and you cannot have manual control over which backups to restore.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-recovery-using-backups

Below how to perform a manual geo-restore:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-disaster-recovery

To configure Azure Recovery Services Vaults for Azure SQL Database please follow instructions provided on the following documentation:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-long-term-backup-retention-configure-vault

Instead of looking at server level go to the specific database, you will see an horizontal menu on the right side with option like Copy, Restore, Export, Set server firewall, Delete, Connect with and Feedback. Please choose Restore.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 6

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-disaster-recovery    <<< when I click geo-restore , it direct me to point in time recovery , may I know it's the same thing ??

and for https://docs.microsoft.com/en-us/azure/sql-database/sql-database-long-term-backup-retention-configure-vault  again. I don't have the following button in my portal .

Answer 7

Hello,

That article is deprecated since July 1.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-long-term-backup-retention-configure-vault

Please follow instructions of below article:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-long-term-backup-retention-configure

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 8

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-long-term-backup-retention-configure  << I follow this but it doesn't effective after apply

what's service tier support this ?

Answer 9

Hello,

It works for all service tiers.

Please explain why you say it does not work? Please try to describe a little bit more.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 10

it's it also work from free subscription ?

seems work now, I set it to 10 weeks

So when I will see the available backup in the following screen ?

Answer 11

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-disaster-recovery    <<< when I click geo-restore , it direct me to point in time recovery , may I know it's the same thing ??

Answer 12

Hello,

You are right where you need to be. With a geo-restore you just need to select a target server on another region as destination.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 13

seems I can't choose another target server to be restored to.

And can I choose the source of the backup ?

Answer 14

Hello,

You selected the source database on the previous step.

About the target server, do you have more than one Azure SQL Database server?

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 15

yes. i have myazuresrv20 too