Configuring Replication using SQL Server Management Studio

When configuring a secondary server (High Availability or HA) setup using Microsoft SQL Server (MS SQL), database replication between the primary and secondary MS SQL databases must be set up. This can be accomplished directly from PAM360 or through the SQL Server Management Studio. This document provides a detailed guide to configuring MS SQL Server replication between the primary and secondary databases using SQL Server Management Studio.

1. Prerequisites

1. If you have the primary server with the PAM360 instance running, stop it to continue with your manual replication.
2. The MS SQL primary server used by the PAM360 will act as the primary database. You need to specify another instance of MS SQL as the secondary database during your manual replication configuration via MS SQL Server Management Studio.
3. Next, import the SSL certificate of the MS SQL secondary database into the primary server. If the secondary database is not already configured with SSL, you must set it up using the referenced help document. PAM360 requires SSL to ensure secure communication between the databases. To import the SSL certificate, open the command prompt, navigate to the <PAM360-Primary-Installation-Directory>/bin folder, and execute the following command:
   

      importCert.bat <Absolute-Path-of-the-MS-SQL-Secondary-Database-SSL-Certificate>
   

4. Once the SSL certificate is imported, navigate to the <PAM360-Primary-Installation-Directory>/conf folder and open the masterkey.key file. Copy the SQL Master Key from this file for use in the next step. If, in case, you have moved this key to a secure location as recommended while integrating the SQL server, keep it ready for use in the next step.
5. Ensure that MS SQL Server Management Studio is installed in your system.
6. Verify if replication is installed on the instance of the MS SQL server. Only then the Microsoft SQL Server Management Studio will be able to access the replication components.
7. Before you configure the SQL server replication in the secondary server, create a database in the secondary server and execute the below-mentioned queries:
   

   CREATE TABLE SeqGenState ( ROWGUID UNIQUEIDENTIFIER ROWGUIDCOL UNIQUE DEFAULT NEWSEQUENTIALID(), SEQNAME VARCHAR(100) NOT NULL, CURRENTBATCHEND BIGINT NOT NULL, CONSTRAINT SeqGenState_PK PRIMARY KEY CLUSTERED (SEQNAME));

   CREATE TABLE Ptrx_ResGrpNotifyActionList ( ROWGUID UNIQUEIDENTIFIER ROWGUIDCOL UNIQUE DEFAULT NEWSEQUENTIALID(), NOTIFY_ID BIGINT, ACCOUNT_ID BIGINT NOT NULL, ACTION_TYPE VARCHAR(200), SHARECHANGEUSER_ID VARCHAR(1000), OPERATED_NAME VARCHAR(300), OPERATED_TIME DATETIME, IPADDRESS VARCHAR(255), REASON VARCHAR(2500),PRIMARY KEY CLUSTERED (NOTIFY_ID));

   CREATE TABLE "MSPSeqGenState" ( ROWGUID UNIQUEIDENTIFIER ROWGUIDCOL UNIQUE DEFAULT NEWSEQUENTIALID(), "ORGANIZATIONID" BIGINT NOT NULL, "SEQNAME" NVARCHAR(100) NOT NULL, "CURRENTBATCHEND" BIGINT NOT NULL, CONSTRAINT "MSPSeqGenState_PK" PRIMARY KEY CLUSTERED ("ORGANIZATIONID", "SEQNAME"));

2. Configuring MS SQL Server Replication between the Primary and Secondary MS SQL Databases

The configuration steps are categorized into four sections for clear understanding. Launch the MS SQL Server Management Studio installed in your system, connect to the MS SQL primary server and proceed with the steps that follow.

2.1 Creating a Distribution

1. Under the Object Explorer menu in the MS SQL Server Managemenst Studio, right-click over the Replication module and click Configure Distribution.
   
2. In the Configure Distribution Wizard that opens, click Next to continue.
   
3. In the SQL Server Agent Start page that opens, select Yes, configure the SQL Server Agent service to start automatically to avoid the unattended run of replication agents that synchronize subscriptions.
   
4. In the Distributor page that opens, choose the first option, i.e., choose to use this primary server as its distributor, and click Next.
   
5. Specify the root folder where you want the snapshots to be stored and click Next.
   
6. Enter the distribution database name as pmpdistribution. Choose the folders where you want the distribution database file and log file, respectively, and click Next.
   
7. Select the primary server which this distributor will use when they become the publishers.
   
8. Enable the Configure Distribution checkbox and click Next.
   
9. Click Finish to complete the process or click Back to verify the options you have chosen in the previous screens. This wizard page will show in detail on what happens after you close this wizard.
   
10. Once you click Finish, you will see the configuration processing, as shown in the below screenshot. Click Stop if you want to stop configuring the distribution.
    
11. Close the window once you see the Success message, as shown in the below screenshot.
    

2.2 Creating a Publication

1. In the Object Explorer menu, under the Replication module, right-click on LocalPublication, and click New Publication.
   
2. In the New Publication Wizard that opens, click Next to continue.
   
3. From the New Publication Wizard, choose the database created in the primary server that contains the data and objects to be published and click Next.
   
4. Choose the Publication Type as Merge Publication. Make sure that the publication type you choose is compatible with your application requirements, and click Next.
   
5. Select the Subscriber Type as SQL Server 2008 or later to be used by the subscribers to this publication. Click Next.
   
