Free Essay

Siteprotector Database Tuning Guide

In: Computers and Technology

Submitted By rarenomad
Words 5367
Pages 22
Database Tuning and Administration Guide for SP6.1
February 22, 2007

IBM Internet Security Systems, Inc. 6303 Barfield Road Atlanta, Georgia 30328-4233 United States (404) 236-2600 http://www.iss.net
© IBM Internet Security Systems, Inc. 1994-2006. All rights reserved worldwide. Customers may make reasonable numbers of copies of this publication for internal use only. This publication may not otherwise be copied or reproduced, in whole or in part, by any other person or entity without the express prior written consent of Internet Security Systems, Inc. Patent pending. Internet Security Systems, System Scanner, Wireless Scanner, SiteProtector, Proventia, Proventia Web Filter, Proventia Mail Filter, Proventia Filter Reporter ADDME, AlertCon, ActiveAlert, FireCell, FlexCheck, Secure Steps, SecurePartner, SecureU, and X-Press Update are trademarks and service marks, and the Internet Security Systems logo, X-Force, SAFEsuite, Internet Scanner, Database Scanner, Online Scanner, and RealSecure registered trademarks, of Internet Security Systems, Inc. Network ICE, the Network ICE logo, and ICEpac are trademarks, BlackICE a licensed trademark, and ICEcap a registered trademark, of Network ICE Corporation, a wholly owned subsidiary of Internet Security Systems, Inc. SilentRunner is a registered trademark of Raytheon Company. Acrobat and Adobe are registered trademarks of Adobe Systems Incorporated. Certicom is a trademark and Security Builder is a registered trademark of Certicom Corp. Check Point, FireWall-1, OPSEC, Provider-1, and VPN-1 are registered trademarks of Check Point Software Technologies Ltd. or its affiliates. Cisco and Cisco IOS are registered trademarks of Cisco Systems, Inc. HP-UX and OpenView are registered trademarks of Hewlett-Packard Company. IBM and AIX are registered trademarks of IBM Corporation. Intel and Pentium are registered trademarks of Intel. Lucent is a trademark of Lucent Technologies, Inc. ActiveX, Microsoft, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation. Net8, Oracle, Oracle8, SQL*Loader, and SQL*Plus are trademarks or registered trademarks of Oracle Corporation. Seagate Crystal Reports, Seagate Info, Seagate, Seagate Software, and the Seagate logo are trademarks or registered trademarks of Seagate Software Holdings, Inc. and/or Seagate Technology, Inc. Secure Shell and SSH are trademarks or registered trademarks of SSH Communications Security. iplanet, Sun, Sun Microsystems, the Sun Logo, Netra, SHIELD, Solaris, SPARC, and UltraSPARC are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. in the United States and other countries. Adaptive Server, SQL, SQL Server, and Sybase are trademarks of Sybase, Inc., its affiliates and licensers. Tivoli is a registered trademark of Tivoli Systems Inc. UNIX is a registered trademark in the United States and other countries, licensed exclusively through X/Open Company, Ltd. All other trademarks are the property of their respective owners and are used here in an editorial context without intent of infringement. Specifications are subject to change without notice. InstallShield® is a registered trademark and service mark of InstallShield Software Corporation in the United States and/or other countries. Disclaimer: The information contained in this document may change without notice, and may have been altered or changed if you have received it from a source other than ISS or the X-Force. Use of this information constitutes acceptance for use in an “AS IS” condition, without warranties of any kind, and any use of this information is at the user’s own risk. ISS and the X-Force disclaim all warranties, either expressed or implied, including the warranties of merchantability and fitness for a particular purpose. In no event shall ISS or the X-Force be liable for any damages whatsoever, including direct, indirect, incidental, consequential or special damages, arising from the use or dissemination hereof, even if ISS or the X-Force has been advised of the possibility of such damages. Some states do not allow the exclusion or limitation of liability for consequential or incidental damages, so the foregoing limitation may not apply. Reference herein to any specific commercial products, process, or service by trade name, trademark, manufacturer, or otherwise, does not necessarily constitute or imply its endorsement, recommendation, or favoring by Internet Security Systems, Inc. The views and opinions of authors expressed herein do not necessarily state or reflect those of Internet Security Systems, Inc., and shall not be used for advertising or product endorsement purposes. Links and addresses to Internet resources are inspected thoroughly prior to release, but the ever-changing nature of the Internet prevents Internet Security Systems from guaranteeing the content or existence of the resource. When possible, the reference contains alternate sites or keywords that could be used to acquire the information by other methods. If you find a broken or inappropriate link, please send an email with the topic name, link, and its behavior to support@iss.net. May 11, 2004

