How to host the central repository database in an AlwaysOn Availability Group (4214750)

Applies to ApexSQL Monitor, ApexSQL BI Monitor, ApexSQL VM Monitor

Summary This article describes the steps required to install the central repository database to be part of AlwaysOn Availability Group.

Description

• Why and when should I host the ApexSQL Monitor central repository database in an AlwaysOn Availability Group
• How to install ApexSQL Monitor central repository database in AlwaysOn Availability Group?
• Can I migrate the existing central repository database to be hosted by AlwaysOn Availability Group

Applies to ApexSQL Monitor, ApexSQL BI Monitor, ApexSQL VM Monitor

Summary This article describes the steps required to install the central repository database to be part of AlwaysOn Availability Group.

Description

• Why and when should I host the ApexSQL Monitor central repository database in an AlwaysOn Availability Group
• How to install ApexSQL Monitor central repository database in AlwaysOn Availability Group?
• Can I migrate the existing central repository database to be hosted by AlwaysOn Availability Group

Why and when should I host the ApexSQL Monitor central repository database in a AlwaysOn Availability Group

ApexSQL Monitor collects and stores important performance data necessary for proper tuning of SQL Server performance as well as to maintain the SQL Server fitness at the highest level. Saying that keeping the performance monitoring blackout time at the lowest possible level ensures the availability of all necessary performance data with acceptable performance data lost that should not affect performance analysis anyhow.

Also, monitoring tens or hundreds of SQL Server instances requires ApexSQL Monitor to be tuned carefully and precisely according to the monitored SQL Servers state and importance, in order to get the best possible performance monitoring results, and at the same time to minimize the central repository database storage requirements. All those monitoring configurations are stored in the central repository database, where any loss of the central repository database will cause not only the disastrous collected performance data loss but also the loss of painstakingly established monitoring configurations.

So whenever larger number SQL Server instances have to be monitored, and especially where it is necessary to establish a demanding and precise configuration of the monitoring process, hosting the central repository database as part of AlwaysOn Availability Group is worth of considering.

Can I install the application on a computer that is not a member of the same domain with AlwaysOn AG?

No. The computer that will be used for ApexSQL Monitor application must be a member of the same domain as AlwaysOn AG

How to install the ApexSQL Monitor central repository database in an AlwaysOn Availability Group?

Install the central repository database

To deploy the central repository database in the AlwaysOn Availability group as the part of the ApexSQL Monitor installation process:

1. Run ApexSQL Monitor installer

• At the Setup -> Configuration step set the following:
  • In the Server name combo box, enter the name or IP address of the Primary replica of the AlwaysOn AG that you want to use for hosting the central repository database. In our example, it is 192.168.66.104
  • Enter the Windows AD account user name and password
  
  
  
  
• Press the Change button in the Database name section
  
  
  
  
  Populate the Data file path and Log file path field with a path on the primary replica machine file system where the repository database data files should be stored.
  
  Note: the path must be entered manually by the user
  
  Where required, change the name of the central repository database as well
  
  Press the OK button
• Check once again entered data, and if all is set, press the Next button

• The rest of the installation is standard, and once the application is installed, the successful installation info dialog should be displayed in the last step
  
  
  

Add the central repository database to an AlwaysOn Availability Group

Quick tip: Having a database in full recovery model and after that performing the full database back up are necessary prerequisites for adding a database in AlwaysOn AG. If not, the AlwaysOn Add database wizard will report the error

1. Open the SSMS and connect to the AlwaysOn Availability Group listener or primary replica where the Central repository database is installed

• Change the repository database recovery model to Full
  - Right-click on the repository database and open the properties
  - Select the Options in the Select page left pane
  - Select Full in the Recovery model drop down menu
  - Press OK
  
  
  
• Now the central repository database is set to the Full recovery model; the next step is to perform the Full database back up
  
  - Right click on the database name and select Task -> Back Up - Set the necessary parameters if needed and perform the full database back up
  
• Now, expand the Always ON High Availability ->Availability groups -> <replica_name> nodes
  
• Right click on the Availability Databases node and select Add database from the context menu
  
  
  
• The Add Database to Availability Group wizard Introduction step appears. No actions required here, just press Next
  
  
  
• In the Select Databases step, select the central repository database. In our example, this is the standard named ApexSQLMonitor database. Make sure that the Status column indicates Meets prerequisites
  Press the Next button
  
• In the Connect to Replicas step, all secondary replicas that belong to that Availability Group will be listed. It is in most cases one secondary replica
  
  
  
• Press the Connect button and then connect to the replica via the Connect to Server dialog
  
  
  
  
  
• Once the replica is connected, press Next
  
  
  
• In the Select Data Synchronization, chose the desired synchronization method that will be used for the central repository database. There is not recommended method here, and it depends on the user preferences. Thought, Automatic seeding is the most widely used
  
  Press Next
  
• If everything is OK, the Validation step should have all results marked as Success
  
  
  
• The next Summary step is the final one and serves for verifying the choices made in the wizard
  
  
  
  By pressing the Finish, the process of adding of the central repository database in AlwaysOn Availability group completes and should indicate that it is completed successfully
  
  
  
• Now, we should have our database added, and once the synchronization completes in the background, the (Synchronized) is added next to the database name
  
  
  

Set the ApexSQL Monitor service to use the central repository database via the AlwaysOn AG listener address

Now that the central repository database is the member of the AlwaysOn AG, it is necessary to instruct the ApexSQL Monitor service to communicate with the database using the AlwaysOn AG listener instead of accessing the primary replica directly

To do that, editing of the <drive-letter>:\ProgramData\ApexSQL\ApexSQLMonitor.Monitor.dat file is required. That change should instruct the ApexSQL Monitor service to use the new AlwaysOn Availability Group listener address for accessing the central repository database. The file can be edited using any text editor

Replace the existing name or IP address of the AlwaysOn AG primary replica SQL Server with the name or IP address of the AlwaysOn AG listener in the following line:

<MonitorServerName>AlwaysOn_AG_Listener </MonitorServerName>

Replace the highlighted part with the AlwaysOn AG listener name or IP address. It should look like in the below example

<MonitorServerName>192.168.66.109,5024</MonitorServerName>

Save the changed Monitor.dat file. The changes will not take effects until ApexSQL Monitor service is restarted



Now, all ApexSQL Monitor communication with the central repository database will be performed via the AlwaysOn listener

Can I migrate the existing central repository database to be hosted by an AlwaysOn Availability Group?

Yes. The only difference is that instead of the Install the central repository database step described in this article, the existing repository database should be migrated to the AlwaysOn Availability group primary replica by the user. Migrating method is not relevant in this case.

Once the migration is completed, just perform the rest of the steps described in the previous section