Ways to replicate

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Ways to replicate

Ways to replicate

Rate Topic

Display Mode

Topic Options

Author

Message

zouzou

Posted Tuesday, March 12, 2013 4:39 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 1:47 AM
Points: 22, Visits: 191

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

Post #1429670

Pulivarthi Sasidhar

Posted Tuesday, March 12, 2013 4:59 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:30 AM
Points: 870, Visits: 522

Use snapshot for Reporting purpose.

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

Post #1429675

zouzou

Posted Tuesday, March 12, 2013 5:02 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 1:47 AM
Points: 22, Visits: 191

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

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

Post #1429678

zouzou

Posted Tuesday, March 12, 2013 5:25 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 1:47 AM
Points: 22, Visits: 191

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?

Post #1429689

Duncan Pryde

Posted Friday, March 15, 2013 5:18 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 3,046, Visits: 1,309

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.

Post #1431461

david.alcock

Posted Friday, March 15, 2013 6:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 117, Visits: 454

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'

Post #1431490

zouzou

Posted Friday, March 15, 2013 6:20 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 1:47 AM
Points: 22, Visits: 191

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..

Post #1431496

Duncan Pryde

Posted Friday, March 15, 2013 6:42 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 3,046, Visits: 1,309

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.

Post #1431507

david.alcock

Posted Friday, March 15, 2013 8:06 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 117, Visits: 454

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'

Post #1431550

Alan.B

Posted Friday, March 15, 2013 4:30 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:06 AM
Points: 223, Visits: 1,137

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.

-- AJB
xmlsqlninja.com

Post #1431834

« Prev Topic | Next Topic »

Permissions