6. The next step is to select the tables that are to be published as articles. Select all the tables except the below ones and click Next:
   1. SeqGenState
   2. Ptrx_ResGrpNotifyActionList
   3. MSPSeqGenState
   
7. Select the issue displayed on the screen that may require changes to your application and click Next. So that it continues to function as expected.
8. This step allows you to Filter Table Rows. Click Next if you do not want to filter the data in your application. Click Add to start adding filters to your publication.
   
9. Select whether to run the Snapshot Agent immediately or schedule the agent to run on the specified day/time. Click Next.
   
10. Click the Security Settings to specify the account details for the agent to run and its connection settings.
    
11. Choose the domain or machine account where the Snapshot Agent will run. Choose the option "Run under the SQL Server Agent service account". Also, choose method "By impersonating the process account" to connect to the publisher and then click OK to continue.
    
12. Enable the Create the Publication checkbox and click Next.
    
13. Specify the Publication name as PMP-HA and click Finish to complete the process, or click Back to verify the options you have chosen in the previous screens. To know in detail what happens after you close this wizard, see the below screenshot.
    
14. Once you click Finish, you will see the publication is created, as shown in the below screenshot. Click Stop if you want to stop creating the publication.
    
15. Close the window once you see the Success message, as shown in the below screenshot.
    

To View Snapshot Agent Status:

Right-click on Publication and click View Snapshot Agent Status. A window opens showing the progress of the snapshot creation, and the process takes a few minutes. Close the window once the snapshot is successfully generated. You can also Start the agent or Monitor it by clicking the respective buttons.

2.3 Creating a Subscription

1. In the Object Explorer menu, under Replication >> Local Publications, right-click on the publication that is created in section 2 and then click New Subscriptions.
   
2. In the New Subscription Wizard displayed, select the publication (created in section 2 (PMP-HA)) for which the subscription is to be created, and then click Next.
   
3. Choose the location to run the Merge Agent(s). Choose the first option as shown in below screenshot and click Next.
   
4. In the Subscribers wizard that opens, select subscriber and choose the secondary server database for the subscription. Now, click Add SQL Server Subscriber to add the secondary server information.
   
5. In the pop-up that opens, enter the secondary MS SQL server information and then click Connect.

6. Once you are connected to the secondary database server, click Next from the New Subscription Wizard.
   
7. Complete the security information for the subscriptions. Mention the process account and connection options for each Merge Agent.
   
8. As shown in the below screenshot, specify the account where the Merge Agent will run when the subscription is synchronized. Choose the way to connect to the publisher and distributor. In addition, choose the SQL Server login to connect to the subscriber with the respective secondary server login credential, and click OK.
   
   
9. Choose a Synchronization Schedule for each agent from the available options under the Agent Schedule drop-down and click Next.
   
10. Choose whether to initialize or not initialize the subscriptions with a snapshot of the publication data and schema and then click Next.
    
11. Choose a Subscription Type for the subscriptions from the available options. Also, choose a Priority for conflict resolution and then click Next.
    
12. Enable the Create the subscription(s) checkbox and click Next.
    
13. Click Finish to complete the process, or click Back to verify the options you have chosen in the previous screens. To know in detail what happens after you close this wizard, see the below screenshot.
    
14. Once you click Finish, you will see the subscription(s) being created, as shown in the below screenshot. Click Stop if you want to stop creating the subscription(s).
    
15. Close the window once you see the Success message, as shown in the below screenshot.
    

Viewing Synchronization Status

Right-click on Subscription and click View Synchronization Status. You will see the below window showing the progress of the synchronization, and it takes a few minutes. Close the window once the synchronization is 100% complete. You can also Stop the synchronization or Monitor it by clicking the respective buttons.

Now, the SQL server replication has been completed successfully.

2.4 Executing Queries in the Primary Server

Execute the below-mentioned queries in the primary server:

update Ptrx_HighAvailability set INSTANCENAME='MASTER_INSTANCE' where Ptrx_HighAvailability.ISMASTER='true'; 

For example: update Ptrx_HighAvailability set INSTANCENAME='pam-win11' where Ptrx_HighAvailability.ISMASTER='true';

update Ptrx_HighAvailability set INSTANCEPORT='MASTER_PORT' where Ptrx_HighAvailability.ISMASTER='true'; 

For example: update Ptrx_HighAvailability set INSTANCEPORT='1433' where Ptrx_HighAvailability.ISMASTER='true';

update Ptrx_HighAvailability set INSTANCENAME='SLAVE_INSTANCE' where Ptrx_HighAvailability.ISMASTER='false'; 

For example: update Ptrx_HighAvailability set INSTANCENAME='pam-secwin11' where Ptrx_HighAvailability.ISMASTER='false';

update Ptrx_HighAvailability set INSTANCEPORT='SLAVE_PORT' where Ptrx_HighAvailability.ISMASTER='false';

For example: update Ptrx_HighAvailability set INSTANCEPORT='1433' where Ptrx_HighAvailability.ISMASTER='false';

Where MASTER_INSTANCE is the primary SQL server, SLAVE_INSTANCE is the secondary SQL server, and the MASTER/SLAVE_PORT is the port used by the SQL server. For example:

Now start the primary MS SQL server. If you face any issues with the replication, navigate to Replication >> Local Publications, right-click on the created publication and click Launch Replication Monitor. In the pop-up that opens, you can check the synchronization status, last sync details, etc.

Now, continue with the secondary server configuration as mentioned here.