Зеркальное отображение (безопасность на основе сертификатов, следящий сервер).

Сегодня речь пойдет о реализации такого решения высокого уровня доступности как зеркалирование баз данных. Это очень полезное и легкое в развертывании решение, которое может значительно повысить доступность приложений. Когда зеркалирование появилось в MS SQL 2005 его даже стали называть убийцей кластеров за отсутствие сложных административных настроек, легкость в реализации, возможность автоматической отработки отказа. При этом в отличие от кластеров здесь нет общего дискового хранилища. Вместо этого каждый участник сеанса зеркального отображения может располагаться на собственных дисках, что еще больше повышает безопасность. В качестве примера создадим простую базу данных с одной таблицей для хранения документов. В данном решении будут задействованы 3 сервера: основной, к которому могут подключаться приложения, зеркальный с синхронизированной резервной копией данных и следящий, который нужен для автоматической отработки отказа (если работа будет настроена в безопасном режиме).

if db_id ( N'Docs' ) is null
begin
 create database Docs
  on primary
  (
   name  = DocData,
   filename = 'C:\В\DbData\DocData.mdf',
   size  = 10 Mb,
   filegrowth = 10 Mb,
   maxsize  = unlimited
  )
  log on
  (
   name  = DocLog,
   filename = 'C:\В\TranLog\DocLog.ldf',
   size  = 10 Mb,
   filegrowth = 10 Mb,
   maxsize  = unlimited
  )
end
go

alter database Docs set recovery full
go

use Docs
go

create table dbo.Documents
(
 iDocId int identity ( 1, 1 ) not null,
 vcDoc varchar ( 100 )  not null,
 varDoc varbinary ( max ) not null,
 constraint PK_Documents_iDocId primary key clustered ( iDocId asc ) on [primary],
 constraint AK_Documents_vcDoc unique nonclustered ( vcDoc asc ) on [primary]
) on [primary]
go

insert into dbo.Documents ( vcDoc, varDoc )
 values ( 'Test1', 0x )
go

Теперь для подготовки сеанса зеркального отображения необходимо сделать полный бэкап базы и бэкап ее журнала транзакций:

backup database Docs
 to disk = 'C:\В\Docs.bak'
 with format, init, compression, stats = 1, norewind, checksum
go

backup log Docs
 to disk = 'C:\В\Docs.trn'
 with format, init, compression, stats = 1, norewind, checksum
go

Созданные резервные копии необходимо восстановить на зеркальном сервере с опцией norecovery.

if db_id ( N'Docs' ) is null
begin
 restore database Docs
  from disk = 'C:\В\Docs.bak'
  with
   move 'DocData' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.MIRROR\MSSQL\DATA\DocData.mdf',
   move 'DocLog' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.MIRROR\MSSQL\DATA\DocLog.ldf',
   stats = 1,
   norecovery
 restore log Docs
  from disk = 'C:\В\Docs.trn'
  with
   stats = 1,
   norecovery
end
go

Приступим к настройке объектов для передачи данных между серверами. Понадобятся конечные точки и учетные записи. Безопасность конечных точек лучше настраивать с помощью сертификатов (а не Windows). В этом случае не придется беспокоиться о том под какими учетными записями работают службы сервера (локальными или доменными), входят ли компьютеры в один домен или в разные домены с определенными отношениями (или же находятся вне доменов).

Итак, на основном сервере требуется создать логин для доступа к зеркальному и следящему серверам, настроить сертификат, создать конечную точку, подготовить резервные копии сертификата для восстановления на двух остальных серверах.

use master
go

if suser_id ( N'MainRepl' ) is null
begin
 create login MainRepl
  with
  password  = N'2#$@#!',
  check_policy  = off,
  default_database = master,
  default_language = russian
end
go

use master
go

if database_principal_id ( N'MainReplUser' ) is null
begin
 create user MainReplUser
  from login MainRepl
  with default_schema = dbo
end
go

use master
go

if not exists
(
 select *
 from sys.symmetric_keys
 where name = N'##MS_DatabaseMasterKey##'
)
begin
 create master key
  encryption by password = N'$R#tt5feR#R#D'
end
go

use master
go

