sql server table partioning- is this correct?

Question

read many sites, guides, etc

note :- cannot test what i am asking as do not have two computers :) so asking..

table partitioning simply means that the table is logically divided, such that a particular range is handled by a particular partition only there by reducing load and also allowing parallelism.

there is a mention of partitions being on different hard disks

but

there is never any mention of partitions being on different computers

is that possible?

data with id<50000 go to computer local, rest go to computer B?

thankyou

Answer 1

partitioned functions introduced in SQL 2005 don't support this AFAIK

it is possible when you create federated partitioned views, you have to use linked servers to do that

example on server 1

create view MyView
as
Select * from Database.dbo.Table1
union all
Select * from Server2.Database.dbo.Table1

on server 2

create view MyView
as
Select * from Database.dbo.Table1
union all
Select * from Server1.Database.dbo.Table1

on each server you would then have this view...however (and this is a big however) if one of the servers is down the the view will be unavailable on all servers

Also take a look at this link: http://blogs.msdn.com/b/sqlcat/archive/2007/06/20/distributed-partitioned-views-federated-databases-lessons-learned.aspx

Answer 2

Distributing across computers is not a builtin feature of standard SQL Server. Partitioning does not really reduce load or do anything specially better for parallelism than indexes. It DOES offer easier management of swapping partitions in or out. Partitions can be stored on different file groups for easier storage management.

There is a SQL Server Parallel Data Warehouse appliance product now (from the acquisition of DATAllegro).