federated query architecture advice

I'm involved in a project building a federated query app. There is an ASP.NET web app talking to a central SQL Server 2005 db. Users input query parameters to the web ap, the request is submitted as a stored procedure to a central db, and from there it is distributed to the selected remote SQL Server 2005 db servers with similar but not necessarily identical schemas where the actual data resides.

We must wait for all servers to execute the query and return the results, then aggregate those query results and present them to the web app user.

The question is, what is the best way to design this with Server 2005? We need to be able to initiate multiple concurrent queries, then wait (up to some max timeout value) for all responses to return. What way(s) do I have with SQL Server 2005 to (1) initiate multiple queries, and (2) wait on the events (queries) to complete, up to some max timeout value?

I've looked at Service Broker some but that (maybe) seems like more than we need because all we're doing are queries. The tricky part seems to be initiating concurrent queries and waiting for all responses. Any advice or comments are appreciated.

Using SQL Server 2005 and ADO.NET 2.0 will enable you to do async queries / commands to the database. THis might be the best solution for you.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||I was hoping for a single stored procedure (sp) with a parameter naming the data sources to be queried. The sp would start multiple parallel queries, then consolidate the results into a single result set to return to the caller. Any ideas along these lines?|||

Just to get some understanding why do need to use federated searches. Are you running into scale problems?

The one solution is to use partitioned views, this enables the Query Optimiser to figure out which servers to query.

|||

We have multiple SQL Server databases with essentially identical schemas. These databases are located remotely from our central server (assuming we'll connect via vpn or...?). The idea is for a stored procedure call to be made to the central server that will in turn query the remote servers, with the consolidate / federated result set from all servers involved returned to the caller as a single result set.

Looks like I need to look into partitioned views; I presume this requires linked servers? If so, I've heard about problems with links staying "up" over extended periods when the remote servers are not connected to a LAN - any feedback on this question?

|||

Federation is for searching across multiple servers in a single location (in my opinion) not bringing remote data together.

Based on what you have said I believe you should be looking at a replication model. Each of your sites replicates data to the central point. The queries at the central point are then on local copies of the data.

Doing distributed queries when you have a poor connection is just not practical. Replication can handle this level of connectivity.

If you have Site A, Site B and SIte C and you need to have users in all sites seeing data from the other sites, you probably want to look at peer to perr transactional replication.

|||

Normal replication is not an option. Here's the scenario: we have multiple heterogeneous "source" systems (Oracle, Informix, SQL Server, etc.), each unique and different. Co-located with each source system is a SQL Server db. On a daily basis, the relevant tables from the source systems (which can be several GB's in total) will be copied into equivalent SQL Server tables using SSIS. (This is why replication to a central db is not an option; take this as a given, trust me). From the source schema, the data will be transformed (again via SSIS) into a "provider" schema on the co-located server. These provider schemas will be similar but not identical. We want to query and aggregate results from the provider schemas.

For example, if we have 4 sites (A, B, C, and D), and the user wants to query sites A and C, we want to fire-off parallel queries at site A nd site C. The rowset structure from the two sites will be identical, but the queries that produce them will not necessarily be identical. The final step is to combine the result sets from sites A and C and return the combined rowset to the stored procedure caller.

Once again, the co-located SQL Server boxes will be accessed over the Internet (via vpn?).

|||When you say trust me are you referring to office politics or are you referring to a technical problem? It appears (to me) that the co-located Sql Server databases (sources) could replicate to a single SQL Server database (target) from which querying could be performed. The target database could define a field to show the source of the data co-located Sql Server databases.

Feasibility of Filtering in this Scenario...

Background:
I'm a young developer working on a project that involves merge replication between SQL Server 2005 Mobile Edition and SQL Server 2005 and I've been having a hard time wrapping my head around exactly how to implement the best filters for the subscription(s) and publication(s).

Users of our application are Auction goers who collect data about AuctionItems in a mobile db and sync that information with a central db once he or she is finished. The central db is, obviously, pre-populated with most of the information about any/all AuctionItems. Central db information is available to view/change via web access.

Multiple users can change the same Auction data so there will be overlapping partitions.

What I would like to do is:
Present the user with a list of all available Auctions for the next 2 weeks (select * from Auctions where blah blah blah). This is a simple publication to create. Now, I'd like to be able allow the user to select AuctionID 4, AuctionID 3, AuctionID 12 and then, via a seperate AuctionListing publication only download AuctionItems that apply to those IDs.

Obviously, the publication has to be created and include the AuctionItem table and all necessary related tables/columns. But how do I create a parameterized filter based on that AuctionID?

Can a subscription be "dynamically created" once those AuctionIDs are known? Obviously, I don't want to bog my mobile devices down with tens of thousands of auction listings to auctions the user is not planning to attend.

Basically, my question is: am I wasting my time or is there some clever manipulation of HOST_NAME(), SUSER_NAME(), both, or some other method that I've missed that can get only those Auctions where the the ID matches one selected by the user at runtime?

You can do it with either one publication or 2 publications.

With one publication, I can think of dynamically populating a table with the IDs from what the user chooses. The filter will be based on IDs present in this table. However note that populating the table and synchronization need to be in 2 different transactions because if they are in the same one, enumerations will be looking at not correct data.

Let me know if you have trouble setting the filter.

|||Could you elaborate a little more on some of the parameters/steps involved involved with the filtering process?

How would we do it, exactly, in one publication? Do I need to use a

mix of RDA and merge replication (or is that even possible)?

In my head I think I know how it needs to work but, as a SQL Server beginner (just started using it this year), I don't know if I can describe the problem and solution eloquently with the available tools.|||Mahesh:

I've decided to go with one publication. How can I separate the population of the database dynamically from the sync call that I will need to make to pull the results down? How do split this action into separate transactions.|||One more thing to consider: I can see the feasibility of using two publications to accomplish this tasks and in some ways it might be easier. Nevertheless, consider this scenario.

We use a table called 'AuctionDownload' that is populated with the AuctionIDs and some string value that we can filter on using HOST_NAME(). This table will be included in the publication along with the main 'Auction' table.

User A subscribes, calls sync, gets a list of auctions and chooses IDs 1 and 2 that he wants listings for. He populates the rows in 'AuctionDownload' on his mobile device and syncs. That's fine.

However, imagine User B is also using the system at the same time as User A. The first time he calls sync to get the list of available auctions, his EMPTY AuctionDownload table will need to be merged with the central AuctionDownload table. Thus User A's values are erased before he or she could call sync on a different publication to get the actual listings!

Is there a work around or am I imagining this syncing scenario incorrectly.

Failure to create a Report Project within Visual Studio 2003

I'm having a problem creating a Report Project within Visual Studio. I have
VS.NET 2003 installed and I had then installed Sql Reporting Services with
the latest service pack. If I try and then create a reporting project within
a pre-existing solution, it gives me the following error.
Class already exists.
I looked at the knowledge base and it seems that this behaviour is caused by
a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
add-in manager and tested again. The problem still exists.
Any other ideas on what I should try?
Thanks,
Darren.On Thu, 7 Apr 2005 07:55:02 -0700, "dmpenner"
<dmpenner@.discussions.microsoft.com> wrote:
>I'm having a problem creating a Report Project within Visual Studio. I have
>VS.NET 2003 installed and I had then installed Sql Reporting Services with
>the latest service pack. If I try and then create a reporting project within
>a pre-existing solution, it gives me the following error.
>Class already exists.
>I looked at the knowledge base and it seems that this behaviour is caused by
>a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
>add-in manager and tested again. The problem still exists.
>Any other ideas on what I should try?
>Thanks,
>Darren.
Darren,
If you try to create a new project from the File menu (choose the
Wizard option for speed) does that create a working report or not?
Andrew Watt
MVP - InfoPath|||Andrew,
Just tried creating a new report project from the file menu and I still get
the same "Class already exists" message. If I look in my local directory
where I'm trying to create the new project, a folder is created there with a
virtually empty .rptproj file with the following contents.
<?xml version="1.0"?>
<Project xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
xmlns:xsd="">http://www.w3.org/2000/10/XMLSchema">
<DataSources />
<Reports />
</Project>
I can't however, see the project in the solution explorer and it doesn't
seem to be added to the solution file. Any other suggestions?
Thanks,
Darren.
"Andrew Watt [MVP - InfoPath]" wrote:
> On Thu, 7 Apr 2005 07:55:02 -0700, "dmpenner"
> <dmpenner@.discussions.microsoft.com> wrote:
> >I'm having a problem creating a Report Project within Visual Studio. I have
> >VS.NET 2003 installed and I had then installed Sql Reporting Services with
> >the latest service pack. If I try and then create a reporting project within
> >a pre-existing solution, it gives me the following error.
> >
> >Class already exists.
> >
> >I looked at the knowledge base and it seems that this behaviour is caused by
> >a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
> >add-in manager and tested again. The problem still exists.
> >
> >Any other ideas on what I should try?
> >
> >Thanks,
> >Darren.
> Darren,
> If you try to create a new project from the File menu (choose the
> Wizard option for speed) does that create a working report or not?
> Andrew Watt
> MVP - InfoPath
>|||Darren,
This isn't something I have seen before so the following questions are
simply trying to identify more clearly your specific
setup/circumstances.
Can you describe your setup in more detail? Which add-ins do you have?
When did you disable them?
Have you ever (I assume not, but want to be clear) created an RS
report successfully on that machine?
Apart from RS is Visual Studio working normally?
Andrew Watt
MVP - InfoPath
On Thu, 7 Apr 2005 08:55:03 -0700, "dmpenner"
<dmpenner@.discussions.microsoft.com> wrote:
>Andrew,
>Just tried creating a new report project from the file menu and I still get
>the same "Class already exists" message. If I look in my local directory
>where I'm trying to create the new project, a folder is created there with a
>virtually empty .rptproj file with the following contents.
><?xml version="1.0"?>
><Project xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
>xmlns:xsd="">http://www.w3.org/2000/10/XMLSchema">
> <DataSources />
> <Reports />
></Project>
>I can't however, see the project in the solution explorer and it doesn't
>seem to be added to the solution file. Any other suggestions?
>Thanks,
>Darren.
>"Andrew Watt [MVP - InfoPath]" wrote:
>> On Thu, 7 Apr 2005 07:55:02 -0700, "dmpenner"
>> <dmpenner@.discussions.microsoft.com> wrote:
>> >I'm having a problem creating a Report Project within Visual Studio. I have
>> >VS.NET 2003 installed and I had then installed Sql Reporting Services with
>> >the latest service pack. If I try and then create a reporting project within
>> >a pre-existing solution, it gives me the following error.
>> >
>> >Class already exists.
>> >
>> >I looked at the knowledge base and it seems that this behaviour is caused by
>> >a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
>> >add-in manager and tested again. The problem still exists.
>> >
>> >Any other ideas on what I should try?
>> >
>> >Thanks,
>> >Darren.
>> Darren,
>> If you try to create a new project from the File menu (choose the
>> Wizard option for speed) does that create a working report or not?
>> Andrew Watt
>> MVP - InfoPath