if cert_id ( N'MainReplHost' ) is null
begin
 create certificate MainReplHost
  authorization dbo
  with subject = N'Сертификат главного сервера.',
  expiry_date = '2017-12-31',
  start_date = '2016-07-01'
end
go

if not exists
(
 select *
 from sys.endpoints
 where name = N'Mirroring'
)
begin
 create endpoint Mirroring
  state = started
  as tcp
  (
   listener_port = 7024,
   listener_ip = all
  )
  for database_mirroring
  (
   authentication = certificate MainReplHost,
   encryption = required algorithm aes,
   role = all
  )
end
go

backup certificate MainReplHost
 to file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MIRROR\MSSQL\DATA\MainReplHost.cer'
go
backup certificate MainReplHost
 to file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.WITNESS\MSSQL\DATA\MainReplHost.cer'
go

В коде, приведенном выше, перед созданием сертификата в master был создан главный ключ БД для защиты сертификата, так как последний не защищен паролем. Для конечной точки прописана безопасность на основе сертификата. Резервная копия сертификата создана дважды: в доступных местах для зеркального и следящего серверов (следящий и зеркальный сервера на моем компьютере работают под локальными учетными записями). Аналогичные действия требуется выполнить на двух оставшихся серверах. Вот код для зеркального сервера:

use master
go

if suser_id ( N'MirrorRepl' ) is null
begin
 create login MirrorRepl
  with
  password  = N'2#$@#!',
  check_policy  = off,
  default_database = master,
  default_language = russian
end
go

use master
go

if database_principal_id ( N'MirrorReplUser' ) is null
begin
 create user MirrorReplUser
  from login MirrorRepl
  with default_schema = dbo
end
go

use master
go

if not exists
(
 select *
 from sys.symmetric_keys
 where name = N'##MS_DatabaseMasterKey##'
)
begin
 create master key
  encryption by password = N'^YgfeHEF$#23'
end
go

use master
go

if cert_id ( N'MirrorReplHost' ) is null
begin
 create certificate MirrorReplHost
  authorization dbo
  with subject = N'Сертификат зеркального сервера.',
  expiry_date = '2017-12-31',
  start_date = '2016-07-01'
end
go

if not exists
(
 select *
 from sys.endpoints
 where name = N'Mirroring'
)
begin
 create endpoint Mirroring
  state = started
  as tcp
  (
   listener_port = 7025,
   listener_ip = all
  )
  for database_mirroring
  (
   authentication = certificate MirrorReplHost,
   encryption = required algorithm aes,
   role = all
  )
end
go

backup certificate MirrorReplHost
 to file = 'C:\В\MirrorReplHost.cer'
go
backup certificate MirrorReplHost
 to file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.WITNESS\MSSQL\DATA\MirrorReplHost.cer'
go

Здесь все аналогично коду для основного сервера, только для конечной точки указан другой порт из-за того что в данном примере все 3 сервера находятся на одном компьютере. Наконец приведем такой же код для следящего сервера (у конечной точки опять-таки порт отличен от портов конечных точек основного и зеркального серверов).

use master
go

if suser_id ( N'WitnessRepl' ) is null
begin
 create login WitnessRepl
  with
  password  = N'erg343grF#$FE',
  check_policy  = off,
  default_database = master,
  default_language = russian
end
go

use master
go

if database_principal_id ( N'WitnessReplUser' ) is null
begin
 create user WitnessReplUser
  from login WitnessRepl
  with default_schema = dbo
end
go

use master
go

if not exists
(
 select *
 from sys.symmetric_keys
 where name = N'##MS_DatabaseMasterKey##'
)
begin
 create master key
  encryption by password = N'we32@#ESe'
end
go

use master
go

if cert_id ( N'WitnessReplHost' ) is null
begin
 create certificate WitnessReplHost
  authorization dbo
  with subject = N'Сертификат следящего сервера.',
  expiry_date = '2017-12-31',
  start_date = '2016-07-01'
end
go

if not exists
(
 select *
 from sys.endpoints
 where name = N'Mirroring'
)
begin
 create endpoint Mirroring
  state = started
  as tcp
  (
   listener_port = 7023,
   listener_ip = all
  )
  for database_mirroring
  (
   authentication = certificate WitnessReplHost,
   encryption = required algorithm aes,
   role = all
  )
