Federated server table partition

In a federated server, you create the same table on more than one server and
use a view to link them all together. I have two questions:
1) Can modulo division be used to determine which server a row goes on?
My reason is that modulo division will give the most even
distribution of the data.
2) Can I put a 2nd table on the same server, but in a different filegroup?
The DDL will get complicated and I want to start with 6 table
fragments on 2 servers, eventually moving to 3, then 6 servers, without
having to make wholesale changes to the DDL.
Oops: SQL Server 2000
"JayKon" wrote:

> In a federated server, you create the same table on more than one server and
> use a view to link them all together. I have two questions:
> 1) Can modulo division be used to determine which server a row goes on?
> My reason is that modulo division will give the most even
> distribution of the data.
> 2) Can I put a 2nd table on the same server, but in a different filegroup?
> The DDL will get complicated and I want to start with 6 table
> fragments on 2 servers, eventually moving to 3, then 6 servers, without
> having to make wholesale changes to the DDL.
|||Answers inline.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:A4D5D4C5-75C5-4B19-9BD9-E9B318787460@.microsoft.com...
> In a federated server, you create the same table on more than one server
> and
> use a view to link them all together. I have two questions:
> 1) Can modulo division be used to determine which server a row goes on?
> My reason is that modulo division will give the most even
> distribution of the data.
Yes, but then the query optimizer will not be able to take advantage of
Distributed Partitioned View, or even the indexes on table partitioning.
> 2) Can I put a 2nd table on the same server, but in a different filegroup?
> The DDL will get complicated and I want to start with 6 table
> fragments on 2 servers, eventually moving to 3, then 6 servers, without
> having to make wholesale changes to the DDL.
Absolutely! Some people run all the member tables on DPVs on the same
database. However you are not getting scale out performance this way. If you
have large data sets being returned you should use different servers, if the
results sets are small the network hop becomes the bottleneck and you should
move all tables locally.
|||Questions inline.

> Yes, but then the query optimizer will not be able to take advantage of
> Distributed Partitioned View, or even the indexes on table partitioning.
Even if the rows are accessed my the IDENTITY column (which is what I want
to do the modulo on)? I'm thinking of a title/item structure, or a customer
structure.
The lookup of the number would either be from a search engine, or a seperate
lookup table.

> Absolutely! Some people run all the member tables on DPVs on the same
> database. However you are not getting scale out performance this way. If you
> have large data sets being returned you should use different servers, if the
> results sets are small the network hop becomes the bottleneck and you should
> move all tables locally.
This is understood, however, I was thinking about this as more a way to keep
DDL changes to a minimum when expanding the number of servers in the cluster,
while at the same time reducing hard drive and I/O controller (and posibily
buss) activity.
Also, I intended seperate NIC's for the DB Servers to talk to each other.
One other question.
I saw a reference that said only the index for the key on a DPV was allowed.
Does that mean that if I wand another index, I need to build a seperate data
structure that will contain the PK for the table?
|||If your queries are by identity value, yes it should.
I would advise you to test this out with a representative load to see if it
will work.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:70BF14C6-7CC8-4098-BC5D-53B9A79EE0D1@.microsoft.com...
> Questions inline.
>
> Even if the rows are accessed my the IDENTITY column (which is what I want
> to do the modulo on)? I'm thinking of a title/item structure, or a
> customer
> structure.
> The lookup of the number would either be from a search engine, or a
> seperate
> lookup table.
>
> This is understood, however, I was thinking about this as more a way to
> keep
> DDL changes to a minimum when expanding the number of servers in the
> cluster,
> while at the same time reducing hard drive and I/O controller (and
> posibily
> buss) activity.
> Also, I intended seperate NIC's for the DB Servers to talk to each other.
> One other question.
> I saw a reference that said only the index for the key on a DPV was
> allowed.
> Does that mean that if I wand another index, I need to build a seperate
> data
> structure that will contain the PK for the table?
sql

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.

federated databases

I am wondering if federated databases is reliable enough
for a large production server. I am seeking opinion of
those who are using federated databases in in large
production environment only.
Thanks.Reliable enough is a relative. If it wasn't reliable it wouldn't be in the
product but you might want to consider scaling up instead of out. It's a
much simpler technology and with less servers involved there is less chance
of something breaking.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2d7be01c469d4$9d71a330$a301280a@.phx
.gbl...
> I am wondering if federated databases is reliable enough
> for a large production server. I am seeking opinion of
> those who are using federated databases in in large
> production environment only.
> Thanks.
>

federated databases

I am wondering if federated databases is reliable enough
for a large production server. I am seeking opinion of
those who are using federated databases in in large
production environment only.
Thanks.
Reliable enough is a relative. If it wasn't reliable it wouldn't be in the
product but you might want to consider scaling up instead of out. It's a
much simpler technology and with less servers involved there is less chance
of something breaking.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2d7be01c469d4$9d71a330$a301280a@.phx.gbl...
> I am wondering if federated databases is reliable enough
> for a large production server. I am seeking opinion of
> those who are using federated databases in in large
> production environment only.
> Thanks.
>

federated databases

I am wondering if federated databases is reliable enough
for a large production server. I am seeking opinion of
those who are using federated databases in in large
production environment only.
Thanks.Reliable enough is a relative. If it wasn't reliable it wouldn't be in the
product but you might want to consider scaling up instead of out. It's a
much simpler technology and with less servers involved there is less chance
of something breaking.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2d7be01c469d4$9d71a330$a301280a@.phx.gbl...
> I am wondering if federated databases is reliable enough
> for a large production server. I am seeking opinion of
> those who are using federated databases in in large
> production environment only.
> Thanks.
>

Federated database VS single database server

Is there any expert in designing federated database ? I need a reference in designing a federated database. Also a comparison - Federated database VS single database server. I need a information in decided what solution i should go for.
Best regards,
TawanaCheck out the Microsoft Patterns and Practices web site. There is a white
paper there on designing federated databases along with best practices and
comparisons.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"tawana" <anonymous@.discussions.microsoft.com> wrote in message
news:38841121-069F-487F-A2C5-082ACA97A93B@.microsoft.com...
> Is there any expert in designing federated database ? I need a reference
in designing a federated database. Also a comparison - Federated database
VS single database server. I need a information in decided what solution i
should go for.
>
> Best regards,
> Tawana|||Why are you considering federated DBs? What are you
trying to achieve? Then we may be able to help you better.
>--Original Message--
>Is there any expert in designing federated database ? I
need a reference in designing a federated database. Also
a comparison - Federated database VS single database
server. I need a information in decided what solution i
should go for.
>
>Best regards,
>Tawana
>.
>|||Using a federation of servers comes with its own set of problems... MS
recommends ONLY using a federation when you have already scaled-up, meaning
you have bought the biggest, baddest, SMP box, and that box isn;t big
enough...
Make sure you have exhausted the alternatives before jumping into
federations..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"tawana" <anonymous@.discussions.microsoft.com> wrote in message
news:38841121-069F-487F-A2C5-082ACA97A93B@.microsoft.com...
> Is there any expert in designing federated database ? I need a reference
in designing a federated database. Also a comparison - Federated database
VS single database server. I need a information in decided what solution i
should go for.
>
> Best regards,
> Tawanasql