SQL Server creates a horizontal distributed database attempt

Creating a horizontal distributed database requires a two-step implementation: dividing subsets and working with subsets. The advantage of distributed database is that the IO is dispersed, so it is easy to read the data quickly and disadvantage is to consume a lot of network bandwidth resources.

Dividing a subset is to divide the original table horizontally into several smaller member tables, each of which is a partition of the complete set (the assembly of each subset is the complete, and the intersection is the empty). Each member table contains the same number of columns as the original table, and each column has the same attributes (such as data type, size, collation) as the corresponding column in the original table, horizontally slicing the original table, also called the database horizontal Shard, sharding. At query time, in order to realize the user transparency of the internal level shards of SQL Server, the partitioned view must be implemented by the partitioned views to perform a set operation on the partitioned data of the member tables distributed on different servers so that the data looks like it comes from a table.

Design Purpose: The table dbo. The data level shard of person, distributed to two days on SQL Server, Column [PersonType] Total 6 values, respectively: (' 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, open Win10 MSDTC

Refer to "Win10 open msdtc", do not repeat

Step2, create databases and tables on two servers, respectively, DBtest1 and DBTest2, DBTest1 as Master db, DBTest2 as slave db.

--Default InstanceCREATE TABLE [dbo].[ Person](    [PersonID] [int]  not NULL,    [PersonType] [nchar](2) not NULL,    [FirstName]sysname,[MiddleName]sysname,[LastName]sysname,constraintChk__person_persontypeCheck([PersonType] inch('inch','EM','SP')) );--named instanceCREATE TABLE [dbo].[ Person](    [PersonID] [int]  not NULL,    [PersonType] [nchar](2) not NULL,    [FirstName]sysname,[MiddleName]sysname,[LastName]sysname,constraintChk__person_persontypeCheck([PersonType] inch('SC','VC','GC')) );

STEP3, in master db, add linked Server

--Add linked serverexecSys.sp_addlinkedserver@server=N'DB1'    ,@srvproduct=N'Remoteserverdb'    ,@provider=N'SQLNCLI'    ,@datasrc=N'Ljhpc\namedinstance1'      ,@location= NULL    ,@provstr= NULL     ,@catalog=N'Dbtest2'--CheckSelect * fromsys.serverswhereIs_linked=1--drop Linked server--EXEC sys.sp_dropserver @server =n ' db1 ', @droplogins = ' droplogins '--Add Loginexecsp_addlinkedsrvlogin@rmtsrvname = 'DB1'      ,@useself=false,@locallogin=NULL     ,@rmtuser ='SA'     ,@rmtpassword='SA'

STEP4, creating a distributed horizontal partitioned view

Create ViewDbo.view_person as    Select [PersonID]          ,[PersonType]          ,[FirstName]          ,[MiddleName]          ,[LastName]     from [dbo].[ Person]   with(NOLOCK)where [PersonType] inch('inch','EM','SP')    Union  All    Select [PersonID]          ,[PersonType]          ,[FirstName]          ,[MiddleName]          ,[LastName]     fromDB1.[DBTest2].[dbo].[ Person]  with(NOLOCK)where [PersonType] inch('SC','VC','GC')     with Check OPTION;

STEP5, querying distributed data, viewing execution plans

SELECT *  from  where in ('em','sc' )

Appendix

--SQL Server blocked the component ' Ad Hoc distributed Queries 'execsp_configure'Show advanced Options',1 Reconfigure execsp_configure'Ad Hoc Distributed Queries',1 Reconfigure --when you are finished using, close ad Hoc distributed Queries:execsp_configure'Ad Hoc Distributed Queries',0 Reconfigure execsp_configure'Show advanced Options',0 Reconfigure 

SQL Server creates a horizontal distributed database attempt