Networks

Ask something
Question Locked

How do I link an outside server to MS SQL 2005 server?

0 helpful
6 replies

by cc3p0

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have been working on our SQL server for quite some time. I am in a bump in the road and would appreciate any help that anyone would be willing to offer. I have a production MIS system that runs on an IBM box and has AIX version: 05.03.0008.0000 TCP/IP as the base operating system. I am trying to transfer/link data from the AIX to the SQL server 2005. I know that I have to use a .dll but am stumped as to which one I should use. I want the data on SQL to be able to update as clients enter data into the AIX server. Does anyone have any suggestions or comments? If i didn't provide enough info and you need more to help I would be more than willing to elaborate more on any information you need. Thanks a bunch.
CC

6 replies

by robo_dev

You have an application that is using the database on your AIX server and you want the same application to write data to your SQL server at the same time?

What interface are you using for the AIX application? Web? X-windows? Some custom client front-end?

Do you plan to re-write the front end application, or have the data movement happen behind-the-scenes? (database-to-database)

The most typical approach to making this work is to transfer/synchronize data from database-to-database.

Typically you would stage the data on the AIX box (assuming that this is a DB2 database) and do a periodic update to the SQL database, after the data has been validated.

There are replication/synchronization tools built into SQL server and DB2, but you may need some middleware such as IBM WebSphere or InfoSphere depending on how complex your database is and what your requirements are.

by cc3p0

Really what I want to do is perhaps have the SQL server updated a couple of times a day it doesn't have to be instant.
The interface that I use is Unix through pretty much any terminal. The clients use a custom interface called Browser User Interface (BUI). This interface is a little less complicated for clients to use.
I would really like to have the data movement happen behind the scenes. I have a script that I use to dump the data. The script reads the DST structure & dumps a file for each record it finds with the file name of the record header DST. Standalone DSTs are dumped with the top layer only in a file DB##. This is called by a unix wrapper script to export the data. So, I have data but SQL can't read it.

by robo_dev

So the dumping of the data...does that come from a DB2 database on the AIX box? or somewhere else?

So it sounds like what you need is basically an automated import utility for SQL server? Since MS SQL is so popular, there are tons of these...

Google found:
http://www.sqlmanager.net/products/mssql/dataimport

I am not familiar with 'DST structure' files?

It's easy to import most things into SQL server, but sometimes you need a utility to parse, re-map fields, do data conversion,etc.

by cc3p0

Yes the dumping comes from the DB2 on AIX. DST(data specification tables), This is the DB, Catalog would be the record header and the tables and views and everything else are known as transaction. I really appreciate all of your help, it has been informative. If I get it working I'll let you know, thanks a bunch.
CC

by robo_dev

Here you go:

Conversion of DB2 Data to SQL Server through DTS

http://www.databasejournal.com/features/db2/article.php/1444051/Conversion-of-DB2-Data-to-SQL-Server-through-DTS.htm

And most importantly:

http://www.starquest.com/

"StarQuest Data Replicator (SQDR) provides fast, reliable data replication between DB2, Microsoft SQL Server, Oracle, Informix, and MySQL databases. It provides a centralized replication management dashboard including event notification, scheduling and powerful configuration, customization and control of all replication activities and includes bundled support for snapshot (full refresh) replication, and features checkpoint restart/recovery, bulk loading and parallel streams."

This discussion is closed to new replies.