使用 Microsoft SQL Server AlwaysOn 可用性群組

Microsoft SQL Server AlwaysOn 可用性群組功能是可用性高的災害復原解決方案，提供資料庫鏡像的企業層級替代選項。

必要條件

用於連線至個別複本的認證應與用於連線至 AlwaysOn Listener 的認證相同。

複本應設定為允許連線。

若要設定此，DBA 應執行下列指令碼：

ALTER AVAILABILITY GROUP [{ag_name}]
MODIFY REPLICA ON '{replica_name}'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = 
'TCP://{replica_name}:{Port}'))

設定資料移動閘道以存取 AlwaysOn Listener

連線屬性中的當使用 AlwaysOn Availability Groups 任務，您必須指定 AlwaysOn Listener 的 IP 位址/主機名稱和存取憑證。

限制

不支援 DAG (已發佈的可用性群組)。

存取 AlwaysOn 可用性群組中的備份記錄

相對於在 AlwaysOn 可用性群組同步的作用中交易記錄，每個個別複本的備份交易記錄會不同。因此，若資料移動閘道需要存取備份交易記錄，則如果複本有所需的記錄，就會輪流「詢問」每個複本，直到找到備份交易記錄為止。請注意，若資料移動閘道嘗試取得的複本之一是離線，資料移動閘道將會等待該複本重新上線，然後查詢以取得備份交易記錄。

資料移動閘道在下列情境中，需要在每個複本存取備份交易記錄 (或直到找到所需記錄為止)：

在僅備份模式中運作。

如需更多關於此模式的資訊，請參閱 內部部署 Microsoft SQL Server (記錄為主)。
由於延遲，亦即若有資料移動閘道無法僅使用作用中的記錄處理的高事件 (變更) 率。

從 AlwaysOn 可用性群組讀取備份記錄需要開啟前往所有複本的連線。若您不想這麼做，將連線屬性中的變更處理模式設定為僅線上記錄。

在處理 AlwaysOn 可用性群組時設定非系統管理員使用者

此區段說明在處理 AlwaysOn 可用性群組時如何支援非系統管理員使用者。如需關於在標準環境中設定非系統管理員的資訊，請參閱 在獨立環境中設定非 sysadmin 使用者。

若要在處理 AlwaysOn 可用性群組時支援非系統管理員使用者：

按 設定 Microsoft SQL Server 以便複寫 中所述設定 Microsoft SQL Server 以便複寫。
在來源資料庫上啟用 MS-REPLICATION。進行方式可以是手動或使用系統管理員使用者執行任務一次。

資訊備註
MS-REPLICATION 發佈程式應設定為本機或以允許透過關聯的連結伺服器存取非系統管理員使用者的方式來設定。

在每個複本執行下列步驟 (a-l)：

在主要資料庫中建立結構描述 [attrep]。

在主要資料庫建立資料表值式函數 [attrep].[split_partition_list]，如下所示：

USE [master]
GO

set ansi_nulls on
go

set quoted_identifier on
go

if (object_id('[attrep].[split_partition_list]','TF')) is not null
drop  function [attrep].[split_partition_list];
go

create function [attrep].[split_partition_list] 
( 
@plist varchar(8000),	--A delimited list of partitions	
@dlm nvarchar(1)	--Delimiting character
) 
returns @partitionsTable table --Table holding the BIGINT values of the string fragments
(
pid bigint primary key
) 
as 
begin
declare @partition_id bigint;
declare @dlm_pos integer;
declare @dlm_len integer;

set @dlm_len = len(@dlm);

while (charindex(@dlm,@plist)>0)
begin 
set @dlm_pos = charindex(@dlm,@plist);
set @partition_id = cast( ltrim(rtrim(substring(@plist,1,@dlm_pos-1))) as bigint);
insert into @partitionsTable (pid) values (@partition_id)
set @plist = substring(@plist,@dlm_pos+@dlm_len,len(@plist));
end 
set @partition_id = cast (ltrim(rtrim(@plist)) as bigint);
insert into @partitionsTable (pid) values (  @partition_id  );
return
end
GO

