Can we create Database snapshot on secondary replica in AG

Yashwant Vishwakarma 116

Dear Folks,

is it possible to create database snapshot on secondary read replica in AG with resource governor limiting queries from application.?

CathyJi-MSFT 22,331 Reputation points Microsoft Vendor

2021-02-12T08:45:52.21+00:00

Hi @Yashwant Vishwakarma ,

We have not received a response from you. Did the replies could help you? If the response helped, do "Accept Answer". If it is not, please let me know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Accepted answer

Yashwant Vishwakarma 116 Reputation points

2021-02-12T15:33:41.073+00:00

Hi Sean,

I created the database snapshots for one of our database which is of almost 4.0 TB, I observed when the third party tool BryteFlow get connected and started fetching queries latency on secondary replica slowly increased by approx. 7000 seconds and after that database snapshot went into suspect mode.

When I tried to search logs I found the error < Z:\Snapshots\dbname_ss.ss: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered. Error: 17053, Severity: 16, State: 1.>

I also created resource governor for that tool not to take more than 15% CPU and 25% memory and DOP =2

is there any way I can meet this requirement ?
Please sign in to rate this answer.
Sean Gallardy - MSFT 1,891 Reputation points Microsoft Employee

2021-02-12T16:17:08.857+00:00

Let me quote myself:

Is it a good idea? Probably not.

and

Generally speaking, this causes the secondary replica to be starved of resources and cause synchronization issues, which defeats the purpose of having the AG.

To your specific issue: When I tried to search logs I found the error < Z:\Snapshots\dbname_ss.ss: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered. Error: 17053, Severity: 16, State: 1.>

Yep, that's expected. This is what happens when you use NTFS (probably aren't using 4k file record segments) and have monolithic files.

is there any way I can meet this requirement ?

Physically architect the database to meet the needs. Break up large files into much much smaller ones, use filegroups, etc.

I also created resource governor for that tool not to take more than 15% CPU and 25% memory and DOP =2

Hopefully that's enough for your application/end users...
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

3 additional answers

Sean Gallardy - MSFT 1,891 Reputation points Microsoft Employee

2021-02-05T17:28:55.283+00:00

Is it possible? Yes.
Is it a good idea? Probably not.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Yashwant Vishwakarma 116 Reputation points

2021-02-06T11:40:20.09+00:00

Hi Sean Gallardy,

Thanks for your reply.
I am curious to know why it is not a good idea so that I can decide for my environment ?
Please sign in to rate this answer.
Sean Gallardy - MSFT 1,891 Reputation points Microsoft Employee

2021-02-06T12:40:21.587+00:00

Database snapshots work using a copy-on-write process. This means that until the database snapshot becomes the same size as the original database, changed data is continually going to be copied into it, making it larger and causing more IO to be consumed. Since AGs are already IO heavy, especially around log and redo, this adds an additional burden. Dropping and re-creating the snapshot for the next set of static use will cause the same jump in cpu, memory, and disk activity. Since it is being used as a readable copy, this means queries and other items are going against it, using more resources.

Generally speaking, this causes the secondary replica to be starved of resources and cause synchronization issues, which defeats the purpose of having the AG. Your mileage may vary, it might be perfectly fine in your environment... or it might crash and burn.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
CathyJi-MSFT 22,331 Reputation points Microsoft Vendor

2021-02-08T08:41:41.557+00:00

Hi @Yashwant Vishwakarma ,

> Can we create Database snapshot on secondary replica in AG

Yes, you can. You can create a database snapshot on an primary or secondary database in an availability group. The replica role must be either PRIMARY or SECONDARY, not in the RESOLVING state. Refer to MS document Database Snapshots with Always On Availability Groups.

If the response is helpful, please click "Accept Answer", thank you.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Can we create Database snapshot on secondary replica in AG

3 additional answers

Your answer