SQL Server Database
Administrator’s Guide
SQL Server Database Administrator’s Guide
Table of Contents
About This Document....................................................................................................................3
Sophos Database Descriptions.....................................................................................................3
Sophos - LoadWH SQL Server Task Description..........................................................................4
Database Deployment Considerations..........................................................................................4
Database Sizes............................................................................................................................. 4
Database Properties......................................................................................................................6
Database Location ........................................................................................................................6
Maintenance Recommendations...................................................................................................9
Database Best Practices........................................................................................................................................ 9
Backup and Recovery Best Practices .................................................................................................................. 10
SQL Server Maintenance Plan Best Practices..................................................................................................... 11
2
SQL Server Database Administrator’s Guide
About This Document
This document describes how Sophos NAC u se s SQL Server 20 00 databases in its operation. The document’s
purpose is to provide information to the database administrator who will maintain corporate SQL servers, but who
may not necessarily have the responsibility for administering Sophos NAC. This document includes the following:
▪ Sophos database descriptions
▪ Sophos -LoadWH SQL Server task description
▪ Database deployment considerations
▪ Maintenance recommendations
Sophos Database Descriptions
Sophos SQL Server database components contain all databases used by Sophos which include the following:
▪ AlertStore: This database store holds the information used to send alerts when user defined criteria is met.
One such example would be an alert that is setup to send the admin an email when the CEO is non-compliant.
▪ AuditStore: This database contains the audit data which tracks what users make changes in the NAC Web
interface. Sophos NAC maintains full audit information of every update made through the Sophos Web
interface. This audit information is kept in this database.
▪ GeneralStore: This database holds general data such as user home page preferences (the graphs the user
sees when they login), behavior categories, system objects, and Metadata related to PolicyStore schema
structures for the Middleware (MW).
▪ PolicyStore: This database holds the application, policy, network access, and registration data, required by the
software to determine security policy compliance and network access. The Sophos application server executes
a PatchLoader job daily (2 AM by default) which updates patch definitions in this database. Aside from this job,
the contents of the application and policy portion of the database typically change only as the administrator
updates these definitions. However, the registration, network access, and alerting information are updated as
the Sophos Agents assess endpoints. The CurrentDefsLoader (scheduled task that imports current virus/app
definitions) is also run as a scheduled task against this database.
▪ ReportStore: This data store holds the data reported by the Sophos Agents that can be viewed in the Sophos
Web interface Reports area. The ReportStore also contains staging tables that are used by the warehouse load
process. Data is constantly being added to this database as endpoints report assessment results. Data i s also
archived daily as the report data migrates to the report store warehouse.
▪ ReportStoreCache: This database is a work database that does not hold any permanent data. It contains data
from archived reports run throughout the day to facilitate performance.
▪ ReportStoreWH: This database contains archived warehouse report information that is displayed in the
Sophos Web interface Reports area. Each night (by default), data is copied from the ReportStore to the
ReportStoreWH and data that has aged past the retention period is purged from the ReportStoreWH. This data
store is primarily read as archived reports are displayed online or the SQL views are used to generate reports.
The update of this database occurs once per day during the when the job runs.
▪ SecurityStore: This database contains the account login informati on for the NAC Web interface users.
3
SQL Server Database Administrator’s Guide
Sophos - LoadWH SQL Server Task Description
Sophos – LoadWH: The warehouse load task is responsible for moving report data to the warehouse
(ReportStoreWH). When the task runs, it pulls rep ort data from the current reports, archives it in the report
warehouse, and deletes the data from the report store. It also removes data from the warehouse that is older than
the defined retention period. When run, the warehouse load task moves an entire day’s worth of data from the
report database to the warehouse and also purges an entire day’s data from the archive.
The default retention period for the archived report data is 90 days. This value can be changed by following the
procedure described in the Sophos NAC Advanced Installation Gui de. Redu cing the retention days results in a
smaller report warehouse store, but does not reduce the amount of data that is moved when the Sophos – LoadWH
task runs.
This task is scheduled to run once every 24 hours. By default, it runs at 2:30 AM local server time. The
administrator can change the task schedule so that it runs at a different time. The administrator can also manually
run the scheduled task using the SQL task scheduler. This job moves a si gnificant amount of enterprise report data
between two different databases, so do not run or schedule the job to run during a busy time of day or while
performing server backups.
Database Deployment Considerations
When you install the Sophos NAC databases, eight NAC databases are created, if they do not already exist, in
the default location for that SQL Server (usually “\Program Files\Microsoft SQL Server\MSSQL”).
Note: Once Sophos NAC operations have begun, policies are defined and Sophos Agents are depl oyed, it is very
difficult to make changes to the databases because the Sophos services have to be shut down during the
maintenance. Though it is not required, Sophos recomme nds establishing the database settings before Sophos is
deployed. Set the database size, location, and properti es immediately after installation so normal operations do not
have to be interrupted.
Database Sizes
During the Sophos database install, the databases are created with a small fixed size. All enterprises should
resize the databases for production purposes. Even though SQL Server databases can grow in size, the entire
database is locked when it expands. Therefore, improve the overall performance by specifying a database at a
large enough size so that it does not expand frequently. Many different factors affect the size of these
databases, so precise formulas are difficult to obtain. However, estimates can be made to provide a size
calculation that is adequate.
AlertStore
The amount of data in the AlertStore depends on many variables including number of alerts that are configured. In
most cases this db should be set to auto grow by 25 MB increments.
4
SQL Server Database Administrator’s Guide
AuditStore
The AuditStore will grow based upon how many updates are made in the NAC Web interface. If there are many
users logging in and making changes to the NAC Web interface then this database can grow significantly. In most
cases this db should be set to grow in 100 MB increments.
GeneralStore
The size of the GeneralStore will grow dependent on how many additions/customizations are made to user home
pages (the graphs the user sees when they login), as well as application behavior categories, and other system
objects. This database should remain constant in size and in most cases, setting this to auto grow in 25 MB
increments is sufficient.
PolicyStore
The amount of data in the PolicyStore depends on many variables including number and size of policies, number of
registered endpoints, and number of alerts. The PolicyStore for a large enterprise (thousands of users) with a policy
size of less than 100 applications will approach 50 0 MB. Each 1000 registered endpoints also require an additional
2 MB of space.
ReportStore
The ReportStore size depends on the n umber of endpoints that report assessments each day. This databa se
should be set to a Fixed Growth Size of 500MB and will grow based on the following calculation:
.4 KB x [number of profiles in policy] x [number of endpoints].
Policies with more applications result in a larger growth rate.
ReportStoreWH
The ReportStoreWH size corresponds with the number endpoints that have archived reports in this database. This
database should be set to a Fixed Growth Size of 500MB and will grow based on the following calculation:
.4 KB x [number of profiles in policy] x [number of endpoints] x [purge data value in days].
ReportStoreCache
This database does not hold any permanent data. It contains cached data from viewing archived reports and is
purged daily. A database size of 250 MB set to auto grow by 25 MB increments is sufficient in most cases.
SecurityStore
This database holds data corresponding to the number of accounts which have been created and includes
encrypted credentials for any accounts which are configured to use the internal user store. In most cases, this
database should be set to auto grow in 25 MB increments.
Transaction Logs
No formula can be used to determine the appropriate transaction log size. Microsoft suggests monitoring the
transaction log size over time and making it large enough so that it does not frequently expand.
Tempdb
Sophos uses tempdb during normal operations by using cursors and created temp tables. For this reason, tempdb
must be set to automatically grow at a fixed value. Assuming the SQL Server is dedicated to the NAC installation
and applications an allocated size of 1 GB should be sufficient. If tempdb requires more spa ce, it expand s to
accommodate these needs.
When the SQL Server restarts, tempdb is rebuilt with a default size. This size can potentially cause performance
issues after the restart because tempdb occasionally expands again while normal operations proceed. To avoid
this, set the size of tempdb so that it rarely has to expand. Unfortunately, there are no metrics that can be used to
5
SQL Server Database Administrator’s Guide
predict the required size of tempdb. Microsoft su ggests monitoring the tempdb size over time and making it large
enough so that is does not frequently expand.
Database Properties
Databases are created by default to allow auto grow, prevent auto shrink, and automatically update statistics. Use
these default settings for all Sophos NAC databases.
Auto Grow Database by Fixed Size
Set the NAC databases to automatically grow by a fixed size rather than a percentage. Even if appropriately sized
from the beginning, some event may cause a database to require more space. Set databases to grow automatically
to ensure that if more space is needed, it is available. Verify that there is enough room on the disk so that the
databases will grow and continue normal operations. After all Sophos Agents are deployed and Sopho s NAC has
been running for a time period equal to the retention days of the report archive, the databases will have reached a
stable size so they will not expand frequently.
Note: Altering Sophos NAC environment, such as ch anging the report interval or adding many new users, causes
the databases to expand.
Prevent Auto Shrink Database
Set the databases to prevent them from automatically shrinking. This setting is the default. Sophos databases do
get to a relatively static point over the course of operations. This means the databases do not appreciably grow or
shrink, unless some change takes place, such as changing the report archive retention days. Once the databases
reach a stable size, they more than likely will never shrink.
Auto Update Statistics
By default, the NAC databases are created with Auto create statistics and Auto update statistics enabled.
The value of these database properties can be specified in the database Properties dialog in SQL Server Manager.
Enable these properties for each database so that queries are o ptimized based on the current index statistics.
Recovery Mode
There are three recovery modes available for each database: simple, bulk-logged, and full. Full is the default setting
and offers the best protection against failure by allowing databases to be restored from full or differential backups.
Bulk-logged recovery mode does not log such op erations as SELECT INTO and bulk load.
Database Location
Moving all or part of databases to separate drives can improve performance of the application. The following
section describes how to move databases, log files, and indexes to separate devices.
Database
It is possible to locate one or more of the NAC databases to another location. This option can help improve
performance if databases with different update profiles are placed on different physical drives managed by separate
controllers. For instance, placing the PolicyStore and ReportStore on separate physical drives will eliminate the I/O
contention between the databases during the normal operations of retrieving policy and reporting compliance.
The easiest way to relocate databases to another location is to install the NAC dat abases using the installation
package and then detach the newly created databases, then move them (with their corresponding transaction logs)
to the new location, and then re-attach them. If Sophos NAC is already installed and running, then put the NAC
6
SQL Server Database Administrator’s Guide
server into maintenance mode, detach the databases, move the data and log files, re-attach the database s, and
restart the services.
Note: Putting the server into maintenance mode does not prevent the report warehouse load task from running,
since this is a SQL job and not a service. Put the server into maintenance mode, move the databases, and then
stop maintenance mode to allow the NAC services to reconnect. These are the commands to put the NAC server
into maintenance mode and out of maintenance mode from a command prompt:
“%programfiles%\endforce\support tools\”maintmode.exe /start
“%programfiles%\endforce\support tools\”maintmode.exe /stop
The following procedure uses the ReportStore datab ase to show how to move one of the databases to a different
location.
1. Start the maintenance mode using the start command.
2. On the NAC SQL Server, detach the database.
sp_detach_db 'ReportStore'.
3. Move the files ReportStore_Data.MDF and ReportStore_Log.LDF from their location to a new location on a
different drive. For example, move the database to D:\Sophos.
4. Reattach the database.
EXEC sp_attach_db @dbname = 'ReportStore',
@filename1 = 'D:\Sophos \ReportStore_Data.MDF',
@filename2 = 'D:\Sophos \ReportStore_Log.LDF'
5. Stop the maintenance mode using the stop command.
Log File Location
The log files of the NAC databases can be moved to different locations as well. This option can improve
performance by allowing log file I/O to be separate from data file I/O. The following procedure uses the PolicyStore
database to show how to move a log file to a different disk drive.
1. Start the maintenance mode using the start command.
2. On the Sophos SQL Server, detach the database.
sp_detach_db 'PolicyStore'
3. Move the file PolicyStore_Log.LDF from its location to a new location on a different drive. For example, move
the log file to D:\Sophos.
4. Reattach the database.
EXEC sp_attach_db @dbname = 'PolicyStore',
@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL',
@filename2 = 'D:\Sophos\ReportStore_Log.LDF'.
5. Stop the maintenance mode using the stop command.
7
SQL Server Database Administrator’s Guide
Index Location
Placing non-clustered indexes on a drive separate from the data can improve performance. NAC databases are
deployed with indexes that have been designed and tested for the NAC application. Since indexes are already in
place, moving them to another location involves scripting the DROP and CREATE statements indexes and altering
the ON Primary parameter of the CREATE statement to use a filegroup that is on a different drive. The SQL
Enterprise Manager can be used to create and edit these scripts to change the filegroup. The Query Analyzer can
then be used to run the scripts.
RAID
RAID storage is natively supported by SQL Server and Windows Server 2003. Since the three primary NAC
databases have the potential for significant write a ctivity at various times throughout the day, care must be taken to
choose the RAID level that offers the best combination of economy and performance. RAID 0 or disk striping
improves performance by spreading read/write operations across disks, but does not offer fault tolerance. RAID 1,
or mirroring, keeps identical copies of the selected disk. It provides good fault tolerance, but may degrade write
performance. Many RAID controllers offer a hybrid solution known as RAID 0+1. RAID 5 is called striping with parity
and offers data redundancy with the parity information. RAID 1 and RAID 0+1 offer better data protection and
performance than RAID 5, but at higher cost in terms of the number of disks required. For more information on
RAID, see the SQL Server Books Online.
Log shipping
SQL Server Enterprise Edition provides log shipping. Log shippin g create s wa rm standby servers that offer backup
of the primary production server. To set up log shipping between t he primary and backup servers, follow the
instructions for implementing log shipping in the SQL Server do cumentation.
Note: If the primary server runs into problems, the secondary server can be promoted to primary.
1. Start the maintenance mode using the start command.
2. Follow the instructions in SQL Server documentation for changing the primary role of a SQL Server.
3. From the Sophos application server, use Add or Remove Programs Control Panel to uninstall the Sophos
application server.
4. From the Sophos application server, reinstall the Sophos application server. When prompted for SQL Server
name, specify the new primary SQL Server.
5. Stop the maintenance mode using the stop command.
Failover Clustering
Failover clustering provides a high availability solution because a SQL Server will immediately fail over to
the second node. Since Active/Passive clustering is designed to be transparent to the end u ser, no special
considerations need to be made for the NAC databases.
Note: Sophos NAC does not currently support Active/Active clustering.
8
SQL Server Database Administrator’s Guide
Maintenance Recommendations
Database Best Practices
The following database best practices ensure that the NAC databases run without errors and that the report
database archives and purges data without errors. NAC reports are e nterprise data-driven and are built with large
enterprises in mind. The reports are comprehensive, robust, and produce large amounts of reporting informat ion.
Best
Monitor your SQL Server log and SQL Server
event log to verify that no server or SQL errors or
warnings are causing problems with the NAC
report data archiving and purging processes.
Verify that the NAC Report Warehouse Job is
moving report data from the current reports to
the archive reports on a daily basis according
to the specified schedule.
Sophos recommends that no processes or
backups run when the NAC report ware house is
moving report data from the current reports to the
archive reports. The task runs at 2:30 AM daily by
default.
On a weekly basis, verify that ample disk
space is available on the SQL Server for the
NAC databases.
Description
The server log and the event log provide details around
warnings or errors that occur on the SQL Server. By
proactively monitoring these logs, NAC databases
remain operational and fully functional.
Access the Sophos Web interface and view an Archive
report. The date and time to the right of the report title
indicates the time/date of the most recent warehouse
load. If this process does not run daily, report data may
be lost and can cause database performance issues on
the Report Store.
The NAC report warehouse task can be SQL Server
intensive and will complete faster when other processes
and backups are not running at the same time. Other
processes and backups can cause resource contention,
and this contention will affect the timeliness of the
warehouse task.
The size of the NAC databases will grow substantially if
the NAC report warehouse and/or report purge processes
have not been successfully running. Additional changes to
the NAC system, such as adding users, will also increase
the amount of space required.
Verify that the SQL Agent is turned on and is
running. The SQL Agent must be running for the
Sophos report data archiving and purge processes
to run as scheduled.
If the SQL Agent is not running, these processes will not
run and the report databases will grow substantially.
9
SQL Server Database Administrator’s Guide
Backup and Recovery Best Practices
The following backup and recovery recommendations constitute best practices for the backup and recovery of the
Sophos Alert Store, Audit Store, General Store, Policy Store, Report Store, Report Store Cache, Report Store
Warehouse, and the Security Store databases:
Best
Create backup schedules for the Alert Store, Audit
Store, General Store, Policy Store, Report Store,
Report Store Cache, Report Store Warehouse, and
the Security Store databases based on size,
tolerance for data loss, and the time available for
backup and recovery.
For the NAC PolicyStore database, use a
combination of database, differential, and log
backups. For example, back up the entire
database nightly, perform a differential backup
hourly, and back up the transaction log every 20
Create a step-by-step recovery plan for the NAC
PolicyStore, ReportStore, ReportStoreWH, and
ReportStoreCache databases. Test the recovery
plan using real backups.
Store backups in a secure off-site location. Store all NAC database backups at an off-site location to
Create a process to notify the person or
persons responsible for enterprise disaster
recovery.
Database backup schedules en sure that data is
backed up. Failure to backup the NAC databases
could result in unrecoverable policy and repo rt data.
The combination of database, differential, and log
backups for the PolicyStore database provides
comprehensive policy backup coverage. This
combination ensures that no policies or policy changes
are lost.
The step-by-step recovery plan and test guarantees
that policy and report information are recoverable in the
event of database corruption or hardware failure.
guarantee data integrity when and if the need to use the
backups occurs. If database backups are unavailable , all
policy and report data will be lost and unrecove ra ble.
When NAC database recovery is required, contact all
individuals responsible for recoverin g databases. This
process ensures that policy and repo rt data are
restored both efficiently and timely with minimal errors
and data loss.
Description
10
SQL Server Database Administrator’s Guide
SQL Server Maintenance Plan Best Practices
The Microsoft SQL Server Maintenance Plan Wizard provides the easiest way to create a maintenance plan that
protects your NAC data. Using the wizard automates essential maintenance tasks. Performing these tasks, which
include integrity checks, backups, and database optimization, keeps the Sophos databa ses running efficiently.
Once you complete the maintenance wizard, you should test it and verify that it performs as expected.
Microsoft SQL Server Maintenance Plan Wizard Settings
The following table provides the Sophos recommended settings for the SQL Server Maintenance Wizard.
Remember to not schedule these maintenance tasks at the same time that the data warehouse load task is running.
Setting Name Setting
Data Optimization Information
Important: Manually initiate the data optimization process only du ring a maintenance window.
Reorganize data and index pages Select
Free space per page percentage 10
Schedule
Duration No End Date
Enable Schedule Yes
Database Integrity Check
Check database integrity Select
Include indexes Select
Attempt to repair any minor problems Select
Perform these checks before doing backups Select
Schedule
Duration No End Date
Enable Schedule Yes
Specify Database Backup Plan
Back up the database as part of the maintenance plan Select
Verify the integrity of the backup when complete Select
Determined by DBA
Determined by DBA
Location to store the backup file Disk
11
SQL Server Database Administrator’s Guide
Setting Name Setting
Schedule
Determined by DBA
Duration No End Date
Enable Schedule Yes
Specify Backup Disk Directory
Use the default backup directory Select
Remove files older than
Determined by DBA
Backup file extension BAK
Specify the Transaction Log Backup Plan
Back up the database as part of the maintenance plan Select
Verify the integrity of the backup when complete Select
Location to store the backup file Disk
Schedule
Determined by DBA
Duration No End Date
Enable Schedule Yes
Specify Transaction Log Backup Disk Directory
Use the default backup directory Select
Remove files older than
Depends on log file size (2
weeks to 3 days)
Backup file extension TRN
Reports to Generate
Write report to a text file in directory Select
Maintenance Plan History (local server)
Write history to the msdb.dbo.maintplan_history table on this serve r Select
Limit the rows in the table to 1000 rows for this plan
Database Maintenance Plan Wizard
Assign name to the maintenance plan Yes
12
Loading...