Set up custom SQL networking
In order for SQL to work, different servers need to be able to talk to each other; if you use nonstandard ports, the easiest way to manage this is to use SQL Alias configured in SQL Configuration Manager. The alias connections that will be needed are:
MyPRIMARY01 + MyPRIMARY02: will need aliases to the distributor and any transactional subscribers
MyDISTRIBUTOR: will need alias to the Primary Listener and any transactional subscribers
Transactional Subscriber(s): will need alias to the Primary Listener and Distributor
For safety, I usually make these connections as both 32-bit and 64-bit aliases.
Now for the workaround that makes the rest of this possible - after struggling with windows-internal DNS tools, we eventually gave up and made our own DNS solution. A combination of OpenVPN and DNS-MASQ makes it possible to have a dynamically-controlled DNS address that's handled through automation. This will show up as a DNS entry that's not publicly available (shown here as myprimary.vpn), controlled by an automation script. This custom dynamic DNS in combination with replication made it possible to put all the pieces together so that we could have a failover cluster that worked with both merge and transactional replication. One of the complexities involved is that SQL can only handle NETBIOS server names (not URLs), so we used aliases to hide this complexity from SQL SERVER:
Then, for replication to work you'll need to setup server linkages between the DISTRIBUTOR server and both PRIMARY servers.
Run this distributor + linkage creation script on the DISTRIBUTOR server:
also, create a "distrib_admin" account on the distributor server. If you run into problems with these linkages, a useful script is:
sp_droplinkedsrvlogin 'MYPRIMARY01', null
go
sp_dropserver 'MYPRIMARY01'
go
Then, run this script on both Primary servers:
GO
DECLARE @installed int;
EXEC @installed = sys.sp_MS_replication_installed;
SELECT @installed;
GO
exec sp_addremotelogin 'MYDISTRIBUTOR', 'Your_SQL_Admin'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYDISTRIBUTOR',@useself=N'False',@locallogin=NULL,@rmtuser=N'distributor_admin',@rmtpassword='YourPassword'
Setting up Replication
I'm going to assume you already have a replication script; if not, check out one of the other replication tutorials like this one. You'll want to make sure that the DISTRIBUTOR server is defined as the distributor and that a network path is used for the replication snapshot folder. Unlike most other setup steps, this replication script only needs to run on one primary server (MYPRIMARY01). For example, your merge replication setup script may look like:
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'MYDATABASE', @optname = N'publish', @value = N'true'
GO
exec [MYDATABASE].sys.sp_addlogreader_agent @job_login = N'MYDISTRIBUTOR\MY_Logreader', @job_password = N'MYPASSWORDFORLOGS', @publisher_security_mode = 0, @publisher_login = N'MY_PUBLISHER_ADMIN', @publisher_password = N'PUBLISHER_ADMIN_PASSWORD'
GO
exec [MYDATABASE].sys.sp_addqreader_agent @job_login = N'MYDISTRIBUTOR\MY_Queuereader', @job_password = N'MYPASSWORDFORQUEUES', @frompublisher = 1
GO
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'MYDATABASE', @optname = N'merge publish', @value = N'true'
GO
-- Adding the merge publication
use [MYDATABASE]
exec sp_addmergepublication @publication = N'MYDATABASE', @description = N'Merge publication of database ''MYDATABASE'' from Publisher ''MYPRIMARY''.', @sync_mode = N'native', @retention = 27, @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, @ftp_subdirectory = N'ftp', @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'true', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', @validate_subscriber_info = N'HOST_NAME()', @max_concurrent_merge = 10, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = N'true', @publication_compatibility_level = N'100RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot = N'true', @allow_web_synchronization = N'true', @web_synchronization_url = N'https://mygateway.mydomain.org:8443/SQLReplication/replisapi.dll', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'both', @automatic_reinitialization_policy = 0
GO
exec sp_addpublication_snapshot @publication = N'MYDATABASE', @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 = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'MYDISTRIBUTOR\MY_Snapshot', @job_password = N'MYPASSWORDFORSNAPSHOTS', @publisher_security_mode = 0, @publisher_login = N'MY_PUBLISHER_ADMIN', @publisher_password = N'PUBLISHER_ADMIN_PASSWORD'
exec sp_grant_publication_access @publication = N'MYDATABASE', @login = N'MY_Merge'
GO
exec sp_grant_publication_access @publication = N'MYDATABASE', @login = N'YOUR_SQL_ADMIN'
GO
-- Adding the merge articles
use [MYDATABASE]
exec sp_addmergearticle @publication = N'MYDATABASE', @article = N'MY_EXAMPLE_TABLE', @source_owner = N'dbo', @source_object = N'MY_EXAMPLE_TABLE', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 2, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0
GO
while your transactional replication setup may look like:
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'MYDATABASE', @optname = N'publish', @value = N'true'
GO
exec [MYDATABASE].sys.sp_addlogreader_agent @job_login = N'MYDISTRIBUTOR\MY_Logreader', @job_password = N'MYPASSWORDFORLOGS', @publisher_security_mode = 0, @publisher_login = N'MY_PUBLISHER_ADMIN', @publisher_password = N'PUBLISHER_ADMIN_PASSWORD'
GO
exec [MYDATABASE].sys.sp_addqreader_agent @job_login = N'MYDISTRIBUTOR\MY_Queuereader', @job_password = N'MYPASSWORDFORQUEUES', @frompublisher = 1
GO
-- Adding the transactional publication
use [MYDATABASE]
exec sp_addpublication @publication = N'MYDATABASEReporting', @description = N'Transactional publication of database ''MYDATABASE'' from Publisher ''MYPRIMARY''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @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'false', @allow_sync_tran = N'false', @autogen_sync_procs = 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'MYDATABASEReporting', @frequency_type = 4, @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 = N'MYDISTRIBUTOR\MY_Snapshot', @job_password = N'MYPASSWORDFORSNAPSHOTS', @publisher_security_mode = 0, @publisher_login = N'MY_PUBLISHER_ADMIN', @publisher_password = N'PUBLISHER_ADMIN_PASSWORD'
exec sp_grant_publication_access @publication = N'MYDATABASEReporting', @login = N'MY_PUBLISHER_ADMIN'
GO
exec sp_grant_publication_access @publication = N'MYDATABASEReporting', @login = N'MY_Merge'
GO
exec sp_grant_publication_access @publication = N'MYDATABASEReporting', @login = N'YOUR_SQL_ADMIN'
GO
-- Adding the transactional articles
use [MYDATABASE]
exec sp_addarticle @publication = N'MYDATABASEReporting', @article = N'MY_EXAMPLE_TABLE', @source_owner = N'dbo', @source_object = N'MY_EXAMPLE_TABLE', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C0357DF, @identityrangemanagementoption = N'none', @destination_table = N'MY_EXAMPLE_TABLE', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboMY_EXAMPLE_TABLE]', @del_cmd = N'CALL [sp_MSdel_dboMY_EXAMPLE_TABLE]', @upd_cmd = N'SQL'
GO
Run these scripts (note that the two scripts provided have some overlap/redundancies, you can ignore these warnings) and you should now have a replication setup - but only from PRIMARY01 to the distributor. The real magic of the replication configuration happens with this publisher redirection script (run on the DISTRIBUTOR server in the distribution database as per this guide):
EXEC sys.sp_redirect_publisher
@original_publisher = 'MYPRIMARY01',
@publisher_db = 'MYDATABASE',
@redirected_publisher = 'MYLISTENER';
This script makes it possible for the Listener (pointed at the currently active primary server) to be used as the replication publisher. Note that on its own, this isn't enough to get full failover behavior - you still need to handle the DNS for the Listener (covered in Part 6). Before setting that up, you'll also want to confirm the publication settings using this script:
USE distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
@original_publisher = 'MYPRIMARY01',
@publisher_db = 'MYDATABASE',
@redirected_publisher = @redirected_publisher output;
If you see this error, it means you need to specify a SQL alias and valid DNS for the listener:
Msg 21892, Level 16, State 1, Procedure sp_hadr_validate_replica_hosts_as_publishers, Line 60 [Batch Start Line 2]
Unable to query sys.availability_replicas at the availability group primary associated with virtual network name 'MYLISTENER' for the server names of the member replicas: error = 53, error message = Error 53, Level 16, State 1, Message: Named Pipes Provider: Could not open a connection to SQL Server [53]. .',
No comments:
Post a Comment