SiteProtector Database Tuning and Administration Guide

Page 3 of 18

INTRODUCTION ......................................................................................................................... 4 ADMINISTRATION..................................................................................................................... 4 ADMINISTRATION BEST PRACTICES ........................................................................................... 4 CONFIGURATION BEST PRACTICES ............................................................................................ 5 TUNING ......................................................................................................................................... 6 DATABASE MAINTENANCE......................................................................................................... 6 General Tab .......................................................................................................................... 6 Time Tab................................................................................................................................ 6 Purge tab ............................................................................................................................... 7 Advanced Purge tab .............................................................................................................. 8 Daily Backup tab................................................................................................................... 8 INDEX FRAGMENTATION ............................................................................................................ 9 SITE FILTERS .............................................................................................................................. 9 DATA RETENTION ....................................................................................................................... 9 ADDITIONAL SITEPROTECTOR TUNING .................................................................................... 11 SQL SERVER............................................................................................................................. 11 ADVANCED TUNING ................................................................................................................. 12 TROUBLESHOOTING TIPS.................................................................................................... 12 CONSOLE GENERATED QUERIES .............................................................................................. 12 DATABASE DETAILS ................................................................................................................. 13 LOG FILE DETAILS .................................................................................................................... 13 Where are the log files? ...................................................................................................... 13 Can it be deleted?................................................................................................................ 13 How do you change the logging level? ............................................................................... 14 What is “noise”?................................................................................................................. 14 What is significant?............................................................................................................. 14 DATABASE RECOVERY TOOL ................................................................................................... 15 DATABASE BACKUP TOOL ....................................................................................................... 15 SERVER PERFORMANCE OPTIMIZATION TOOL ......................................................................... 15 SPECIAL PURPOSE PROCEDURES .............................................................................................. 15 REFERENCES ............................................................................................................................ 16 SCALABILITY GUIDE................................................................................................................. 16 SITEPROTECTOR INSTALLATION GUIDE ................................................................................... 16 SYSTEM REQUIREMENTS .......................................................................................................... 16 SITEPROTECTOR TECHNICAL REFERENCE GUIDE .................................................................... 16 BEST PRACTICES GUIDE ........................................................................................................... 16 KNOWLEDGE BASE ................................................................................................................... 17 DIAGNOSTICS ........................................................................................................................... 18 DBSERVERINFO........................................................................................................................ 18 DBServerInfo Users Guide.................................................................................................. 18 DBSENSORANALYSIS ............................................................................................................... 18 DOCUMENT HISTORY............................................................................................................ 18

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 4 of 18

Introduction
This document was developed from information contained in the SiteProtector documents listed in the references section. This was done to simplify finding information on database tuning and administration, by presenting the key information together here. This guide is for advanced users of SiteProtector that want to maximize the stability of their SiteProtector installations and get the best performance possible. The information is specific the SiteProtector Service Pack 6.1. For more details, please go to the documents listed in the references section.

Administration
Administration Best Practices
If your site is now updated to SiteProtector SP6.1 or later, we recommend that you upgrade your SQLServer to the 2005 version. There are many reasons, including faster transaction processing, improved response times on queries and higher availability. For the Microsoft whitepaper, click here: http://www.microsoft.com/sql/techinfo/whitepapers/why-upgrade.mspx FOR SQL2000: Review and follow the practices presented in the SQLServer 2000 Operations Guide: System Administration chapter, currently found at the following location: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops4.mspx Also, review the monitoring and control chapter, at the following location: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops5.mspx Topics include:

• • •

Performance monitoring Hardware monitoring Disk Space usage monitoring

