Distributed Partitioned Views/Federated Servers anyone?

[AngieK]

I'm trying to do some researh on the use of SQL's DPV. I'm looking for feedback from people who've actually done this production to know more about the design challenges and level of added administration required. Any information will be much appreciated. Thanks.


aK

[Markus]

No, I haven't worked with paretioned views in the real world yet, but there's a good article about some of the design challenges in SQL Server Magazine. Check out: http://www.sqlmag.com/Articles/Index...rticleID=37889

M

[Brent]

We set up a federated server structure, but ran into problems with inserts, updates, and deletes with our OLE DB applications. Apparently when you modify the tables, you have to change your transaction queries to allow these types of updates. Had to change some code, but not too painful.

[AngieK]

Originally posted by Brent
We set up a federated server structure, but ran into problems with inserts, updates, and deletes with our OLE DB applications. Apparently when you modify the tables, you have to change your transaction queries to allow these types of updates. Had to change some code, but not too painful.

Originally posted by Brent
We set up a federated server structure, but ran into problems with inserts, updates, and deletes with our OLE DB applications. Apparently when you modify the tables, you have to change your transaction queries to allow these types of updates. Had to change some code, but not too painful.

Hmmm... strange. I thought it would be transparent. What exactly did you change and what did you have to do to make it work?

Also, what kind of application is it?

Thanks for responding.

aK

[Brent]

We thought it would be transparent as well, until we started seeing this on updates/inserts/deletes to the view that spanned multiple servers...: [Server: Msg 7392, Level 16, State 2, Could not start a transaction for OLE DB provider 'SQLOLEDB'. [OLE/DB provider returned message: Only one transaction can be active on this session.]

Well, it was a choice between a full table split and reference of both the DB's in our code, instead of a partitioned view, or turning nested rollback off, which allows the full rollback of transaction. Apparently all by design. We use ColdFusion for our App Server, but any App that uses a native ODBC SQL driver most likely does not allow nested transactions.

Here are some KB.

http://support.microsoft.com/default...b;EN-US;306649

http://support.microsoft.com/default...b;EN-US;177138