SQLServer集群搭建

1.前言

今天又接到了一个新的需求，就是搭建数据库服务器集群。因为原始的服务器是Server 2008,并且使用了SQL Server 2008作为数据服务器，如果要单纯的将数据库切换到其他的数据库，可能对老的系统有一定的影响，所以需要在尽量不改变原有的客户端代码的情况下，尽量的做到平滑的升级或者是负载均衡，故障转移，灾难恢复。这也让我进入到了分布式数据库的研究行列中来，哪些好用的分布式数据库？价格如何，使用难度如何？如何和现有的数据库进行兼容或者是平滑过渡？

1.SQL Server集群可行性分析

Linux

2.双机热备(发布订阅模式)

SQL Server的双机热备，一种方式，就是使用发布订阅模式实现的。基本的步骤就像参考文章3中的一样，就是在复制中创建本地发布，然后在备份机上创建本地订阅，如果在使用中有什么问题，可以查看我下面写的问题。主要有几点需要注意：

• 1.要使用SQL Server2008自带的管理工具进行发布和订阅，而不是官方提供的单独的SSMS安装包

• 2.配置别名
  首先要在进行发布的服务器上运行 sql 命令：select @@SERVERNAME ，查找服务器名称；然后将得到的服务器名称，在需要进行订阅的服务器上，打开SQL Server配置管理工具，在两个Native Client上新键别名，填入发布服务器查询到的名称，ip地址和端口；

• 3.修改hosts文件
  最后是在订阅服务器上，修改hosts文件，填入发布服务器ip地址，和发布服务器查询的服务名称

• 4.进行订阅服务器登录时，用户要有sysadmin权限。

用户要有对数据库的完全访问权限,如果查看复制监视器，显示：无法作为数据库主体执行，因为主体”dbo”不存在、无法模拟这种类型的主体，或您没有所需要的权限。这就是权限的问题，可以使用如下的语句进行数据库权限的修改。

1
2
3
4
5
6
7

--修改数据库所有者
use dbname
go
sp_changedbowner sa

--或者暴力卸载
exec sp_removedbreplication dbname

• 5.在发布时的连接到服务器的名称，最好是有较高权限的用户，还有就是订阅的时候的登录用户，和发布的用户是一个用户，这个其实我不是很确定。

• 5.日志文件夹要第一次要同步
  (1) 新建发布时，在快照的文件夹中(右键发布属性，可以查看)，会生成发布日志，要将发布的数据库文件日志，拷贝到订阅服务器中。
  (2) 新键订阅时，要将发布服务器上的unc日志文件拷贝到订阅服务器的自定义目录中，也就是同步发布服务器和订阅服务器中的unc文件夹，比如我的是：D:\sqlserver_data\PHEMS_ReplData，那么就会有D:\sqlserver_data\PHEMS_ReplData\unc 文件夹，下面有各个从发布服务拷贝来的文件。否在就会出现 进程无法读取文件“xxx.pre” 问题(当然也可以使用共享文件夹的方式，只要是能让订阅服务器读取到发布服务器的日志文件就可以了)。创建订阅后，要右键订阅的属性，将快照文件夹选择为备份文件夹，然后填入unc文件夹所在的上一级文件夹，也就是：D:\sqlserver_data\PHEMS_ReplData 文件夹。编辑完成之后，要右键订阅，重新进行初始化操作。

3.总结

使用的SQL Server 2008 的发布订阅模式，主要有以下几个步骤：

(1) 一台服务器在数据库复制中，创建发布。
(2) 另一台服务器在同样数据库版本的复制中，创建订阅

注意：
(1) 从订阅服务器创建订阅时需要用用户名密码的方式登录到发布服务器，这个时候的用户要有数据库 sysadmin 权限。
(2) 在发布服务器上进行发布后，要将发布后的日志文件，拷贝到订阅服务器上，并右键修改订阅服务器的订阅中的快照文件夹，指向该日志文件所在的位置。

缺点
使用发布订阅模式其实只是实现了数据库的复制和备份，但是无法实现高可用，也就是数据库一个发生了故障，另一个切换为主节点。

问题

(1) 创建本地发布的时候出现无法登录问题
当我右键复制，创建发布的时候，出现了这个问题。

解决方法，就是重新使用计算机名称连接，而不是使用localhost进行数据库连接。

上面的图是我目前计算机的名称，作为服务名称，使用的Windows身份认证方法进行登录的，原先我使用的是localhost登录名， 使用Windows进行身份认证登录的。

(2) SQL Server无法将 xxx 配置为分发服务器
按照参考文章一步步的做，最后还是出现了错误，“设置连接字符串后无法更改或者读取属性LoginSecure”,

首先，查看服务名称是否一致。我执行了之后，计算机名称和服务名称两个名称一致，所以就不需要进行服务名称的修改，需要找另外的方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT @@SERVERNAME,SERVERPROPERTY('SERVERNAME')


