November 29, 2007 at 8:45 am
We have a vendor developed product that is transaction intesinve.
They have canned reports that we would like to implement. These
reports can be run off an "archived" db or current db. I am looking into
db replication/mirroring the live db and run the reports of this. The
latest data reports can have can be as of yesterday.
The db is set in simple mode. This is sqlserver 2000 on windows 2003.
what is an optimum solution: I am new to mirroring and replication also.
Thanks
November 29, 2007 at 11:14 am
Mirroring is not an option to offload reporting. The mirror db is in an offline status.
Log shipping is a possible solution, since the db can be left in a standby or read only status - however each time a log is delivered all user conns are killed.
Transactional replication is a perfect option for this.
Regards,
ChrisB MCDBA
MSSQLConsulting.com
Chris Becker bcsdata.net
November 29, 2007 at 1:46 pm
Thanks chris,
Was thinking on the same lines. Also did not see mirroring as an option for SQL2000. This is available in 2005. I could update the subscriber nightly. As I said reporting could be a day off which is fine.
Anything I need to check on performance/etc... Tips as I am starting on a clean slate.
November 29, 2007 at 1:58 pm
You're right - mirroring not an option in 2000.
I would look at using a dedicated distributor server since your vendor app is transaction intensive.
You have a choice to go with snapshot or transactional replication. Read books online about the two. Even though your subscriber can have day old data I would implement transactional personally and you get the benefit of latency being in the seconds if you leave your log reader & distribution agent running continuously. Or you can run your agents to deliver changes hourly/nightly. Keep in mind with transactional you cannot truncate your db log until log reader picks up commands to be replicated.
Do not consider merge replication, this will add a column (rowguid) to each replicated table - not good when app is not yours...
Snapshot will deliver entire tables each time it is ran, where transactional will only deliver changes.
ChrisB
MSSQLConsulting.com
Chris Becker bcsdata.net
November 30, 2007 at 1:44 pm
The db is set in simple mode: We do not want to hog resources by hourly updates. Most probably: snapshot nightly should do. As the apps is transaction intensive, cannot have large logs just waiting to be updated...
-c
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply