Sql server表分区——我可以跨服务器分发表吗?

时间:2021-09-08 14:04:37

I have read many sites, guides, etc; also note I cannot test what I am asking as do not have two computers.

我读过许多网站、指南等;注意,我不能测试我所问的问题,因为我没有两台电脑。

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?

这有可能吗?

So for example, data with id<50000 will be stored on one computer and the remainder will be stored on the other?

例如,id<50000的数据将存储在一台计算机上,其余的将存储在另一台计算机上?

2 个解决方案

#1


3  

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

SQL 2005中引入的分区函数不支持这个AFAIK

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

在创建联合分区视图时,有可能需要使用链接服务器来实现这一点

example on server 1

示例服务器1

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

on server 2

在服务器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

还可以查看这个链接:http://blogs.msdn.com/b/sqlcat/archive/2007/06/20/distributed-partitioned- views-federateddatabass-learned.aspx

#2


1  

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.

在计算机上分布不是标准SQL Server的内置特性。与索引相比,分区并不能真正减少负载,或者在并行性方面做得更好。它确实提供了更容易的对进出分区的管理。分区可以存储在不同的文件组中,以方便存储管理。

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

现在有一个SQL Server并行数据仓库设备产品(来自获取DATAllegro)。

#1


3  

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

SQL 2005中引入的分区函数不支持这个AFAIK

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

在创建联合分区视图时,有可能需要使用链接服务器来实现这一点

example on server 1

示例服务器1

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

on server 2

在服务器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

还可以查看这个链接:http://blogs.msdn.com/b/sqlcat/archive/2007/06/20/distributed-partitioned- views-federateddatabass-learned.aspx

#2


1  

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.

在计算机上分布不是标准SQL Server的内置特性。与索引相比,分区并不能真正减少负载,或者在并行性方面做得更好。它确实提供了更容易的对进出分区的管理。分区可以存储在不同的文件组中,以方便存储管理。

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

现在有一个SQL Server并行数据仓库设备产品(来自获取DATAllegro)。