0

We plan to incorporate Log Shipping for basic disaster recovery with reporting needs. Server A Source Database will log ship to Server B database. We will conduct log shipping every few hours.

Looking at setup, there is an option for "Use a Monitor Server Instance". Currently we do not have budget/time resources to setup third monitor server.

In that case, what is the second best recommendation for Monitor Server to store status/history of log shipping activities? On the source primary server or destination server ?

We are mildly concerned about performance/activity of Source Server. Sometimes CPU can get moderate high. So I think Destination Server is better option. Also, in case primary server fails, we have status/history of when last time transaction logs backup/shipped/restored. If monitor was on Primary server failed, we would not know this.

I am trying to research if there is any missing reason to place monitoring on Source Server.

Improve this question
0

1 Answer 1

Reset to default
4
+50

If you are doing log shipping, I would highly recommend (and the best option I believe) you do have a separate monitoring instance. The monitoring instance could be in Express Edition so you don't have to worry about licensing. You just need to know that after you setup log shipping and you then in the future want to change the monitoring instance or add one, you may need to reconfigure your log shipping. The upside is that you can use the monitoring instance for multiple log shipping and have the view of all the primary/secondary databases status in one location. Once you completed the log shipping setup with monitoring instance, on the monitoring instance, right click on the instance -> Reports -> Standard Reports -> Transaction Log Shipping Status.

For reporting side of things, you need to disconnect the users/clients when doing a restore or wait until no one is connected to the database (delay restore). So plan for it. Another thing to be mindful of is that you can't add indexes or statistics on secondary database during standby mode. Doable but personally i would not use log shipping for reporting (only for DR purpose).

Personally, I would consider and recommend transactional replication for reporting. This gives you more control on your reporting database as you can create indexes, stats, create objects, etc on the subscriber database.

HTH

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.