Unanswered: Federation/replication - DB2 & Microsoft SQL Server 2000
I'm new to this forum, though not new to sql server am new to DB2.
We are running Microsoft SQL Server 2000 but we have recently acquired similar business in another country which runs IBM DB2 database system. We are interested in incorporating the data from DB2 in our system. The tables are slightly different in DB2.
Q. what is the best way to integrate the two systems? Should we take the route of a federated system or replication of data?
We are interested in knowing what is happening in the other business (DB2) but we what the two systems to operate in parallel for about a year, before we come up with the best solution.
The company is after a short term and long term solution.
Can any one please help or point me to the right place?
What you are describing is a consulting engagement. I would STRONGLY suggest that you consider paying someone with an appropriate level of expertise to come examine your systems and give you a professional opinion. While you can get good ideas from the internet, you really need something more solid than a good idea, and you really need someone with the experience to know what questions you didn't ask and the time and energy to both ask and answer them for you. I'm not trying to sell you anything, and I'm not volunteering for the engagement, I'm just trying to keep you from hurting yourself!
With that said, I'd suggest you consider using sp_addlinkedserver. It will allow your SQL Server to "see" the AS400 data, and that will be a stepping stone to allowing you to build reports, etc from the merged companies combined perspective.
Don't give up on using the Internet to get good ideas, it is great for that. As your business grows, don't "bet the farm" on what you get from the Internet until you get to the point that you can critically examine the information and know at least enough to know if you've asked all of the relevant questions.
As a side note, you might consider one of the firms that helps firms like yours grow. There are several of them, and while they aren't cheap they can easily save 100 times what they cost. I work for one of those firms (I don't do client engagements, just IT), and I see examples of mergers that went well (and poorly) every day.
Find the appropriate expertise, even if it is expensive. As the old saying goes: If you think education is expensive, you should consider the cost of ignorance!
Thanks a lot for your advice. What you said is right we should get a professional to do the job for us and as you rightly pointed out it will probably be expensive.
I want to point out that our business is very small and we can't afford to pay a professional, however, I and my business pattern studied IT and designed the current system we are using. But that was a long time ago and we don't have much experience in integrating data from different databases plus new technologies come up every day. And its for that reason/s, that I asked the question/s on this forum to find out what is out there that we can use. Or probably some body has integrated data from the two systems mentioned and would like to give us some advice.
Thanks again for sharing your wisdom.
Any more advice on the mentioned issue will be highly appreciated.