High Availability for MS SQL with Replication using SQL Server Management Studio
(Feature available only in Premium and Enterprise Editions. Procedure applicable only for builds 6400 and later)

In mission-critical environments, one of the crucial requirements is to provide un-interrupted access to passwords. Password Manager Pro provides the High Availability feature just to ensure this. While configuring High Availability (HA) for the setup running in Microsoft SQL (MS SQL) server, you can carry out the replication between master and slave MS SQL databases using the MS SQL Server Management Studio. There is another document, which discusses the same High Availability configuration for the MS SQL server, with the only difference that the replication between master and slave MS SQL databases is carried out using Password Manager Pro itself.

Steps Required

Step 1:

Stop the primary server, if running.

Step 2:

The MS SQL server, which is used by the Password Manager Pro primary server, will act as the Master database. You need to specify another instance of MSSQL as the slave database. Then, import the SSL certificate of MS SQL server slave database into the Password Manager Pro primary server. Before proceeding with this step, ensure that the MS SQL slave server is also configured with SSL. You can do this by carrying out these steps.

To import the SSL certificate of slave SQL server into the Password Manager Pro primary:

Navigate to <Password Manager Pro Primary Installation Folder>/bin directory and execute the command importCert.bat <slavecert.cer >.

Step 3:

Navigate to <Password Manager Pro Primary Installation Folder>/conf directory, open the file masterkey.key and copy the SQL Master Key. You will use this in the next step. (In case, you have moved this key to a secure location as recommended while integrating the SQL server, keep the key ready for use in the next step).

Step 4:

Configure MS SQL server replication between the master and slave MS SQL databases.

Prerequisites:

1. Ensure that MS SQL Server Management Studio is installed in your system.

2. 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.

3. Before you configure the SQL server replication in the secondary server, create a database and execute the below mentioned queries:

i) 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));

ii) 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), PRIMARY KEY CLUSTERED (NOTIFY_ID));

iii) 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"));

Configuring SQL Server Replication

Launch the MS SQL Server Management Studio. The configuration steps are split into three parts for clear understanding.

Points to Remember:

- Read the instructions carefully in each wizard before you click Next.

- Click Finish only when the complete replication process is over. Clicking Finish in the middle of the process would result in a broken or incomplete configuration.

- Click Cancel anytime to abort the configuration process.

Part I - Create Distribution:

Under the Object Explorer, right-click on Replication and click Configure Distribution.
In the Configure Distribution Wizard displayed, choose the first option, i.e., choose to use this server itself as its own distributor, as shown in the below screenshot. Click Next.
Specify the root folder, where you want the snapshots to be stored. Click Next.
Specify a name for the distribution. Choose the folders where you want the distribution database file and log file, respectively. Click Next.
Select the server(s) which this distributor will use when they become the publishers.
Select what you wish to happen when you quit this wizard. You can either choose to Configure Distribution or Generate a scrip file, or both. Click Next.
Click Finish to complete the process or click Back to verify the options you have chosen in the previous screens. To know in detail on what happens after you close this wizard, see the below screenshot.
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.
Close the window one you see the Success message as shown in the below screenshot.

Part II - Create Publication:

1. Under the Object Explorer, under Replication, right-click on Local Publication and then click New Publication.

2. In the New Publication Wizard displayed, choose the primary database that contains the data and objects to be published. Click Next.

3. Choose the Publication Type as Merge Publication. Make sure, the publication type you choose is compatible with your application requirements. Click Next.

4. Select the Subscriber Type as SQL Server 2008 to be used by the subscribers to this publication. Click Next.

5. Next step is to select tables to be published as articles and click Next. Select all the tables except the below ones:

SeqGenState

Ptrx_ResGrpNotifyActionList

MSPSeqGenState

6. Select the issue displayed in the screen that may require changes to your application so that it continues to function as expected. Click Next.

7. This step allows you to filter table rows. Click Add to start adding filters to your publication, Click Next, if you do not want to filter the data in your application.

8. Select whether to run the Snapshot Agent immediately, or schedule the agent to run at the specified day/time. Click Next.

9. [Mandatory] Specify the Account name for the agent to run, and its Connection Settings. Click Next.

10. This is the step where you 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 the method "By impersonating the process account" to connect to the publisher. Click OK.

11. Select what you wish to happen when you quit this wizard. You can either choose to Create the Publication or Generate a scrip file, or both. Click Next.

12. Specify the Publication name. Click Finish to complete the process or click Back to verify the options you have chosen in the previous screens. To know in detail on what happens after you close this wizard, see the below screenshot.

13. Once you click Finish, you will see the publication being created as shown in the below screenshot. Click Stop if you want to stop creating the publication.

14. Close the window one you see the Success message as shown in the below screenshot.

