Database Replication

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ryan - 29 Nov 2006 03:44 GMT

we have setup a sql2005 server for reporting. we want to replicate the
transaction databases which used for live applications to that server such
that all reports will be generated in that server.

This reporting server is supposed read-only and with let's say 30 mins delay
from production data.

With this requirement, should we use the transactional replication or there
any method?

Thanks,
Ryan

Reply to this Message

Paul Ibison - 29 Nov 2006 09:42 GMT

Ryan,
transactional replication is often used for this type of reporting
requirement. You could also enhance the system by using the snapshot
committed isolation levels to maintain access while the distribution agent
is running. The main 'competitor' technology on SQL Server 2005 is database
mirroring with database snapshots. There's no detailed list of pros and
cons, but as I'm a replication guy I'll point out that mirroring doesn't
support FTI and you can't take back ups of snapshots :)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message

ReasonRules@gmail.com - 30 Nov 2006 14:38 GMT

Paul-
I have client who maintains a local SQL 2005 database which has
numerous bulk updates applied throughout the day. They wish to
replicate (most of) this data to their web host which is in another
city. They are trying to decide whether to use Transactional
Replication or once-per-day Merge Replication. (Concurrency is not a
big issue here).

What method would you recommend? What are the most important
considerations?

Thanks,

Paul

> Ryan,
> transactional replication is often used for this type of reporting
[quoted text clipped - 6 lines]
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message

Paul Ibison - 30 Nov 2006 15:20 GMT

Merge is generally slower. If there are numerous updates to the same row,
then it can approach transactional times, but I have rarely seen cases of
someone claiming it to be faster. It's geared up for offline updates at teh
subscriber and conflict resolution, neither of which you'll need. I'd
definitely go with transactional.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message

ReasonRules@gmail.com - 30 Nov 2006 16:12 GMT

Thanks, Paul!

Does Transactional require an "always on" connection to the subscriber?

> Merge is generally slower. If there are numerous updates to the same row,
> then it can approach transactional times, but I have rarely seen cases of
[quoted text clipped - 3 lines]
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message

Paul Ibison - 30 Nov 2006 16:58 GMT

No - as long as there is a connection when the distribution agent is
scheduled to run you're ok (different for immediate updating subs but not
relevant in your case).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message