end
go

backup certificate WitnessReplHost
 to file = 'C:\В\WitnessReplHost.cer'
go
backup certificate WitnessReplHost
 to file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MIRROR\MSSQL\DATA\WitnessReplHost.cer'
go

После того как все конечные точки созданы необходимо обеспечить доступ с каждого сервера на 2 других сервера. Для этого для сертификата и логина на каждом сервере необходимо на двух других серверах восстановить резервную копию сертификата и создать одноименного пользователя с правами на конечную точку и на сертификат. Так это делается на основном сервере (пароли на каждом сервере не обязаны совпадать).

use master
go

if suser_id ( N'MirrorRepl' ) is null
begin
 create login MirrorRepl
  with
   password  = 'EG#WferWF43edf',
   check_policy  = off,
   default_database = master,
   default_language = russian
end
go
if suser_id ( N'WitnessRepl' ) is null
begin
 create login WitnessRepl
  with
   password  = 'rf34ewfFWfdsd#@#$$',
   check_policy  = off,
   default_database = master,
   default_language = russian
end
go

use master
go
if database_principal_id ( N'MirrorReplUser' ) is null
begin
 create user MirrorReplUser
  from login MirrorRepl
  with default_schema = dbo
end
go
if database_principal_id ( N'WitnessReplUser' ) is null
begin
 create user WitnessReplUser
  from login WitnessRepl
  with default_schema = dbo
end
go

use master
go
if cert_id ( N'MirrorReplHost' ) is null
begin
 create certificate MirrorReplHost
  authorization MirrorReplUser
  from file = 'C:\В\MirrorReplHost.cer'
end
go
if cert_id ( N'WitnessReplHost' ) is null
begin
 create certificate WitnessReplHost
  authorization WitnessReplUser
  from file = 'C:\В\WitnessReplHost.cer'
end
go

use master
go
grant connect on endpoint::Mirroring to MirrorRepl, WitnessRepl
go

Аналогичный код прогоняется на зеркальном сервере (создаются логины и пользователи для основного и следящего серверов, создаются сертификаты из резервных копий, пользователи делаются владельцами своих сертификатов, логинам предоставляется доступ на подключение к конечной точке).

use master
go

if suser_id ( N'MainRepl' ) is null
begin
 create login MainRepl
  with
   password  = '43#T%Fev',
   check_policy  = off,
   default_database = master,
   default_language = russian
end
go
if suser_id ( N'WitnessRepl' ) is null
begin
 create login WitnessRepl
  with
   password  = 'dgf3t4SVSDv',
   check_policy  = off,
   default_database = master,
   default_language = russian
end
go

use master
go
if database_principal_id ( N'MainReplUser' ) is null
begin
 create user MainReplUser
  from login MainRepl
  with default_schema = dbo
end
go
if database_principal_id ( N'WitnessReplUser' ) is null
begin
 create user WitnessReplUser
  from login WitnessRepl
  with default_schema = dbo
end
go

use master
go
if cert_id ( N'MainReplHost' ) is null
begin
 create certificate MainReplHost
  authorization MainReplUser
  from file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MIRROR\MSSQL\DATA\MainReplHost.cer'
end
go
if cert_id ( N'WitnessReplHost' ) is null
begin
 create certificate WitnessReplHost
  authorization WitnessReplUser
  from file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MIRROR\MSSQL\DATA\WitnessReplHost.cer'
end
go

use master
go
grant connect on endpoint::Mirroring to MainRepl, WitnessRepl
go

В последний раз запустим аналогичный код для сервере-свидетеле:

use master
go

if suser_id ( N'MainRepl' ) is null
begin
 create login MainRepl
  with
   password  = 'rfT#$FRged',
   check_policy  = off,
   default_database = master,
   default_language = russian
end
go
if suser_id ( N'MirrorRepl' ) is null
begin
 create login MirrorRepl
  with
   password  = 'e3e#Rwd',
   check_policy  = off,
   default_database = master,
   default_language = russian
end
go

use master
go
if database_principal_id ( N'MainReplUser' ) is null
begin
 create user MainReplUser
  from login MainRepl
  with default_schema = dbo
