Microsoft SQL Server AlwaysOn Kullanılabilirlik Gruplarıyla Çalışma

Microsoft SQL Server AlwaysOn Kullanılabilirlik Grupları özelliği, veritabanı yansıtma özelliğine kurumsal düzeyde alternatif sağlayan, yüksek kullanılabilirliğe sahip bir olağanüstü durum kurtarma çözümüdür.

Önkoşullar

Tek tek çoğaltmalara bağlanırken kullanılan kimlik bilgileri, AlwaysOn Dinleyicisi'ne bağlanırken kullanılanlarla aynı olmalıdır.

Çoğaltmalar, bağlantılara izin verecek şekilde yapılandırılmalıdır.

Bunu yapılandırmak için, DBA aşağıdaki kodu çalıştırmalıdır:

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 Dinleyicisi'ne erişmek için Veri Hareketi ağ geçidi çözümünü yapılandırma

Bağlantı özelliklerinde AlwaysOn Kullanılabilirlik Grupları ile çalışrken AlwaysOn Dinleyicisi'nin IP adresini/konak adını ve erişim kimlik bilgilerini belirtmeniz gerekir.

Sınırlamalar

DAG (Dağıtılmış Kullanılabilirlik Grupları) desteklenmez.

AlwaysOn Kullanılabilirlik Gruplarında yedekleme günlüklerine erişim

AlwaysOn Kullanılabilirlik Grubu genelinde eşitlenen etkin işlem günlüklerinin tersine, yedekleme işlem günlükleri tek tek her çoğaltma için farklıdır. Sonuç olarak, Veri Hareketi ağ geçidi çözümünün yedekleme işlem günlüklerine erişmesi gerektiğinde, yedekleme işlem günlüklerinin yeri belirlenene kadar sırayla her Çoğaltmaya gerekli günlüklere sahip olup olmadığını "sorar". Veri Hareketi ağ geçidi çözümünün ulaşmaya çalıştığı Çoğaltmalardan biri çevrimdışı olduğunda, Veri Hareketi ağ geçidi söz konusu Çoğaltmanın yeniden çevrimiçi olmasını bekler ve ardından yedekleme işlem günlükleri için onu sorgular.

Aşağıdaki senaryolarda, Veri Hareketi ağ geçidi çözümünün her Çoğaltmadaki yedekleme işlem günlüklerine erişmesi gerekir (veya gerekli günlükleri bulana kadar):

Yalnızca yedekleme modunda çalışma.

Bu mod hakkında daha fazla bilgi için bkz. Şirket içi Microsoft SQL Server (günlük tabanlı).
Gecikmeden dolayı; örneğin olayların hızının, Veri Hareketi ağ geçidi çözümünün yalnızca etkin günlüğü kullanarak işleyemediği kadar yüksek olması durumu.

AlwaysOn Kullanılabilirlik Grupları'ndan yedekleme günlüklerini okumak için, çoğaltmaların tümünde bağlantıların açılması gerekir. Bunu yapmak istemiyorsanız, bağlantı özelliklerinde İşleme modunu değiştir'i Yalnızca Çevrimiçi Günlükler olarak ayarlayın.

AlwaysOn kullanılabilirlik gruplarıyla çalışırken sysadmin olmayan bir kullanıcı ayarlama

Bu bölümde AlwaysOn kullanılabilirlik gruplarıyla çalışırken sysadmin olmayan bir kullanıcının nasıl destekleneceği açıklanmaktadır. Standart bir ortamda sysadmin olmayan bir kullanıcıyı ayarlama hakkında bilgi için bkz. Bağımsız bir ortamda sysadmin olmayan bir kullanıcı ayarlama.

AlwayOn kullanılabilirlik gruplarıyla çalışırken sysadmin olmayan bir kullanıcıyı desteklemek için:

