Replication Subscribers and AlwaysOn Availability Groups (SQL Server)

------- BEGIN: Configure schema on Publisher -------
:Connect ALWAYSON-SRV2
CREATE DATABASE DB_Publisher;
GO
USE DB_Publisher;
GO
CREATE TABLE T1 (col1 int PRIMARY KEY, col2 varchar(256));
GO
INSERT INTO T1 VALUES (1, 'Row 1');
GO
SELECT * FROM DB_Publisher..T1;
------- END: Configure schema on Publisher -------

------- BEGIN: Configure schema on Subscriber -------
:Connect ALWAYSON-SRV4
CREATE DATABASE DB_Subscriber;
------- END: Configure schema on Subscriber -------

------- BEGIN: on Distributor, configure the distributor 
--   as a remote distributor for the primary publisher -------
:connect ALWAYSON-SRV8\distributor
-- configure the remote publisher
USE master;
GO
EXEC sys.sp_adddistpublisher 
@publisher = 'alwayson-srv2', 
@distribution_db='distribution',
@working_directory='C:\Program Files\Microsoft SQL Server\MSSQL11.DISTRIBUTOR\MSSQL\ReplData',
@security_mode=1;
------- END: on Distributor, configure the distributor 
--   as a remote distributor for the primary publisher -------

------- BEGIN: Configure Replication on Publisher -------
:connect ALWAYSON-SRV2
-- Configure the remote distributor
USE master;
EXEC sp_adddistributor @distributor = N'ALWAYSON-SRV8\distributor', @password = N'SQLServer2012';
GO
-- configure the publication database
USE master;
GO
EXEC sys.sp_replicationdboption @dbname='DB_Publisher', @optname='publish', @value='true';
GO
-- create the publication for transactionnal replication
USE [DB_Publisher];
EXEC sp_addpublication @publication = N'MyPublication', @description = N'Transactional publication of database ''DB_Publisher'' from Publisher ''ALWAYSON-SRV2''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false';
GO
EXEC sp_addpublication_snapshot @publication = N'MyPublication', @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1;

USE [DB_Publisher];
EXEC sp_addarticle @publication = N'MyPublication', @article = N'T1', @source_owner = N'dbo', @source_object = N'T1', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'T1', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboT1', @del_cmd = N'CALL sp_MSdel_dboT1', @upd_cmd = N'SCALL sp_MSupd_dboT1';
GO
EXEC sp_changepublication @publication = 'MyPublication', @property = 'allow_initialize_from_backup', @value = 'true';

-- Start the snapshot agent

-- create push subscription
:Connect to SRV2
USE [DB_Publisher];
EXEC sp_addsubscription @publication = N'MyPublication', @subscriber = N'ALWAYSON-SRV4', @destination_db = N'DB_Subscriber', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0;
EXEC sp_addpushsubscription_agent @publication = N'MyPublication', @subscriber = N'ALWAYSON-SRV4', @subscriber_db = N'DB_Subscriber', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 0, @active_start_date = 0, @active_end_date = 19950101, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor';
GO

-- Sync the distribution agent

-- Connect to the subscriber and check that data is there
:connect ALWAYSON-SRV4
SELECT * FROM DB_Subscriber.dbo.T1;

--Check replication is running properly
--Connect to publisher and change data
:connect ALWAYSON-SRV2
USE DB_Publisher;
GO
UPDATE t1 SET col2 = 'Something else' WHERE col1 = 1;
GO
SELECT * FROM DB_Publisher..T1;
GO

-- Run distribution agent and check that data is there
:connect ALWAYSON-SRV4
SELECT * FROM DB_Subscriber..T1;
------- BEGIN: Configure Replication on Publisher -------

------- BEGIN: Configure AlwaysOn on the Publisher -------
-- Connect to primary publisher
:connect ALWAYSON-SRV2
BACKUP DATABASE DB_Publisher TO DISK = '\\alwayson-srv8\MyBackups\DB_Publisher_Full';