end
go
if database_principal_id ( N'MirrorReplUser' ) is null
begin
 create user MirrorReplUser
  from login MirrorRepl
  with default_schema = dbo
end
go

use master
go
if cert_id ( N'MainReplHost' ) is null
begin
 create certificate MainReplHost
  authorization MainReplUser
  from file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.WITNESS\MSSQL\DATA\MainReplHost.cer'
end
go
if cert_id ( N'MirrorReplHost' ) is null
begin
 create certificate MirrorReplHost
  authorization MirrorReplUser
  from file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.WITNESS\MSSQL\DATA\MirrorReplHost.cer'
end
go

use master
go
grant connect on endpoint::Mirroring to MainRepl, MirrorRepl
go

Все готово к включению сеанса зеркального отображения. Для этого сперва надо на зеркальном сервере указать основной сервер в качестве партнера:

alter database Docs set partner = 'tcp://LAPTOP-LBO51UJL:7024'

Выше в качестве значения свойства partner указывается полное доменное имя и через двоеточие порт конечной точки на другом сервере (впереди пишется "tcp://"). Следующим шагом нужно на основном сервере указать адрес зеркального:

alter database Docs set partner = 'tcp://LAPTOP-LBO51UJL:7025'

Зеркальное отображение заработало. Надо отметить что зеркалирование может работать в одном из двух режимов: режим высокой безопасности либо режим высокой производительности. При втором режиме фиксация транзакции происходит асинхронно относительно передачи данных на зеркальный сервер. Этот режим подходит для серверов, в которых не происходят массовые операции и допустима некоторая потеря данных. В асинхронном режиме недоступна автоматическая отработка отказа. Если же требуется наивысший уровень безопасности и нет массовых операций, то можно использовать зеркалирование с синхронном режиме. Для асинхронного режима требуется выполнить инструкцию на основном сервере:

alter database Docs set partner safety off

Опция full позволяет работать в синхронном режиме:

alter database Docs set partner safety full

На основном или зеркальном сервере можно выполнить запрос:

select db_name ( database_id ), *
from sys.database_mirroring
where  db_name ( database_id ) = 'Docs'

Это позволит проверить роль текущего сервера (основной или зеркальный), состояние сеанса зеркалирования (синхронизировано либо идет прием новых строк журнала транзакций), адрeса партнеров и следящего сервера, состояние соединения со свидетелем и режим зеркалирования.
Для выполнения ручной отработки отказа, нужно на сервере, являющемся на данный момент основным выполнить код (требуется перевод в режим высокой безопасности):

use master alter database Docs set partner failover

В этот момент на сервере, который был основным, база данных Docs перейдет в состояние restoring, а на сервере, который был зеркальным, она станет доступной. Перенос данных уже пойдет в обратную сторону: от зеркального сервера к основному. Эти изменения немедленно отразятся и в представлении sys.database_mirroring. Зеркальное отображение можно приостановить, если в этом есть необходимость, а затем продолжить. Для этого используются соответственно инструкции:

alter database Docs set partner suspend
alter database Docs set partner resume

Надо только помнить, что если сеанс приостановлен, то журнал транзакций в базе на основном сервере не будет усекаться до тех пор пока зеркало не станет синхронизированным с основным. Для удаления сеанса используется значение off для опции partner:

alter database Docs set partner off

Если основной сервер стал недоступным и не произошло отработки отказа (при этом следящего нет либо он подключен к зеркальному), то можно принудительно сделать доступным зеркальный сервер. Это приведет к остановке зеркалирования и возможно потере данных, которые не были реплицированы с основного сервера. Зеркальный сервер делается доступным с помощью такой инструкции:

alter database Docs set partner force_service_allow_data_loss

Подключим теперь следящий сервер к основному для возможности автоматической отработки отказа:

alter database Docs set witness = 'tcp://LAPTOP-LBO51UJL:7023'

Для отключения следящего сервера достаточно выполнить код:

alter database Docs set witness off

