High Availability Procedures for Supportworks

Status:	Published
Version:	1.1
Authors:	HTL QA
Applies to:	Supportworks ESP 7.x

High Availability Procedures for Supportworks

Introduction

This document provides an overview of how to achieve high availablity of your Supportworks system. It is not a step-by-step guide but rather outlines the major procedures to perform and provides specific examples of some of them.

The document covers the setting up of a real-time replica of your Supportworks system that you can switch over to on failure of the live server, as well as the full backup of your system on a separate backup server. It also describes methods by which you can restore your system from the backup server to the system's original pre-replication state, to its previous nightly state, or to the database's state at any point in time.

It is assumed that the database server you are using to hold all your Supportworks application data is the supplied SwSQL server.

Note: Any kind of data replication, such as that described in this document, is only of use for recovery from hardware failures or network/operating-system failures. If failures are caused by configuration or data issues, these would merely be replicated in the replica server. It should also be borne in mind that switch-overs from live to replica server, as described in this document, will still cause a certain amount of disruption among users, since it is unavoidable for network connections to be lost and services to be restarted.

The Required Server Environment

In order to achieve high availability, you will need three Supportworks servers: your live server, the replica server and a backup server. You will need to purchase an additional server licence for the replica server. (For pricing information on additional server licences, please contact your Hornbill account manager.) The live server and the replica server would each consist of a full, identically configured Supportworks installation. The backup server could just be a clean machine. All the servers must be able to communicate with each other over a network and may well be physically located at different sites.

The Live Server

This is the server used by your analysts on a day-to-day basis. You will be configuring it (and the replica server) to replicate your SwSQL database data to the replica server. Non-SQL data (held outside of the databases) such as call/e-mail file attachments and configuration files will be replicated from the live server to the replica server by running Windows xcopy commands on a five-minute scheduled basis on the live server.