FOR SQL 2005: Review Books on line. Look in particular in the SQL Server Database Engine> Administering the Database Engine; in the Backing Up and Restoring Database in SQL Server, Automating Administrative Tasks and Monitoring and Tuning for Performance sections.

FOR BOTH: All the information is useful and should be reviewed, but pay particular attention to the following areas: • Make sure you have a backup strategy and a backup validation practice for: RealSecureDB, master, and msdb. • Understand your choice in recovery model, either Simple, Full, or Bulk-Logged. • DBCC: understand and use. Possibly run CHECKDB regularly when system usage is low. • Define a schedule of daily, weekly and monthly tasks. You may wish to use database maintenance plans, just be careful not to reindex while SiteProtector services are running (Do not use online reindex if you are running SQL2000, it is OK if you are running SQL2005).

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 5 of 18

Finally, plan for off-line index rebuilds, please refer to the knowledge base article 2721: How Can I

rebuild my indexes? https://iss.custhelp.com/cgi-bin/iss.cfg/php/enduser/std_adp.php?p_faqid=2721 Note: indexes can be rebuilt while the application is on-line if you are running on SQL 2005. If you are running SQL 2005, it may be best to schedule this maintenance to run every week.

Configuration Best Practices Shorting the SQLServer’s memory allocation will cause poor performance. Check the scalability Guide, reviewing the performance note for the size of your implementation to ensure you have the proper amount of memory, then also the ‘awe enabled’ setting, and the ‘max server memory’ setting. If the memory usage specified in the performance section of the main DBServerInfo report is at (or almost at) the maximum server setting, more memory may be needed. Priority boost must be zero (0) Check the database details dialog to see if these settings are as recommended: • defrag frequency—daily • purge frequency—daily or weekly. • auto shrink—off; unless you are told otherwise by ISS. Database options turned ON for the SiteProtector site database (RealSecureDB) select into/bulkcopy Required trunc. log on chkpt* Recommended if using the SIMPLE recovery model torn page detection Highly recommended db chaining Required auto create statistics Required auto update statistics Required Database options turned OFF for the SiteProtector site database (RealSecureDB) Autoshrink Autoclose dbo use only read only single user If data is not loading, make sure the ‘SQLSERVERAGENT’ service is running. If you plan to have/retain a large data set, set the initial database size as large as possible to prevent disk fragmentation. When the drives are setup with a SCSI controller, ensure its set for 64k clusters, as well as the NTFS formatting is done w/ 64k clusters.

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 6 of 18

Tuning
Database Maintenance This section briefly describes the database maintenance settings and provides recommendations for database tuning. General Tab Defragment—set to Daily. Since spikes of data may enter the system at any time, it is best to keep the database as defragmented as possible. The defragment feature is safe to run while the system is in use. Only indexes that need it will be defragmented. This feature does not re-index your database, for more information on re-indexing, please see the Index Fragmentation section. Maximum Log Entry Age—keep default. These tables do not contain security or configuration data, only logging that is used by DBServerInfo for diagnostics purposes.

Time Tab Perform these tasks in a maintenance window of time where you will not interrupt other activities, such as backups, large scans, or other jobs that may run on the server. Perform these tasks during off-peak usage when security analysis activities are low. These times are used by defragment, purge, and backup features.

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 7 of 18

Purge tab Emergency purge—ISS cannot make a general recommendation for emergency purges. Most customers use this feature because they would rather lose older data than stop seeing new data. The database size threshold and purge margin should be left at their default values. If you don't want to turn this option on, and your database reaches 95% full, the system will shut down the Event Collectors and Agent Managers until more space is made available by either purging data or increasing the space available to the database. Either way, monitor your database size to prevent it from filling up or from invoking the emergency purge. Purge frequency—daily. By purging daily, the amount of data to be purge is reduced, causing the jobs to run in a shorter period of time and causing less fragmentation. Maximum Item age—set these values according to your data retention policy. See the data retention section later on in this topic for more information on each of the available settings.

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 8 of 18

