DB mirroring and Snaphot

Question

Hi Friends,

I'm using DB mirroring and have a snapshot DB of that mirror DB to be able to report off of that. My challenge is to be able to find a better way to define when refreshed are needed for snapshot DB. currently it's based on a schedule, like every 4 hours, but my goal is to set a process that it be smart enough to detect when mirroring has enough transactions going on and it needs to refresh the snapshot.

Any idea ?

Thanks in advance,

-Patrick

---

Patrick Alexander

Answer 1

Hi, Patrick

->"but my goal is to set a process that it be smart enough to detect when mirroring has enough transactions going on and it needs to refresh the snapshot.Any idea ?"

How about create using a Sql agent job to keep monitoring the mirroring database and run a T-Sql when some metrics are met. so that you can create the snapshot dynamically.

TechNet Subscriber Support
If you are TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.

Hope it helps.
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 2

that's the goal, but what matrix should be monitored and how ?

---

Patrick Alexander

Answer 3

Can you explain what did you mean by " has enough transactions going "? You know since SQL Server 2012 you can use AlwaysOn AG to be able report on the  "mirror" database.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 4

Hi, Patrick

->"but what matrix should be monitored and how ?"
It would depend on your requirement, for example: you wish to refresh snapshot after 1000 row updates. then you can refresh snapshot by counting the number of updated row.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 5

The only thing you really can go on is the LSN, which gives you a rough idea of how much has been going on... But unfortunately the LSN will also advance if transactions are rolled back, so it's not an exact science...