SQL Server 多实例下的复制

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介: 原文:SQL Server 多实例下的复制一.本文所涉及的内容(Contents) 本文所涉及的内容(Contents) 背景(Contexts) 搭建步骤(Procedure) 注意事项(Attention) 二.
原文: SQL Server 多实例下的复制

一.本文所涉及的内容(Contents)

  1. 本文所涉及的内容(Contents)
  2. 背景(Contexts)
  3. 搭建步骤(Procedure)
  4. 注意事项(Attention)

二.背景(Contexts)

  在服务器A有一个数据库Task,需要把部分表部分字段发布订阅到服务器B的TaskSiteInfo数据库上,但是A服务器有些特别,因为它除了有个默认的实例之外,还有一个命名实例:TZR06\SQLSERVER2008R2,如果是默认实例到不会遇到太多的问题,现在因为有命名实例在创建发布订阅的过程中出现了一些异常,所以这里做为记录;

三.搭建步骤(Procedure)

(一) 环境信息

系统环境:Windows Server 2008 R2 + SQL Server 2008 R2

发布服务器:192.168.100.6,1433,服务器名称:tzr06

发布服务器命名实例:SQLSERVER2008R2

发布数据库:Task

分发服务器:与发布服务器同一台机器

订阅服务器:192.168.100.8,1433,服务器名称:tzr08

订阅数据库:TaskSubscribe

数据库帐号:ReplicationUser/ ReplicationPassword

(二) 搭建过程

  上面提到的,发布服务器上有个默认实例和一个命名实例,本来默认实例的数据库端口为1433,后来我把它禁用了,再把命名实例的端口设置为1433,所以这个需要借助别名来实现发布订阅。

A. 发布服务器配置

首先在发布数据库和订阅数据库上创建相同的帐号和密码(ReplicationUser/ ReplicationPassword),并且设置Task数据库的安全对象,设置这样的帐号的目的就是为了和程序连接到数据库的帐号区分开,可以做权限上的控制,方便问题的排查;

--创建发布服务器帐号密码
USE [master]
GO
CREATE LOGIN [ReplicationUser] WITH PASSWORD=N'ReplicationPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'ReplicationUser', @rolename = N'sysadmin'
GO
USE [Task]
GO
CREATE USER [ReplicationUser] FOR LOGIN [ReplicationUser]
GO
USE [Task]
GO
ALTER USER [ReplicationUser] WITH DEFAULT_SCHEMA=[dbo]
GO

wps_clip_image-8981

(Figure1:帐号密码)

在E盘目录下创建文件夹:E:\ReplData,并设置这个文件夹为共享目录,共享用户为bfadmin;

wps_clip_image-8763

(Figure2:文件夹权限)

需要设置SQL Server Agent登陆帐号为上面文件夹访问用户bfadmin;

wps_clip_image-29477

(Figure3:SQL Server Agent登陆帐号)

wps_clip_image-2693

(Figure4:测试网络共享)

wps_clip_image-1661

(Figure5:分发服务器)

如果你设置快照文件夹路径为:E:\ReplData,即使你的发布服务器本身就是分发服务器,如果订阅服务器是另外一台机器,那么在请求(Pull)订阅(如果是推送(Push)订阅就没有这个限制)模式下订阅代理是无法访问到这个快照文件的;除非你发布服务器、分发服务器和订阅服务器都是同一台机器;你应该设置快照文件夹路径为:\\tzr06\ ReplData;

wps_clip_image-27389

(Figure6:快照文件夹)

wps_clip_image-6197

(Figure7:选择发布数据库)

wps_clip_image-5271

(Figure8:事务发布)

wps_clip_image-5826

(Figure9:选择对象)

wps_clip_image-24573

(Figure10:选择对象)

wps_clip_image-13321

(Figure11:选择对象)

wps_clip_image-3034

(Figure12:初始化订阅)

wps_clip_image-7934

(Figure13:安全设置)

使用上面创建好的ReplicationUser帐号作为连接到发布服务器的帐号和密码;

wps_clip_image-4731

(Figure14:设置帐号密码)

wps_clip_image-31543

(Figure15:发布选项)

wps_clip_image-22937

(Figure16:发布名称)

wps_clip_image-3613

(Figure17:查看复制情况)

B. 订阅服务器配置

创建完发布服务器(分发服务器也一起创建了),接下来就可以创建订阅服务器了,下面是订阅服务器设置的具体步骤:

--创建订阅服务器帐号密码
USE [master]
GO
CREATE LOGIN [ReplicationUser] WITH PASSWORD=N'ReplicationPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'ReplicationUser', @rolename = N'sysadmin'
GO
USE [TaskSiteInfo]
GO
CREATE USER [ReplicationUser] FOR LOGIN [ReplicationUser]
GO
USE [TaskSiteInfo]
GO
ALTER USER [ReplicationUser] WITH DEFAULT_SCHEMA=[dbo]
GO

创建完了数据库帐号,我们接着来创建订阅,按照前面提到的在发布服务器上有命名实例,所以这里是按照TZR06\SQLSERVER2008R2来设置服务器名称的,但是在连接过程中出现了下面的错误:

wps_clip_image-30021

(Figure18:查找发布服务器错误信息)

使用上面的配置在订阅服务器上使用【连接服务器】的方式同样无法登录到发布服务器,防火墙的入站规则已经加入允许1433端口了,而且在发布服务器使用netstat查看端口,也是有监听的,为什么会连接不上呢?后来在【连接服务器】加入1433是可以登录的,如下图所示:

wps_clip_image-5793

(Figure19:登录发布服务器)

使用同样的方式却无法查找到发布服务器,出现了新的错误信息,如下图所示:

wps_clip_image-16198

(Figure20:查找发布服务器错误信息)

既然需要加端口号,那我们就尝试使用别名的方式,在64位的操作系统中,需要同时设置32位和64位的网络配置,设置别名为:TZR06

wps_clip_image-11165

(Figure21:别名参数值)

wps_clip_image-20082

(Figure22:32位别名)

wps_clip_image-7762

(Figure23:64位别名)

wps_clip_image-30153

(Figure24:查找发布服务器错误信息)

难道是TZR06有冲突?修改别名为:TZR06Task

wps_clip_image-2451

(Figure25:修改32位和64位的别名)

wps_clip_image-29011

(Figure26:查找发布服务器错误信息)

在发布服务器上创建发布的时候,如果SQL Server数据库实例名与服务器名不一致,也会出现上面的错误,所以在发布服务器上执行下面的SQL语句:

/*
返回有关服务器实例的属性信息
Windows 服务器和与指定的SQL Server 实例关联的实例信息
*/
SELECT CONVERT(sysname, SERVERPROPERTY('servername'));
/*
返回运行SQL Server 的本地服务器的名称
如果连接默认实例,则@@SERVERNAME仅返回servername
如果连接命名实例,则@@SERVERNAME函数返回的字符串以servername\instancename的格式标识实例名
*/
SELECT @@SERVERNAME AS 'Server Name'

上面的结果为:

wps_clip_image-15272

(Figure27:查找发布服务器错误信息)

如果两个值不同,那到可以通过下面的方式进行修改:

/*
SQL Server数据库实例名与服务器名不一致的解决办法
*/
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

后来请教高文佳,突然想到:“在分发服务器和订阅服务器上设置别名的时候,别名应该跟服务器的实例名要一致”继续做尝试,修改别名为:TZR06\SQLSERVER2008R2

wps_clip_image-22296

(Figure28:修改32位和64位的别名)

wps_clip_image-22511

(Figure29:选择发布)

wps_clip_image-21671

(Figure30:请求订阅)

wps_clip_image-14008

(Figure31:选择订阅数据库)

wps_clip_image-17928

(Figure32:分发代理安全性)

wps_clip_image-32000(Figure33:设置帐号密码)

wps_clip_image-22798

(Figure34:同步计划)

wps_clip_image-29422

(Figure35:初始化)

wps_clip_image-18531

(Figure36:创建订阅)

wps_clip_image-5947

(Figure37:本地订阅)

四.注意事项(Attention)

1. 如果一开始你在发布服务器上设置的快照文件为本地路径,比如设置成E:\ReplData,那么有可能出现下面的错误:

wps_clip_image-21441

(Figure38:系统找不到指定的路径)

这个时候你重新发布订阅是没有默认路径可以设置的,可以修改?我没找到可以设置的地方,只能通过另外一种方式进行修改,在发布属性中修改快照路径:

wps_clip_image-7924

(Figure39:默认文件夹)

wps_clip_image-1850

(Figure40:设置文件夹)

在订阅服务器上修改订阅属性的快照文件夹:

wps_clip_image-19512

(Figure41:备用文件夹)

2. 在订阅服务器上同样需要设置SQL Server Agent登陆帐号为上面文件夹访问用户bfadmin,不然会出现下面的错误:

wps_clip_image-12671

(Figure42:错误信息)

设置帐号之后需要重启SQL Server Agent服务

wps_clip_image-7125

(Figure43:订阅服务器SQL Server Agent设置)

3. 在发布服务器上无法对订阅服务器进行【重新初始化】,报下面错误信息,即使在发布服务器上设置了:

wps_clip_image-10620

(Figure44:错误信息)

上面这个错误暂时还没有解决,不过关于命名实例的复制已经成功了,虽然成功了,但是还是要建议大家尽量不要在生产环境中安装多实例,避免出现不必要的问题;

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
6天前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
12天前
|
SQL 存储 安全
sql server 数据库实例
SQL Server 数据库实例是指在 SQL Server 中创建的一个独立的数据库环境。每个数据库实例都拥有自己的一套完整的数据库文件、配置设置、用户和权限等,可以独立地进行管理和操作。以下是关于
|
30天前
|
SQL API 流计算
实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
156 1
|
8天前
|
SQL Oracle 关系型数据库
若干SQL创建数据库实例
在SQL中创建数据库,各系统方法各异。MySQL和SQL Server使用`CREATE DATABASE`语句,如`CREATE DATABASE mydb;`。Oracle通常借助DBCA,但也可用复杂SQL命令,而PostgreSQL同样使用`CREATE DATABASE`。SQLite在首次连接到不存在的数据库文件时会自动创建。注意各系统中的选项如大小、编码、权限等。
|
24天前
|
SQL 存储 搜索推荐
SQL server增删改查(1)
SQL server增删改查(1)
106 0
|
25天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
169 2
|
25天前
|
SQL JSON atlas
实时计算 Flink版产品使用合集之SQL Server CDC是否支持抽取SQL Server视图
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
30天前
|
SQL 数据处理 API
实时计算 Flink版产品使用合集之遇到SQL Server锁表问题如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
31 0
|
1月前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之当 SQL Server 源数据库中的数据更新后,CDC 吐出的操作(op)是怎样的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1月前
|
SQL XML Linux
SQL Server的版本
【5月更文挑战第14天】SQL Server的版本
32 3