--或者，下面的命令，两个结果都一样
use master
go
select @@servername;
select serverproperty('servername')

--执行修改命令，将"服务器名", 修改为正确的计算机名
EXEC sp_dropserver '服务器名[/命名实例名]';  -- 即旧的计算机名
GO
EXEC sp_addserver '计算机名[/命名实例名]', local; -- 即新的计算机名

go

解决方法：因为我第一个问题没有解决，所以有了第二个问题，其实我先解决了第一个问题，那么第二个就不存在了。

(3) 对象名syservers无效
这个问题真的非常的奇怪，奇怪到我在网上都找不到相关的文章，我搜sysserver这个关键字，也没有相关的信息，似乎没有人遇到过啊。

• 第一次尝试:数据库降级
  我创建本地发布的SQL Server版本为2008,但是我另一台服务器创建本地订阅的服务器安装的是2019,我怀疑是这个问题，于是就进行了数据库降级。但是我降级之后，在另一台虚拟机中安装了同样的2008,进行本地订阅的创建，还是出现了这个问题，也就是说，不是数据库版本的问题。

• 第二次尝试:同样的数据库版本
  我在两台虚拟机中创建了sql server2008进行订阅和发布，也还是会出现这个问题。

1
2
3
4
5
6
7

SELECT 
SERVERPROPERTY('servername') AS 实例名,
SERVERPROPERTY('ProductVersion') AS 实例版本,
SERVERPROPERTY('Edition') AS 产品版本,
SERVERPROPERTY('ProductLevel') AS 版本级别,
@@VERSION AS 版本信息

• 第三次尝试:管理工具的统一
  我梳理了自己安装的程序，在一台服务器上叫A吧，sql server和管理工具(在SQL Server安装介质中安装的)都是Sql Server2008，在另外的两台服务器上，安装的是Sql Server 2008 R2，管理工具安装的是SSMS 18.9.1,独立在官网(SSMS)上下的管理工具,没有使用sql server 2008R2 里面自带的管理工具。

其实很早我就怀疑过了，错误提示说是：” 无效的对象名称sysservers “,那会不会是在创建sql语句是产生的选择语句不同呢？于是，这一次，我将默认的管理工具安装上了(刚开始我也不知道自带了一个管理工具)，然后再次从同一个机器上，进行发布和订阅的尝试，果然好用了。

我忘记在哪篇文章中看到的了，说是进行订阅和发布时不能隔两代，我的理解就是，SQL Server2008不能和SQL Server2016进行相互订阅和发布

(4) SQL Server复制需要有实际的服务器名称才能连接到服务器。不支持通过服务器别名、IP地址或者任何其他备用名称进行连接
这个问题更加的奇怪，我在使用SSMS18.9进行订阅的时候，可以使用ip地址进行连接，但是使用了SQL Server2008 R2自带的管理工具，进行连接的时候，就会出现这个问题，但是如果不用ip地址的话，怎么连接远程地址呢？如果还配置服务器名称呢？这不是让人蛋疼的事情吗？如果我知道服务器名称，那不就是本机了吗？

我在SQL Server配置管理工具中，添加了别名，在系统的hosts中添加了对应的别名地址，还是不行(简直令人头大)。

因为我的两台计算机是虚拟机，复制出来的，所以计算机名称一样，于是我尝试了修改其中的一台计算机名称，和服务名称

(5) 仍有对服务器 ‘xxx’ 的远程登录或链接登录。
在进行服务器名称和计算机名称修改时出现的错误，我修改了计算机名称，但是进行sql server服务器名称修改的时候，总是不成功，提示：仍有对服务器 的远程登录或链接登录。解决方法如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- 1.查询服务名和计算机名
use master 
go 
select @@servername
select serverproperty('servername')

-- 2.删除所有之前配置的publishers（'old_server_name'为之前的服务名）
select * from msdb.dbo.MSdistpublishers
DELETE FROM msdb.dbo.MSdistpublishers
select * from msdb.dbo.MSdistpublishers
exec sp_droplinkedsrvlogin 'old_server_name',null
exec sp_dropserver 'old_server_name', 'droplogins' 

-- 执行完后，重启SQL Server服务

-- 3.配置计算机名与服务名一致
USE master
 GO
 if serverproperty('servername') <> @@servername   
begin   
declare @server sysname   
set   @server = @@servername   
exec sp_dropserver @server = @server   
set   @server = cast(serverproperty('servername') as sysname)   
exec sp_addserver @server = @server , @local = 'LOCAL'   
end 

-- 执行完后，重启SQL Server服务

-- 4.再次查看
sp_helpserver

(6) 没有将”xxx”配置为远程服务器
当我重新修改了一台服务器的名称之后，然后将两台服务器连接入同一个局域网中，虽然在查找服务器的时候，可以查找到这台服务器，但是选择连接这台服务器的时候，出现了这个问题：没有将”xxx”配置为远程服务器。

