SQL Server 创建分布式数据库(水平切分Sharding方式) -- 转载

转载自 http://www.cnblogs.com/ljhdo/p/5597222.html

设计目的：将table dbo.Person 的数据水平分片，分布到两天SQL Server上，Column [PersonType] 共有6个值，分别是：('IN','EM','SP'),('SC','VC','GC')； 

CREATE TABLE [dbo].[Person]
(
    [PersonID] [int] NOT NULL,
    [PersonType] [nchar](2) NOT NULL,
    [FirstName] [sysname] NOT NULL,
    [MiddleName] [sysname] NOT NULL,
    [LastName] [sysname] NOT NULL
)

 

step1，打开Win10 MSDTC（Microsoft Distributed Transaction Coordinator）

参考 http://www.cnblogs.com/ljhdo/p/5597078.html  

 

step2，分别在两台Server上创建数据库和表，数据库分别是DBtest1 和 DBTest2，将DBTest1作为Master DB，将DBTest2作为Slave DB。

--default instance
CREATE TABLE [dbo].[Person](
    [PersonID] [int] NOT NULL,
    [PersonType] [nchar](2) NOT NULL,
    [FirstName] sysname,
    [MiddleName] sysname ,
    [LastName] sysname,
    constraint   chk__Person_PersonType check([PersonType] in ('IN','EM','SP')) 
);

--named instance
CREATE TABLE [dbo].[Person](
    [PersonID] [int] NOT NULL,
    [PersonType] [nchar](2) NOT NULL,
    [FirstName] sysname,
    [MiddleName] sysname ,
    [LastName] sysname,
    constraint   chk__Person_PersonType check([PersonType] in ('SC','VC','GC')) 
);

Step3，在Master DB中，添加Linked Server
1. 代码Process:  

--add linked server
exec sys.sp_addlinkedserver @server= N'db1'
    ,@srvproduct= N''
    ,@provider= N'SQLNCLI'
    ,@datasrc=  N'LJHPC\NamedInstance1'  
    ,@location= null
    ,@provstr= null 
    ,@catalog= N'DBTest2'

--check 
select *
from sys.servers
where is_linked=1
或
exec [sys].[sp_linkedservers]

--drop linked server 
--EXEC sys.sp_dropserver @server=N'db1', @droplogins='droplogins' 

--add login 
exec sp_addlinkedsrvlogin @rmtsrvname = 'db1' 
,@useself=false 
,@locallogin=null 
,@rmtuser ='sa' 
,@rmtpassword='sa'

2. UI新建链接服务器（Linked Server）

step4，创建分布式水平分区视图

create view dbo.view_Person
as
    select [PersonID]
          ,[PersonType]
          ,[FirstName]
          ,[MiddleName]
          ,[LastName]
    from [DBtest1].[dbo].[Person]  with(nolock)
    where [PersonType] in('IN','EM','SP')
    union all
    select [PersonID]
          ,[PersonType]
          ,[FirstName]
          ,[MiddleName]
          ,[LastName]
    from db1.[DBTest2].[dbo].[Person] with(nolock)
    where [PersonType] in('SC','VC','GC')
    with check OPTION;

Step5，查询分布式数据，查看执行计划

SELECT *
from dbo.view_Person p 
where p.PersonType in ('em','sc')

 

Step6，优化

分布式事务使用的资源远大于内部事务，通常使用OPENQUERY等相关行集函数，避免过度依赖分布式事务。

1，使用OpenQuery，避免DTC的干预

create view dbo.view_Person
as
    select [PersonID]
          ,[PersonType]
          ,[FirstName]
          ,[MiddleName]
          ,[LastName]
    from [dbo].[Person]  with(nolock)
    where [PersonType] in('IN','EM','SP')
    union all
    select [PersonID]
              ,[PersonType]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
    from OPENQUERY ( db1 ,
        N'select [PersonID]
              ,[PersonType]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
        from db1.[DBTest2].[dbo].[Person] with(nolock)
        where [PersonType] in(''SC'',''VC'',''GC'')' ) as p
    with check OPTION;

2，在Local Server上更新分片数据

update db1.DBTEST2.dbo.person 
   set FirstName=N'Harm' 
where PersonId=102;

--修改成
exec db1.DBTEST2.sys.sp_executesql N'update dbo.person 
   set FirstName=N''Harm'' 
   where PersonId=102;'

 

Appendix

--SQL Server 阻止了对组件 'Ad Hoc Distributed Queries'  
exec sp_configure 'show advanced options',1 
reconfigure 
exec sp_configure 'Ad Hoc Distributed Queries',1 
reconfigure 
--使用完成后，关闭Ad Hoc Distributed Queries： 
exec sp_configure 'Ad Hoc Distributed Queries',0 
reconfigure 
exec sp_configure 'show advanced options',0 
reconfigure