An important step in setting up your Lumension Endpoint Security environment is making sure the database is properly maintained. The below recommendations are meant as guidelines, and should only be used when your organization does not have an existing policy in place.
The Lumension Endpoint Security database stores everything relevant to the product, including all of the permissions and options set to manage your environment. At minimum, it is recommended to run weekly differential backups and monthly full backups. Most organization's have their own requirements for backing up SQL databases, so it is recommended that you check with your SQL Database Administrator or management before setting anything up.
The database not only stores the various permissions and options necessary for the operation of the product, but also contains historical log data from the client machines. Because every organization's data retention policy differs, it is necessary to define a maintenance plan to delete the old historical log data that gives you the data retention your organization needs. Typically 60-90 days of retention is acceptable for operational purposes, but it is important to check your organization's polices beforehand. Please make sure you have confirmed your organization's data retention policies before proceeding.
This article is divided into two sections based on the edition of SQL being used. SQL 2005/2008 Standard Edition (and higher) can make use of the SQL Server Agent and Maintenance Plans to automate database maintenance, where as Express editions cannot. Because of this, the use of 3rd party tools is required when automating Database Maintenance for the Lumension Endpoint Security Database.
Note: The below information is only meant to be used with Lumension Endpoint Security versions 4.4 or higher. If you are running 4.3 or below, please contact Support for more information.
Note: This article is targeted towards new installs of Lumension Endpoint Security. If you have an existing installation of Lumension Endpoint Security and wish to set up an automated maintenance plan, please contact Support before attempting the below steps.
SQL 2005/2008 - STANDARD EDITION OR ABOVE
Editing the Data Retention SQL Script
The Data Retention SQL Script is responsible for deleting historical log data from the Lumension Endpoint Security Database. Because every organization has different data retention policies, it is important to make sure the attached script is properly configured for your policies. As such, the below steps should be performed before continuing to the next step. Failure to do so could result in the unintentional deletion of historical log data from the Lumension Endpoint Security database.
-
If you have not already done so, download the "del_by_date_44.sql" file at the bottom of this article.
-
Open the "del_by_date_44.sql" with Notepad.
-
Locate the line that says "SET @inCutOffTime = DATEADD(DAY, -7, GETUTCDATE())". This line in the script controls how old the historical log data must be in order to be deleted by the script. By default, the above line is set to delete any data older than 7 days. Based on your organization's data retention policies, you will need to update this line to fit your organization's needs. For example, if you needed to save 60 days worth of historical log data, you would change the -7 to -60.
-
Save your change to "del_by_date_44.sql" and close Notepad.
Once you have updated the script to correctly reflect your organization's data retention policies, you can now create the "job" in SQL to automate it.
Creating the SQL Job
SQL 2005/2008 Standard Edition (and higher) allow the creation of Jobs to automate the various tasks a database administrator may need to perform as part of their daily duties. Now that the Data Retention SQL Script has been edited to fit your organization's needs, it must be put into a Job so that it can be scheduled as part of the automated maintenance process.
-
Connect to the SQL Server using Microsoft SQL Server Management Studio (SSMS).
-
In the Object Explorer pane, expand the listing for the server name as well as the SQL Server Agent listing below it.
-
Below the SQL Server Agent listing, right-click Jobs and choose New Job.
-
On the General page, enter "Database Cleanup Script" in the Name box.
-
Clear the Enabled check box to prevent the job from running immediately following its creation.
-
Open the Steps page by clicking Steps on the left-hand side.
-
Click the New button to create a new Step.
-
Enter "Cleanup Script" in the Step name box.
-
Select your Lumension Endpoint Security Database from the Database pull-down (The database name is sx by default).
-
To the right of the Command box, click the Open button and select the previously downloaded "del_by_date_44.sql" file.
-
Verify that the contents of the "del_by_date_44.sql" file are in the Command box. Assuming they are, OK to close the window and complete the creation of the Step.
-
(Important) Do not set a schedule for this job using the Schedules page.
-
(Optional) If you wish to set up notifications for when the job completes or fails, you can do so on the Notifications page. Please see this MSDN article for information on utilizing this page. Additionally, if you have not already done so, you may need to create an Operator within SQL for each person you want to receive notifications. For more information, please see this MSDN article.
-
Click OK to close the window and complete the creation of the job.
Creating the SQL Maintenance Plan
Using a SQL Maintenance Plan enables you to easily automate the entire database maintenance process. You can use a Maintenance Plan to backup the database, delete the old historical log data, rebuild the various table indexes and verify the database integrity afterwards. As the Maintenance Plan will include setting a database backup schedule, it is recommended that you check with your SQL Database Administrator or management before continuing. At minimum, Lumension recommends that you run weekly differential backups and monthly full backups. Most organization's have their own requirements for backing up SQL databases, so please confirm your organization's policies beforehand.
-
Connect to the SQL Server using Microsoft SQL Server Management Studio (SSMS).
-
In the Object Explorer pane, expand the listing for the server name as well as the Management listing below it.
-
Below the Management listing, right-click Maintenance Plans and choose Maintenance Plan Wizard.
-
If the wizard does not start on the Select Plan Properties window, click Next.
-
Enter a descriptive name for the Maintenance Plan, such as "LES Database Maintenance - Differential".
-
Click Next.
-
Put a check next to Check Database Integrity, Rebuild Index, Execute SQL Server Agent Job, and Back Up Database (Differential). Click Next aftewards.
-
Use the Move Up... and Move Down... buttons to rearrange the list in the following order, clicking Next afterwards:
-
Back Up Database (Differential)
-
Execute SQL Server Agent Job
-
Rebuild Index
-
Check Database Integrity
-
Select your Lumension Endpoint Security Database from the Database(s) pull-down (The database name is sx by default) and click Next.
-
Put a check next to the job you previously created in the Creating the SQL Job section of this KB and click Next.
-
On both the Define Rebuild Index Task and Define Database Check Integrity Task windows, select your Lumension Endpoint Security Database from the Database(s) pull-down (The database name is sx by default) and click Next.
-
Click the Change... button to set the schedule for this Maintenance Plan. Whatever you set here should be set with a differential backup in mind. If you are following Lumension's recommendation of weekly differential backups, your schedule should reflect that. Click OK when finished.
-
Click Next on the Select Report Options window.
-
Click Finish.
-
Create a second Maintenance Plan for just the Back Up Database (Full) task. When scheduling this task, you should schedule it for a different time than that of the first Maintenance Plan and it should be scheduled with a full backup in mind. This will prevent both from running at the same time. If you are following Lumension's recommendation of monthly full backups, your schedule should reflect that.
Note: For more information on using the Maintenance Plan Wizard and explanations for each of the choices, please see this article.
SQL 2005/2008 EXPRESS
Note: Express editions of SQL do not include the SQL Server Agent, which is needed to schedule jobs from within SQL. As such, the below steps make use of the Windows Scheduler and must be performed on the machine where SQL is installed. Should you run into issues using the Windows Scheduler, it is recommended that you contact Microsoft for assistance.
Note: Due to the fact that Express editions of SQL do not include the SQL Server Agent, please note that the below steps include the use of one or more 3rd party utilities. As Lumension did not develop these utilities, we are limited to providing best effort support and may need to refer you to the developer of the application should you need further assistance with it.
Note: The method below does not provide a mechanism to provide notifications should the script fail to run successfully. Should you need this functionality, it is recommended that you upgrade SQL to Standard edition or higher.
Editing the Data Retention SQL Script
The Data Retention SQL Script is responsible for deleting historical log data from the Lumension Endpoint Security Database. Because every organization has different data retention policies, it is important to make sure the attached script is properly configured for your policies. As such, the below steps should be performed before continuing to the next step. Failure to do so could result in the unintentional deletion of historical log data from the Lumension Endpoint Security database.
Note: The below steps should be performed before continuing. Failure to do so could result in the unintentional deletion of historical log data from the Lumension Endpoint Security database.
-
If you have not already done so, download the "del_by_date_44.sql" file at the bottom of this article.
-
Open the "del_by_date_44.sql" with Notepad.
-
Locate a line that says "SET @inCutOffTime = DATEADD(DAY, -7, GETUTCDATE())". This line in the script controls how old the historical log data must be in order to be deleted by the script. By default, the above line is set to delete any data older than 7 days. Based on your organization's data retention policies, you will need to update this line to fit your organization's needs. For example, if you needed to save 60 days worth of historical log data, you would change the -7 to -60.
-
Save your change to "del_by_date_44.sql" and close Notepad.
Creating the Batch File
As stated previously, the Windows Scheduler must be used when using an Express edition of SQL. Because of the limitations of the Windows Scheduler, the easiest way to schedule the maintenance task is by putting everything that needs to run into a single batch file. Once you have updated the script to correctly reflect your organization's data retention policies, you can now create the batch file needed to automate the entire maintenance process. When finished, there will be two batch files. The first one will perform a differential backup, cleanup the old historical log data, then re-index the database and verify it's integrity. The second batch file will just perform the full backup.
-
Download the ExpressMaint tool from CodePlex for your version of SQL and extract it locally to the SQL Server. ExpressMaint is a command-line utility and can be used in conjunction with the Windows Scheduler to run various maintenance tasks on the SQL Server.
-
Create two batch files that contain the necessary command-line options for both your differential and full backups. Each one must be in it's own batch file. Review this article to determine what command-line options you need in your environment. Examples of differential and full backup commands can be seen below, but will require that you replace the <server> with the name of the server and <database> with the name of the Endpoint Security database (default is sx):
-
Full backup - "Expressmaint -S <server> -D <database> -T DB -R C:\Expressmaint -RU WEEKS -RV 1 -B C:\Expressmaint -BU WEEKS -BV 1 -V -C"
-
Differential backup - "Expressmaint -S <server> -D <database> -T DIF -R C:\Expressmaint -RU WEEKS -RV 1 -B C:\Expressmaint -BU WEEKS -BV 1 -V -C"
-
Save the batch files locally, making sure they are in the same directory as the previously downloaded "del_by_date_44.sql" file.
-
Reopen the batch file for your differential backup and add the below command, replacing <server> with the name of the server and <database> with the name of the Lumension Endpoint Security database (default is sx):
-
Add the below lines to the differential backup batch file to allow it to re-index the database and verify it's integrity:
-
Save your changes to the batch file and close it.
-
Using the Windows Scheduler, create a reoccurring scheduled task for each of the batch files created above. When setting the schedule for these scripts, make sure that you schedule them with your organization's policies in mind. For example, if your organization requires weekly differential backups and monthly full backups, the differential backup batch file should be scheduled to run weekly and the full backup batch script should be scheduled to run monthly. Additionally, it's important to make sure to schedule the full backup for a time when the differential is not running in order to avoid both trying to run at the same time. Should you need assistance creating the scheduled tasks, please see Microsoft KB 308569.
-
(Important) At minimum, it is recommended to run weekly differential backups and monthly full backups. Most organization's have their own requirements for backing up SQL databases, so it is recommended that you check with your SQL Database Administrator or management before setting your schedule up.
File Attachments:
File:
del_by_date_44.sql