A few quick tips for using SQL Server 2005 Linked Servers
I've just completed a short,rushed project that involved moving data from one SQL Server 2005 in a DMZ to another SQL Server 2005 server inside the network. The database in the DMZ is deployed on a per-web site basis, and there can be many copies of the same database sending data back into the central database. I initially choose a push model, which would ease the deployment burden, as each copy of the website database could point back to the central server to push its data back. If a new site was deployed, there was no configuration necessary to get the data pushed back to the central location apart from setting some meta-data in a particular table, and scheduling a stored procedure to run.
As is typical in rushed projects, there was no consultation with the infrastructure guys about the feasibility of the design, and we didn't find out a few issues till we where scheduled to deploy. The problems (in the order that they occurred) where:
For someone that has always had network guys to set up DTC, its not as straight forward as I'd assumed. I spent ages setting all the correct DTC configuration values on the remote machine without ever enabling it on the central server. When I tried to issue a BEGIN DISTRIBUTED TRANSACTION from the central server, I got an error saying that the remote server had disabled its support for distributed transactions. The error message was wrong - the local server hadn't been set up for distributed transactions. Turning on DTC on both servers (obviously) fixed the problem.
Getting DTC working through a firewall is hard. This KB article documents how to do it, but as there was no name resolution between the web server and the central server (to quote the KB article 'DTC also requires that you can resolve computer names by using NetBIOS or DNS'), distributed transactions weren't a goer.
@@IDENTITY and scope_identity() don't flow across servers. Using the push model, I needed to set a foreign key in one of the inserted tables, which means I needed to know the primary key on the row inserted earlier in the stored proc. In the end, I had to go to a pull model, which was a pretty easy change.
While it would have been nice to identity and sort out all these issues before we started baning out TSQL, the project scope was small enough that we could resort to heroics to overcome design short-comings.