ENTERPRISE SYSTEM HEALTH CHECK PRESENTED BY ERIC GERRITY

1. ENTERPRISE SYSTEM HEALTH CHECK PRESENTED BY ERIC GERRITY

2. Eric Gerrity,Sr. Technical ConsultantTechnical Services Grouperic.gerrity@thomsonreuters.com

3. Enterprise System Health Check -What to look for DB SERVER: SQL Server logs: A veritable wealth of information within the SQL logs. These show backups, object creation/use; job information & and even compiling errors Windows logs: just like driving, be careful with yellow & red!

4. Enterprise System Health Check -What to look for (con’t) Application Server: Windows logs: just like driving, be careful with yellow & red! Elite logs (residing in /elite/work/logs): Each Elite-related service has it’s own log file for each db instance; all errors for any of the instances write to ‘errlog’ Scheduled Tasks: log file within Control Panel -> Scheduled Tasks (not in date order)

5. Enterprise System Health Check -What to look for (con’t) Webview Server: Windows logs: just like driving, be careful with yellow & red! Almost all Webview informational and error messages output to Windows Application logs; document these so that you can add them to your Support case.

6. Enterprise System Health Check – Learn and Practice… • To maintain system AVAILABILITY • To maintain system PERFORMANCE

7. Enterprise System Health Check –Requirements Gap-Analysis • Maintain compliance with requirements • Elite’s Product System Requirements (PSR) document • Hardware • OS Edition • SQL Server Edition • Your potential requirements • Fault-tolerance • Disaster-recovery

8. Enterprise System Health Check- Configuration Gap-Analysis • Maintain compliance with Elite’s installation model • Summarized in Elite’s Enterprise Administrator’s Guide Also see Elite’s • Product System Requirements (PSR) guide • Windows Server 2003/2008 Installation guide • SQL Server 2005/2008R2 Components Installation guide • *** DO NOT apply SP1 for Windows 2008R2 without opening a case with Support first! There is a patch for application servers if SP1 is desired ***

9. Enterprise System Health Check- SQL Server Memory • Minimum of 2 GB • Prefer lower of 10% database size or 8 GB • Set “Lock pages in memory” local security policy • Fix min. & max. SQL Server memory

10. Enterprise System Health Check- SQL Server Parallelism • Max degree of parallelism = half # physical cores • Disable Hyper-Threading • Explore optimizations with your networking vendor

11. Enterprise System Health Check- NetCPS • NetCPS 1.0 - Entering client mode. Press ^C to quit • Connecting to 191.161.1.112 port 4455... Connected! • ---> CPS    965428.00 KPS:   942.80   MPS: 0.92 • Avrg CPS    493292.00  KPS:   481.73  MPS: 0.47 • Peek CPS   1555187.38  KPS:  1518.74  MPS: 1.48 • Done. 104857600 Kb transferred in 212.57 seconds. server 1: netcps –s server2 : netcps <IP of server 1>

12. Enterprise System Health Check- SQL Server Agent Jobs

13. Enterprise System Health Check- Windows Scheduled Tasks

14. ENTERPRISE SYSTEM HEALTH CHECKLOG FILE MONITORING • Check log files daily • Windows • SQL Server • Application Automate as much as possible!

15. Enterprise System Health Check- Database Mail In SQL2005: Enable via Surface Area Configuration tool OR • exec sp_configure 'show advanced options', 1 • reconfigure • exec sp_configure 'Database Mail XPs', 1 • Reconfigure • SQL2008R2: • Available natively; • Define operator(s) & conditions

16. Enterprise System Health Check- Database Mail (cont’d) sendmail.bat set mailbody=%1 set mailbody=%mailbody:~1,-1% sqlcmd -S.\sql2008 -E -Q "declare @subject sysname; set @subject = 'Performance alert on ' + @@SERVERNAME; EXEC msdb.dbo.sp_send_dbmail @recipients=' <your_email_address>@company.com ',@subject = @subject , @body = '%mailbody%', @body_format = 'TEXT' ;“ Then configure Perfmon to run sendmail.bat for threshold alerts

