Right Azure storage replication for SQL Data Log file
As we know starting SQL Server 2014 we can easily place our SQL Data and log files in Azure, it is indispensable to choose Right Azure storage replication for SQL Data & Log file – to know more click here to know about the feature to place SQL Data & Log Files on Azure, click here to know how to configure this and click here to configure Azure storage security for this feature
Right Azure storage replication for SQL Data & Log file
It is very important to understand the available replication types before configuring your azure storage account so that you can plan for the worse. Here are the different options you have:
- Locally redundant storage (LRS)
- Zone redundant storage (ZRS)
- Geo redundant storage (GRS)
- Read access Geo redundant Storage (RA-GRS)
Let’s understand each one of them:
- Locally redundant storage (LRS)
As the name describes, your data gets replicated within the region only. So all your data gets copied in three different individual nodes in separate fault & upgrade domains. This is done to make sure no single hardware failure\upgrade affects the availability of your data. A request returns successfully only once it has been written to all three replicas. (Just like synchronous mirroring)
2. Zone redundant storage (ZRS)
This replication type copies your data across two to three facilities either in the same or across two different regions hence provides high durability than locally redundant storage. By choosing this type you will still be able to get your data even if the complete facility gets affected & unavailable. (Special Note: selecting ZRS you cannot convert it to use any other type of replication or vice versa.)
3. Geo redundant storage (GRS)
This type of storage replication makes sure that your data gets copied locally as well as to a secondary region which is hundreds of miles away from your primary region. Which means your data has 6 different copies each maintained on separate node. By choosing this you make sure your data is safe even in the case of a complete region outage or a disaster affecting your primary region. All your data changes first gets written on primary region where it gets replicated three times & once it gets complete the data changes are replicated to the secondary region and there also three copies are maintained across separate fault and upgrade domains. Means the Geo replication is asynchronous in nature which means a little data loss is expected if your primary region is not available; so no data performance hit for your SQL Workload just like Asynchronous mirroring J . You can’t change your secondary region.
4. Read access Geo redundant Storage (RA-GRS)
This is the replication level which provides maximum availability of your data by providing read-only access from secondary region. It provides all features of Geo redundant storage (GRS) the name of the endpoint gets a suffix –secondary to the account name which makes it easy to remember. For example, if the primary endpoint of one of my storage accounts is: https://sqldbfiles4sqlchamp.blob.core.windows.net/ the secondary endpoint will be: https://sqldbfiles4sqlchamp-secondary.blob.core.windows.net/ the storage keys will however remain same that means your primary access keys will work without any issue on your secondary endpoints also.
Choose Right Azure storage replication for SQL Data & Log file carefully, since this will directly impact the availability and DR capabilities to retrieve your SQL files.
Is it possible when was the last time my secondary storage account was synced with primary?
Yes it is very much possible with the help of Last Sync Time for RA-GRS. A REST API named “Get Service Stats” is available which can help you get this value. This is a UTC time. You get stats in two pieces: (a) Status of Geo-Replication & (b) Last Sync Time.
Stats of the Geo Replication can be:
- Live: means replication is enabled, active and operational.
- Bootstrap: means the replication has just started for the first time and secondary is not available for read-only workload.
- Unavailable: either there is an outage and last sync time is not computable or it has not yet calculated.
Last Sync Time: this shows the replication lag time for the service. The value is always in UTC Time zone.
If needed you can easily copy your data to another storage account using AZCopy utility. click here to know more on this. (updated)
Hopefully this blog will certainly help you to choose Right Azure storage replication for SQL Data & Log file.
Happy Learning!
feel free to leave a comment. 🙂