03浪潮ERP-GS-性能优化-数据库分离方案

浪潮ERP-GS-性能优化-数据库分离方案-MSS

--SQL Server 2005

修改历史：

反馈及勘误：

如果您遇到以下情况请发送邮件到：gsperf@https://www.wendangku.net/doc/7011288642.html,

●发现了本文档中的错误

●对本文档有新的建议

●有与本文档相关的经验需要一起分享

一、概述

1．概念

[查询]：本文档中的查询是指非实时性的，涉及表较多，数据量较大，通常会有大量Scan 的查询。

2．为什么要进行分离

我们的软件中的不同功能依据其使用其数据库的方式可以分为两类：业务处理和大查询。业务处理是指字典定义、单据保存、记账等日常业务操作；大查询是指类似于科目余额表、三栏明细账、库存明细账等消耗大量数据库资源的查询功能。

跟OLTP（联机事务处理）与OLAP（联机分析处理）的对比类似，业务处理几乎可以归为OLTP，而大查询包括一些BI的功能可以归为OLAP。二者在硬件需求、数据库结构设计以及操作方面都有着很多差异：

特性业务处理大查询

数据操作数据，是数据的来源合并数据，来自于表

用途控制和运行基础业务操作为分析、计划及决策支持服务

设计聚合连接等操作的复杂查询

操作最终用户频繁、短、快的插入和更新操作，

简单查询，返回确定的数据

一般很快根据涉及的数据量不同差距较大

处理

速度

数据

高范化反范化

库设

计

索引少多

备份数据来源，要求较高，持续备份往往通过重新装载数据作为恢复策略

I/O 读、写读scan

根据以上对比可以看出以上两类功能在对数据库的使用以及对数据库的要求上是互斥的，例如针对业务处理建立更少的索引可以提高数据插入、修改及删除的性能，但是却会损害查询的性能。范化的数据结构可以减少数据的冗余，提高并发性能，而对于查询却会导致更多的join，降低性能并消耗更多的数据库资源。将两类数据放在一个数据库里必定会影响数据库的性能。因此将二者分离，使两类功能分别使用不同的数据库服务器，并且分别优化其数据库设计以及调整索引必定能够使性能得到显著的提升。

3．使用何种方式同步数据

为了能将查询相关的数据从业务数据库中分离出来，我们必须寻求一种将业务数据库中的数据抽取到查询数据库的方式。以下为几种同步方式的对比：

特性数据库复制镜像SSIS 第三方ETL

实时性事务日志方式接近

实时

实时定期同步是不同产品而定

改动表结

构

表结构可以不变完全相同表结构可以不变表结构可以不变

性能影响发布数据库影响很

小事务要在两个数

据库提交，性能

影响大

需要读取源数据

库

是不同产品而定

成本数据库自带企业版自带企业版自带非常高

额外开发不需要不需要需要不需要数据库复制有多种复制方式，采用事务日志方式如果硬件及网络环境符合要求，可以达到接近于实时同步的效果，一般能在几秒内就会将数据同步过去，而且由于分发数据库通过读取数据库的日志文件来捕捉变化，对发布数据库的性能影响非常小，复制的过程能够保持数据结构不变，对应用程序透明，不需要对程序进行改动，也不需要进行额外的开发，使用向导进行配置即可。

数据库镜像的方式主要是用作高可用性，操作模式也分为高可用性、高级别保护和高性能，采用的操作模式不同对性能影响也有所差别，数据库镜像不同于事务复制，事务复制可以挑选特定的表进行复制，而数据库镜像是将所有数据库事务传递到镜像数据库。

SSIS(SQL Server 2005 Integration Services)即集成服务，替代了原来SQL Server 2000及早期版本的DTS，使用此种方式必须自己开发SSIS包，而且为了识别数据的变动要么通过触发器的方式要么通过加时间戳列的方式用以区别变更的数据，然后定期将变更的数据抽取到目的数据库，这种方式的缺点是需要进行开发，而且要改动数据结构，在抽取数据时对数据库的性能有影响。

第三方的ETL工具是一个选择，产品价格非常高，各种特性也依据不同产品有所差别。

经过不同方式的对比，我们决定使用数据库复制来实现从业务数据库到查询数据库的同步。

二、数据库复制

1．复制发布模型概述

复制使用出版业术语表示复制拓扑中的组件，其中有发布服务器、分发服务器、订阅服务器、发布、项目和订阅。可借助杂志的概念来帮助理解Microsoft SQL Server 复制：

