Ways to replicate

Question

Post reply

Ways to replicate

zouzou

SSCrazy

Points: 2292
More actions
March 12, 2013 at 4:39 am

#290869

Hello,
I have a live db that users need to access it for reporting purposes. What is the best way to let them access ir without affectinglive?
They need the recent copy of the db every minute. Is there any way of replicating the data somewhere else and be able to let the users access that without issues?
Your help is much appreciated.
Riri

Viewing 10 posts - 1 through 9 (of 9 total)

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

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

Use snapshot for Reporting purpose.

For every minute, you can use log shipping for Ur requirement..

zouzou SSCrazy Points: 2292 More actions · Answer 2

I m using log shipping for the database now but i have to kill the connections to restore the recent backup. 🙁

Is there any other way of not disturbing the users to be connected?

zouzou SSCrazy Points: 2292 More actions · Answer 3

I dont want my users to access the live db instead the standby mode log shipped db but this has issues since i need to kill the connections. Is there any other way to replicate data somewhere else and let the users logged on and not kick them out?

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 4

Would transactional replication work for you? You could replicate to another box which could then be accessed by those who need reporting, without affecting your live database. It's not usually got much latency - a few seconds perhaps - so should fit your needs there.

There's a lot to consider - what to replicate, where to put the distributor, whether to use push or pull subscriptions and so on, but it can work well for what I think you are describing.

david.alcock SSCertifiable Points: 6021 More actions · Answer 5

We have several data capture systems where we utilise transactional replication for reporting purposes. I used to think it was a little misuse of a high availability solution but in truth it works well. Depends on the age of the data requirement I guess, if its 'live' then trans repl works well but we also use SSIS for similar functionality.

'Only he who wanders finds new paths'

zouzou SSCrazy Points: 2292 More actions · Answer 6

Thank you guys,

Will it work if the users connect to the replicated database all the time? Excel creates a connection that always stays there until you close the spreadsheet..

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 7

zouzou (3/15/2013)
Thank you guys,
Will it work if the users connect to the replicated database all the time? Excel creates a connection that always stays there until you close the spreadsheet..

I don't see why it wouldn't work - after all it's for reporting purposes, right? The only time you run into problems with transactional replication is if you start changing records at the subscriber, in which case you need to account for that somehow.

david.alcock SSCertifiable Points: 6021 More actions · Answer 8

Is the original source database used purely for this reporting access or are there other production processes going on? If it is used for a bundle of things then undoubtedly moving the data to a pure reporting store will be beneficial. Then the user access can be managed accordingly.

'Only he who wanders finds new paths'

Alan Burstein SSC Guru Points: 61141 More actions · Answer 9

zouzou (3/12/2013)
Hello,
I have a live db that users need to access it for reporting purposes. What is the best way to let them access [it] without [affecting live]?
They need the recent copy of the db every minute. Is there any way of replicating the data somewhere else and be able to let the users access that without issues?
Your help is much appreciated.
Riri

For what you are doing transactional replication is the way to go. You could set transactional replication to run continuously so the users would have up-to-date data at the subscriber.

I have always had good luck with SQL Replication (2008+). Make sure to do your homework as setting up replication can be tricky. Set it up in a test environment first to get comfortable first and make sure to use at least two servers (or at least two instances) when you test.

Important Note:

I highly recomend PULL replication from your subscriber database if your published DB is in Production. When you setup a publication SQL creates stored procs whereever the pushing or pulling is happening... If you setup push replication from your live server then you will create a bunch of new stored procs on that published DB as well.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001