Advanced Purge tab These settings allow you to take more granular control over the items configured to purge in the purge tab. If your data retention policy is such that using these settings is helpful, then override the data types you wish. The impact this has on purging is that the purge must run one for each different days to keep number appearing on this tab. So if you want to set 4 data types to 30 days and 6 data types to 45 days, the purge will run 2 times. If you set 10 data types to 10 unique days to keep values, the purge will run 10 times. So use this feature with caution.

Daily Backup tab Perform automatic daily backup—on (unless you have your own backup jobs). If your recovery model is 'Simple', a full backup will de done daily. If your recovery model is something other than 'Simple', then the system performs a daily incremental and weekly full backup.

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 9 of 18

Index Fragmentation The main report in DBServerInfo shows how fragmented the indices are. Don’t worry about database objects if the RowsInIndex is less than 50,000. • ScanDensity—the closer to 100 the better. 80 or more is recommended. • LogicalFrag—the closer to 0 the better. 20 or less is recommended. Symptoms may include slow data loading and/or post processing (seen in the SQL Agent Job History section of the DBServerInfo_sql_agent.txt report from the DBServerInfo diagnostic tool). Use database maintenance to keep your indexes defragmented. If you want to rebuild your indexes, please refer to knowledge base article 2721: How Can I rebuild my indexes? https://iss.custhelp.com/cgi-bin/iss.cfg/php/enduser/popup_adp.php?p_faqid=2721 Important Note: If you choose to rebuild your indexes, and are on SQL Server 2000, this task must be done while all of the SiteProtector services are shut down. Site Filters If too many Observances are tagged by incidents and exceptions, you will experience poor system performance. The number of rows in ObservanceSiteFilters should not be larger than 20% of the rows in Observances. The DBServerInfo_SiteFilters report will provide more details about which filters are problematic. Look for incidents and exceptions that have large numbers of records in the ObservanceSiteFilters table (this information is in the “How Many ObsSF” column) in the “Show Top 50 Site Filters Applied to Observances” report. You will be able to see much of the details in the “Show Details of all Incidents and Exceptions by activity” report. These filters may have no end date, or too wide of an IP range. The recommended best practice is to use policy tuning to prevent events from showing in the console. It would be a good practice to check this report on a regular basis and use the top 25 events report in DBServerInfo to identify events that need policy tuning attention.

Data Retention The amount of data SQLServer is storing and processing plays a very large role in database performance. To get a result for a request it receives, it has to decide the best way to get the data, then read the data from tables to provide the result. These operations involve using CPU, memory, and disk access. Only storing the data that you will use will help to improve performance so that SQLServer does not have to process information you don’t need. Consider the following information on each “Maximum Item Age,” located on the Purge tab in the database maintenance window, to help you define a data retention policy and configure your settings: Let’s clarify some terminology before we jump into the data retention settings. What is Agent Data? Agent Data contains the actual events sent from the agents. These events also contain additional parameters or details which are stored in additional tables, which join back to Agent Data. Every Agent Data purge will also purge the additional details associated with the

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 10 of 18

events getting removed. The Agent Data tables can account for up to 65% of your total database storage needs. This is why it is prudent to keep less Agent Data than Observances. What is Observances? Observances is a table that contains summarized event information, created during the data loading process, and is used for all non-detail event analysis views in the console. Most customers would want to keep this information longer than the actual events it summarizes. Setting Audit Description Information is stored about activities performed by users of the system. This information is available from the Audit Report found on the reporting tab. The recommendation is to keep at the default unless you need to see more data on an audit report. By using the incidents feature, you can designate events to be “more significant” than other events. If this is used sparingly, you can configure the system to keep the details and observances (see below) longer than other events. The system calculates information to be used by the Summary portals in the console and stores it in several tables which are purged by the Metrics configuration. Use this setting to control how much information you want to be available in the summary portals. These tables are highly summarized and use very little disk storage. By using the clear event feature, you can cause events and observances you don’t want to consider again to no longer appear in the analysis console. The cleared observances can also be removed from the database on a different schedule, designed to be removed sooner, than non-cleared observances. Similar to Cleared Observances, the cleared agent data information to be removed on a different schedule than the non-cleared agent data. Tickets that have been resolved. By using the exceptions feature, you can designate events to be “less significant” than other events. If this is used sparingly, you can configure the system to keep the agent data and observances (see below) less time than other events. The system uses jobs to perform actions to sensors such as apply policy, updates and start scans. These actions are recorded as jobs, and stored in the database. This information is seen in the command jobs pane from the sensor tab. Contains the majority of the information seen in the console sensor analysis tab. If cleared observances are in the time range to be purged, they will be removed. Agent data contains the actual events sent from the agents. If cleared agent data events are in the time range to be purged, they will be removed Any IP address that comes into the system from an event as a source, target, or sensor, is added to the asset table set. The asset table also contains all items found on the Asset tab. This purge will remove hosts if it’s “added date” is beyond the age specified, are not Default 14

