Distributed Partitioned Views / Federated Databases: Lessons Learned

Good Distributed Partitioned Views / Federated Databases Article

Denis Gobo — Thu, 21 Jun 2007 17:47:12 GMT

The Microsoft SQL Server Development Customer Advisory Team has a nice blog post about Distributed Partitioned

re: Distributed Partitioned Views / Federated Databases: Lessons Learned

darrenweiner — Wed, 27 Jun 2007 23:01:44 GMT

Great overview/summary..thanks!

re: Distributed Partitioned Views / Federated Databases: Lessons Learned

EwanCourtney — Wed, 29 Aug 2007 10:46:54 GMT

Good article. I have a question on Definition 3, around the GOOD and BAD examples for the partitioned view.

If server1 hosts DB2005 locally, how will the same view work on server2? I can see how changing the linked server definition gives you indirection for the non-local databases, but what happens to the local database? What am I missing here? The BAD example looks to me to be the 'correct' (but inelegant) solution.

Thanks

Ewan

re: Distributed Partitioned Views / Federated Databases: Lessons Learned

tasmisr — Thu, 03 Jan 2008 20:21:56 GMT

great article. I'm also having difficulty implementing definition 3 above:

On Server1: when you create a linked server in sql server 2005, in the "New Linked Server" window / general Tab, If I use an alias like for example "Server2", and then choose "Other data source", and then I put in the server ip in the data source, it gets added, but I cannot run queries against it, I get the following error:

OLE DB provider "SQLNCLI" for linked server "SERVER2" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "SERVER2" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

but when I use the option "Sql Server" in the Sql type, it works but I cannot use the alias "Server2" in that case, hence I will not be able to do it like in definition 3.

re: Distributed Partitioned Views / Federated Databases: Lessons Learned

jadeflon — Mon, 14 Jan 2008 18:23:06 GMT

Good article.

May you post more articles about this theme.

More detail would be welcome.

regards

re: Distributed Partitioned Views / Federated Databases: Lessons Learned

clientserverarch — Mon, 28 Jan 2008 21:47:24 GMT

Good article.

We have VLDB on SQL2000. Our primary table contains slightly less than 1B records, and I am anticipating 450M records per year.

Thus we have implemented distributed view on 2000 across 6 clusters, and it works reasonably well. As we are migrating to SQL2005, I am not certain of the maturity of partitioning to migrate to that architecture, as well as having everything on a single server. My first reaction is to architect a hybrid solution, where I would use partitioning on the local servers, and then have a distributed view on top of all the partitions.

I have concern implementing the above as MS is saying that distributed view is going to be removed from future releases, but I doubt that partitioning would be able to handle our data volume.

Any thoughts would be appreciated.

re: Distributed Partitioned Views / Federated Databases: Lessons Learned

kevincox — Mon, 28 Jan 2008 22:33:56 GMT

Distributed view is not going to be removed from future releases, at least not any time soon.

Table Partitioning would handle your data volume as 1B records is not very much in today's world. Of course that depends on what type of hardware you are running on. But if you choose table partitioning you may be able to minimize the impact by marking some of the partitions with older data as read-only.

Your hybrid solution could also work well because you can use table partitioning on the local servers as well as use a distributed view on top of all the tables. The Optimizer is very good in this case to only touch the tables and partitions it needs if you supply all the partitioning keys in the queries.

re: Distributed Partitioned Views / Federated Databases: Lessons Learned

jadeflon — Tue, 29 Jan 2008 22:38:04 GMT

If identities are not allowed in distributed views.

May you help me underestand how do you handle non significant pk fields? do you use a separated table with a counter per table?

Where can I get an detailed example?

Thanks

re: Distributed Partitioned Views / Federated Databases: Lessons Learned

kevincox — Thu, 31 Jan 2008 03:23:58 GMT

A GUID is the most popular solution instead of using Identities when you want to create a partitioned view.