This discussion is archived
11 Replies Latest reply: Jan 31, 2013 12:36 PM by Catfive Lander RSS

Oracle-SQLServer Connectivity

Catfive Lander Explorer
Currently Being Moderated
It's a requirement that's been expressed in vague terms: a handful of huge (multi-million) row tables in SQL Server being joined with a handful of multi-million row tables in Oracle for reporting purposes, the aim being to do the reporting out of the Oracle database in the end. The reporting requirement is not real-time, but the data would need to be maybe refreshed overnight. The concern is that generating a report from a dynamic link between the two databases would take too long and shift too much data between the two databases; the thought is that the SQL server data would be better batch-extracted into the Oracle database overnight, so all the joining etc is done locally in that database.

Various words have been bandied around, like 'Goldengate', about which I know nothing.
I myself thought, 'isn't that what database gateways are supposed to allow happen? Why not just create [fast refreshable?] materialized views in oracle on the SS tables and then do the reporting in Oracle?'
Would plain old ODBC be enough to get the SS data into Oracle, even?

Are there other options that others have implemented?

I am just trying to gather my thoughts on the subject, never having done Oracle<->SS before. Would appreciate any thoughts/strategies/approaches.
  • 1. Re: Oracle-SQLServer Connectivity
    Aman.... Oracle ACE
    Currently Being Moderated
    I think if the requirement is to load the data into the Oracle database and from there, the report has to be generated, GoldenGate should be a good choice if not the best. I thought about the Heterogenous Services but since you want to load the data, it's not going to be helpful as it can only connect the databases. If you want to have a look at the Golden Gate, here is the documentation link for it,
    http://docs.oracle.com/cd/E15881_01/index.htm

    Hope it helps.

    Regards
    Aman....
  • 2. Re: Oracle-SQLServer Connectivity
    Catfive Lander Explorer
    Currently Being Moderated
    Ah. This is exactly the sort of information I was after.
    Are you saying I can't issue an Oracle command to 'create table A as select * from Z@mssql' once HS is installed? (Not sure what "can only connect the databases" means otherwise).

    Thanks for the input.

    Have you used Goldengate? Is it reliable? Costly? Complicated to administer?
  • 3. Re: Oracle-SQLServer Connectivity
    sb92075 Guru
    Currently Being Moderated
    Catfive Lander wrote:
    Ah. This is exactly the sort of information I was after.
    Are you saying I can't issue an Oracle command to 'create table A as select * from Z@mssql' once HS is installed? (Not sure what "can only connect the databases" means otherwise).
    You can't do DDL across DBLINK; regardless of "flavor".
  • 4. Re: Oracle-SQLServer Connectivity
    Catfive Lander Explorer
    Currently Being Moderated
    Well, I know it wasn't very clear, but I'm not doing DDL across a database link. I'm creating a table in the local oracle database, based on a select of data from a remote database. That, I know, works perfectly fine in an oracle/oracle setup:
    SQL> create table t as  
    select table_name from all_tables@ets 
    where table_name like 'ORDER%';
    
    Table created.
    I agree that doing 'update something@ets set col=value' is a non-starter, but that's not the issue.

    I want to do 'create table as select * from table@mssql'. Not doable via gateways??
  • 5. Re: Oracle-SQLServer Connectivity
    Justin Cave Oracle ACE
    Currently Being Moderated
    Assuming you use the ODBC gateway to create a database link between Oracle and SQL Server, it would be entirely possible to create a materialized view in the Oracle database that selects data from the SQL Server database and set it to do a complete refresh every night. That's a perfectly reasonable approach. It's also what I would consider one possible method of doing an "overnight batch extract" of the SQL Server data.

    Is your overnight batch window large enough to copy all the data from all the tables from SQL Server to Oracle? If so, that would be a relatively easy option to implement.

    Incidentally, it is also perfectly possible to
    update something@ets set col=value
    though you may have issues with transaction integrity since the ODBC gateway doesn't support two-phase commits (if memory serves, I haven't read that manual lately).

    GoldenGate would be more appropriate if you want to allow incremental updates (i.e. only replicating changes from SQL Server to Oracle rather than replicating every row every day) or if you want more of a real-time replication process. Of course, GoldenGate requires additional licensing. You can also use Streams for heterogeneous replication which saves on license fees but isn't nearly as well suited to heterogeneous replication environments as GoldenGate. Oracle's statement of direction is to move toward GoldenGate and not to enhance Streams so that may also be a consideration.

    Justin

    Edited by: Justin Cave on Jan 31, 2013 10:55 AM
  • 6. Re: Oracle-SQLServer Connectivity
    Catfive Lander Explorer
    Currently Being Moderated
    Thanks Justin. That's helpful.

    I am not sure we'd want to pull millions of rows across every night... but it might be possible to do a MERGE so that only new and changed records get pulled across. Haven't begun to think of how that might scale yet.

    I take it creating a materialized view on a SS table is a no-no, then? Thinking about it, it would make sense for it to be.

    I get the idea that Goldengate is more for real-time, or incremental, data pulls. Have you any rough-and-ready idea of licensing costs on that? No specific numbers, but just a ball-park idea ("Cheaper than Partitioning, but more expensive than Tuning and Diagnostic packs" for example??) I realise the sales rep is the only way to go for a definitive answer, but I'd rather not bother her if I know ahead of time it's golden-plated as well as -gated. Suppose the MS Server was a dual quad-core and the Oracle database was on a 16-thread Sparc9 processor, for example? I went to shop.oracle.com and said '16 cpu perpetual'; and once I'd recovered from my fainting fit, read $273,000. Just don't know if that's roughly what things might turn out to be or not, though.

    Anyway... you have certainly helped me start to ask the right sort of questions of my own mind. So thank you.
  • 7. Re: Oracle-SQLServer Connectivity
    Aman.... Oracle ACE
    Currently Being Moderated
    Catfive Lander wrote:
    Ah. This is exactly the sort of information I was after.
    Are you saying I can't issue an Oracle command to 'create table A as select * from Z@mssql' once HS is installed? (Not sure what "can only connect the databases" means otherwise).
    I don't know TBH because I didn't try doing that.
    Thanks for the input.

    Have you used Goldengate? Is it reliable? Costly? Complicated to administer?
    Yes, I have used it. Reliable, yes and also, to some extent, much simple compared to Streams in both usability and troubleshooting. Costly, I am not sure that I know it. You can search for "gavin soorma golden gate" in Google. He has given some articles about how to set up things in GG from the scratch.

    Regards
    Aman....
  • 8. Re: Oracle-SQLServer Connectivity
    Justin Cave Oracle ACE
    Currently Being Moderated
    Catfive Lander wrote:
    I take it creating a materialized view on a SS table is a no-no, then? Thinking about it, it would make sense for it to be.
    It's perfectly valid to create a materialized view on the SQL Server table. That just requires that you do a complete refresh (since SQL Server doesn't know how to create a materialized view log).
    I get the idea that Goldengate is more for real-time, or incremental, data pulls. Have you any rough-and-ready idea of licensing costs on that? No specific numbers, but just a ball-park idea ("Cheaper than Partitioning, but more expensive than Tuning and Diagnostic packs" for example??) I realise the sales rep is the only way to go for a definitive answer, but I'd rather not bother her if I know ahead of time it's golden-plated as well as -gated. Suppose the MS Server was a dual quad-core and the Oracle database was on a 16-thread Sparc9 processor, for example? I went to shop.oracle.com and said '16 cpu perpetual'; and once I'd recovered from my fainting fit, read $273,000. Just don't know if that's roughly what things might turn out to be or not, though.
    As you're well aware, you'd need to chat with Oracle Sales to get a final price-- a great deal will depend on what sort of discount you can negotiate off the list price ($17,500/ processor). Oracle's licensing definition of a processor is different than a normal person's definition, though. If you have 16 total cores, I believe all Sparc chips use a core factor of 0.25 so you'd need 16*0.25 = 4 processor licenses ($70k list). But this is where you'd need to chat with Sales to make sure that my math is correct and to figure out what sort of a discount you can get off list (this often depends on whether you can wrap up your request with other requests from your organization).

    Justin
  • 9. Re: Oracle-SQLServer Connectivity
    jgarry Guru
    Currently Being Moderated
    Catfive Lander wrote:
    Well, I know it wasn't very clear, but I'm not doing DDL across a database link. I'm creating a table in the local oracle database, based on a select of data from a remote database. That, I know, works perfectly fine in an oracle/oracle setup:
    SQL> create table t as  
    select table_name from all_tables@ets 
    where table_name like 'ORDER%';
    
    Table created.
    I agree that doing 'update something@ets set col=value' is a non-starter, but that's not the issue.

    I want to do 'create table as select * from table@mssql'. Not doable via gateways??
    Some DDL has issues over links, like you can't do a truncate over a link. Some MV has issues, like you need a primary key. Some people think ODBC connections are flaky (I've given up on them myself, so I don't really know). Millions of rows aren't particularly big tables in Oracle, but refreshing them over a link might not be the quickest way. You can do DDL over gateways indirectly, see http://docs.oracle.com/cd/E11882_01/server.112/e11050/majfeat.htm

    Depending on your network speed between the boxes, you might want to extract the data on the sql side, and then either move it over and sqlldr it, or sqlldr it as a client from the SS side. If the network is too slow, compressing/decompressing the data may make a difference. This is like the big cheap, dumb rocket approach. Not as cool as space shuttles, but where are we with space shuttles?

    You need to define the limits of what you are doing with the connectivity. For "just reporting with an overnight update" you really don't need much. Sometimes it makes sense to say "we can do this much on the cheap, if you want to do that much, start saving your pennies." Everyone always wants everything up to the second, until they have to pay.
  • 10. Re: Oracle-SQLServer Connectivity
    Gamblesk Explorer
    Currently Being Moderated
    Couple other options that might be doable depending on your circumstances

    1) SQL Server Replication - It has been a couple of years since I did this but it is doable. SQL Server can push changes directly to Oracle. This could be done either transactionally or snapshots. Last time I worked with this the snapshots were inserting row by row instead of bulk loading but that was SQL 2005 and may have been addressed in future releases/drivers.
    Transactional replication worked just fine.

    2) SSIS might be an option worth exploring as well. SSIS with the Attunity Oracle drivers can bulk load data pretty quickly. Kind of depends on how many millions or rows there really are.

    I do not believe there is any additional license needed for replication and SSIS can be used on Enterprise Editions but that would have to be verified first to be sure.

    Edited by: 889915 on Jan 31, 2013 10:21 AM
  • 11. Re: Oracle-SQLServer Connectivity
    Catfive Lander Explorer
    Currently Being Moderated
    Thanks to everyone. I have lots of options to research now! Exactly what I was after. I won't mark the question as 'answered', because it was open-ended in nature, but I hope I've awarded all the participants their 'helpful' points, because you all have been.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points