jump to navigation

Oracle to SQL Server Replication-Design considerations May 21, 2013

Posted by msrviking in Configuration, Data Integration, Design, Heterogeneous, Integration, Replication.
Tags: , , , , , , ,
add a comment

I am thinking I have covered all and best of the points for one to configure a transactional replication between Oracle and SQL Server in all my previous posts. In this post I would like to give my 2 cents on design considerations and limitations of Oracle publishers that I had to keep in mind during this exercise. There is a neat list of other points published in this Technet article.

  1. Configuration of Oracle Publisher with options

    Per the above article I learnt that the Oracle publishing can be done using the Oracle Gateway and Oracle Complete options. Few things that make difference these two options is that Oracle Gateway provides better performance but doesn’t let multi-transactional publications of a single to-be or published table. Whereas in the case of Oracle Complete option the table can be published in several publications, but lesser performance.

    I tried digging in Google to see on how these two options work as described, because I didn’t try on the setup I built to find the difference. I didn’t find any reliable sources that talk about difference in detail but one of the posts by Richard Douglas (Editor in Chief of SQL ServerPedia) gives a short description “Gateway is for high performance replication solutions. Complete is when you want to transform your data or to do row filtering”. I might have to try and see what it means in here.

    I remember seeing row filtering in a transaction replication using the option of Oracle Gateway, and I am yet to find out on how this is different when Oracle Complete is chosen. For now I shall park for future tries on this limitation and share my findings.

  2. It’s quite obvious with this point (supports few objects) because the similarities that Oracle and SQL Server have at object level implementation are few. So,

    What are the objects that are supported?

    1. Tables
    2. Index-organized tables
    3. Indexes
    4. Materialized views (as tables)

    What are those objects that cannot be replicated, but can exist on published tables?

    1. Domain-based indexes
    2. Function-based indexes
    3. Defaults
    4. Check constraints
    5. Foreign keys
    6. Storage options (tablespaces, clusters, etc.)

    What are the objects that cannot be replicated at all?

    1. Nested tables
    2. Views
    3. Packages, package bodies, procedures, and triggers
    4. Queues
    5. Sequences
    6. Synonyms

    Okay with this list we know what can be replicated, what cannot be replicated? I picked the list from the above post, but was hunting to find out why we can’t replicate these objects. One simple reason that keeps coming to my mind is that “some of the above objects in the ‘cannot’ list don’t have equivalents in SQL Server”. Okay, so aren’t there workarounds for these objects? For example Packages have bundle of programmed objects like packaged variables, cursors and few others which have to be converted through a specific process, and this specific process can’t be through Replication. That exercise could be migration of objects from Oracle to SQL Server, and surely can’t be part of Replication topology. Does this apply for simple objects also? I don’t have any technical reason to explain for this too so concluding it with my 2 cents. Meanwhile I shall see if I can find better “reasons”.

    Here is a link from Microsoft on what it all means to convert some of the above listed objects. This was one of the reasons that I helped me to conclude logically that this is part of migration from Oracle to SQL Server and can’t be part of Replication at a conceptual or architectural level.

Since my setup was to test the new Oracle Publisher I didn’t have to keep tab on other design considerations, but I am sure I will have to deal with others too when this gets on to full deployment mode than a POC.

I hope this short post helps you folks out there, and happy reading.

The usual ask – please feel free to share your thoughts or comments.

Cheers!

Advertisements

SQL Express Edition in Production? Yes you could, but No you shouldn’t.. May 10, 2013

Posted by msrviking in Uncategorized.
Tags: , , , , ,
add a comment

A very important realization when one of my team mates came up to me and asked this question – Can we deploy SQL Server Express Edition in Production?

At first I said, “No”, and I was very confident of it. But because of some misled information I had in my mind I was wrong. So I had to understand who else does this and how..but strongly I don’t recommend SQL EE in Production and you would do it only if you are not too serious about the data you deal in production /business.

Here are few links which talk on this topic, and you may also feel confident of taking decision like this based on your requirements.

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/46ad8aa8-2097-49df-984c-58634e1ecfa2

http://social.msdn.microsoft.com/forums/en-us/sqlexpress/thread/4619FD15-13C3-4914-B1DD-B65D3154BD5B

http://stackoverflow.com/questions/180537/sql-express-for-production