●杂志出版商（发布服务器）生产一种或多种刊物（发布）

●刊物（发布）包含文章（项目）

●出版商（发布服务器）可以直接发行（分发）杂志，也可以使用发行商（分发服务

器）

●订阅者（订阅服务器）接收订阅的刊物（发布）

虽然杂志术语有助于理解复制，但重要的是要注意到SQL Server 复制包含有这套术语未予以表述的功能，尤其是订阅服务器进行更新的功能以及发布服务器将增量更改发送到发布中的项目的功能。

“复制拓扑”定义了服务器和数据副本间的关系，并阐明了决定数据如何在服务器之间流动的逻辑。有若干复制进程（称为“代理”）负责在发布服务器和订阅服务器之间复制和移动

数据。下图为复制中所涉及的组件和进程的概述。

发布服务器

发布服务器是一种数据库实例，它通过复制向其他位置提供数据。发布服务器可以有一个或多个发布，每个发布定义一组要复制的具有逻辑关系的对象和数据。

分发服务器

分发服务器也是一种数据库实例，它起着存储区的作用，用于复制与一个或多个发布服务器相关联的特定数据。每个发布服务器都与分发服务器中的单个数据库（称作分发数据库）相关联。分发数据库存储复制状态数据和有关发布的元数据，并且在某些情况下为从发布服务器向订阅服务器移动的数据起着排队的作用。在很多情况下，一个数据库服务器实例充当发布服务器和分发服务器两个角色。这称为“本地分发服务器”。当发布服务器和分发服务器按各自的数据库服务器实例配置时，把分发服务器称为“远程分发服务器”。

订阅服务器

订阅服务器是接收复制数据的数据库实例。订阅服务器可以接收来自多个发布服务器和发布的数据。根据所选的复制类型，订阅服务器还可以将数据更改传递回发布服务器或者将数据重新发布到其他订阅服务器。

项目

项目用于标识发布中包含的数据库对象。一次发布可以包含不同类型的项目，包括表、视图、存储过程和其他对象。当把表作为项目发布时，可以用筛选器限制发送到订阅服务器的数据的列和行。

发布

发布是一个数据库中的一个或多个项目的集合。将多个项目分组成一个发布，使得更便于指定一组作为一个单元复制的、具有逻辑关系的数据库对象和数据。

订阅

订阅是把发布副本传递到订阅服务器的请求。订阅定义将接收的发布和接收的时间、地点。有两种类型的订阅：推送订阅和请求订阅。有关推送订阅和请求订阅的详细信息，请参阅订阅发布。

2．复制类型与试用场景对比

快照复制

快照处理通常用于为事务和合并发布提供初始的数据集和数据库对象，但快照复制还可为其自身所用。当符合以下一个或多个条件时，使用快照复制本身是最合适的：

●数据很少更改。

●在一段时间内允许具有相对发布服务器已过时的数据副本。

●复制少量数据。

●在短期内出现大量更改。

在数据更改量很大，但很少发生更改时，快照复制是最合适的。例如，如果某销售组织要维护一个产品价格表，并且这些价格每年要在固定时间进行一两次更新，那么建议在数据更改后复制完整的数据快照。

事务性复制

事务性复制通常用于服务器到服务器环境中，在以下各种情况下适合采用事务性复制：希望发生增量更改时将其传播到订阅服务器。

●从发布服务器上发生更改，到更改到达订阅服务器，应用程序需要这两者之间具有

较低的滞后时间。

●应用程序需要访问中间数据状态。例如，如果某一行更改了五次，事务性复制将允

许应用程序响应每次更改（例如，激发触发），而不只是响应该行最终的数据更改。

●发布服务器有大量的插入、更新和删除活动。

●发布服务器或订阅服务器不是SQL Server 数据库（例如，Oracle）。

默认情况下，事务发布订阅服务器应作只读处理，因为更改并不传播回发布服务器。但是，事务性复制确实提供了允许在订阅服务器上进行更新的选项。有关详细信息，请参阅本主题中的“在订阅服务器中更新数据”部分。

合并复制

合并复制通常用于服务器到客户端的环境中。合并复制适用于下列各种情况：

●多个订阅服务器可能会在不同时间更新同一数据，并将这些更改传播到发布服务器

和其他订阅服务器。

●订阅服务器需要接收数据，脱机进行更改，并在随后与发布服务器和其他订阅服务

器同步更改。

●每个订阅服务器都需要不同分区的数据。

