From Interoperability to Migration: SQL Server and Linux Databases Working Together#

This is the same session I did at Tech Ed San Diego with my buddy Steve Forte... unfortunately, Steve had a problem with his flights that meant he had to leave before the session actually started, making my duet more of a solo...

Steve and I have been doing duets for years, so I dragged a couple of photos out that we had used in the past for duet sessions...

This picture was taken around 1998 at the Papendal facility near Arnhem in the Netherlands, Steve (looks awfully young, doesn't he?) and I are working on our first joint session called “XML for N-Tier”.

This photo was taken at a costume party on Halloween of 2000 in Phoenix, Steve and I are dressed as the three musketeers, our third (Tom Howe) is taking the picture.

The interoperability session focuses on how you can use SQL Server to interoperate with other databases, in this case its Oracle 10g running on Red Hat Linux Fedora Core 1. The idea was to run two Virtual PC sessions, one with Windows 2003 and SQL Server 2000, the other Red Hat and Oracle 10g.

I used Werner Puschitz's step-by-step instructions to Installing Oracle 10g on Red Hat. Even then, its tricky, lots of places to go wrong. And since I'm running Linux from a Virtual PC session, part of the challenge is getting Red Hat happy in a VPC environment. Networking is also tough - I didn't want to use fixed IPs on the VPCs, since that would cause other network problems when plugged into various conference networks. Each VPC session grabs a dynamic IP, but the way Oracle is configured, you have to alter a listener file to tell Oracle what IP you're now running under.

On the SQL Server side of things, you need to install the Oracle tools to get Net Manager, which provides communication to Oracle from the Windows 2003 environment. That's another point where you have to specify the IP address of the Oracle server.

In SQL Server itself, the key to making the connection is linked servers - and the easiest way is using sp_addlinkedserver and sp_addlinkedsrvlogin stored procedures to establish the connection. The parameters look like this:

sp_addlinkedserver @server = 'OrclDB', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'larryserv'

  • @server is the name you want to refer to the linked server with.
  • @srvproduct is the name of the database you're linking to. SQL Server checks this name carefully, if you misspelled Oracle, it won't work.
  • @provider is the name of the OLE DB provider to use, in this case its the Microsoft Oracle provider.
  • @datasrc is the name of the Oracle database from Net Manager.

sp_addlinkedsrvlogin @server = 'OrclDB', @useself = false, @locallogin = 'sa', @rmtuser = 'scott', @rmtpassword = 'tiger'

  • @server is the name of the linked server as specified in sp_addlinkedserver.
  • @useself is a flag to tell SQL Server to use the account currently logged into SQL Server on the remote server. Setting this to false means that the login will use the remote user and password supplied.
  • @locallogin is the name of the account that this login can be used by. You can set it to NULL and it works with all accounts. The account can be a SQL login or a Windows login.
  • @rmtuser is the user name to log into the remote server with.
  • @rmtpassword is the password to log into the remote server with.

Once the linked server is established, you can do queries like this:

select * from OrclDB..SYSTEM.TBLCONTACTS where lastname = 'Campbell'

You can see the only unusual aspect of this query is the table notation, indicating the linked server and the schema that the table is associated with. With Oracle, table names are case sensitive, so you have to get the case right.

In the session we discuss not only how to interoperate, but also how to migrate. The key to migration is to move the data last, not first. Build a .NET application that calls SQL Server stored procedures, and use the stored procedures to make the calls through to Oracle. That way you can build your new client without turning off the old client, and the two clients can run side-by-side for as long as necessary. Eventually, everyone will be using the new client since its getting all the new features.

You can start adding tables to SQL Server to add new features, linked servers will allow you to do joining between the two databases if you like. Eventually, when no one is using the old client any more, you can shut it down and begin moving tables from Oracle to SQL Server. You don't have to do them all at once, just move the tables as you have time, altering the stored procedures to point to SQL tables, rather than the linked Oracle tables. Ultimately, all the tables will be moved and you can shut down the remote connection.

On the other hand, you might discover that there's stuff in Oracle you can't do without, and you can maintain this level of interoperability indefinitely.

The session went very well, unfortunately its tough to give out as a demo, since everything is in VPC images... the Oracle/Red Hat image is nearly six gigabytes!

Thursday, September 16, 2004 9:09:09 PM (Pacific Standard Time, UTC-08:00) #    Comments [1]  | 


All content © 2020, Richard Campbell
On this page
This site
<August 2020>
Blogroll OPML

Powered by: newtelligence dasBlog 1.9.7067.0

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

Send mail to the author(s) E-mail

Theme design by Jelle Druyts

Pick a theme: