February 11, 2015 at 10:46 am
Hi,
We have a SQL Server 2008 that is being replicated transactionally (log shipping) onto another SQL Server 2008.
Can we report against the replication slave without disrupting the replication process? I have been informed that the slave server needs exclusive access to the db in order to apply the transaction log.
If this is the case, are there any configuration changes which would allow us to report from the slave without impacting replication?
Thanks in advance.
February 11, 2015 at 1:59 pm
Do you have actually replication or log shipping? In case of former, you don't need to do any changes. In case of latter, you can access database between applying logs.
February 11, 2015 at 2:04 pm
We have log shipping.
Does that mean we can't report while logs are being applied?
What method of replication would be more appropriate?
The requirements is that we need to report from the slave. The master is a high-volume transactional db, with a lot of inserts and updates.
Thanks.
February 11, 2015 at 2:13 pm
TS00 (2/11/2015)
We have log shipping.Does that mean we can't report while logs are being applied?
YES
What method of replication would be more appropriate?
Transactional replication
The requirements is that we need to report from the slave. The master is a high-volume transactional db, with a lot of inserts and updates.
Thanks.
February 15, 2015 at 11:31 am
Some progress, but still somewhat stuck.
To recap: we have a transactional DB on server [A], which is log shipping to a backup DB on server . This arrangement works fine, however, we want to be able to run reporting against the DB on server , to reduce the load on server [A].
We tried switching to Transactional Replication, but couldn't, as many of the tables don't have primary keys.
Adding primary keys is not an option, as it would break the application.
Using Merge replication, which automatically ads a new PK field, is also not an option, because it would leave us with a DB which we can't restore & connect to the application.
So, we are looking at these options (both for server ):
1. Dump the DB, and restore to a reporting DB
2. Snapshot replicate the DB to a reporting DB
However, it appears we can't do either of these with the DB in Standby mode.
Any ideas on how we can run reports against the data we have on server ?
Thanks,
February 16, 2015 at 7:26 am
Given that your talking about reporting purposes how up to date does the data need to be and how long a window can you have where the DB is unavailable for reporting purposes?
If the answers are immediate and no down time, then your only options are upgrade to SQL 2012 and use always on availability groups or change your database to support primary keys and use transactional replication.
If you can have a period where reporting is unavailable and the data is up to a few hours behind, you can continue to use log shipping and have the business understand that the reporting database not be available during set times or consider mirroring.
With mirroring you can take a snapshot of the mirrored database that can be reported from. Keep in mind that a snapshot is a point in time image of your data that will not change till a subsequent snapshot is taken.
MCITP SQL 2005, MCSA SQL 2012
February 16, 2015 at 7:29 am
We can be up to 24 hours behind for the reporting.
The issue isn't with being up to date, it's that reporting will prevent logs from being applied for ~14 hours/day, which means there's a possibility that logshipping may never catch up.
February 16, 2015 at 9:09 am
8 hours should be plenty providing your not dealing with TB's of logs to replay. In our setup we have 7 servers all with highly transactional databases that all restore to a single reporting server once per day.
It takes 3.5 hours to replay all logs, before finally going into standby once applying the final log per DB.
We process on average 400GB of compressed transaction log backups per day.
How is your log shipping setup?
MCITP SQL 2005, MCSA SQL 2012
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply