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:Backupdb1.mdf` )
 LOG ON 
( NAME = N`db1_log`, FILENAME = N`E:Backupdb1_log.LDF` )

更新資料庫

1. 更改屬性需要符合路徑規範

ALTER DATABASE db
MODIFY FILE 
( NAME = N`db`, FILENAME = N`E:Backupdb.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