Windows XP SP2's new firewall features really improve the built-in firewall for Windows.  However, I was caught off guard when dealing with my remote Oracle installs.  The XP SP2 firewall agressively has blocked nearly all the ports.  This is generally a good thing.  So I opened up 1521 for Oracle expecting this to open up access to Oracle.

Instead, I kept getting timeout errors.  After some trial and error (and using the logging built into the firewall), I determined that Oracle uses many ports that seem to be changing.  I was unaware of this.  That makes it really hard to figure out which port to open up.

I found a solution though that seems to work very well.  You can not only add ports to the firewall exception list, you can add applications.  I added both Oracle.exe and Tnslsnr.exe to the exception list.  This got connectivity back up and running again.

Opening access directly to applications should also help when you don't know the port that your server is using or you want to be able to change it and have the firewall dynamically handle it.

With SQL Server 2005 Beta 2 out the door, it's time to start sharing information on the upcoming features.  One of the big features I've been working on the past three years is Heterogeneous Replication.  Put simply, Oracle is a fully supported publisher for SQL Server 2005 Replication.  I'm going to share some general overview information over the next few weeks.

So, what is it and why should you care?  Basically, if you have an Oracle server that you want to serve data to SQL Server subscribers, this feature is for you.  The integration of Oracle support is really deep.  We have tried very hard to make Oracle publishers look just like SQL Server publishers.  We support Oracle publishers for transactional and snapshot replication (no merge).  Before everyone asks me, merge wasn't a target mostly because we didn't feel it fit the typical Oracle scenario (i.e. large legacy Oracle servers) and it would have been just that much more work to get it in.  We'll definitely consider it for post-2005 releases but the scope of just tran/snapshot was more than enough for this release.

The new SQL Server 2005 Workbench has full support for Oracle publishers so you can pretty much point and click your way through.  For those of you that roll up your sleeves, most of the replication API stored procedures have a new parameter (@publisher) that is only used for Oracle.  Subscribers barely even know they are dealing with Oracle data.  Once the data is in the distributor, it looks no different than data that originally came from SQL Server.

The release in SQL Server 2005 Beta 2 is pretty much feature complete.  I'm spending most of my time now between bug fixes and some performance rewrites.  But you should be able to start investigating scenarios now with the beta.

Future posts will cover suggested scenarios and the architecture in some detail.

I am a developer at Microsoft working on SQL Server Replication technology.  In particular, I work on replication with heterogeneous sources like Oracle.  I hope to post on SQL Server replication as well as how to integrate other database systems with SQL Server.