Is it recommended to use two versions of SQL Server (2005 and 2008) for storing data of an application. We have a situation where we have an existing web application consuming SQL server 2005 as a database. Some enhancements in the application required us to solution a FILESTREAM data store. Now we have our data in SQL Server 2005 and associated FILESTREAM data in SQL Server 2008. Being very new to SQL Server I would like to ascertain how bad is this in terms of performance? How can we achieve atomicity across both the database versions, is it via using a linked server or some other mechanism? Any alternative solutions would be fine except that we just cannot migrate the existing application to SQL Server 2008. Thanks in advance
A:
To keep your transactions ACID, you can use distributed transactions at a stored procedure level, or from a code level using DTC (e.g. via a .net TransactionScope). DTC would need to be present on both SQL Servers, and on your App Server as well if you do the ACID from there. There is some overhead with DTC
Edit : You will also need to link the servers with sp_addlinkedserver and provide credentials with sp_addlinkedsrvlogin
nonnb
2010-08-27 10:59:48
Is it necessary to link the servers for using .NET Transaction scope?
Msdnexpert
2010-08-27 11:12:02
Hi MSDN Expert - No, with DTC and TransactionScope across the 2 servers you wouldn't need to link the servers - DTC will coordinate the 2 connections independently - whereas a solution with a SQL BEGIN DISTRIBUTED TRANSACTION would require the linked servers. Apologies for not being more clear on this.
nonnb
2010-08-28 10:19:01