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