Or, really, is log shipping the right choice for the following
The production server (A) must be highly available throughout the day
to its users. There are also some reporting tasks that run throughout
the business day that slow things down a bit. These tasks are to be
offloaded to a second SQL Server (B).
I've setup log shipping from A to B (B in STANDBY mode) every 5
minutes and gotten this to work fairly consistently. The reporting
jobs run off server B well, too. However, the reporting jobs appear to
interfere with the replication.
I leave server B alone, replication runs fine. As soon as I access
server B for anything, replication stops working they become out of
sync. If I close all connections to server B again, replication seems
to pick back up.
My assumption is that the connections to server B are stopping the
server B from being able to restore new transaction logs. Is this
The problem being partially that the reports run through an ASP.NET
application, which pools connections to server B in ADO.NET... this
the connection remains longer than the actual queries. To test my
theory, I did an IISRESET after I was done running some reports,
effectively closing all connections, and that is when log shipping
picked up again.
My end goal in THIS scenario is to have server B contain very recent
data from server A (a lag time of 5-10 minutes is acceptable) and
allow queries to be run against server B. No modifications of data
will be made to server B.
What is the best way to implement this with the minimal performance
impact on server A and server A's users? I am think that transactional
replication will be the way to go. The servers are in the same rack
and we want updates to server B to be as recent as possible compared
to when they occur on server A.