在主要資料庫建立程序 [attrep].[rtm_dump_dblog]，如下所示：

USE [master]
GO

use [MASTER] 
go 

if (object_id('[attrep].[rtm_dump_dblog]','P')) is not null
drop procedure [attrep].[rtm_dump_dblog]; 
go

set ansi_nulls on
go 

set quoted_identifier on 
go
							
create procedure [attrep].[rtm_dump_dblog]
(
@start_lsn        varchar(32),
@seqno            integer,
@filename         varchar(260),
@partition_list        varchar(8000), -- A comma delimited list: P1,P2,... Pn
@programmed_filtering integer,
@minPartition     bigint,
@maxPartition     bigint
) 
as begin

        declare @start_lsn_cmp varchar(32); -- Stands against the GT comparator

        SET NOCOUNT ON  -- Disable "rows affected display"

        set @start_lsn_cmp = @start_lsn;
        if (@start_lsn_cmp) is null
                set @start_lsn_cmp = '00000000:00000000:0000';

        if (@partition_list is null)
        begin
                RAISERROR ('Null partition list waspassed',16,1);
                return
                --set @partition_list = '0,';    -- A dummy which is never matched
        end

        if (@start_lsn) is not null
                set @start_lsn = '0x'+@start_lsn;

if (@programmed_filtering=0)
        SELECT
                [Current LSN],
                [operation],
                [Context],
                [Transaction ID],
                [Transaction Name],
                [Begin Time],
                [End Time],
                [Flag Bits],
                [PartitionID],
                [Page ID],
                [Slot ID],
                [RowLog Contents 0],
                [Log Record],
                [RowLog Contents 1] -- After Image
        FROM
                fn_dump_dblog (
                        @start_lsn, NULL, N'DISK', @seqno, @filename,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default)
        where [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS -- This aims for implementing FN_DBLOG based on GT comparator.
        and
        (
          (  [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') )
          or
          (  [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')
                and
                ( ( [context]   in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX') )
                and       [PartitionID] in ( select * from master.attrep.split_partition_list (@partition_list,','))
          )
          or
          ([operation] = 'LOP_HOBT_DDL')
        )
else
        SELECT
                [Current LSN],
                [operation],
                [Context],
                [Transaction ID],
                [Transaction Name],
                [Begin Time],
                [End Time],
                [Flag Bits],
                [PartitionID],
                [Page ID],
                [Slot ID],
                [RowLog Contents 0],
                [Log Record],
                [RowLog Contents 1] -- After Image
        FROM
                fn_dump_dblog (
                        @start_lsn, NULL, N'DISK', @seqno, @filename,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default)
        where [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS -- This aims for implementing FN_DBLOG based on GT comparator.
        and
        (
          (  [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') )
          or
          (  [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')
                and
                ( ( [context]   in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX') )
                and           ([PartitionID] is not null) and ([PartitionID] >= @minPartition and [PartitionID]<=@maxPartition)
          )
          or
          ([operation] = 'LOP_HOBT_DDL')
        )
        SET NOCOUNT OFF -- Re-enable "rows affected display"

end
GO

在主要 DB 建立憑證：

Use [master]
Go
CREATE CERTIFICATE [attrep_rtm_dump_dblog_cert]
ENCRYPTION BY PASSWORD = N'choose_your_own_pwd'
WITH SUBJECT = N'Certificate for FN_DUMP_DBLOG Permissions';

從憑證建立登入，如下所示：

Use [master]
Go
CREATE LOGIN attrep_rtm_dump_dblog_login FROM CERTIFICATE
[attrep_rtm_dump_dblog_cert];

將登入新增至系統管理員伺服器角色，如下所示：

ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_dump_dblog_login];

依憑證將簽章新增至 [master].[attrep].[rtm_dump_dblog]，如下所示：

Use [master]
GO
ADD SIGNATURE
TO [master].[attrep].[rtm_dump_dblog]
BY CERTIFICATE [attrep_rtm_dump_dblog_cert]
					WITH PASSWORD = 'choose_your_own_pwd';

若重新建立儲存的程序，您需要重新新增簽章。

在主要資料庫建立程序 [attrep].[rtm_position_1st_timestamp]，如下所示：

use [master]
if object_id('[attrep].[rtm_position_1st_timestamp]','P') is not null
DROP PROCEDURE [attrep].[rtm_position_1st_timestamp];
go
create procedure [attrep].[rtm_position_1st_timestamp]
(
@dbname                sysname,      -- Database name
@seqno                 integer,      -- Backup set sequence/position number within file
@filename              varchar(260), -- The backup filename
@1stTimeStamp          varchar(40)   -- The timestamp to position by
) 
as begin

SET NOCOUNT ON       -- Disable "rows affected display"

declare @firstMatching table
(
cLsn varchar(32),
bTim datetime
)

declare @sql nvarchar(4000)
declare @nl                       char(2)
declare @tb                       char(2)
declare @fnameVar                 nvarchar(254) = 'NULL'

set @nl  = char(10); -- New line
set @tb  = char(9)   -- Tab separator

if (@filename is not null)
set @fnameVar = ''''+@filename +''''

set @sql='use ['+@dbname+'];'+@nl+
'select top 1 [Current LSN],[Begin Time]'+@nl+
'FROM fn_dump_dblog (NULL, NULL, NULL, '+ cast(@seqno as varchar(10))+','+ @fnameVar+','+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default)'+@nl+
'where operation=''LOP_BEGIN_XACT''' +@nl+
'and [Begin Time]>= cast('+''''+@1stTimeStamp+''''+' as datetime)'+@nl

--print @sql
delete from  @firstMatching 
insert into @firstMatching  exec sp_executesql @sql    -- Get them all

select top 1 cLsn as [matching LSN],convert(varchar,bTim,121) as [matching Timestamp] from @firstMatching;

SET NOCOUNT OFF      -- Re-enable "rows affected display"

end
GO

在主要 DB 建立憑證：

Use [master]
Go
CREATE CERTIFICATE [attrep_rtm_position_1st_timestamp_cert]
ENCRYPTION BY PASSWORD = N'choose_your_own_pwd'
WITH SUBJECT = N'Certificate for FN_POSITION_1st_TIMESTAMP Permissions';

從憑證建立登入，如下所示：

Use [master]
Go
CREATE LOGIN attrep_rtm_position_1st_timestamp_login FROM CERTIFICATE
[attrep_rtm_position_1st_timestamp_cert];

將登入新增至系統管理員伺服器角色，如下所示：

ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_position_1st_timestamp_login];

依憑證將簽章新增至 [master].[attrep].[rtm_position_1st_timestamp]，如下所示：

Use [master]
GO
ADD SIGNATURE
TO [master].[attrep].[rtm_position_1st_timestamp]
BY CERTIFICATE [attrep_rtm_position_1st_timestamp_cert]
WITH PASSWORD = 'choose_your_own_pwd';

若重新建立儲存的程序，您需要重新新增簽章。

透過下列權限/角色建立使用者 (在下列每個資料庫)：

資訊備註
應在每個複本透過相同的 SID 建立登入。
- 主要 DB (在每個複本)：
  - 在 sys.fn_dblog 選取
  - 檢視任何定義
  - 檢視伺服器狀態 (應向登入授予)。
  - 在 sp_repldone 執行
  - 在 sp_replincrementlsn 執行
  - 在 sp_addpublication 執行
  - 在 sp_addarticle 執行
  - 在 sp_articlefilter 執行
  - 在 [attrep].[split_partition_list] 選取
  - 在 [attrep].[rtm_dump_dblog] 執行
  - 在 [attrep].[rtm_position_1st_timestamp] 執行
- MSDB DB (在每個複本)：
  - 在 msdb.dbo.backupset 選取
  - 在 msdb.dbo.backupmediafamily 選取
  - 在 msdb.dbo.backupfile 選取
- 對於來源資料庫 (由於資料庫已同步，這只能在主要複本進行)：
  - db_owner 角色

此頁面是否對您有幫助？

若您發現此頁面或其內容有任何問題——錯字、遺漏步驟或技術錯誤——請告知我們可以如何改善！

在此留下意見回饋