●可能会发生冲突，并且在冲突发生时，您需要具有检测和解决冲突的能力。

●应用程序需要最终的数据更改结果，而不是访问中间数据状态。例如，在订阅服务

器与发布服务器同步前，如果订阅服务器上的行更改了五次，则该行将只在发布服

务器上更改一次，以反映最终数据更改（也就是更改为第五个值）。

合并复制允许不同站点自主工作，并在以后将更新合并成一个统一的结果。由于更新是在多个节点上进行的，同一数据可能由发布服务器和多个订阅服务器进行了更新。因此，在合并更新时可能会产生冲突，合并复制提供了多种处理冲突的方法。

3．复制类型选择

快照复制同步的方式为将对象完整的复制一次，并非增量，每当有更新都需要将所有数据进行同步，而浪潮ERP-GS之类的管理软件业务数据库的操作非常频繁，每次同步所有数据没有必要也不现实，因此快照复制仅适用于事务复制和合并复制进行初始时使用。

事务性复制的原理为代理读取数据库的事务日志文件，然后捕捉变化，然后在订阅服务器上重演这些变化来实现复制，表结构不需改动。事务性复制比较适合单向复制。

合并复制使用的方式是在数据库发布的项目上增加GUID列并增加触发器来捕捉变更

的，由于中间变化不会在订阅服务器上体现，仅仅复制最终状态，因此适合定期同步而且更新频繁的场景。但是增加的列会导致一些书写不规范的SQL无法执行，例如SELECT *(没有标明选取的列)原来用来索引字段的序号会不匹配，INSERT INTO TABLE1 VALUES(…)不写明具体字段也会导致不匹配。。。

综合来看事务性复制对程序透明，不需要数据结构的变更，对发布数据库性能影响非常小的特点最适合我们使用，而且在没有特殊需求的情况下我们不推荐使用双向复制，双向复制不仅对性能影响较大，而且会带来操作上的复杂性以及数据风险，例如使用单向复制时，订阅数据库仅用作查询，如果订阅数据库出现问题可以随时重新初始，备份时仅备份业务数据库即可。

因此本方案采用单向的事务性复制，业务数据库作为发布数据库，订阅数据库用作查询数据库。

三、方案的部署

在开始您的一切相关工作以前首先要针对方案进行规划。

确定分离的方式，单订阅，多订阅。

确定分离的内容，那些功能。

确定发布的项目。

服务器、网络资源、域用户、分发者模式、数据库实例、数据库，快照目录等。

1．注意事项及准备工作

1.1事务日志空间

对于要使用事务性复制发布的每个数据库，请确保已为事务日志分配了足够的空间。请确保已为事务日志分配了足够的空间。已发布数据库的事务日志可能要比相同的未发布数据库的日志需要更大的空间，因为日志记录被移至分发数据库之后才会被截断。当执行复制一段时间后，数据库的日志可能越来越多，以致服务器硬盘比较紧张，影响正常使用。这时，请先使用“完整（FULL）”备份类型来备份数据库，然后你可以截断数据库日志，再收缩数据库。

1.2分发数据库的磁盘空间

请确保具有足够的磁盘空间存储分发数据库中复制的事务，虽然使快照能够立即为订阅服务器所用可以提高新订阅服务器访问发布的速度，但此选项会增加分发数据库存储所需的磁盘空间。这还意味着每次快照代理运行时，都将生成一个新的快照。如果不使用此选项，则仅当存在新订阅时才会生成新快照。

1.3每个已发布表的主键

在事务性复制中所有已发布的表都必须包含已声明的主键。在我们当前的应用中，对于

将要发布的表如果不含主键，请咨询相关业务组来确定以表的那几列作为主键。

1.4SQL Server 代理

复制使用称为代理的独立程序执行与跟踪更改和分发数据相关联的任务。默认情况下，复制代理运行为SQL Server 代理下计划的作业。必须运行SQL Server 代理才能运行作业。默认情况下，安装完SQL Server 2005 之后，SQL Server 代理服务处于禁用状态，除非在安装过程中明确选择自动启动该服务。发布服务器的SQL Server agent账号用本地服务账号.

1.5将本地分发和订阅服务器加入同一域或工作组

在本方案中我们将用在用一台服务器做发布服务器和分发服务器，所以需要将本地分发和订阅服务器加入到同一域或工作组。

注意:如果客户使用双机热备或多机热备, 分发服务器要建立在公用存储上.

2．执行复制

我们将采用单向的事务性复制，业务数据库作为发布数据库，订阅数据库用作查询数据库。复制由下列几个阶段组成：

