query cross federated tables in different federation members

Question

If I want to query customer order history based on custom zip code: 98052,  However, customer w/ 98052 zip code are in number of Federation members.  Is it even possible query directly ? if so, how

---

Max

Answer 1

Hi Max,

You should be able to achieve what you described using Fan-out Querying for Federations in SQL Azure. There is a very nice (2 part) article, written by Cihan Biyikoglu, about this technique:

What is a fan-out query?

Federations provide a model for partitioning parts of your schema over to multiple member databases for harnessing scalability of many nodes. However applications still need for querying all of the data across federation members. Fan-out is a technique for querying data in your federation, across many federation members. Fan-out queries are much like map/reduce in that it is formed in 2 parts;

Member query is the piece that is sent over to all members involved in the query and
Summary query is the query that is the post processing piece to allow condensing the results from the member query to desired final result-set.

With fan-out queries the member query is always there but summary query may not be needed. For example if you are simply doing DML (we’ll have some examples like data pruning or reference data management etc) or DDL (we’ll look at schema deployment in detail below), fan-out would only have a member query but no summary query is needed. It is only when you need post processing, you need the summary query.

You can find the article here: Part 1, Part 2.

Hope this helps!

---

Best Regards,
Carlos Sardo

Answer 2

i could be wrong, but it seems only using fan-out query utility page (http://federationsutility-scus.cloudapp.net/) to get results from all members ? I need to do it pragmatically, mean in my code. I can't use this utility page.

i also need do data aggregation like count, ave, max/min, like summary queries

---

Max

Answer 3

Hi Max,

You can find the code in the article bellow (Part 1), therefore, you can do it programatically. The web utility tool is a proof-of-concept. 

You can also perform data aggregation using summary queries. I strongly advise you reading the complete article!

Hope this helps!

---

Best Regards,
Carlos Sardo

Answer 4

thanks.. will try out and confirm

---

Max

Answer 5

Carlos, like I mentioned in my post that I do not want to use the utility page. It is application approach. I need query over SQL management studio. Likes look not possible.

---

Max