解决方法：因为我修改了计算机名和sql server服务器名，但是复制还是保留着以前的服务器。可以右键复制->然后选择”禁用发布和分发”，然后就可以删除这个发布了。

(6) 无法启动代理，因为该作业已应 用户 sa 的请求正在运行。该作业尚未完成就被 用户 sa 停止。 启动顺序 0 调用了该作业
在自动发布的时候，出现了这个问题。

更进一步的消息：进程无法连接到Subscriber

这个问题其实我也不知道怎么解决，只是说，删除了发布和订阅，然后重新创建了发布和订阅。

(7) 用户登录失败
在进行订阅的时候，我选择了在订阅服务器上运行代理，打开复制监视器，出现了了 “xxx” 用户登录失败的问题，但是我也很奇怪，我没有 phems 这个用户，所有的订阅和发布订阅的时候，唯一和phems相关的就是用户phems_test的名称，难道是因为直接将后面的短线忽略掉了吗？

解决方法：我把订阅和发布都删除了，然后把phems这个用户也删除了，最后重新进行发布和订阅。

(8) 由于出现操作系统错误 3，进程无法读取文件“xxx.pre”
这个问题是由于无法读取文件造成的，sql server2008发布-订阅实现异地热备份总结及常见问题分析 这篇文章中的最后给了建议。

解决方法：

• 在发布服务器上，右键发布，打开属性，找到快照,然后找打快照的存在位置，然后拷贝文件夹下的全部文件，这里是unc文件。

  

• 在订阅服务器上，右键订阅，打开属性，找到快照位置，改为备用文件夹，然后设置快照文件夹和发布服务器上的快照路径一致，这里为：“D:\sqlserver_data\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData”，然后，将发布服务器上的快照存放位置处的文件夹都拷贝到该文件夹中，这里是unc文件。

  

(9) 远程服务器 “IZ23TFQL1HVZ” 不存在，未被指定为有效的发布服务器，或您无权查看可用的发布服务器。
这是在A服务器上进行发布，然后B服务器上订阅的结果，因为A服务器上的端口号为5433,所以需要使用别名和hosts文件进行配置，即便是配置了hosts和别名，还是出现了错误。

解决方法:创建登录用户时，授予用户服务器角色： sysadmin

(10) 对象 WxUser 中的列 UserBill 包含类型 VarCharMax，此类型不受目标服务器版本 SQL Server 2000 支持。
这个错误，是我在sql server 2008上进行发布，然后sql server 2008 r2上进行订阅时出现的问题。

我查看了自己的数据表，发现真的有一个UserBill 字段的类型为varchar(MAX)，索性就直接改掉好了,将varchar(MAX)改为了varchar(5000)。除了这个之外，还有就是time类型，也出现了不兼容,我暂时不知道这个问题怎么处理，除了修改项目类型之外，还有其他的方法吗？因为我发布使用的是SQL Server2008，订阅服务器安装的是SQL Server2008 R2啊，没有SQL Server 2000。

当我查看初始化的设置时，发现了一个：将发布服务器数据库兼容级别设置为80,这才找到了为什么是不兼容2000的原因了。

当我查看初始化的设置时，发现了一个：将发布服务器数据库兼容级别设置为80,这才找到了为什么是不兼容2000的原因了。

但是修改发布兼容性级别，这个只在发布类型为合并复制的时候才有的选项，事务性发布没有修改兼容性级别的地方。

我是用下面的语句，查看数据的兼容级别，发现我复制的数据库的兼容性级别被改为了80,我想能不能进行兼容性级别的改变呢？

1
2

-- 获取全部的数据库兼容性级别
SELECT name, compatibility_level FROM sys.databases; 

根据参考文章4，修改数据库的兼容性级别如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 获取数据库兼容级别
SELECT name ,compatibility_level ,recovery_model_desc FROM sys.databases WITH(NOLOCK)

-- 将用户设置为单用户访问模式
ALTER DATABASE test SET SINGLE_USER

-- 修改数据库的兼容级别
ALTER DATABASE TEST
SET COMPATIBILITY_LEVEL = 90
-- or
EXEC sp_dbcmptlevel TEST, 90;
GO

-- 将用户设置为多用户访问模式
ALTER DATABASE test SET MULTI_USER

订阅服务器类型 这里说，创建发布快照之后，可以在 “发布属性” 对话框的 “常规” 页上提高发布兼容级别（使其更为严格）；无法降低兼容级别。但是我的发布属性的常规页面种，没有这个发布兼容级别选项

(11) 由于被引用对象或列在订阅服务器上不存在，因此无法复制视图或函数。
使用事务进行复制的时候，不支持视图的复制。

(12) 订阅已在订阅数据库中
这个问题，是因为我手贱，先是把数据库删除了，然后再删除的订阅，最后订阅删除不了了，但是也新键不了，蛋疼。

解决方法，就是把订阅关连的数据库整个的都删掉就可以了。