●配置复制和发布数据

●创建和初始化订阅

●同步数据

2.1配置复制和发布数据

复制部署始于配置发布服务器和分发服务器。分发服务器在事务性复制中的作用十分重要，但在合并复制和快照复制中的作用比较有限，仅用于代理历史记录和错误报告及监视。合并复制和快照复制通常使用与发布服务器在同一台计算机上运行的分发服务器，而事务性复制可能使用远程分发服务器，尤其在发布服务器为高吞吐量的OLTP 系统时。

首先，我们介绍一下如何配置分发，配置分发服务器时，需指定下列内容：

●快照文件夹，默认情况下供使用此分发服务器的所有发布服务器使用。请确保此文件夹已共享并

设置了适当的权限。

●分发数据库的名称和文件位置。分发数据库创建后不能重命名。若要对数据库使用其他名称，必

须禁用并重新配置分发。

●获得授权使用分发服务器的发布服务器。如果要指定分发服务器而非发布服务器运行实例，还必

须指定发布服务器连接到远程分发服务器所用密码。

A．配置分发

在本方案中，分发服务器与发布服务器是同一台服务器（本地分发服务器），具体配置

分发的步骤，请参见以下图例：

Step1. 启动SQL Server2005,如图：

Step2. 以sa用户连接服务器。

Step3. 在对象资源管理器中，展开当前服务器，右键“复制”节点，选择“配置分发”。

Step4 在“配置分发向导”中，选择本机作为自己的分发服务器，即图中第一个选项。

Step5：指定快照文件夹的位置，在这个地方我们推荐使用本机机器名加快照文件夹名字，例如：\\performancetsql\Snapshot。在这里需要指出，快照文件夹必须设置为共享，并且文件夹的权限，必须加上在发布数据这一步中指定的运行SQL Server Agent 的帐户名称，并且为完全控制。如图：rep这个windows帐户为以后发布数据这一步中指定的运行SQL Server Agent进程的帐户名称，权限为完全控制。

注意:在双机热备或多机热备中快照文件夹要放在公用存储上，不能放到本地磁盘,并且

快照文件夹在集群中设置为共享。

Step6：在分发数据库对话框中，保持默认配置，点击下一步即可。

Step7 在发布服务器对话框上保持默认配置，点击下一步即可。

Step8 选择配置分发，点击下一步即可。

Step9 点击完成即可完成配置分发。

B. 发布数据

在上述操作中我们已经配置了分发，接下来我们将发布数据。

Step1. 在对象资源管理器中，展开当前服务器，找到“复制”节点，右键复制节点中的

本地发布节点，选择新建发布。如图：

Step2 ：在新建发布向导中选择要发布的数据库，然后点击下一步，如图:

Step 3 ：选择发布类型，这里我们选择“事务性发布”，然后点击下一步。如图：

参见附录。如图：

如果遇到下述如图情况，某些表不能发布，因为它没有主键列，事务性发布中的所有表都需要主键列。现在，请联系相关业务组申请升级主键sql来给相关表附加主键。然后再重复上

述过程，添加发布表，发布数据。

Step5. 选择“项目属性“中的设置所有表项目的属性。其中，复制非聚集索引、复制检查约束、复制唯一键约束为true。然后，点击确定，如图：

S tep6. 在筛选表行对话框，保持默认配置点击下一步即可。

点击下一步即可。如图：

Step8：在代理安全性对话框中，点击快照代理的“安全设置”按钮。如图：

Step9：在快照代理安全性对话框中，指定运行快照代理进程的域或计算机用户。这个用户使用前面设置快照文件夹权限的windows帐户。连接到发布服务器使用的SQL Server登陆名为sa和其密码，然后点击确定即可。

Step 10:在向导操作对话框，选择“创建发布”，然后点击下一步即可。如图：

Step11: 在完成该向导对话框，输入发布名称，然后点击完成即可。

2.2创建和初始化订阅

在2.1中我们已经配置了分发和发布了数据，在这一章节将阐述如何创建和初始化订阅。

请先连接到订阅服务器。

Step1：在对象资源管理器中，展开服务器节点，找到其中的复制节点，然后右键复制节点里面的本地订阅，选择新建订阅，如图：

Step2：在发布对话框中，发布服务器选择<选择SQL Server 发布服务器…>,如图：

Step3：连接发布服务器，如图：

Step4：在选择了发布服务器后，选择发布服务器上的发布，然后点击下一步即可.如图：