Our setup:

  • City A - Primary server for logshipping
  • City B - Secondary server for logshipping

Now there is a lot of data being updated from A - B.

And the server in City B is underutilized, and it is a very powerful machine.

Can I install a Reporting server and a mirroring server on the server which hosts the secondary logshipping server, and run the Reporting and mirroring server to get the data from the sSecondary logshipping server?

We cannot have the data come across 3 times (one for Logs, Report, Mirror) - and the secondary server is updated regularly enough to allow for our requirements.

But the secondary logshipping server is obviously always in Standby/Readonly mode - is this all possible?

  • What do you mean by "mirroring server"? This question is very confusing. – Jon Seigel Mar 2 '13 at 15:36
  • If you are going to go to the trouble of setting up mirroring between City A and City B (which is what I'm guessing that the OP means), what does the log shipping get you? – Darin Strait Mar 2 '13 at 16:40
  • want to setup Mirroring from the secondary log shipping server in City B --> to a mirror instance of it. I'm told to do it, so I have to make this work somehow unfortunately. – Peter PitLock Mar 2 '13 at 17:36

The question is confusing but I think what you're trying to say is that you want to access the data at City-B, using your existing log-shipping setup.

  1. With regards to the log shipping, at City-B, there is an option [RESTORE ... WITH STANDBY] which will leave the database in restoring mode, however in a READONLY state so it's accessible for SELECT queries. The main restriction with this approach, is that you can only restore a log when all users are out of the database. Depending on your log shipping restore schedule (very frequent/this may not work then), you'd have to kill all the connections prior to restoring the next log. Test this and re-do your log shipping config.

  2. Depending on your version of SQL, perhaps explore using DB Snapshots at city-B - need to research how this would interact log shipping.

  3. You could install/configure Reporting Services at City-B (which would create the two system report databases). Reports could then query the databases (at city-b) which are involved in log-shipping (but read-only) as per my first point.

  4. If you going to create another instance on City-B, then how about creating a job that performs a backup of databases on City-B (instance1/logshipping) and restoring on City-B (instance2/Reporting Instance) - which you can then access as required.

  5. Mirroring does not let you read from the mirrored instance.

I hope this helps.

Not the answer you're looking for? Browse other questions tagged or ask your own question.