ApsaraDB For SQL Server Multi-AZ 高可用版数据库常用功能使用介绍

2021-07-08 20:42:23

ApsaraDB For SQL Server Multi-AZ 高可用版数据库使用介绍

引言

RDS SQL Server Multi-AZ 高可用版涵盖了SQL Server 2008 R2标准版和企业版、SQL Server 2012 标准版和企业版、SQL Server 2014 标准版和企业版、SQL Server 2016 标准版和企业版。目前能够购买的是SQL Server 2012 标准版和企业版和SQL Server 2016 标准版和企业版。高可用的基本原理是基于数据库镜像技术实现Master-Slave架构。在高可用版本中，我们为什么叫 Multi-AZ，是因为首先我们的默认是基于多可用区的，当然也可以是单可用区，都是兼容的。本次发布的产品，相对于老的SQL Server 2008 R2具有很多特性。既满足了传统用户的使用习惯，又适应了云服务化的数据库特性，因此在构建产品过程中选择了比较合理的方案，其目的是简化用户工作，又能增强数据库的安全与稳定。我们的期望是让用户使用简单、快速、高效、精细。

新架构下高可用特性

1. 更安全：RDS始终位于用户自己的私有网络中（VPC)

2. 更易用：权限开放足够大，用户自操作很强

3. 扩展好：弹性升级和空间扩展会非常快速和稳定

4. 更亲民：克隆实例和克隆数据库让你操作简单和快速，上云方式更简洁精确

5. 更高效：舍弃很多OPENAPI，直接利用T-SQL或者Ali-T-SQL对数据库进行操作和管理

LOGIN的使用

Login的使用

RDS SQL Server Multi-AZ 高可用版创建Login与单机版类似，但会做一些操作日志和规范，比如不能删除RDS系统的相关Login，也不能更改其密码，如果删除和更改就会失败。同时主库和备库实例的Login存在一个同步问题。具体如下所述。前提是加入有初始账号（这里是test)。

创建Login

基于 SQL Server Multi-AZ的2008 R2高可用版本（非原来2008 R2）版本：


CREATE LOGIN test001 
WITH PASSWORD=N'123@#$Dfk',
CHECK_POLICY=OFF

MSG:
Login User [test] grant login [test001] server role.
User [test001] server level permissions handled completed.
user [test001] in msdb permissions handled completed.
Handle user [test001] permissions completed.

注意：由于SQL Serve 2008 R2 设计机制问题，在创建Login时，增加服务器级别的角色不可以在登录触发器里面有效运行，原因是事务不能包含在登录触发器，所以未主动加入processadmin和setupadmin角色,由于RDS SQL Server 2008 R2 Multi-AZ 高可用版的初始账号具有processadmin和setupadmin角色，因此用户可以手动加入这两个角色，前者会影响KILL权限，后者会影响创建链接服务器的使用。

其他版本（SQL Server Multi-AZ 2012/2014/2016）：

CREATE LOGIN test001 
WITH PASSWORD=N'123@#$Dfk',
CHECK_POLICY=OFF

Login User [test] grant login [test001] server role.
User [test001] server level permissions handled completed.
user [test001] in msdb permissions handled completed.
Handle user [test001] permissions completed.

与2008 R2不用的是processadmin和setupadmin角色会自动加入到新建用户中。

3. 更新Login

你可以更改你的登录账户的密码，例如：

 ALTER LOGIN test001 
WITH PASSWORD=N'123',
CHECK_POLICY=OFF

但你不能更改RDS系统相关账户的密码，例如：

ALTER LOGIN rds_ha_sec_user 
WITH PASSWORD=N'123',
CHECK_POLICY=OFF

MSG:

删除Login

同样，你不能删除你创建的任何之外的LOGIN，否则会出现一下错误：

 DROP LOGIN rds_ha_sec_user

Login的主备同步

RDS SQL Server Multi-AZ 高可用版是master-slave架构，虽然数据库级别在做镜像后是可以同步到slave节点，但是实例级别的很多对象都是无法自动同步过去的，凡是对象存储在系统数据库master、msdb中的，都需要主动实现同步，RDS采用了准实时的同步策略，当你创建Login后，Login会很快同步到slave中，同步过程中会将LOGIN的sid和hash passward带到slave，当你的RDS实例主备切换后，你无需新建Login，可无缝保持业务永续！

Database的使用

Database 的使用

RDS SQL Server Multi-AZ 高可用版在数据库层面做了非常多的改善，也提供了很多有用的功能，但这些功能是有些限制的，不过只要遵守这些规则，用起来还是会感觉到很清爽。

创建数据库

创建数据库时，你无需指定路径，路径会规范好，即使指定路径，也是要符合规范，否则就会创建失败。例如：
成功：

CREATE DATABASE db

这你会看到当你创建一个数据库后，你就被授予了这个数据库的db_owner角色，拥有了这个角色，你可以为其他用户分配权限和角色。但所有数据库用户会回收掉数据库备份权限，并且你通过其他方式加不回去这个权限的。

违反规范：

CREATE DATABASE db1 
ON  PRIMARY 
( NAME = N'db1', FILENAME = N'E:\Backup\db1.mdf' )
 LOG ON 
( NAME = N'db1_log', FILENAME = N'E:\Backup\db1_log.LDF' )

更新数据库

1. 更改属性需要符合路径规范

ALTER DATABASE db
MODIFY FILE 
( NAME = N'db', FILENAME = N'E:\Backup\db.mdf' )

2.不能将数据库恢复模式设置为simple和 bulk_logged

ALTER DATABASE db
SET PARTNER OFF

ALTER DATABASE db
SET RECOVERY SIMPLE

更改前需要移除镜像关系。

3. 不能将数据库设置为offline

ALTER DATABASE db
SET PARTNER OFF

ALTER DATABASE db
SET  OFFLINE

以前我们有个专门让offline上线的存储过程，但现在我们的策略是不准确让用户OFFLINE

EXEC sp_rds_set_db_online 'db_name'

删除数据库

因为有镜像关系存，所以不能直接删除数据库，需要将数据库的镜像关系先移除，注意，因为考虑到用户可能无意间解除镜像关系，我们在48秒后会考虑重新恢复镜像关系，所以需要考虑及时性。

ALTER DATABASE db SET PARTNER OFF
DROP DATABASE db

你可能会遇到删除数据库失败的问题，因为可能存在一些SESSION占用，需要将这些SESSION KILL掉就好了。
RDS也提供一个方便的T-SQL帮助你一键搞定：

EXEC sp_rds_drop_database 'db_name'

克隆数据库

克隆数据库的使用在ERP软件中非常广泛，在构造测试数据库，初始化数据库得到应用，正常的数据库上云可能会花掉20分钟，克隆数据库只需要几分钟就搞定，你只需要指定下面命令即可：

EXEC sp_rds_copy_database 'db1','db1_copy'

CDC

直接使用CDC功能是需要很高权限的，因此我们提供了一个T-SQL接口，让用户可以设置：
启用DB的CDC功能


SELECT SUSER_NAME()

USE db1
GO

EXEC sp_rds_cdc_enable_db

SELECT 
    name,is_cdc_enabled
FROM sys.databases
WHERE name='db1'

关闭CDC功能


SELECT SUSER_NAME()
SELECT 
    name,is_cdc_enabled
FROM sys.databases
WHERE name='db1'

USE db1
GO
EXEC sp_rds_cdc_disable_db

SELECT 
    name,is_cdc_enabled
FROM sys.databases
WHERE name='db1'

Change Tracking

开启变更跟踪：

SELECT SUSER_NAME()

EXEC sp_rds_change_tracking 'db1',1

SELECT DB_NAME(database_id), * FROM sys.change_tracking_databases

关闭变更跟踪：

SELECT SUSER_NAME()

SELECT DB_NAME(database_id), * FROM sys.change_tracking_databases

EXEC sp_rds_change_tracking 'db1',0

SELECT DB_NAME(database_id), * FROM sys.change_tracking_databases

创建用户

USE db1
GO
SELECT SUSER_NAME()
CREATE USER test001

删除用户

USE db1
GO
SELECT SUSER_NAME()
DROP USER test001

分配角色

USE db1
GO
SELECT SUSER_NAME()
EXEC sp_addrolemember 'db_owner','test001'

授权数据库

在很多用户使用数据库过程中，会遇到一些全局授权的问题。我们提供了T-SQL来一键实现
对一个用户针对所有用户数据库授权：

EXEC sp_rds_set_all_db_privileges 'login-name','db_role'

对一个用户的某些用户数据库授权：

EXEC sp_rds_set_all_db_privileges 'login-name','db_role','db1,db2,db3,db4...'

数据库主备同步

数据库同样存在主备同步的问题，而且在创建数据库，删除数据库，克隆数据库都会同步。创建和删除在规则约定上相对容易，触发机制简单，DDL触发器完成。克隆数据库的触发用户运行命令完成。创建和克隆数据库会走主备搭建镜像的逻辑。克隆数据库如果源数据库较大，需要的时间比较长。

数据库备份

RDS提供备份服务，不需要用户备份，备份权限回收。

DBCC 设置

目前支持的标记有:(1222),(1204),(1117),(1118),(1211),(1224),(3604) 。使用方法：
开启：

SELECT SUSER_NAME()
EXEC sp_rds_dbcc_trace 1222,1
DBCC TRACESTATUS(-1)

关闭：

SELECT SUSER_NAME()
DBCC TRACESTATUS(-1)
EXEC sp_rds_dbcc_trace 1222,0
DBCC TRACESTATUS(-1)

数据库实例参数设置

目前受支持的参数设置有：
(N''fill factor (%)'',0),
(N''max worker threads'',1),
(N''cost threshold for parallelism'',1),
(N''max degree of parallelism'',1),
(N''min server memory (MB)'',1),
(N''max server memory (MB)'',1),
(N''blocked process threshold (s)'',1)

设置参数：

SELECT * FROM sys.configurations
WHERE name='max degree of parallelism'

EXEC sp_rds_configure 'max degree of parallelism',4

SELECT * FROM sys.configurations
WHERE name='max degree of parallelism'

创建链接服务器

创建链接服务器是个复杂的工作，如果只做简单的链接服务器，就很容易，如果要利用到分布式事务，就需要特别对待，但我们提供了一键部署链接服务器和分布式事务的方案，同时支持HA切换无缝对接，让业务永续，请注意，一定要利用我们的方案，否则将无法实现功能。

EXEC sp_rds_add_linked_server
    'mylink',  -- link serve name
    'gttestsync1152016std.mssql.76be0d97-c.rds.aliyuncs.com,1433', --link server address: dns address and port
    'test_link', --link server destination user
    '123',--link server destination user
    'test',--link server source user,use slave create link server
    '123456'

还可以指定一个参数，指定链接服务器的属性：例如

DECLARE
        @linked_server_name sysname = N''yangzhao_slb'',
        @data_source sysname = N''****.sqlserver.rds.aliyuncs.com,3888 '',   --style: 10.1.10.1,1433
        @user_name sysname = N''ay15'' ,
        @password nvarchar(128) = N''******'',
        @source_user_name=N''test'',
        @source_password=N''******''
        @link_server_options xml
        = N''
            <rds_linked_server>
                <config option="data access">true</config>
                <config option="rpc">true</config>
                <config option="rpc out">true</config>
            </rds_linked_server>
        ''
        EXEC sp_rds_add_linked_server
            @linked_server_name,
            @data_source,
            @user_name,
            @password,
            @source_user_name,
            @source_password,
            @link_server_options

链接服务器验证非常简单：

SELECT * FROM mylink.master.sys.servers

分布式事务验证：
第一步：在目的实例创建一个账户test_link

CREATE LOGIN test_link 
WITH PASSWORD='123',
CHECK_POLICY=OFF

第二步：以test_link用户创建一个db

CREATE DATABASE db

第三步：在db库中创建一个存储过程

USE db
GO

CREATE PROC  p_get_host_name
AS

SELECT HOST_NAME()

第四步：在源实例执行下列代码：直接执行EXEC mylink.db.dbo.p_get_host_name是不要求分布式事务开通，但将EXEC mylink.db.dbo.p_get_host_name结果插入到一个表对象是需要开启分布式事务，如果有结果集生成，表示分布式事务功能正常：

DECLARE
    @link TABLE (
        host sysname
    )

INSERT INTO @link
EXEC mylink.db.dbo.p_get_host_name

SELECT * FROM @link

SQL Agent

SQL Agent创建的owner是创建者，不能删除别人创建的JOB，同时，JOB其实也是存储在MSDB中的，如果HA切换，JOB也是需要同步过去的，我们的系统也是在准实时同步JOB的新建，更改和删除。做到让用户业务永续。

KILL权限

RDS SQL Server Multi-AZ 高可用版支持，直接使用KILL 进程号。例如：杀掉55号进程：

KILL 55

Profiler权限

RDS SQL Server Multi-AZ 高可用版支持性能跟踪权限

数据库优化顾问向导

RDS SQL Server Multi-AZ 高可用版支持数据库优化顾问向导

查看数据库日志

非常简单：

EXEC sp_rds_read_error_log

单机版请参考下列链接

https://www.atatech.org/articles/60838

码农公寓