Microsoft SQL Server'ı çoğaltma için ayarlama bölümünde açıklandığı gibi Microsoft SQL Server'ı Çoğaltma için ayarlayın.
Kaynak veritabanında MS-REPLICATION öğesini etkinleştirin. Bu işlem manuel olarak veya sysadmin kullanıcısını kullanıp görevi bir kez çalıştırarak yapılabilir.

Bilgi notu
MS-REPLICATION dağıtımcısı yerel olarak veya ilişkilendirilmiş bağlantılı sunucu üzerinden sysadmin olmayan kullanıcılara erişim veren bir yolla yapılabilir.

Her çoğaltmada aşağıdaki adımları (a-l) uygulayın:

Ana DB'de [attrep] şeması oluşturun.

[attrep].[split_partition_list] fonksiyonunu içeren tabloyu Ana veritabanında aşağıda gösterildiği gibi oluşturun:

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] prosedürünü Ana veritabanında aşağıda gösterildiği gibi oluşturun:

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

Ana DB'de sertifika oluşturun:

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';

Aşağıda gösterildiği gibi sertifikadan oturum açma bilgileri oluşturun:

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

Aşağıda gösterildiği gibi oturum açma bilgilerini sysadmin sunucu rolüne ekleyin:
```
ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_dump_dblog_login];
```
[master].[attrep].[rtm_dump_dblog] ögesine sertifika imzasını aşağıda gösterildiği gibi ekleyin:
```
Use [master]
GO
ADD SIGNATURE
TO [master].[attrep].[rtm_dump_dblog]
BY CERTIFICATE [attrep_rtm_dump_dblog_cert]
					WITH PASSWORD = 'choose_your_own_pwd';
```
Bilgi notu
Saklı prosedür yeniden oluşturulursa, imzayı yeniden eklemeniz gerekir.

[attrep].[rtm_position_1st_timestamp] prosedürünü Ana veritabanında aşağıda gösterildiği gibi oluşturun:

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

Ana DB'de sertifika oluşturun:

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';

Aşağıda gösterildiği gibi sertifikadan oturum açma bilgileri oluşturun:

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

Aşağıda gösterildiği gibi oturum açma bilgilerini sysadmin sunucu rolüne ekleyin:
```
ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_position_1st_timestamp_login];
```
[master].[attrep].[rtm_position_1st_timestamp] ögesine sertifika imzasını aşağıda gösterildiği gibi ekleyin:
```
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';
```
Bilgi notu
Saklı prosedür yeniden oluşturulursa, imzayı yeniden eklemeniz gerekir.

Aşağıdaki izinlere/rollere sahip bir kullanıcı oluşturun (aşağıdaki veritabanlarının her birinde):

Bilgi notu
Oturum açma bilgileri her çoğaltmada aynı SID ile oluşturulmalıdır.
- Ana DB (her çoğaltmada) :
  - sys.fn_dblog üzerinde select
  - view any definition
  - view server state (oturum açma bilgilerine verilmelidir).
  - sp_repldone üzerinde execute
  - sp_replincrementlsn üzerinde execute
  - sp_addpublication üzerinde execute
  - sp_addarticle üzerinde execute
  - sp_articlefilter üzerinde execute
  - [attrep].[split_partition_list] üzerinde select
  - [attrep].[rtm_dump_dblog] üzerinde select
  - [attrep].[rtm_position_1st_timestamp] üzerinde execute
- MSDB DB (her çoğaltmada):
  - msdb.dbo.backupset üzerinde select
  - msdb.dbo.backupmediafamily üzerinde select
  - msdb.dbo.backupfile üzerinde select
- Kaynak veritabanı için (veritabanı eşitlendiğinden, yalnızca birincil çoğaltmada yapılabilir):
  - db_owner ROLE

Bu sayfa size yardımcı oldu mu?

Bu sayfa veya içeriği ile ilgili bir sorun; bir yazım hatası, eksik bir adım veya teknik bir hata bulursanız, bize bildirin, düzeltelim!

Geri bildiriminizi buradan iletin