Incidents

90

Metrics

180

Cleared Observances

14

Cleared agent data Resolved tickets Exceptions

14

30 14

Job History

7

Observances

90

Agent data

30

Unused assets

30

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 11 of 18

Mail data

a member of a group, have no sensors registered to it, and no events are associated with it in the Observances table. Purge Mail data from the Proventia Mail products

90

Note: Although you can, there should be no reason to keep Agent data longer than Observances. Without associated Observances, Agent data records are effectively 'lost', i.e. not accessible from the SiteProtector Console. In addition, since Observances are a summarization for the real events, they take up much less space in the database. Your selected Observances age should be greater than or equal to your selected Agent data age.

Additional SiteProtector Tuning Each installation is different, consider these issues: • Do you use desktop agents? Can you reduce the heartbeat interval? • Tune policies rather than build exceptions. • Increase console refresh interval (if possible) (or turn off auto refresh) • Resolve agents with clock not set to the correct time. SQL Server Check these items for SQLServer tuning: • Set the database growth interval to 256MB, 384MB, or 512MB for the primary data file. • Set the database growth interval to 128MB for the transaction log file. • Memory: – SQL 2000 • Turn on AWE and use the /3GB and /USERVA switch if the computer SQL Server 2000 is installed on has 3GB or more of RAM and has SQL Server enterprise edition. For more information: http://support.microsoft.com/kb/316739 • Upgrade to enterprise edition if using standard edition. Make sure you can take advantage of all the memory possible from your computer. • For more information on memory usage, refer to this article from Microsoft at the following location: http://support.microsoft.com/?id=274750 • If you are running SQL 200 SP4, make sure you have the necessary hot fix to avoid a bug with AWE. https://iss.custhelp.com/cgibin/iss.cfg/php/enduser/popup_adp.php?p_faqid=3125 – SQL 2005 • The SQL Server 2005 Standard and Enterprise editions allows for use of all the OS available memory. • Make sure these database options are turned on: – auto create statistics – auto update statistics • The best configuration is to separate the following data file(s) onto different physical disk and controllers if possible: – RealSecureDB data file(s) – RealSecureDB transaction log file(s) – Tempdb – OS files

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 12 of 18

Advanced Tuning For high volume or high storage databases, this list is very important: • To make sure your hardware is adequate, please refer to knowledge base article 2804: SQL Server Hardware Performance Checklist https://iss.custhelp.com/cgi-bin/iss.cfg/php/enduser/std_adp.php?p_faqid=2804 •

For high event volumes or very large databases, consider: – Separating data and log files onto separate physical disks (if possible) – Daily defragmenting – Daily purging – Disabling disk counters • DISKPERF –N (from command prompt)

Troubleshooting Tips
Console Generated Queries This query will get the last select statement generated by the SQL builder: select SQLStmt from analysislog where starttime = (select max(starttime) from analysislog)

Copy and paste the output into the top pane of query analyzer, and then, debug, analyze and test. Notes: • The where clause can be adjusted to get different results. • Make sure your query analyzer is properly configured to view the large output of the above query: Click ‘Tools’, then ‘Options’, then the results tab, and set the ‘Maximum characters per column’ to 8000.

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 13 of 18

Database Details On the agent tab, right click on the “SP Database” entry and then select “properties”. A new tab appears. Click on the Agent Details icon. Information is shown which may quickly identify a problem.

