This knowledgebase article contains our suggested best practices for maintaining the SQL Server database included in our Endpoint products. We suggest that you also refer to Microsoft's general SQL server best practice articles, which can be found at https://msdn.microsoft.com/en-us/sqlserver/bb671430.aspx.
1. Use a dedicated SQL Server
With larger installations (over 3000 clients), we find that having all the management components installed on a single server can slow down operations in Enterprise Console. Of the server-side components that make up the Sophos management server, the three most likely to consume resources are (from left-to-right: most consumed to least consumed):
- Sophos Management Service (Mgntsvc.exe) - CPU, Memory, Disk I/O.
- Sophos Message Router (RouterNT.exe) - Memory, CPU, Disk I/O.
- SQL Server (sqlservr.exe) - Disk I/O, Memory, CPU.
Moving the SQL process to a dedicated machine will give the Sophos Message Router and the Sophos Management Service more resources, resulting in faster message processing and a faster response time from Enterprise Console.
Other ways you can free up resources on the management server include:
- Setting up message relays - this ensures that clients are not directly connect to the server for management. This will help to reduce the memory consumption of the message router on the management server and also decrease the number of inbound and outbound TCP connections. See Enterprise Console: configuring message relay computers for details.
- Using a dedicated file server to share out the distribution points where possible. Separating out the roles as much as possible should result in the most scalable configuration.
If your company has a dedicated SQL DBA team managing your SQL Servers, it may be more appropriate to delegate the management of the database component to your DBAs as they should have dedicated hardware optimally configured for running SQL server. Also, existing maintenance software such as monitoring and backup could then be used to manage your Sophos database along with any other standard maintenance plans.
To sum up, a dedicated SQL server should make for a higher performing system and it is therefore worth considering using a dedicated SQL server when managing a larger installation (more than 3000 clients). Also, consider the demands to be placed on the server before installing Enterprise Console. If you will be managing one or two thousand clients on a server that also runs several other management programs, you should consider installing the database on a separate server.
Finally, if your company is required to comply with PCI regulations, you may need to install the database on a server that is dedicated to this role.
2. Get the right version of SQL Server
Currently the version shipped with Sophos Enterprise Console is SQL Express 2012 R2. This version is suitable for most deployments; however due to certain limitations or personal preference, other versions of SQL Server may be used. Please see http://www.sophos.com/products/enterprise/endpoint/security-and-control/management/sysreqs.html for details of the versions of SQL Server supported by Endpoint Security and Control.
The following Microsoft article might help you decide and compare the features of the various editions of SQL server:
The only real consideration for a Sophos installation is database size; fortunately, even SQL 2005 Express Edition's limit of 4GB should be ample storage space for the Sophos database.
3. Keep the database files on non-system drives
With disk I/O being crucial to the performance of SQL Server, we recommend that where possible:
- SQL data files (.mdf) are stored on a dedicated physical drive.
- SQL log files (.Ldf) are stored on a dedicated physical drive, ideally a different physical drive to the data file.
These practices will ensure disk I/O is as fast as possible; placing the files on non-system disks (disks that do not have the operating system installed) will further improve performance.
SQL Server best practices are described in a Microsoft article: https://msdn.microsoft.com/library/cc966412.
4. Purge alerts regularly
To ensure query times are kept as low as possible, and generally improve system performance, we recommend that you review and purge historic data periodically.
By default, the database is checked every 24 hours for Alerts, Errors and Events that are older than 12 months. These records are automatically purged. However, if the alerts or errors are outstanding, they are never purged. Therefore, we recommend that you regularly check and clear your alerts to optimize system performance.
You can change the defaults in Enterprise Console from the Tools menu | Configure Reporting | Purge tab.
To supplement the built in purge task, Enterprise Solutions also comes with a command line tool called PurgeDB.exe. Aside from purging events and alerts, this tool also allows you to purge machines that have not reported in to the management server within X number of days.
5. Database recovery model set to Simple
To ensure the database does not grow too large set the recovery model to Simple. For details on how to do this see https://msdn.microsoft.com/en-us/library/ms189272.aspx.
For details on why this option is set to prevent the transaction log files from growing unexpectedly see Microsoft article: https://support.microsoft.com/kb/873235.
6. Make sure the Sophos management service and SQL servers have a good connection between them
Due to the constant flow of data between the Sophos Management Service and the SQL database, if a distributed installation is deployed, the network latency between the two should be kept to a minimum, this will help to ensure that Enterprise Console and the processing of messages runs smoothly.
It is worth noting that the load placed on SQL by the Sophos Management service will vary throughout the day depending on many variables, such as:
- Time of day. If all machines are started up at 9am, this will generate a number of status messages coming in from clients. These will all need to be processed by the management service and will generate a higher than normal number of reads and writes to the database.
- The number of remote Enterprise Consoles in use at any given time.
- Making policy changes.
- Running reports.
It is therefore worth considering this in line with any other demands on the network at similar times.
7. Try to keep the SQL instance or SQL server separate from others
In an ideal world each SQL database for a single application would be hosted in a dedicated SQL server instance on dedicated hardware (as long as it does not cross-reference another database in the same instance). It is not always possible to do this, but if you are considering adding the Sophos database to an existing SQL instance, it is worth ensuring that the additional load on resources is accounted for.
In terms of Sophos products, the PureMessage for Microsoft Exchange and Sophos for Sharepoint databases are totally independent from the Enterprise Console database, so there is no need for them to reside in the same SQL Server instance.
We also recommend using the name SOPHOS for the instance of SQL that you use for the Enterprise Console database. This is because we have documented many alternative ways of performing operations on the database and we assume that a SOPHOS instance is used in all of the articles. When you install Enterprise Console, the installation program creates the SOPHOS instance for you automatically.
8. Exclude SQL files in your Anti-Virus program
When configuring your SQL server, you should also consider our recommended practices with regards to exclusions. For more information see article 35970.
9. Back up your database regularly
Regular database backups are an essential part of any database maintenance plan.
Using SQL Management Studio
Regular backups can be configured using a wizard in Microsoft SQL Server Management Studio (under Management | Maintenance Plans). If you are using the version of SQL Express that was installed by the Endpoint Security and Control installation program, you may not have this installed on your network. However, it is a free download from Microsoft and can be found by searching for “Microsoft SQL Server Management Studio”. At the time of writing SQL Management Studio Express 2008, 2012 and 2014 are available.
The next section “Creating a maintenance plan” explains some of the options you may wish to consider when creating a maintenance plan.
Using scripts installed with Endpoint Security and Control
Sophos Endpoint Security and Control comes with a couple of batch files which make it simple to take a full backup of the SOPHOS database and perform a restore. You can find these tools in the following directory on the server where the database resides, by default this is %program files%\Sophos\Enterprise Console\DB\
The filenames are:
To create a full backup you can run:
BackupDB.bat SOPHOS.bak DBServer\SOPHOS SOPHOS521
SOPHOS.bak is the resulting backup file.
DBServer\SOPHOS is a combination of the database server name and SQL instance name.
SOPHOS521 is the database name to be backed up. This is the database for Enterprise Console 5.3.1.
To restore the previous backup run:
RestoreDB.bat SOPHOS.bak DBServer\SOPHOS SOPHOS521
SOPHOS.bak is the backup file.
DBServer\SOPHOS is a combination of the database server name and SQL instance name.
SOPHOS521 is the database name to be restored. This is the database for Enterprise Console 5.3.1.
Note: See article 17323 for a full list of database names by version.
You should schedule a regular backup and take backups after major changes to policies or where there are other significant changes in your network or group structure.
Note: Depending on which account your SQL Server service is running as, the directories it can write to when creating the backup might be limited. If you have any errors when taking a backup please use a directory which the account SQL service is running as can write to, e.g. %windir%\temp\.
10. Create a maintenance plan
Note: Due to the Express editions of Microsoft SQL Server not supporting SQL Server Integration Services (SSIS) maintenance plans can only be created when using a full version of Microsoft SQL Server.
Once you have installed Microsoft SQL Server Management Studio, started the application and connected to the SQL instance hosting the Sophos database, you can begin to create your maintenance plan.
In the Object Explorer tree view on the left, you should see the databases attached to the instance. Depending on the version of Enterprise Console you have installed, this might be SOPHOS521 (Enterprise Console 5.3.1) or SOPHOS540 (Enterprise Console 5.4.0). You might see more than these if you have upgraded from version to version over time as the previous database is not deleted after upgrade. If you are confident you can delete previous versions, you are free to do so and they can be deleted from within Microsoft SQL Server Management Studio.
Once you have found the Sophos database for the version of Enterprise Console that you have installed, expand the “Management” folder. You can then right-click on the subfolder called “Maintenance Plans” and choose either: “New Maintenance Plan” or “Maintenance Plan Wizard”. For the purposes of this document we will use the Wizard which will guide us through the tasks. The following article gives an overview of the wizard: https://msdn.microsoft.com/en-us/library/ms189036.aspx.
Give the maintenance plan a name and choose whether you would like to create a single schedule for all tasks or use the same schedule. This is up to you and how you feel it is best to organize the tasks running on the server.
More details on the maintenance options available can be found on the Microsoft SQL Best Practice site: https://msdn.microsoft.com/en-us/sqlserver/bb671430.aspx.