Автоматизация обновления базы данных SQL Server, входящей в группу доступности

Tags: database, SQL Server, автоматизация

Thursday, February 15, 2018 10:33:03 PM

У вас есть база данных SQL Server, которая является частью группы доступности AlwaysOn, имеющей одну вторичную реплику только для чтения. Время от времени возникает необходимость обновлять основную базу данных с другого сервера, что требует от нас удаления AlwaysOn и перенастройки. Как можно автоматизировать этот процесс?

Как вы, наверное, знаете, мы не можем восстановить базу данных, которая является частью группы доступности SQL Server AlwaysOn. Нам нужно сначала удалить базу данных SQL Server из группы доступности, чтобы восстановить базу данных. В этой статье мы рассмотрим, как мы можем автоматизировать этот процесс, чтобы гарантировать, что AlwaysOn будет восстановлен без ручного вмешательства.

Сперва мы рассмотрим все шаги, которые нужно сделать, а затем поговорим о том, как их можно автоматизировать с помощью  SQL Server Agent Jobs.

Удалите базу данных SQL Server из группы доступности AlwaysOn на основном сервере

Первым шагом является удаление базы данных из группы доступности. Мы можем добиться этого, используя команду ниже.

Примечание. Вам нужно будет заменить значения  <Availability Group>  и  <Database Name> на ваши значения.

-- runs on primary server

USE master
GO
ALTER AVAILABILITY GROUP [<Availability Group>] REMOVE DATABASE [<Database Name>];
GO


Восстановление базы данных SQL Server на основном сервере

Следующим шагом будет восстановление резервной копии для обновления базы данных на вашем основном сервере. Вы также можете добавить дополнительные шаги для любых конкретных требований для запуска после восстановления, таких как предоставление разрешений и т. д.

Эта резервная копия поступает с другого сервера, который будет использоваться для обновления базы данных на основном сервере.

-- runs on primary server

USE master
GO
RESTORE DATABASE [<Database Name>]
FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH REPLACE
GO

Убедитесь, что восстановленная база данных SQL Server находится в режиме полного восстановления на основном сервере

Нам необходимо убедиться, чтобы режим восстановления базы данных был установлен в режиме FULL, чтобы соответствовать требованиям AlwaysOn.

-- runs on primary server

USE master
GO
ALTER DATABASE [<Database Name>] SET RECOVERY FULL WITH NO_WAIT
GO

Резервное копирование базы данных SQL Server и журнала на основном сервере

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

-- runs on primary server

BACKUP DATABASE [<database Name>]
TO DISK='<Shared Network Location>\<Database Name>.bak' WITH FORMAT, INIT, COMPRESSION
GO
BACKUP LOG [<database Name>]
TO DISK='<Shared Network Location>\<Database Name>.trn' WITH FORMAT, INIT, COMPRESSION  
GO

Добавьте базу данных SQL Server в группу доступности на основной сервер

Затем нам нужно добавить базу данных обратно в группу доступности.

 

-- runs on primary server

USE master
GO
ALTER AVAILABILITY GROUP [<Availability Group>] ADD DATABASE [<Database Name>];  
GO

 

Теперь вы выполнили все шаги на сервере PRIMARY.

Восстановление базы данных SQL Server на вторичном сервере

Затем нам нужно восстановить полное резервное копирование и резервное копирование журнала на вторичном сервере.

 

-- runs on secondary server

USE master
GO
RESTORE DATABASE [<database Name>]
FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH FILE=1, REPLACE, NORECOVERY
GO
RESTORE LOG [<database Name>]
FROM DISK='<Shared Network Location>\<Database Name>.trn' WITH FILE=1, REPLACE, NORECOVERY  
GO

 

Проверьте состояние базы данных SQL Server и затем добавьте в группу доступности на вторичном сервере

Следующий шаг важен, он позволяет синхронизировать данные после того, как база данных была восстановлена и стала готова присоединиться к группе доступности.

-- runs on secondary server

-- Wait for the replica to start communicating
begin try
  declare @conn bit
  declare @count int
  declare @replica_id uniqueidentifier
  declare @group_id uniqueidentifier
  set @conn = 0
  set @count = 30 -- wait for 5 minutes
 
   if (serverproperty('IsHadrEnabled') = 1)
     and (isnull((select member_state
                  from master.sys.dm_hadr_cluster_members
                  where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
     and (isnull((select state
                  from master.sys.database_mirroring_endpoints), 1) = 0)
  begin
     select @group_id = ags.group_id
     from master.sys.availability_groups as ags
     where name = N'<Availability Group>'

     select @replica_id = replicas.replica_id
     from master.sys.availability_replicas as replicas
     where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

     while @conn <> 1 and @count > 0
     begin
        set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
        if @conn = 1
        begin
           -- exit loop when the replica is connected, or if the query cannot find the replica status
           break
        end
        waitfor delay '00:00:10'
        set @count = @count - 1
     end
  end
end try

begin catch
  -- If the wait loop fails, do not stop execution of the alter database statement
end catch

ALTER DATABASE [<Database Name>] SET HADR AVAILABILITY GROUP = [<Availability Group>];
GO

Работа агента SQL Server

Чтобы автоматизировать этот процесс, мы можем создать задания агента SQL Server на обоих серверах:

  1. Первичный сервер. Создайте одно задание, которое выполняет следующие действия:
  1. Удаляет базу данных из группы доступности
  2. Восстанавливает базу данных
  3. Задает модель восстановления базы данных в режиме FULL
  4. Добавляет базу данных в группу доступности
  5. Создает резервное копирование базы данных и журнала
  6. Опционально - запускает работу на вторичном сервере с помощью sp_start_job (для этого необходимо будет осуществить некоторые другие настройки)
  1. Вторичный сервер. Создайте одно задание, которое выполняет следующие действия:
  1. Восстанавливает резервной копии базы данных и резервного копирования журнала
  2. Проверяет состояние базы данных и добавляет базу данных в группу доступности

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



No Comments

Add a Comment