Replication stategy advice

Hi,
I would like some advice to choose the correct replication stategy. I've got
an Oracle database which is copied accross to SQL server every morning. This
is done via SSIS delete/ insert. This process copies approx 130K rows from
oracle into SQL Server every morning.
Now the SQL server database is replicated to a Disaster Recovery server. At
the moment this is done via Snapshot replication every hour.
I would like to know what's the best strategy. Can I use transactional
replication instead of snapshot? Personally I don't see a big issue with 120K
rows inserted every morning, transactional should handle it. Then once this
is done, there aren't many updates during the day. Is Log shipping a better
option? What's the best strategy if you assume that the initial sync may
double or triple is size to 400K rows? If I use log shipping are there any
extra contrains I need to be aware of, ie don't allow truncate statements etc
on the database?
Thank you for your help.
Panos
By the way, this is on 2005. Can I also consider database mirroring in this
case? Thanks.
"Panos Stavroulis." wrote:

> Hi,
> I would like some advice to choose the correct replication stategy. I've got
> an Oracle database which is copied accross to SQL server every morning. This
> is done via SSIS delete/ insert. This process copies approx 130K rows from
> oracle into SQL Server every morning.
> Now the SQL server database is replicated to a Disaster Recovery server. At
> the moment this is done via Snapshot replication every hour.
> I would like to know what's the best strategy. Can I use transactional
> replication instead of snapshot? Personally I don't see a big issue with 120K
> rows inserted every morning, transactional should handle it. Then once this
> is done, there aren't many updates during the day. Is Log shipping a better
> option? What's the best strategy if you assume that the initial sync may
> double or triple is size to 400K rows? If I use log shipping are there any
> extra contrains I need to be aware of, ie don't allow truncate statements etc
> on the database?
> Thank you for your help.
> Panos
|||Panos,
please take a look at this article:
http://www.replicationanswers.com/Standby.asp which should help with your
questions.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Panos - yes, database mirroring is an option. However in your case I
wouldn't expect that the latency advantage will be enough of a reason to use
it. Also, you'll have to set up database snapshots to make it available
which can't be backed up and can't have FTI - I'll do a proper (article)
comparison some time soon.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for this Paul very useful. A final question, if you are doing truncate
table statements on the publisher database, can you use this database for
logshipping/ mirroring?
The fact that Truncates don't register into the log doesn't affect the
Mirroring stategy? or if it is affected what can be done to sink Publisher &
subscriber? We would like to keep using truncate. My idea is when you
truncate the table in the publisher you can also truncate it in the
subscriber DB as it only happens by a scheduled job. Thanks.
Panos.
"Paul Ibison" wrote:

> Panos,
> please take a look at this article:
> http://www.replicationanswers.com/Standby.asp which should help with your
> questions.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||I think the answer is here:
http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?topicid=1&faqid=4
So the DBA at my last job wasn't very up to date?
Panos.
"Panos Stavroulis." wrote:
[vbcol=seagreen]
> Thanks for this Paul very useful. A final question, if you are doing truncate
> table statements on the publisher database, can you use this database for
> logshipping/ mirroring?
> The fact that Truncates don't register into the log doesn't affect the
> Mirroring stategy? or if it is affected what can be done to sink Publisher &
> subscriber? We would like to keep using truncate. My idea is when you
> truncate the table in the publisher you can also truncate it in the
> subscriber DB as it only happens by a scheduled job. Thanks.
> Panos.
> "Paul Ibison" wrote:
|||Panos - this is correct. From the point of view, truncate will be OK.
However, you won't be able to do a truncate if there is a FK declared on the
table, which usually precludes this operation for me.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .