MSQL - AlwaysON Availability Groups
Mise en place d'une instance AlwaysOn
-- Create and delete AlwaysOn Instances via T-SQL
--
--
-- Remplacer Your-AlwaysOn-Instance-Name par le nom de l'instances AlwaysON de test que vous utiliserez
-- Remplacer Your-AlwaysOn-Database-Name-01 et Your-AlwaysOn-Database-Name-01 par le nom des bases de données de test que vous utiliserez
-- Remplacer Primary-SQL-Server-AlwaysON par le nom du serveur SQL primaire de vos instances AlwaysOn
-- Remplacer Secondary-SQL-Server-AlwaysON par le nom du serveur SQL secondaire de vos instances AlwaysOn
-- Remplacer Your-Domain-Name par votre nom de domaine court et your-SQL-Admin-Account par un login Windows qui a les droits d'admin sur vos instances de tests
-- Remplacer Your-Domain-FQDN par votre domaine FQDN
-- Remplacer Your-AlwaysOn-Group-Name par le nom du groupe AlwaysON
-- Remplacer \\Primary-SQL-Server-AlwaysON\Your-Share-Backup par le chemin où entreposer temporairement vos sauvegardes de bases
--
--
-- (à faire sur Primary-SQL-Server-AlwaysON) --> Delete the database Your-AlwaysOn-Database-Name-01 and Your-AlwaysOn-Database-Name-02 from ALways On DAG Your-AlwaysOn-Group-Name :
----------------------------------------------------------------------------------------------------------------------------------------
USE [master]
GO
ALTER AVAILABILITY GROUP [Your-AlwaysOn-Group-Name]
REMOVE DATABASE [Your-AlwaysOn-Database-Name-01];
GO
ALTER AVAILABILITY GROUP [Your-AlwaysOn-Group-Name]
REMOVE DATABASE [Your-AlwaysOn-Database-Name-02];
GO
-- (à faire sur Primary-SQL-Server-AlwaysON) --> Remove the actual Replica from Always On DAG Your-AlwaysOn-Group-Name:
-----------------------------------------------------------------------------------------------------------------------
USE [master]
GO
ALTER AVAILABILITY GROUP [Your-AlwaysOn-Group-Name]
REMOVE REPLICA ON N'Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name';
GO
-- (à faire sur Secondary-SQL-Server-AlwaysON) --> DropDatabases if existes on second server before create Always ON :
-----------------------------------------------------------------------------------------------------------
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Your-AlwaysOn-Database-Name-01'
GO
USE [master]
GO
DROP DATABASE [Your-AlwaysOn-Database-Name-01]
GO
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Your-AlwaysOn-Database-Name-02'
GO
USE [master]
GO
DROP DATABASE [Your-AlwaysOn-Database-Name-02]
GO
-- (à faire sur Primary-SQL-Server-AlwaysON) --> Add replica to ALways On DAG :
--------------------------------------------------------------------------------
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect Primary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
/* use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Your-Domain-Name\your-SQL-Admin-Account]
GO */
:Connect Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO
/* use [master]
-- GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Your-Domain-Name\your-SQL-Admin-Account]
GO */
:Connect Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect Primary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
USE [master]
GO
ALTER AVAILABILITY GROUP [Your-AlwaysOn-Group-Name]
ADD REPLICA ON N'Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name' WITH (ENDPOINT_URL = N'TCP://Secondary-SQL-Server-AlwaysON.Your-Domain-FQDN:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO
:Connect Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
ALTER AVAILABILITY GROUP [Your-AlwaysOn-Group-Name] JOIN;
GO
:Connect Primary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
-- Add database Your-AlwaysOn-Database-Name-02 to Availibility group :
----------------------------------------------------------------------
USE [master]
GO
ALTER AVAILABILITY GROUP [Your-AlwaysOn-Group-Name]
ADD DATABASE [Your-AlwaysOn-Database-Name-02];
GO
:Connect Primary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
BACKUP DATABASE [Your-AlwaysOn-Database-Name-02] TO DISK = N'\\Primary-SQL-Server-AlwaysON\BDDShare01\Your-AlwaysOn-Database-Name-02.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
:Connect Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
RESTORE DATABASE [Your-AlwaysOn-Database-Name-02] FROM DISK = N'\\Primary-SQL-Server-AlwaysON\BDDShare01\Your-AlwaysOn-Database-Name-02.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect Primary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
BACKUP LOG [Your-AlwaysOn-Database-Name-02] TO DISK = N'\\Primary-SQL-Server-AlwaysON\BDDShare01\Your-AlwaysOn-Database-Name-02_20140819091024.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
:Connect Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
RESTORE LOG [Your-AlwaysOn-Database-Name-02] FROM DISK = N'\\Primary-SQL-Server-AlwaysON\BDDShare01\Your-AlwaysOn-Database-Name-02_20140819091024.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
-- 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'Your-AlwaysOn-Group-Name'
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 [Your-AlwaysOn-Database-Name-02] SET HADR AVAILABILITY GROUP = [Your-AlwaysOn-Group-Name];
GO
GO
-- Add database Your-AlwaysOn-Database-Name-01 to availibility group :
----------------------------------------------------------------------
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect Primary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
USE [master]
GO
ALTER AVAILABILITY GROUP [Your-AlwaysOn-Group-Name]
ADD DATABASE [Your-AlwaysOn-Database-Name-01];
GO
:Connect Primary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
BACKUP DATABASE [Your-AlwaysOn-Database-Name-01] TO DISK = N'\\Primary-SQL-Server-AlwaysON\BDDShare01\Your-AlwaysOn-Database-Name-01.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
:Connect Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
RESTORE DATABASE [Your-AlwaysOn-Database-Name-01] FROM DISK = N'\\Primary-SQL-Server-AlwaysON\BDDShare01\Your-AlwaysOn-Database-Name-01.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect Primary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
BACKUP LOG [Your-AlwaysOn-Database-Name-01] TO DISK = N'\\Primary-SQL-Server-AlwaysON\BDDShare01\Your-AlwaysOn-Database-Name-01_20140819105607.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
:Connect Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
RESTORE LOG [Your-AlwaysOn-Database-Name-01] FROM DISK = N'\\Primary-SQL-Server-AlwaysON\BDDShare01\Your-AlwaysOn-Database-Name-01_20140819105607.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name
-- 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'Your-AlwaysOn-Group-Name'
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 [Your-AlwaysOn-Database-Name-01] SET HADR AVAILABILITY GROUP = [Your-AlwaysOn-Group-Name];
GO
-- Just ADD replica to Always On group Your-AlwaysOn-Group-Name :
-----------------------------------------------------------------
USE [master]
GO
ALTER AVAILABILITY GROUP [Your-AlwaysOn-Group-Name]
ADD REPLICA ON N'Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name' WITH (ENDPOINT_URL = N'TCP://Secondary-SQL-Server-AlwaysON.Your-Domain-FQDN:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO
-- Just REMOVE replica Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name from Always On group Your-AlwaysOn-Group-Name :
--------------------------------------------------------------------------------------------------------------------------------
USE [master]
GO
ALTER AVAILABILITY GROUP [Your-AlwaysOn-Group-Name]
REMOVE REPLICA ON N'Secondary-SQL-Server-AlwaysON\Your-AlwaysOn-Instance-Name';
GO
-- Just Remove Database Your-AlwaysOn-Database-Name-01 from Always On group Your-AlwaysOn-Group-Name :
------------------------------------------------------------------------------------------------------
USE [master]
GO
ALTER AVAILABILITY GROUP [Your-AlwaysOn-Group-Name]
REMOVE DATABASE [Your-AlwaysOn-Database-Name-01];
GO
N'hésitez pas à consulter Technet ou MSDN pour plus de détails
Lien vers AlwaysOn sur MSDN
Quelles solutions de réplication choisir ?
Log Shipping :? assure le Disaster Recovery mais pas la haute disponibilité.Dans ce cas, vous acceptez la perte de données potentielle car le principe repose sur l'envoi des journaux de transactions automatique vers une autre machine SQL Serveur selon un processus automatisé (c'est un modèle de réplication asynchrone). L'autre grand inconvénient du Log Shipping est qu'une fois que vous basculez, vous ne pouvez pas ne pas revenir en arrière de façon simple. Vous devrez rétablir reconstruire le mécanisme de Log Shipping dans l'autre sens. Par c'est un mécanisme rapide à mettre en ligne, qui est facile à configurer et à entretenir.
Failover Clustering classique : c'est l'inverse - assure la haute disponibilité mais pas le Disaster Recovery. Nécessite du stockage partagé. Il n'y a pas de réplique des données par ce mécanisme. Une instance est géré par un seul membre du cluster mais elle peut être reprise parr n'importe quel membre du cluster en cas de souci. Assez facile à mettre en place et le basculemnt est gérer de façon automatique et relativement simplement.
Mirroring : lers données sont répliquées vers un autre serveur. Il est possible de basculer vers le serveur de secours très simplement et pareil pour le retour arrière. L'inconvénient majeur est que c'est désormais un mécanisme obsolète. Attention aussi au licensing entre MS SQL Server Enterprise et Standard selon le modèle de réplication synchrone ou asynchrone. Attention aussi aux pénalités sur les performances pour la réplication synchrone qui va entraîner une baisse de performance.Vous devrez aussi implémenter des mécanismes pour automatiser le basculement pour vos applications si c'est la réplique qui devient active (redirection DNS par exemple). Ceci a par ailleurs Microsoft à travailler sur un nouveau modèle : AOAG
AlwaysOn Availability Groups (AOAG) : C'est le meilleur des 3 mondes - Il assure haute disponibilté puisque repose sur Failover Clustering et Disaster Recovery puisque réplique des bases de donné&es entre les serveurs du cluster AlwaysOn. Les basculements et les retours arrières sont gérés de façon transparente par le cluster. Le mode de réplication peut être asynchrone ou synchrone (à vous d'avoir l'infrastructure de réseau adaptée pour de la réplication synchrone comme pour toute solution de réplication !!). On peut même parler de plan de continuité de service plus que de plan de reprise de service puisqu'il est possible d'activer la réplication synchrone.Evidemment, le coût est sans doute supérieur mais il faut savoir ce que l'on veut !
Quelles solutions de réplication choisir ?
Log Shipping :? assure le Disaster Recovery mais pas la haute disponibilité.Dans ce cas, vous acceptez la perte de données potentielle car le principe repose sur l'envoi des journaux de transactions automatique vers une autre machine SQL Serveur selon un processus automatisé (c'est un modèle de réplication asynchrone). L'autre grand inconvénient du Log Shipping est qu'une fois que vous basculez, vous ne pouvez pas ne pas revenir en arrière de façon simple. Vous devrez rétablir reconstruire le mécanisme de Log Shipping dans l'autre sens. Par c'est un mécanisme rapide à mettre en ligne, qui est facile à configurer et à entretenir.
Failover Clustering classique : c'est l'inverse - assure la haute disponibilité mais pas le Disaster Recovery. Nécessite du stockage partagé. Il n'y a pas de réplique des données par ce mécanisme. Une instance est géré par un seul membre du cluster mais elle peut être reprise parr n'importe quel membre du cluster en cas de souci. Assez facile à mettre en place et le basculemnt est gérer de façon automatique et relativement simplement.
Mirroring : lers données sont répliquées vers un autre serveur. Il est possible de basculer vers le serveur de secours très simplement et pareil pour le retour arrière. L'inconvénient majeur est que c'est désormais un mécanisme obsolète. Attention aussi au licensing entre MS SQL Server Enterprise et Standard selon le modèle de réplication synchrone ou asynchrone. Attention aussi aux pénalités sur les performances pour la réplication synchrone qui va entraîner une baisse de performance.Vous devrez aussi implémenter des mécanismes pour automatiser le basculement pour vos applications si c'est la réplique qui devient active (redirection DNS par exemple). Ceci a par ailleurs Microsoft à travailler sur un nouveau modèle : AOAG
AlwaysOn Availability Groups (AOAG) : C'est le meilleur des 3 mondes - Il assure haute disponibilté puisque repose sur Failover Clustering et Disaster Recovery puisque réplique des bases de donné&es entre les serveurs du cluster AlwaysOn. Les basculements et les retours arrières sont gérés de façon transparente par le cluster. Le mode de réplication peut être asynchrone ou synchrone (à vous d'avoir l'infrastructure de réseau adaptée pour de la réplication synchrone comme pour toute solution de réplication !!). On peut même parler de plan de continuité de service plus que de plan de reprise de service puisqu'il est possible d'activer la réplication synchrone.Evidemment, le coût est sans doute supérieur mais il faut savoir ce que l'on veut !
