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

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: ApsaraDB For SQL Server Multi-AZ 高可用版数据库使用介绍 引言 RDS SQL Server Multi-AZ 高可用版涵盖了SQL Server 2008 R2标准版和企业版、SQL Server 2012 标准版和企业版、SQL Server 2014 标准版和企业版、SQL Server 2016 标准版和企业版 。

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权限,后者会影响创建链接服务器的使用。
lg1.png

其他版本(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角色会自动加入到新建用户中。
lg2.png

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:
lg3.png

删除Login

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

 DROP LOGIN rds_ha_sec_user 

lg4.png

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.png

这你会看到当你创建一个数据库后,你就被授予了这个数据库的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' )

db1.png

更新数据库

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

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

db2.png

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

ALTER DATABASE db
SET PARTNER OFF

ALTER DATABASE db
SET RECOVERY SIMPLE

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

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

ALTER DATABASE db
SET PARTNER OFF

ALTER DATABASE db
SET  OFFLINE

DB4.png

以前我们有个专门让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'

db5.png

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'

DB6.png

关闭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'

DB7.png

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'

LINK.png

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

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

相关实践学习
使用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
目录
相关文章
|
2月前
|
SQL 开发框架 .NET
ASP.NET连接SQL数据库:详细步骤与最佳实践指南ali01n.xinmi1009fan.com
随着Web开发技术的不断进步,ASP.NET已成为一种非常流行的Web应用程序开发框架。在ASP.NET项目中,我们经常需要与数据库进行交互,特别是SQL数据库。本文将详细介绍如何在ASP.NET项目中连接SQL数据库,并提供最佳实践指南以确保开发过程的稳定性和效率。一、准备工作在开始之前,请确保您
214 3
|
24天前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
5天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
10天前
|
SQL 数据库
gbase 8a 数据库 SQL优化案例-关联顺序优化
gbase 8a 数据库 SQL优化案例-关联顺序优化
|
24天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第8天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括系统准备、配置安装源、安装 SQL Server 软件包、运行安装程序、初始化数据库以及配置远程连接。通过这些步骤,您可以顺利地在 CentOS 系统上部署和使用 SQL Server 2019。
|
25天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第7天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括系统要求检查与准备、配置安装源、安装 SQL Server 2019、配置 SQL Server 以及数据库初始化(可选)。通过这些步骤,你可以成功安装并初步配置 SQL Server 2019,进行简单的数据库操作。
|
1月前
|
SQL 数据采集 监控
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
19 2
|
2月前
|
SQL JSON Java
没有数据库也能用 SQL
SPL(Structured Process Language)是一款开源软件,允许用户直接对CSV、XLS等文件进行SQL查询,无需将数据导入数据库。它提供了标准的JDBC驱动,支持复杂的SQL操作,如JOIN、子查询和WITH语句,还能处理非标准格式的文件和JSON数据。SPL不仅简化了数据查询,还提供了强大的计算能力和友好的IDE,适用于多种数据源的混合计算。
|
2月前
|
SQL 监控 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
2月前
|
SQL 数据库 数据库管理
数据库SQL函数应用技巧与方法
在数据库管理中,SQL函数是处理和分析数据的强大工具