MySQL Forums
Forum List  »  Microsoft SQL Server

Replicating to a MySql 5.0 reporting/archive database.
Posted by: Michael Kelly
Date: November 27, 2005 07:50PM

Hey folks, we're considering using MySql 5.0 as a reporting/archive database. The idea is that we'd replicate from our MS SQL OLTP database and occasionally prune old data to keep the OLTP database responsive. The MySql reporting/archive database, however, would contain all data.

I'd be interested in chatting with anyone who has attempted something similar.

Here are some of the issues I'm working through:

1. Mapping of types (e.g. varchars larger than 255 characters mapped to text).
2. Using MySql 5.0 stored procedures for data transformations in order to avoid the complexity and performance cost of using DTS.
3. Working around the fact that the "concurrent_c" sync method (which avoids the locking of tables during creation of the snapshot) is not available when using an ODBC driver and there is no supported OLEDB driver for MySql.

This last issue might go away if we only have to create the snapshot once, which might actually be what one wants in an archive scenario. I imagine the solution might look like this.

EXEC sp_addpublication_snapshot
@publication = N'My_Publication',
@frequency_type = 1 -- only create the snapshot once


EXEC sp_addarticle
@publication = N'My_Publication',
@article = N'My_Table',
@source_owner = N'dbo',
@source_object = N'My_Table',
@del_cmd = 'NONE',

I set the publication snapshot to only execute once, i.e. during the maintenance window when it is initially installed. Then, on the tables that will contain archived data, I specify that deletes aren't replicated.

But aren't there inevitably times when you need to resync?

If you could push a new snapshot that dropped the tables on the subscriber and build the thing up from scratch, then things would sync-up just fine. But in this scenario if you drop the subscriber tables then you've just lost your archive.

What's the best way to handle this?

Anyway, I'd appreciate some discussion if anyone has the experience/interest.



Options: ReplyQuote

Written By
Replicating to a MySql 5.0 reporting/archive database.
November 27, 2005 07:50PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.