Log File Details Where are the log files? In addition to the SQLServer log files, SiteProtector has three log tables (all of these tables are used by the DBServerInfo diagnostic tool available from ISS Technical Support): • MessageLog—the database table used to log problems and activities related to the SiteProtector system. This is the main log that is affected by the logging level found under the database properties in the Agent Details section. • MaintenanceLog—the SiteProtector database auto maintenance features log their information here. • AnalysisLog—All queries built by the analysis console is logged here with some additional information.

Can it be deleted? • MessageLog—this can be truncated at any time. Database auto maintenance purges logs according to the appropriate maximum log entry age setting.

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 14 of 18



• •

SQLServer Log—you can cycle the error log by restarting SQLServer, or without restarting SQL Server by running the sp_cycle_errorlog stored procedure. By default, the SQL Server error logs are kept in the SQL Server's Log subfolder. By default, there are seven SQL Server error logs; Errorlog and Errorlog.1 through Errorlog.6. The name of the current, most recent log is Errorlog with no extension. The log is re-created every time that you restart SQL Server. When the Errorlog file is recreated, the previous log is renamed to Errorlog.1, and the next previous log (Errorlog.1) is renamed to Errorlog.2, and so on. Errorlog.6 is deleted. This actually occurs in reverse order, but the result is the same. SQL keeps several versions of the log file, how many you keep can be configured using this registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs. By default, this key is absent. The value is of the REG_DWORD type. Modify the value to the number of logs that you want to maintain. MaintenanceLog—Database auto maintenance will purge according to the appropriate maximum log entry age setting. Don’t delete rows that are not older than 24 hours, those entries are needed by database maintenance to maintain its schedule. AnalysisLog—this can be truncated at any time. Database auto maintenance will purge according to the appropriate maximum log entry age setting.

How do you change the logging level? • MessageLog—it is changeable, but not recommended. The logging level is found by right clicking on the ‘SP Database’ entry on the asset tab, then selecting ‘Details’. Lowering removes entries used by data loading, and raising it will not add any additional information to the log. • SQLServer Log—you can add additional information to the log by turning on certain DBCC TRACEON flags. See information for all of the trace flags at the following location: http://msdn2.microsoft.com/en-us/library/aa260630(SQL.80).aspx for more information. Turning on trace flags 3604 and 1204 works well for deadlock tracing. • MaintenanceLog—Not configurable. • AnalysisLog—Not configurable. What is “noise”? • MessageLog—Using the DBServerInfo utility will reduce most of the noise. Adding the clause “where severityid = 1” will filter out the non-errors. • SQLServer Log—usually no noise. • MaintenanceLog—Most of the entries could be the “regular maintenance”, which is the every 10 minute status/health checking. • AnalysisLog—Entries with short durations are usually of no interest. What is significant? • MessageLog—any errors could be an indication of a serious problem. • SQLServer Log—any errors could be an indication of a serious problem. • MaintenanceLog—the impact and duration of purges and other maintenance tasks can be tracked by finding the beginning of maintenance: select * from MessageLog where message like '%maintenance run beginning.', then scanning the entries until the “Maintenance complete.” entry. • AnalysisLog—Entries with long durations may indicate fragmentation, index issues, load greater than hardware can handle, or just a period of high activity.

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 15 of 18

Database Recovery Tool If you have a corrupt database, you can use a tool like MSSQLRecovery before reinstalling your site. This tool can be found at the following location: http://www.officerecovery.com/mssql/index.htm If you have a database with a deleted log file, information on recovering can be found at this location: http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html Database Backup Tool If you require a faster backup solution with compression, you can use a third party product from a Microsoft Gold certified partner: SQLLiteSpeed at the following location: http://www.quest.com/litespeed_for_sql_server/ Server Performance Optimization Tool If you need a tool to assist in performance monitoring your server, consider: SQLSentry at http://www.sqlsentry.net or SQLcheck at http://www.idera.com/Products/SQLcheck/