17. Enterprise System Health Check- Performance Monitoring • Establish a baseline against which to periodically compare • User experience • Performance metrics • Database server • Application & WebView servers • Citrix servers • Other

18. Enterprise System Health Check- Database Server Disk Subsystem • Test integrity & performance • Microsoft SQLIOSim (replaces SQLIOStress) • Tests I/O path for problems that may corrupt data • Microsoft KB article 231619 • Microsoft SQLIO • Tests I/O capacity • Search Microsoft for documentation

19. Enterprise System Health Check- SQLIOSim RUN C:\SQLIOSim>sqliosim.com -dir c:\SQLIOSimTEST ID User Information Complete -- ---- ----------- -------- 1288 Main User Refreshed 366 times 3408 Display Monitor 9:18:12 280 Overall Test Progress Full Test Run #1 25% 1772 Checkpoint Sleeping 4460 LazyWriter Sleeping, 1141 modified 4620 LogWriter Sleeping, 4915 processed 5016 Random Access 0:460847, Reading page(s) 94% 4592 Random Access 0:123619, Reading page(s) 94% 4928 Bulk Update 0:392084, Reading page(s) 95% 3764 Bulk Update 0:349364, Reading page(s) 95% 3144 Page Audit 0:115776 95% Errors (0), warnings (13) reported to log file

20. Enterprise System Health Check- SQLIOSim Test Results • Consult log file & Windows Event log for details • Consult hardware manufacturer if errors • Capture Win32 API calls

21. Enterprise System Health Check- SQLIO Run • Edit set_proc_sock.txt to set number of physical cores and path of test file • Edit & run sqlio_1v1f(1x8).cmd once for each path to test • Creates time stamped log files at location of .cmd file

22. Enterprise System Health Check- SQLIO Test Results • RANDOM WRITE 64k TEST (1 volume, 1 file) • =================================== • . . . • CUMULATIVE DATA: • throughput metrics: • IOs/sec: 104.69 • MBs/sec: 6.54 • latency metrics: • Min_Latency(ms): 0 • Avg_Latency(ms): 75 • Max_Latency(ms): 639 • Compare results to Elite PSR document

23. Enterprise System Health Check- SQLIO Test Results vs. PSR I/O Guidelines – 15K RPM Drives

24. ENTERPRISE SYSTEM HEALTH CHECKPERFMON • Set up counters to log (Processor queue length; RAM; Logical disk counters; flavor to taste) • Use workstation to collect & analyze data • Sample no more than every 15 seconds • Analyze data

25. Enterprise System Health Check- Log Analysis Microsoft Performance Analysis of Logs (PAL) • Reads Performance Monitor logs • Analysis using role-specific thresholds • HTML-based reports • Requires Microsoft Log Parser • .NET Framework 2.0 • OWC

26. Enterprise System Health Check- SQL Profiler • Simultaneously collect Perfmon & SQL Profiler logs • Allows pinpointing of queries that correspond to exceeded thresholds

27. Enterprise System Health Check- Tips to Protect Performance • BIOS & driver updates • Windows & SQL Server updates • New software • Malware • Disk defragmentation

28. Enterprise System Health Check- Change Management • Authorizes changes • Prioritizes changes • Tests changes • Promotes changes

29. Enterprise System Health Check- Capacity Planning • Performance history • Number of concurrent users • Storage capacity and rate of consumption • New hardware requirements

30. Enterprise System Health Check- Backups and Recovery • Business owners define data retention and recovery requirements • IT selects tools and processes to meet those requirements • Elite defines what data to backup but not how or how often • IT should periodically test recovery

31. Enterprise System Health Check- Thanks for Attending!!!! DON’T FORGET!!! The new method to open cases, check status, etc. can be found here: http://customerportal.elite.com/ Please feel free to contact me via e-mail (eric.gerrity@thomsonreuters.com) or phone (913-422-4228) Please, Please, PLEASE ask questions!!!