Strategy for application connection to MSSQL and DB2
Our .NET developers need to run one (and possibly more in the future) applications that need to action data on both SQL Server (2008r2) and DB2 (in real time - so creating ETL from DB2 to SQL is not an option). There appears to be problems with connecting the client (some web - mostly winforms) to DB2 as drivers that can be distributed easily for app are not available. I have been asked to look into creating a linked server from SQL to DB2 with them putting stored procs in SQL that then get the DB2 data. While I know this should be possible, does anyone have experience of doing something like this and how reliable it is. I at the point of needing to recommend either that we do this or that they should implement web services for the DB2 data. This is for a largish and important project and I know that the business will want a robust and maintainable solution above all else.
If it were my project, I'd figure out why the developers can't simply get a good driver for connecting to DB2 from the app code. That should be a simple thing. I mean, [here's one of the first pages] from IBM on connecting from
ADO.NET to DB2. That's absolutely the better approach. But yes, you can set up linked servers in SQL Server to connect over to DB2 (although, if you're having problems from the app code, why will this be easier?). But, linked servers come with all sorts of issues. You're going to have to write all your queries to use the [OPENQUERY] syntax, which means dynamic T-SQL. You'll need to be very careful of [SQL Injection]. You need to do this because simply writing straight T-SQL to linked servers can be extremely cost intensive. SQL Server doesn't have statistics on a DB2 database, so queries run exclusively from SQL Server require all data, ALL data, be brought back to SQL Server for processing. OPENQUERY puts the processing onto the DB2 box (or whatever box you're connecting to) so you only move the data you need between the servers. So, with a lot of work, you can make this functional, but you'll be much better off just getting the app code fixed. :
Grant, Thanks for the reply. I am actually against the linked server idea as well. The problem we are facing is not so much getting the developer connected but distributing the necessary driver. The is a 'light' runtime client but even this is hard to distribute and does not work with the set up of the database (which is related to a large third party application). This means a not insignification install across >600 clients, non of IBMs solutions and documentation make this trivial within an environment that has updates on a weekly or even daily basis. We have now identified a setup using web services (hence only developers, 1 test machine and 1 live machine to configure) works well for this in the proof of concept I have done and seems faster than linked server (which was very tricky to setup and not wholly stable).