Special Purpose Procedures Use the following procedures for the purpose listed: • iss_db_load_stats—shows the performance of data loading. Don’t run too often, this procedure can impact performance. • iss_StopLoading—Stops the data loading SQL Agent job • iss_StartLoading—Starts the data loading SQL Agent job • iss_Loading_EC_StartOrStopECs @StopECs = 0, @StartECs = 1 – Use this procedure to start and stop the event collectors, 0 means don’t perform that action, 1 means perform that action. You may wish to discuss with support since there are some known issues with this procedure. • iss_TruncateTables—truncates all event data from the database, leaving the configuration information. Please contact ISS Technical Support prior to running this utility.

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 16 of 18

References
For more information, refer to the following documents: Scalability Guide The Scalability Guide is located at the following location: http://documents.iss.net/literature/SiteProtector/SPScalabilityGuide20SP6.pdf SiteProtector Installation Guide The SiteProtector Installation Guide is located at the following location: http://documents.iss.net/literature/SiteProtector/SPInstallationGuide20SP61.pdf System Requirements The System Requirements are located at the following location: http://documents.iss.net/literature/SiteProtector/SPSystemRequirements20SP61.pdf

SiteProtector Technical Reference Guide The SiteProtector Technical Reference Guide is located at the following location: http://documents.iss.net/literature/SiteProtector/SPTechnicalReferenceGuide20SP61.pdf Of special note: • Appendix A: Database Schema. Best Practices Guide The Best Practices Guide is located at the following location: http://documents.iss.net/literature/SiteProtector/SPBestPracticesGuide20SP6.pdf

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 17 of 18

Knowledge Base The following articles are located at https://iss.custhelp.com or http://iss.custhelp.com/cgibin/iss/login : • 2804 - SQL Server Hardware Performance Checklist • 2759 - How do I move my RealSecureDB database files to another drive? • 2743 - How do I maintain my SQL Server for SiteProtector? • 2727 - How do I manually backup my RealSecureDB using MSDE or SQL Query Analyzer? • 2722 - Can database auto maintenance run more than once per day? • 3447 - What do I need to do to move the SQL server to a dedicated server? • 2656 - Why am I receiving data in my SensordataRejected table on the SiteProtector Database? • 2650 - How do I Upgrade SQL Server 2000 Desktop Engine (MSDE) to SQL Server 2000? • 2464 - Database Security in SiteProtector - Part 7 of 7 - Removing Public Role Permissions • 2463 - Database Security in SiteProtector - Part 6 of 7 - Removing Guest Users • 2462 - Database Security in SiteProtector - Part 5 of 7 - Removing Default Databases • 2461 - Database Security in SiteProtector - Part 4 of 7 - Removing xp_cmdshell • 2460 - Database Security in SiteProtector - Part 3 of 7 - Understanding DB Chaining • 2459 - Database Security in SiteProtector - Part 2 of 7 - Setting the Audit Level • 2458 - Database Security in SiteProtector - Part 1 of 7 - SQL Server Authentication • 2456 - What is this sensor’s grouping information and events summary in SiteProtector? • 2352 - What extended procedures does SiteProtector use? • 2345 - How do I maintain my SiteProtector Database manually?

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

SiteProtector Database Tuning and Administration Guide

Page 18 of 18

Diagnostics
DBServerInfo The latest DBServerInfo version is located at the following location: ftp://ftp.iss.net/prv/perm/support/DBServerInfo/DBServerInfo_RSSP.exe DBServerInfo Users Guide Contact ISS Support for this document. DBSensorAnalysis The DBSensorAnalysis utility takes an IP Address for a host containing one or more sensors, then displays the following: • Information about the host • Information about the sensor(s) on that host • Information about the grouping structure • Summary of events detected from this sensor(s) • Summary of events targeting this sensor(s) • Summary of events sourced from this sensor(s) Contact ISS Support for this utility.

Document History
Version 1.0 1.1 1.2 1.3 1.4 1.5 Date Feb. 7, 2005 Feb. 9, 2005 Mar 1, 2005 Mar 18, 2005 Mar 31, 2005 Feb 22, 2007 Notes Original version for SiteProtector 2.0 SP5 Revisions from internal reviews. Revisions to include key content and better arrangement. Revisions from review from technical publications team. Improved data retention section and more references for awe. Revised for SiteProtector SP6.1 and SQL Server 2005

IBM Internet Security Systems – Proprietary and Confidential

2/22/2007

Similar Documents