synch data issue

Question

synch data issue

KATHLEEN Y ZHANG

Hall of Fame

Points: 3534
More actions
May 26, 2010 at 9:42 am

#156982

Hi!
Currently we have a reporting server that takes data changeS from production servers through log shipping. The issue with log shipping is that current users have to be disconnected in order to apply the logs, therefore we can only apply the logs at night. So the data on the reporting server is always a day behind. The business requirement recently changed, we need production logs applied more frequently, ideally every half hour. Log shipping does not work because we would like the reporting processes run continuously without being disconnected while changes are applied.
I've evaluated Quest logreader and ApexSQL log, they both have trouble generating update statements.
I am wondering if anyone here have any other suggetions?
Thanks!
Kathleen

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90312 More actions · Answer 1

If you have enterprise edition, you could setup database mirroring and create a database snapshot from the mirrored database.

Or, you could setup replication and only replicate the tables you need for reporting.

With a snapshot, you would still have to terminate all user access to drop the old snapshot and create the new snapshot. It only takes a few seconds, but the users will be disconnected.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736568

May 26, 2010 at 9:53 am

#1172848

Replication is usually the answer in these situations.

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 3

Thanks, Jeff and Steve.

We are using standard edition right now. My manager is not quite willing to pay 4x the cost for enterprise, unless it is fully justified.

We have transactional replication set up in our production environment. It seems to require some upkeep. In case somehow it breaks, we have to snapshot 1.5TB of data. But I guess we might have to go this route if nothing else works.

Thanks again,

Kathleen

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 4

Snapshots are still an issue of disconnection for users, and in my mind, that's still an issue.

Replication is an issue if it breaks. It should pick up itself, or you can move the data yourself and check the box that you initialized from backup

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 5

Right. To snapshot in replication, even though the users dont get disconnected in a hard way, the tables in snapshot get dropped though for initialization.

I've tried initialization from backup before, I vaguely remember there were some constraints/issues. But I can try it again.

I just found some info about Microsoft Synch Framework. Will read about and see if it works here.

Thanks!