SQL Server distribution distributes AlwaysOn builds

In a replication subscription build for SQL Server, the Distributor is a critical point of vulnerability and now distributes distribution into AlwaysOn high availability mode.

Release: TEST125

distribution1:test177

distribution2:test178

Subscription: TEST129

test177/178 set up distribution, process slightly.

To add a publisher, process a little.

TEST125 Configuring distribution TEST177 and establishing a release test--(process)--test177 job Test125-test-1--logreader

TEST129 Push Subscription--(process)--test177 job test125-test-repl_test-test129-3

Pause Agent-job

The TEST177 distribution property is complete and comprehensive, Copybak to test178,restore with Nocovery.

Because it is not possible to suggest distribution AlwaysOn through the interface, you can only rely on scripts.

The middle establishes the endpoint and opens the EVENT session process slightly.

CREATEAvailabilityGROUP [Dis_awo] with(automated_backup_preference=Secondary,db_failover= OFF, Dtc_support=NONE) for DATABASE [Distribution]REPLICA onN'TEST177'  with(Endpoint_url=N'tcp://test177.test.com:5022', Failover_mode=AUTOMATIC, Availability_mode=Synchronous_commit, backup_priority=  -, Secondary_role (allow_connections=  All)), N'TEST178'  with(Endpoint_url=N'tcp://test178.test.com:5022', Failover_mode=AUTOMATIC, Availability_mode=Synchronous_commit, backup_priority=  -, Secondary_role (allow_connections=  All));

TEST178 on the join available group, Listener: DIS_IP, distribution AlwaysOn complete.

Agentjob test125-test-1 generated script on TEST177, executed on TEST178.

Modify point:

@command = N'-publisher [TEST125]-publisherdb [test]-distributor [dis_ip]-distributorsecuritymode 1  - Continuous'

Agentjob test125-test-repl_test-test129-3 generated script on TEST177, executed on TEST178.

Modify point:

@server = N'TEST178'

@command = N'-subscriber [TEST129]-subscriberdb [test]-publisher [TEST125]-distributor [DIS_IP]- DistributorSecurityMode 1-publication [repl_test]-publisherdb [test]    -continuous'

test177/178 establish the primary and secondary startup job.

Declare @role_desc varchar( -)Select @role_desc=A.role_desc fromSys.dm_hadr_availability_replica_states aJoinSys.dm_hadr_availability_replica_cluster_states b ona.replica_id=b.replica_idwhereB.replica_server_name=@ @SERVERNAMEif @role_desc='PRIMARY'begin execSp_start_job@job_name='test125-test-1'execSp_start_job@job_name='test125-test-repl_test-test129-3'End Else begin execSp_stop_job@job_name='test125-test-1'execSp_stop_job@job_name='test125-test-repl_test-test129-3'End

Process is complete.

This method, do not use if you do not have confidence in yourself, then the Replication Monitor interface will no longer be available, only through the script to monitor replication subscriptions.

Ps:

Throughout the construction of the test process, the spirit of where not to change where, the final test success, the above is a simplified method.

Other scripts used in the process:

execSp_helpdistpublisher N'TEST125'sp_changedistpublisher@publisher = 'TEST125'     ,  @property =  'Active'     ,  @value = 'true' SelectDataSource, Srvid fromMaster.. sysserverswhere Upper(srvname)= Upper(N'TEST125')SelectDataSource, Srvid fromMaster.. sysserverswhere Upper(srvname)= Upper(N'TEST129')Select *  fromMsdistribution_agents

SQL Server distribution distribution AlwaysOn build