На следящем сервере можно делать запросы к представлению sys.database_mirroring_witnesses для просмотра состояния сеанса зеркалирования. Теперь подключения приложений могут автоматически перенаправляться на доступный сервер. Для этого требуется указать дополнительный атрибут в строке подключения. Например, на C# можно так создать экземпляр подключения:

using (SqlConnection cn = new SqlConnection(@"Data Source=LAPTOP-LBO51UJL;Failover Partner=LAPTOP-LBO51UJL\MIRROR;Initial Catalog=Docs;Integrated Security=True;"))

Если в момент отработки отказа соединение будет выполнять какую-либо работу с базой, то активная транзакция откатится, но последующие подключения пройдут без проблем.
То что со свидетелем работает автоматическая отработка отказа, это здорово: повышается доступность приложений. Но хорошо бы получать автоматические уведомления о факте отработки отказа. Приведу пример того как это сделать с помощью уведомлений о событиях. Для удобства на основном и зеркальном серверах создадим базу данных с таблицей, в которой будет храниться лог сообщений о состоянии сеанса зеркалирования. Также понадобится настройка объектов Service Broker и создание уведомления о событии. Итак, код, приведенный ниже, нужно выполнить на основном и зеркальном серверах:

if db_id ( 'CheckData' ) is null
begin
 create database CheckData
end
go
alter database CheckData set enable_broker
go

use CheckData
go
if object_id ( 'dbo.DbMirrorLog', 'U' ) is null
begin
 create table dbo.DbMirrorLog
 (
  dtDate datetime  not null,
  vcLog varchar ( max )  not null,
  vcDb varchar ( 200 )  not null,
  iState int   not null,
  iRowId int identity ( 1, 1 ) not null,
  constraint PK_bMirrorLog primary key clustered ( dtDate asc, iRowId asc ) on [primary]
 ) on [primary]
end
go

if object_id ( 'dbo.LogMirrorState', 'P' ) is null
begin
 exec sp_executesql 'create proc dbo.LogMirrorState as return'
end
go

alter proc dbo.LogMirrorState
as
begin
 declare @xMes xml, @uidHan uniqueidentifier

 ;
 receive top ( 1 ) @xMes = message_body, @uidHan = conversation_handle
 from dbo.MirrorNotify

 if @@rowcount = 0
 begin
  return
 end

 insert into dbo.DbMirrorLog
 (
  dtDate,
  vcLog,
  vcDb,
  iState
 )
 values
 (
  isnull ( @xMes.value ( '(/EVENT_INSTANCE/PostTime)[1]', 'datetime' ), getdate () ),
  isnull ( @xMes.value ( '(/EVENT_INSTANCE/TextData)[1]', 'varchar ( max )' ), '' ),
  isnull ( @xMes.value ( '(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar ( max )' ), '' ),
  isnull ( @xMes.value ( '(/EVENT_INSTANCE/State)[1]', 'int' ), -1 )
 )
end
go

if object_id ( 'dbo.MirrorNotify', 'SQ' ) is null
begin
 create queue dbo.MirrorNotify
 with
  status = on,
  activation
  (
   status = on,
   procedure_name = dbo.LogMirrorState,
   max_queue_readers = 4,
   exec as owner
  )
 on [default]
end
go

if not exists
(
 select *
 from sys.services
 where name = 'MirrorLog'
)
begin
 create service MirrorLog
  on queue MirrorNotify ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] )
end
go

if not exists
(
 select *
 from sys.server_event_notifications
 where name = 'DbMirrorLog'
)
begin
 create event notification DbMirrorLog
  on server for DATABASE_MIRRORING_STATE_CHANGE
  to service 'MirrorLog', 'current database'
end
go

Можно выполнить несколько мануальных отработок отказа, а затем на основном сервере выполнить инструкцию shutdown для его отключения (либо отключить его в диспетчере конфигураций). При этом следящий сервер сразу определит, что основной недоступен и выполнит отработку отказа на зеркало. Затем нужно включить основной сервер, и он синхронизируется с бывшим зеркальным и сам станет зеркальным. Если сделать запросы к таблице DbMirrorLog, то увидим такой лог:

Если настроить на сервере Database Mail, то в процедуре активации очереди можно вызывать процедуру msdb.dbo.sp_send_dbmail для мгновенного оповещения об отработке отказа.