Distributed3:SQL Server 创建分布式数据库

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

分布式数据库的优势是将IO分散在不同的Physical Disk上,每次查询都由多台Server的CPU,I/O共同负载,通过各节点并行处理数据来提高性能,劣势是消耗大量的网络带宽资源,管理难度大。在SQL Server 2012 版本中,创建水平切分的分布式数据库,必须分两步来实现:划分子集和对子集进行并集操作。

划分子集是将原始表水平切分成若干个较小的成员表,每一个成员表都是全集的一个划分(各子集的并集是全集,其交集是空集)。每个成员表包含与原始表相同数量的列,并且每一列具有与原始表中的相应列同样的特性(如数据类型、大小、排序规则),成员表的schema和原始表相同,只是存储的数据不同。水平切分原始表,也叫做数据库水平分片,sharding。在查询时,利用分区视图来实现水平分片对用户透明,分区视图对分布在不同服务器中的分区数据进行并集操作,使数据看起来来自一个表。

分布在不同场地的SQL Server通过Linked Server相互通信,通过MSDTC来保证查询的事务特性。Linked Server定义从某一数据库服务器到另一数据库服务器的单向通信路径,而MSDTC能够保证一个事务在不同的Server上实现ACID属性。例如,在一个事务中存在 Server1上的 Insert 操作和 Server2上 Update 操作 ,如果事务回滚,那么MSDTC保证Server1 和 Server2的操作都要回滚;如果事务提交,MSDTC保证Server1 和 Server2的操作都要Commit。

 

设计目的:将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)

参考《Win10 打开MSDTC》,不再赘述。

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

复制代码
--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 --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'
复制代码


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

复制代码
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 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 
复制代码

 

参考doc:

Top 3 Performance Killers For Linked Server Queries

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
分类: 数据库管理





本文转自悦光阴博客园博客,原文链接:http://www.cnblogs.com/ljhdo/p/5597222.html,如需转载请自行联系原作者
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
10天前
|
SQL 关系型数据库 MySQL
乐观锁在分布式数据库中如何与事务隔离级别结合使用
乐观锁在分布式数据库中如何与事务隔离级别结合使用
|
16天前
|
SQL 开发框架 .NET
ASP.NET连接SQL数据库:详细步骤与最佳实践指南ali01n.xinmi1009fan.com
随着Web开发技术的不断进步,ASP.NET已成为一种非常流行的Web应用程序开发框架。在ASP.NET项目中,我们经常需要与数据库进行交互,特别是SQL数据库。本文将详细介绍如何在ASP.NET项目中连接SQL数据库,并提供最佳实践指南以确保开发过程的稳定性和效率。一、准备工作在开始之前,请确保您
87 3
|
2天前
|
存储 数据挖掘 数据库
数据库数据恢复—SQLserver数据库ndf文件大小变为0KB的数据恢复案例
一个运行在存储上的SQLServer数据库,有1000多个文件,大小几十TB。数据库每10天生成一个NDF文件,每个NDF几百GB大小。数据库包含两个LDF文件。 存储损坏,数据库不可用。管理员试图恢复数据库,发现有数个ndf文件大小变为0KB。 虽然NDF文件大小变为0KB,但是NDF文件在磁盘上还可能存在。可以尝试通过扫描&拼接数据库碎片来恢复NDF文件,然后修复数据库。
|
18天前
|
SQL 关系型数据库 MySQL
Go语言项目高效对接SQL数据库:实践技巧与方法
在Go语言项目中,与SQL数据库进行对接是一项基础且重要的任务
29 11
|
17天前
|
SQL 监控 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
17天前
|
SQL 数据库 数据库管理
数据库SQL函数应用技巧与方法
在数据库管理中,SQL函数是处理和分析数据的强大工具
|
19天前
|
SQL 存储 数据可视化
SQL 数据库大揭秘:连接数字世界的魔法桥梁
在数字化时代,数据如繁星般璀璨,而 SQL 数据库则像强大的引力场,有序汇聚、整理和分析这些数据。SQL 数据库是一个巨大的数字宝库,装满各行各业的“宝藏”。本文将带你探索 SQL 数据库在电商、金融、医疗和教育等领域的应用。例如,在电商中,它能精准推荐商品;在金融中,它是安全卫士,防范欺诈;在医疗中,它是健康管家,管理病历;在教育中,则是智慧导师,个性化教学。此外,还将介绍如何利用板栗看板等工具实现数据可视化,提升决策效率。
|
20天前
|
SQL 关系型数据库 分布式数据库
Citus 简介,将 Postgres 转换为分布式数据库
【10月更文挑战第4天】Citus 简介,将 Postgres 转换为分布式数据库
70 4
|
20天前
|
SQL 存储 Oracle
Oracle数据库SQL语句详解与应用指南
在数字化时代,数据库已成为各类企业和组织不可或缺的核心组件。Oracle数据库作为业界领先的数据库管理系统之一,广泛应用于各种业务场景。掌握Oracle数据库的SQL语句是数据库管理员、开发人员及运维人员的基本技能。本文将详细介绍Oracle数据库SQL语句的基本概念、语法、应用及最佳实践。一、Or
35 3
|
5天前
|
SQL 数据库
SQL数据库基础语法入门
[link](http://www.vvo.net.cn/post/082935.html)