Viewing Snapshot Agent Status:

Right-click on Publication and click View Snapshot Agent Status. You will see the below window showing the progress of 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.

Part III - Create Subscription:

1. Under the Object Explorer, under Replication >> Local Publications, right-click on the publication created in part II (primary) and then click New Subscriptions.

2. In the New Subscription Wizard displayed, select the publication created in part II (PMP-HA). Click Next.

3. Choose the location to run the Merge Agent(s). Choose the first option as shown in the below screenshot. Click Next.

4. [Mandatory] Select subscriber(s) and also choose database for each subscription. Click Next.

5. Now, you will be allowed to Connect to Server. Configure the required server information as shown in the below screenshot. Click Connect.

6. Once you are connected to the database server, the New Subscription Wizard will be shown again. Click Next.

7. [Mandatory] Complete the security information for the subscription(s). Mention the process account and connection options for each Merge Agent. Click Next.

8. Specify the domain or machine account where the Merge Agent will run, when the subscription is synchronized. Choose the way to connect to the publisher and distributer. In addition, choose the way to connect to the subscriber. Tap OK.

9. Specify the process account and connection options for each Merge Agent. Click Next.

10. Choose a Synchronization Schedule for each agent from the available options under the Agent Schedule drop down. Click Next.

11. Choose whether to initialize or not initialize the subscription(s) with a snapshot of the publication data and schema. Click Next.

12. Choose a Subscription Type for the subscription(s) from the available options. Also, choose a Priority for conflict resolution. Click Next.

13. Select what you wish to happen when you quit this wizard. You can either choose to Create the subscription(s) or Generate a scrip file, or both. Click Next.

14. Click Finish to complete the process or click Back to verify the options you have chosen in the previous screens. To know in detail on what happens after you close this wizard, see the below screenshot.

15. 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).

16. Close the window one 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 successfully completed.

Part IV - Execute Queries:

Execute the below mentioned queries in the primary server:

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

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

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

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

Step 5:

Start the primary server.

Step 6:

Install another instance of Password Manager Pro as the secondary server in a separate workstation. To install Password Manager Pro as the secondary, during installation process, choose the option "Configure this server as High availability secondary server (Read Only)". After installation, the PMP Secondary server should not be started.

Step 7:

After installing the Password Manager Pro secondary server, change it to run with MS SQL by carrying out the following steps:

Execute ChangeDB.bat.
Provide the details about the SQL server to Password Manager Pro by editing the file <Password Manager Pro Standby Installation Folder>/bin ChangeDB.bat (Windows) or <Password Manager Pro Standby Installation Folder>/bin sh ChangeDB.sh (Linux).
Select SQL Server and enter the values as given below:
1. Host Name of Slave Database: The Name or the IP address of the machine where MS SQL server is installed.
2. Port: The port number in which Password Manager Pro must connect with the database. Default is 1433.
3. Database Name: Name of the Slave database. Here, take care to specify the name of the slave database exactly as done in Step 4 above.
4. Authentication: The way in which you would like to connect to the SQL server. If you are connecting to the SQL server from Windows, you have the option to make use of the Windows Single Sign On facility provided Password Manager Pro service is running with a service account, which has the privilege to connect to SQL server. In that case, choose the option "Windows". Otherwise, select the option "SQL". It is recommended to choose the option 'Windows' as the username and password used for authentication are not stored anywhere.
5. User Name and Password: If you have selected the option "SQL", specify the user name and password with which Password Manager Pro needs to connect to the database. The username and password entered here will be stored in Password Manager Pro. So, take care of hardening the host.
  Here, you have the option to use even your Windows login credentials, if you are connecting to the database from Windows. In this case, enter the username as <domain-name>\<username>.
6. Encryption Key: The key with which your data is to be encrypted and stored in the SQL server. You may either leave it "Default" making Password Manager Pro to generate a key. If you have configured Master database with custom key, choose "Custom' here also.
  - If you have selected the option "Custom:" After doing the above, provide the certificate name and the symmetric key name in the GUI as mentioned in the Master database:
7. Click Test and then Save.

Step 8:

To apply Password Manager Pro license, custom icons and rebranding settings, if any, in secondary, copy the respective files from primary and place them in secondary.

Step 9:

Copy the manage_key.conf from primary to secondary and edit the file to specify the location of the file, pmp_key.key (encryption master key). Then, start the secondary server. Remember, Password Manager Pro requires the pmp_key.key file to be accessible with its full path, each time it starts up. Anyway, after the successful start-up, the key is not required, hence the device with the key file can go offline.

Verify High Availability Setup

Click to view the steps.

High Availability for MS SQL with Replication using SQL Server Management Studio (Feature available only in Premium and Enterprise Editions. Procedure applicable only for builds 6400 and later)