Replication and log shipping (2005) questions - advice on approach

[cmwright]

Hi all,

I need advice on a strategy to create a reporting instance of a database. There are difficulties involved. I am using SQL Server 2005 Enterprise edition.

The Situation:
1. The original data is in Oracle 10g.
2. The Oracle data is a star schema.
3. This data needs to find its way across the globe to a SQL Server to be used for reporting.
4. Changes in the Oracle schema make Oracle to SQL Server replication impractical.
5. The connection to the server in another part of the world is quite slow.
6. The Oracle database is updated once per hour.

My Thoughts:
I could replicate to SQL Server here since every schema change means I'd have to redo the replication properties. Then, I could in some manner get the data from SQL Server in here to SQL Server out there. I'm worried about timeouts during replication.

I've never done replication, log shipping, or database mirroring, so I need to know what experienced people would do given this scenario. What's my best option?

If I need to provide more details, please ask. I am quite grateful for any assistance/advice I can get.

-Chris

 

[mrdenny]

As you said, do to schema changes replication isn't the best option.

Log shipping won't work as you can't ship logs from Oracle to SQL (or from SQL to Oracle for that matter).

Database mirroring also won't work for the same reason.

Your best bet will probably to create an SSIS package which moves over just the needed data (ie, just the data which has been updated since your last refresh).

Be sure that you setup the feed to query the SQL Server and find out when the last feed was so that you get anything missed. This is very important when dealing with a slow WAN link that you need to drag the data over.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[cmwright]

Hi mrdenny,

Thanks for responding. What do you think about this possible solution? Would this work?

1. Set up an SSIS package to pull the data from Oracle into a local SQL Server.
2. Set up replication from the local SQL Server to a remote SQL Server.
3. Run scripts and alter the package as necessary on the local SQL Server.

I know that you said log shipping wasn't appropriate betweeen Oracle and SQL Server. Having a local SQL Server precludes that, however.

I am still concerned, though, that there could be timeouts that cause the replication to fail. Is there a timeout value in 2005 for replication?

Given that both ends would be SQL Server, would mirroring or log shipping be better options since the latency could get high?

I like the SSIS suggestion. That's probably the best way to go locally between Oracle and SQL.

Would replication, log shipping, or mirroring be appropriate once I get a SQL source server?

I should mention that these SQL servers would not be on the same network. I think that I saw that log shipping would require you to be on the same network. Is that right?

Thanks so much!

-Chris

 

[mrdenny]

Yes, Log shipping requires that you are on the same network (if you use the Microsoft log shipping) as you have to be able to access the other servers hard drive.

Mirroring doesn't give you a readable copy at the destination.

If this was me, I'd probably build my own log shipping solution using ftp to transfer the data between the two sites.

You can use my write your own log shipping faq faq962-5754 as a guide for most of it. The FTP part isn't included in my FAQ.

Replication does have timeouts still, and you will probably have all sorts of time outs going accross such a slow WAN link.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[cmwright]

Hi mrdenny,

Thanks for the advice. FTP is not possible because of security at my location, unfortunately. Very special case. Perhaps SSIS is the way I'll have to go. Useful FAQ, though! Thank you so much!

-Chris

 

[Catadmin]

BTW, you can set up Database Mirroring and snapshot the mirrored Database for a read only report server, but having never done it myself, I don't know what kind of performance issues happen with this.

The way it works, if I understand it correctly, is the snapshot goes to the mirrored db for any data the snapshot doesn't contain which in turn forwards the request to the original server for any data the mirror doesn't contain. This happens because the snapshot only contains the "original" data that has been changed, not any data that hasn't been changed, and there's something similar happening for the mirror. Only I can't remember what it is.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"