You will also be configuring the live server to schedule a daily or nightly backup of SwSQL binary logs (see http://dev.mysql.com/doc/refman/4.1/en/binary-log.html), as well as of non-SQL data such as file attachments and configuration files (again using xcopy). When you enable replication, the first binary log file of SQL statements is created; these logs can be used for point-in-time data restores once the log files have been copied to the backup server.

The Replica Server

This is the server that would be used only when the live server becomes unavailable. It would contain a fully up-to-date replica of the SwSQL databases from the live server and a nearly up-to-date replica of the set of files copied using xcopy on a scheduled basis. (Note that it would be possible to create a Windows replicated folder, but its contents would still not be completely up to date.) You will also be configuring the replica server to schedule a nightly SwSQL data dump to the backup server.

The replica server would require a Disaster Recovery (DR) licence.

The Backup Server

This is the server that would be used to store the following:

• The initial database
• The scheduled backups of the SwSQL binary log files from the live server
• The scheduled backups of the non-SQL files from the live server
• The scheduled SwSQL data dumps from the replica server

The files on the backup server can then be archived to tape on a nightly/weekly/monthly basis.

Configuring SwSQL Data Replication

The SwSQL database system can be configured to dynamically replicate the data in its databases via the TCP networking protocol on communications port 5002. Although the configuration procedure itself requires server down time, the ongoing replication processes that result from this can proceed without any services having to be shut down. A possible set of configuration instructions is given here:

1. On both servers, run the Supportworks Server Configuration utility (by selecting Start > Programs > Supportworks Server > Supportworks Server Configuration).
2. On the Server tab of the live server's instance of the Server Configuration utility, use the Installed Services section to stop all services (starting at the top of the list).
3. On the live server, run the Interactive SQL program (by selecting Start > Programs > Hornbill Core Services > Interactive SQL).
4. Enter the following SQL statement to create a "replication" user account:

   GRANT ALL ON *.* TO user-ID@"%" IDENTIFIED BY 'password';

5. On the live server, open, using a text editor, the file called Swsql.ini, which is located in the SwSQL server’s Bin folder (C:\Program Files\Hornbill\Core Services\SwSqlServer\Bin\ by default) and add the following two lines of text at the end:

   log-bin=replication.log

   server-id=1

6. On the replica server, open, using a text editor, the file called Swsql.ini, which is located in the SwSQL server Bin folder (C:\Program Files\Hornbill\Core Services\SwSqlServer\Bin\ by default) and enter the following lines of text at the end:

   master-host=live-server-IP-address

   master-user=user-ID

   master-password=password

   master-port=5002

   server-id=2

   replicate-ignore-table=swdata.swsystag

   where the user credentials are those of the replication account created above.

7. Stop the SwSqlServer service on the live and replica servers.
8. Copy the contents of the Data folder from the live server to the replica server. By default, its path is C:\Program Files\Hornbill\Core Services\SwSqlServer\Data.
9. Start the SwSqlServer service on the live and replica servers.
10. Run the Interactive SQL program on the replica server (by selecting Start > Programs > Hornbill Core Services > Interactive SQL).
11. Enter the following SQL statement:

    flush slave;

12. On the live server, run the Supportworks Server Configuration utility (by selecting Start > Programs > Supportworks Server > Supportworks Server Configuration).
13. Start all of the installed services, starting from the bottom of the Installed Services list, working your way upwards.

The replica server's embedded SwSQL databases should now keep "in sync" with the live server's SwSQL databases, without any services having to be stopped in the process. In order to confirm that data is in sync, you may use the following SQL statements in the Interactive SQL program:

SHOW SLAVE STATUS; (on the replica server)

SHOW MASTER STATUS; (on the live server)

Scheduling Non-SQL File Replication

For the copying of files held outside of the databases from the live server to the replica server, you could include a command line similar to the following in a regularly scheduled script run (say every five minutes) on the live server:

xcopy C:\Program Files\Hornbill\Supportworks Server\*.* \\replica-server\Hornbill-folder\Supportworks Server /d /e /v /c /i /q /h /r /k /y

where replica-server would be the name of the replica server, and Hornbill-folder would be the network share name representing the Hornbill installation folder on that machine.

This example command would copy all files in your live Supportworks-server installation that have changed and all new files that have appeared.

You may wish to set up the script within the Scheduler tab of the Supportworks Server Configuration utility.

Scheduling Backups of Non-SQL Files and SwSQL Binary Log Files

For the copying of files held outside of the databases and also of SwSQL binary log files from the live server to the backup server, you could include command lines similar to the following in a regularly scheduled script run (say once a day) on the live server:

xcopy C:\Program Files\Hornbill\Supportworks Server\*.* \\backup-server\Hornbill-folder\Supportworks Server /d /e /v /c /i /q /h /r /k /y

xcopy C:\Program Files\Hornbill\Core Services\SwSqlServer\data\replication.* \\backup-server\Hornbill-folder\Core Services\SwSqlServer\data /d /e /v /c /i /q /h /r /k /y

where backup-server would be the name of the backup server, and Hornbill-folder would be the network share name representing the Hornbill installation folder on that machine.

These example commands would back up all files in your live Hornbill installation that have changed and all new files that have appeared.

Scheduling a Nightly SwSQL Data Dump to the Backup Server

The replica server can become the source for a nightly dump of the database, ensuring that no associated downtime will occur on the live server. The dump data is sent to the backup server, from which it (together with all the other backed-up data) can be archived to tape. You can use the dump data to restore the database to a given nightly state, or go further and perform a point-in-time restore should you require one.

In order to dump the database, you will need to create a script containing the following command lines, which you can schedule to run on a nightly basis:

cd\Program Files\Hornbill\Core Services\SwSqlServer\bin

swsql –u root –e “STOP SLAVE”

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_knowledgebase > \\backup-server\DB-backup-folder\sw_knowledgebase_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_messagestore > \\backup-server\DB-backup-folder\sw_messagestore_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_systemdb > \\backup-server\DB-backup-folder\sw_systemdb_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B swdata > \\backup-server\DB-backup-folder\swdata_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_config > \\backup-server\DB-backup-folder\sw_config_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_messenger > \\backup-server\DB-backup-folder\sw_messenger_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_calendar> \\backup-server\DB-backup-folder\sw_calendar_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B sw_tempdb > \\backup-server\DB-backup-folder\sw_tempdb_export.sql

swsqldump -u root --quick --add-drop-table --extended-insert -B mysql > \\backup-server\DB-backup-folder\mysql_export.sql

swsql –u root –e “START SLAVE”

where backup-server would be the name of the backup server, and DB-backup-folder would be the network share name representing the folder on that machine where the database files are held.

Switching Over from Live to Replica Server

Should the live server fail, you can manually switch over to the replica using a fairly simple process. This requires that you first stop replication on the replica server by submitting the STOP SLAVE; command from within Interactive SQL and then start the Supportworks services on the replica server in the following order:

net start SwHttpServer

net start SwServerService

net start SwCalendarService

net start SwSchedulerService

net start SwMessengerService

net start SwMailService

net start SwMailSchedule

net start SwLogService

net start SwIndexServer

net start SwFileService

net stop SwHttpServer

net start SwHttpServer

The replica server will then be live.

Note that you may also choose to change the now live server's IP address or DNS name to that of the original live server, so your analysts do not have to log into a "different" server.

Restoring Your Supportworks System to Its Original State

Should you wish to restore, on a clean machine, the entire database and all files to their respective states prior to the point at which replication was set up, then you would need to perform the following actions on the machine concerned:

1. Install Core Services and Supportworks.
2. Stop all Supportworks/Core services except SwSqlServer.
3. Copy the SwSQL binary logs from the backup server to a temporary folder (say c:\temp\) on the new server.
4. Run the following command in a Windows Command prompt:

   swqlbinlog c:\temp\replication.0001 c:\temp\replication.0002 | swsql -u root -p

   Note that you need to specify each binary log file (replication.xxxx) in order, as shown above for the first two.

5. Copy all files located in the Hornbill\Supportworks Server\ folder and its sub-folders from the backup server to the new server.
6. Start the Supportworks services in the same order as in the section on switching over above.

Restoring Your Supportworks System to Its Previous Nightly State

Should you wish to restore, on a clean machine, the database and all files to their respective states at the point at which the last nightly dump occurred, then you would need to perform the following actions on the machine concerned:

1. Install Core Services and Supportworks.
2. Stop all Supportworks/Core services except SwSqlServer.
3. Copy the SwSQL data dump files from the backup server to a temporary folder (say c:\temp\) on the new server. These files (of which there are nine) would all be of the form *.sql.
4. Run a script containing the following command lines:

   cd\Program Files\Hornbill\Core Services\SwSqlServer\bin

   net stop SwAdminService

   net stop SwCalendarService

   net stop SwFileService

   net stop SwIndexServer

   net stop SwLogService

   net stop SwMailSchedule

   net stop SwMailService

   net stop SwMessengerService

   net stop SwSchedulerService

   net stop SwServerService

   net stop SwHttpServer

   swsql -u root sw_knowledgebase < c:\temp\sw_knowledgebase_export.sql

   swsql -u root sw_messagestore < c:\temp\sw_messagestore_export.sql

   swsql -u root sw_systemdb < c:\temp\sw_systemdb_export.sql

   swsql -u root swdata < c:\temp\\swdata_export.sql

   swsql -u root sw_config < c:\temp\-backup-folder\sw_config_export.sql

   swsql -u root sw_messenger < c:\temp \sw_messenger_export.sql

   swsql -u root sw_calendar < c:\temp\sw_calendar_export.sql

   swsql -u root sw_tempdb < c:\temp \sw_tempdb_export.sql

   swsql -u root mysql < c:\temp \mysql_export.sql

   net start SwHttpServer

   net start SwServerService

   net start SwSchedulerService

   net start SwMessengerService

   net start SwMailService

   net start SwMailSchedule

   net start SwLogService

   net start SwIndexServer

   net start SwFileService

   net start SwCalendarService

   net start SwAdminService

   net stop SwHttpServer

   net start SwHttpServer

5. Copy your backed-up files in the \Supportworks Server folder over to the same folder on the new server.

Restoring Your SQL Data to Its State at Any Point in Time

You can easily restore your live database to its state at any specified point in time. For example, you may need to do this following a rogue SQL statement having being submitted at 11 a.m. You would then need to perform the following actions:

1. Restore your Supportworks system to its previous nightly state as described in the relevant section above, but without copying your backed-up files.
2. In a Windows Command prompt on the live server, run the following command:

   Swsqlbinlog --start-datetime=” YYYY-MM-DD hh:mm” --stop-datetime=” YYYY-MM-DD hh:mm” \ C:\Program Files\Hornbill\Core Services\SwSqlServer\data\replication.xxxx | swsql –u root

   where the two instances of YYYY-MM-DD hh:mm would reflect the time at which the nightly dump was taken and the time just before the rogue SQL statement was submitted, respectively. Note that replication.xxxx would be the name of the last binary log file.

Optional: Replicating Registry Keys

You may also wish to replicate the relevant Windows registry keys in which any HTTP server aliases you may have modified or added have been recorded. You can only do this by manually editing either or both of them on the replica server whenever you have modified the corresponding ones on the live server. The keys are as follows:

HKEY_LOCAL_MACHINE\Software\Hornbill\Core Services\Applications\_Global\HttpAliases

HKEY_LOCAL_MACHINE\Software\Hornbill\Core Services\Applications\Supportworks Server\HttpAliases

Further Reading

Further documentation on backups and restoring from binary logs is available on the MySQL website for the relevant version of MySQL.