-- From the UI create an AG with the wizard

-- Connect to publisher secondary to insure 
-- it is configured for Replication
:connect ALWAYSON-SRV3
SELECT @@SERVERNAME;
USE master;
GO
DECLARE @installed int;
EXEC @installed = sys.sp_MS_replication_installed;
SELECT @installed;

-- Declare the publisher secondary as 
-- a potential failover publisher
:connect ALWAYSON-SRV8\distributor
USE master;
GO
EXEC sys.sp_adddistpublisher 
@publisher = 'ALWAYSON-SRV3', 
@distribution_db='distribution',
@working_directory='C:\Program Files\Microsoft SQL Server\MSSQL11.DISTRIBUTOR\MSSQL\ReplData',
@security_mode=1;

-- Connect to publisher secondary to configure 
-- the remote distributor
:connect ALWAYSON-SRV3
USE master;
EXEC sp_adddistributor @distributor = N'ALWAYSON-SRV8\distributor', @password = N'SQLServer2012';
GO
-- ensure each push subscriber appear as linked servers
EXEC sys.sp_addlinkedserver @server = 'ALWAYSON-SRV4';
GO
EXEC sys.sp_addlinkedserver @server = 'ALWAYSON-SRV5';
GO

-- Redirect the original publisher 
-- to the publisher AG listener name
:connect ALWAYSON-SRV8\distributor
USE distribution;
GO
EXEC sys.sp_redirect_publisher  
@original_publisher = 'ALWAYSON-SRV2', 
@publisher_db = 'DB_Publisher', 
@redirected_publisher = 'PublisherAGList';
GO
-- Show the new metadata table that has been created 
-- and filled to persist this configuration
SELECT * FROM distribution..MSredirected_publishers;

-- Run the Replication validation stored procedure 
-- to verify that primary and secondary publishers 
-- can serve as publishers for the published database
USE distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers @original_publisher = 'ALWAYSON-SRV2', @publisher_db = 'DB_Publisher', @redirected_publisher = @redirected_publisher OUTPUT;
SELECT @redirected_publisher;
------- END: Configure AlwaysOn on the publisher -------

------- BEGIN: Configure AlwaysOn Availability Groups on the Subscriber -------
:Connect to SRV4
BACKUP DATABASE DB_Subscriber TO DISK = '\\alwayson-srv8\MyBackups\DB_Subscriber_Full';
-- From the UI create an AG with the wizard
------- END: Configure AlwaysOn on the Subscriber -------

------- BEGIN: demo subscriber failover and recovery steps -------

-- Fail over the subscriber availability group
:connect to ALWAYSON-SRV5
ALTER AVAILABILITY GROUP SubscriberAG FAILOVER;

-- Get the LSN from the last transaction received 
-- by the subscriber
:Connect to SRV5
SELECT transaction_timestamp, * FROM DB_Subscriber..MSreplication_subscriptions;
--0x00000023000000E60003000000000000

-- Cleanup old subscription from new subscription DB
EXEC sp_subscription_cleanup @publisher = 'ALWAYSON-SRV2', @publisher_db = 'DB_Publisher';

-- Create new subscription to the new Subscriber Primary based 
-- on the LSN of the last successful sync
:Connect to PublisherAGList
USE DB_Publisher;
GO
EXEC sp_addsubscription -- past the LSN before executing
@publication = 'MyPublication', 
@subscriber = 'ALWAYSON-SRV5', 
@destination_db = 'DB_Subscriber',
@subscription_Type = 'Push',
@sync_Type = 'initialize from LSN', 
@subscriptionlsn = 0x00000023000000E60003000000000000, 
@article = 'all',
@update_mode = 'read only',
@subscriber_type = 0;

-- Note that the distribution agent is running

-- Note that data is replicated from publisher

------- END: demo subscriber failover and recovery steps -------