SQL Sentry Quick Start 1 Cover Page QUICK START GUIDE ©2015 SQL Sentry. All Rights Reserved.
SQL Sentry Quick Start 2 2 Table of Contents 1. Cover Page 1 2. Table of Contents 2-3 3. Quick Start Guide 4 4. Important Concepts 5-6 5. Installation Recommendations 7-8 6. System Requirements 7. Installation and Setup Steps 9-11 12 7.1. Step 1: Install SQL Sentry 12-16 7.2. Step 2: The Setup Wizard 16-18 7.3. Step 3: Start Using the Client 18-19 8. Additional Tasks 20 8.1. Add Users and Groups 8.2. Monitor Additional Connections 20-21 8.3.
SQL Sentry Quick Start 3 10.3.1. Object Removal Script for Watched 2000 Servers 40-46 10.3.2. Object Removal Script for Watched 2005+ Servers 47-50 10.4. Watched Server Objects 50-51 10.5. Standard Vs Enterprise Editions 51-52 11. The SQL Sentry User Guide 53 12. Contact Information 54 13. Index ©2015 SQL Sentry. All Rights Reserved.
SQL Sentry Quick Start 3 4 Quick Start Guide PURPOSE OF THIS GUIDE This Quick Start Guide will quickly walk you through the basic Installation and Setup Steps for SQL Sentry. Following this guide you should be able to install SQL Sentry, complete basic configuration, start managing schedules, monitoring performance, and generating notifications across your enterprise within 10 to 30 minutes.
SQL Sentry Quick Start 4 5 Important Concepts SQL SENTRY COMPONENTS SQL Sentry consists of the Client (a thin client application), the Monitoring Service (a Windows service), and a SQL Server Database. The SQL Sentry Database stores event metadata and history information collected by the SQL Sentry Monitoring Service and the SQL Sentry Client provides a thin client interface for viewing and managing this information.
SQL Sentry Quick Start 6 watching connections or objects. When you have SQL Sentry “watch” a connection or object via the context menu this simply means that SQL Sentry will begin monitoring it. Please consider these rules regarding watched connections and objects: 1. When a connection is watched, SQL Sentry will monitor the connection and fire any applicable conditions for the connection based on its type. 2.
SQL Sentry Quick Start 5 7 Installation Recommendations WHERE TO INSTALL THE SQL SENTRY COMPONENTS The SQL Sentry Client, Monitoring Service, and SQL Sentry Database are typically installed as follows: The SQL Sentry Client is installed on your workstation computer(s) The SQL Sentry Database is installed on a SQL Server instance on your local area network The Monitoring Service is installed on the same computer as the SQL Sentry Database, or any other non-production server in the same LAN.
SQL Sentry Quick Start 8 CLUSTERING SQL SENTRY MONITORING SERVICES Multiple Monitoring Services can be installed to handle more than 100 connections, and/or to provide automatic redundancy and load balancing. There is no configuration required to implement a basic SQL Sentry cluster. Simply install more than one Monitoring Service and connect each to the same SQL Sentry Database during setup, and they will automatically distribute the monitoring load evenly between themselves.
SQL Sentry Quick Start 6 9 System Requirements SQL SENTRY COMPONENTS SQL Sentry Client computer Windows version from supported list below Microsoft .NET 4.5 (included in the setup package) Minimum Single 1.6 GHz CPU, 1 GB RAM SQL Sentry Monitoring Service computer Windows version from supported list below Microsoft .NET 4.5 (included in the setup package) Minimum Dual 1.6 GHz CPUs, or 1.
SQL Sentry Quick Start 10 Windows 8 Supported Operating Systems (x64) Windows Server 2008 Windows Server 2008 R2 Windows Server 2012 Windows Server 2012 R2 Windows Vista Windows 7 Windows 8 WATCHED CONNECTIONS Watched (monitored) SQL Server instances SQL Server 2000, both 32-bit and 64-bit (Windows Server 2003 or higher) SQL Server 2005, both 32-bit and 64-bit SQL Server 2008, both 32-bit and 64-bit SQL Server 2008 R2 SQL Server 2012 SQL Server 2014 RTM NOTE: SQL Sentry does not support monitoring any ve
SQL Sentry Quick Start 11 Monitoring Analysis Services with Performance Advisor requires SQL Server 2005 or higher Indexes tab and Fragmentation Manager require SQL Server 2005 or higher Monitoring the Windows Event Log with Event Manager for Windows is only supported for Windows Vista or higher Watched (monitored) Windows instances Windows Server 2003 Windows Server 2008 Windows Server 2008 R2 Windows Server 2012 Windows XP Windows Vista Windows 7 Windows 8 NOTE: Windows Vista introduced Task Scheduler 2
SQL Sentry Quick Start 7 12 Installation and Setup Steps Once you receive your license and setup file download information, copy the setup executable to the server on which you want to install the SQL Sentry Monitoring Service and then run it. If you are upgrading SQL Sentry from a previous version, it is strongly recommended that you backup your SQL Sentry Database prior to beginning the process. Follow these steps: 1. Install SQL Sentry 2. Complete the Setup Wizard 3. Start Using the Client 7.
SQL Sentry Quick Start 13 Only one Monitoring Service is required for your SQL Sentry Enterprise. Unless desired, there is no need to install a Monitoring Service on any DBA workstation machines. To install just the SQL Sentry Client do the following: On the Custom Setup screen, select the drop down arrow next to the Monitoring Service component and choose the Entire feature will be unavailable option.
SQL Sentry Quick Start 14 Click the Test button to validate the chosen credentials. After a successful test, click the Next button to continue the setup. Note: If an existing database has been selected, clicking test will ask you to confirm that you want to upgrade the database. D. SERVICE ACCOUNT INFORMATION At the Service Account Information screen you will enter the Windows account under which the SQL Sentry Monitoring Service will run.
SQL Sentry Quick Start 15 For information on SQL Sentry Monitoring Service security settings see the SQL Sentry Security topics. Click the Test button to validate the chosen credentials. After a successful test, click the Next button to continue the setup. Note: You can change the service account any time after the initial installation by runningtrue the Service Configuration Utility found in the SQL Sentry program group. E.
SQL Sentry Quick Start 16 Click below to proceed: Step 2: Complete the Setup Wizard 7.2 Step 2: The Setup Wizard LAUNCH THE SQL SENTRY CLIENT The first time you launch the SQL Sentry Client, you will see the “License Not Found” message box. Click OK and you will be prompted to enter your license key. At the License Entry screen, you can either paste your license key or drag-and-drop the license file into the large text box. Click Save to continue.
SQL Sentry Quick Start 17 Note: You may need to contact your network administrator first to ensure that the IP address of the Monitoring Service computer has been granted both Connect and Relay permissions for the specified SMTP server. Next, enter the email From Address. This is the address which will appear on the From line of all email notifications sent by SQL Sentry. You can also specify a Username and Password if authentication is required by your SMTP server.
SQL Sentry Quick Start 18 order to monitor SQL Server Agent Alerts with SQL Sentry. See SQL Server Books Online for more information on tokens and security. 2. SELECT CONNECTIONS TO WATCH On the Select Connections to Watch screen the Add Connection dialogue will be displayed. Use the Connection Type drop-down box to choose your connection type. Enter the server name or the server name\instance name and click the Connect button to add the connection.
SQL Sentry Quick Start 19 7.3 Step 3: Start Using the Client Congratulations, you have successfully installed SQL Sentry, configured global notification settings, and are now ready to start using the SQL Sentry Client for managing events across your enterprise. Use the different options on the Get Started screen to start exploring the features in SQL Sentry. Please refer to the SQL Sentry User Guide available online and through the Client Help menu, for additional information about available features.
SQL Sentry Quick Start 8 20 Additional Tasks Overview ADDITIONAL TASKS Refer to these topics for additional information on configuring SQL Sentry. Add Additional Users and Groups Monitor Additional Connections Customize Global Settings 8.1 Add Users and Groups The Contacts node in the Navigator contains the Users and Groups sub-nodes. This is where you create and maintain Users and Groups for notification purposes. At least one user is required for SQL Sentry to be able to send notifications.
SQL Sentry Quick Start 21 any associated configured Conditions and Actions. Note: Immediately after adding a Connection or setting a Connection to watched status, SQL Sentry will begin to synchronize with that Connection. Exactly how long the synchronization process takes depends on the number of objects associated with the Connection, the amount of historical data available, and how many Connections are being watched at the same time.
SQL Sentry Quick Start 22 disable the Send Email Action at the Connection level. This configuration would only apply to that Connection and it would not affect any other server in your environment. This level of control gives you the ability to determine exactly what happens in response to events occurring on your monitored servers. There are several levels within the SQL Sentry Hierarchy where you can configure applicable Actions and Settings. These are outlined below.
SQL Sentry Quick Start 23 Override This behavior can be thought of as a special set of instructions which are followed instead of the passed down(inherited) instructions. Combine This behavior can be thought of as a set of instructions which are followed in addition to the passed down(inherited) instructions. Disable This behavior can be thought of as a special set of instructions which simply disallow the passed down(inherited) set of instructions.
SQL Sentry Quick Start 24 For more information about Actions and Conditions please see the Alerting and Response System topics in the SQL Sentry User Guide. 8.3.2 How to Configure Settings As a reminder, Settings define criteria for when a Condition is considered to be met. Certain Settings known as Source Settings are used to define what events are collected by SQL Sentry. To configure Settings first select the desired node in the Navigator pane.
SQL Sentry Quick Start 25 3. Use the second drop-down list to select Top SQL Source. You should now see the Top SQL Source Settings that are being applied Globally. 4. Change the Minimum Duration to the desired value, it will be saved automatically. If you wanted to configure the Top SQL Minimum Duration Collection Setting for an individual Connection: 1. Select the desired Connection node in the Navigator pane. 2. In the Settings pane, use the drop-down list to select Top SQL Source.
SQL Sentry Quick Start 26 job on the calendar, then following the same steps as above to access and change the job's runtime threshold settings. For more information about Settings please see the Alerting and Response System topics in the SQL Sentry User Guide. ©2015 SQL Sentry. All Rights Reserved.
SQL Sentry Quick Start 9 27 SQL Sentry Security Overview The Quick Start Guide covers the following topics related to SQL Sentry Security, including required permissions for the various SQL Sentry components. Security Topic Description Monitoring Service Security This topic discusses the permissions required by the SQL Sentry Monitoring Service account when watching (monitoring) Connections.
SQL Sentry Quick Start 28 with SQL Sentry Performance Advisor. It is not necessary for this account to be a Domain Administrator account. Instead, it is recommended that the service account be a standard user Domain account that has been added to the local Administrators group of each monitored target. For more information about security and SQL Sentry Performance Advisor, please see the Performance Advisor Security Requirements topic.
SQL Sentry Quick Start 29 1. Select the Services icon from Control Panel -> Administrative Tools. 2. From the list of services select SQL Sentry Monitoring Service, then right-click and select “Start”, or click the “Play” button on the toolbar. REQUIRED PERMISSIONS FOR ORACLE DATABASES The user accounts that the SQL Sentry Client and Monitoring Service use when connecting to Oracle servers must be granted "view" privileges on the sys schema at a minimum.
SQL Sentry Quick Start 30 User Connection Properties. The User Connection Properties for a Connection can be accessed through the right-click context menu of the Connection. First unselect the Use Integrated Authentication check box and then enter your desired account information. For example, for a SQL Server Connection you would want to enter a SQL Server Authentication Account with the desired Server Role.
SQL Sentry Quick Start Option 31 Description Pass-through authentication enables Windows computers in different domains or in nonWindows network environments to communicate with one another by using identical user accounts and passwords on each computer. Pass-through Authentication This solution is ideal when you only need to monitor a few servers outside of your primary domain and you do not have the resources available to install another Monitoring Service in the secondary domain.
SQL Sentry Quick Start 32 the account exists in the local administrators group. Please see the Performance Advisor: WMI or Registry Access KB article for more information and configuration details about using pass-through authentication on Windows Vista and higher: Important: SQL Server authentication can be used for any watched Event Manager SQL Server connection using a connection's "Monitoring Service Connection Properties" context menu item.
SQL Sentry Quick Start 33 9.5 Non-Windows Network Environment Security If you are not using Windows Active Directory for domain management, you may need to take additional steps to ensure SQL Sentry will work properly. The primary means by which this is accomplished is using Windows pass-through authentication. SQL Server authentication can be used for any watched SQL Server connection in a non-Windows network using a connection's "Monitoring Service Connection Properties" context item.
SQL Sentry Quick Start 34 10 Appendix The Appendix contains the following topics: SQL Sentry Performance Advisor Performance Advisor Security Requirements Performance Advisor Required Ports Performance Advisor Data Capacity Planning SMTP Settings Uninstalling SQL Sentry Object Removal Script for Watched 2000 Servers Object Removal Script for Watched 2005+ Servers Watched Server Objects Standard Vs Enterprise Editions 10.
SQL Sentry Quick Start 35 "Services and Applications" in the Computer Management Client. On the Security tab, ensure that the SQL Sentry Monitoring Service account has at least "Enable Account" and "Remote Enable" checked for the CIMV2 and WMI nodes. NOTE: WMI providers and versions will vary from server to server, and whether or not nonadministrative access will function properly for a particular WMI provider is directly dependent on whether or not the provider was designed to support this.
SQL Sentry Quick Start 36 that uses DCOM) connects to a target server initially using port 135, and the target responds with a dynamic port number for WMI to use for the rest of the session. This port can be in one of the ranges above, which are quite large by default. To address this, you can easily specify a custom range for RPC dynamic ports. You may have already done this in your environment in order to enable networked DCOM access for other applications.
SQL Sentry Quick Start 37 10.1.3 Performance Advisor Data Capacity Planning PERFORMANCE ADVISOR DATA CAPACITY PLANNING Performance Advisor (PA) uses the SQLSentry database to store all of the performance data it collects, utilizing a high performance storage scheme. Event Manager (EM) only users should expect their existing database to approximately double in size if all of the existing SQL Servers watched by EM, are watched by PA.
SQL Sentry Quick Start 38 the two minute break level, or PerformanceAnalysisDataRollup2. The retention hours for this, or any other break level, can be adjusted as needed. Retention for raw Top SQL, Blocking and Deadlock data is controlled by the Purge History Older Than setting on the Performance Monitor tab under SQL Sentry Server->Settings in the Navigator pane. The default is 15 days.
SQL Sentry Quick Start 39 'PerformanceAnalysisDataRollup8', 'PerformanceAnalysisTraceData', 'PerformanceAnalysisPlan', 'PerformanceAnalysisPlanOpTotals', 'PerformanceAnalysisTraceCachedPlanItems', 'PerformanceAnalysisTraceDataToCachedPlans', 'PerformanceAnalysisTraceQueryStats', 'MetaHistorySharePointTimerJob', 'PerformanceAnalysisSsasUsageTotals', 'PerformanceAnalysisSsasCubeDimensionAttribute', 'PerformanceAnalysisSsasTraceDataDetail' ) AND OBJECTPROPERTY([object_id], 'IsUserTable') = 1 GROUP BY [object
SQL Sentry Quick Start 40 10.3 Uninstalling SQL Sentry SQL Sentry can be uninstalled through the Control Panel in Windows. When you uninstall the SQL Sentry Client or Monitoring Service, the associated program files will be removed. User preferences stored in the registry, and the SQL Sentry Database will not be deleted. Additionally, the .NET Framework files will not be removed when uninstalling SQL Sentry. This can be accomplished using Add/Remove Programs in the Control Panel.
SQL Sentry Quick Start 41 WHERE (name = N'SQL Sentry 2.0 Queue Monitor') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''SQL Sentry Queue Monitor'' since there is already a multi-server job with this name.', 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.
SQL Sentry Quick Start 42 drop procedure [dbo].[spReadLogFile_20] if exists (select * from dbo.sysobjects where id = object_id(N'[dbo]. [SQLSentryQueueLog_20]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[SQLSentryQueueLog_20] if exists (select * from dbo.sysobjects where id = object_id(N'[dbo]. [SQLSentryLogCache_20]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[SQLSentryLogCache_20] if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
SQL Sentry Quick Start 43 drop procedure [dbo].[spTrapAlert_20] if exists (select * from dbo.sysobjects where id = object_id(N'[dbo]. [spSetupAlertsTrap_20]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[spSetupAlertsTrap_20] if exists (select * from dbo.sysobjects where id = object_id(N'[dbo]. [SQLSentryAlertLog_20]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[SQLSentryAlertLog_20] if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
SQL Sentry Quick Start 44 SELECT @ReturnCode = 0 -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'SQL Sentry 2.0 Queue Monitor') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.
SQL Sentry Quick Start drop procedure [dbo].[spQueueHeartbeat_20] if exists (select * from dbo.sysobjects where id = object_id(N'[dbo]. [spQueueJob_Start_20]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[spQueueJob_Start_20] if exists (select * from dbo.sysobjects where id = object_id(N'[dbo]. [spQueueJob_End_20]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[spQueueJob_End_20] if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
SQL Sentry Quick Start 46 -- There is, so abort the script RAISERROR (N'Unable to import job ''SQL Sentry Alert Trap'' since there is already a multi-server job with this name.', 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'SQL Sentry 2.0 Alert Trap' SELECT @JobID = NULL END COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO if exists (select * from dbo.
SQL Sentry Quick Start 10.3.2 47 Object Removal Script for Watched 2005 and Above Servers Object Removal Script for Watched 2005 and above USE msdb if exists (select * from sys.objects where object_id = object_id(N'[dbo]. [sp_sentry_mail]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1) drop procedure [dbo].[sp_sentry_mail] if exists (select * from sys.objects where object_id = object_id(N'[dbo]. [sp_sentry_mail_20]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1) drop procedure [dbo].
SQL Sentry Quick Start 48 DECLARE @ReturnCode INT SELECT @ReturnCode = 0 -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'SQL Sentry 2.0 Queue Monitor') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.
SQL Sentry Quick Start 49 if exists (select * from sys.objects where object_id = object_id(N'[dbo]. [spQueueMonitor_20]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1) drop procedure [dbo].[spQueueMonitor_20] if exists (select * from sys.objects where object_id = object_id(N'[dbo]. [spReadLogFile_20]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1) drop procedure [dbo].[spReadLogFile_20] if exists (select * from sys.objects where object_id = object_id(N'[dbo].
SQL Sentry Quick Start 50 COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO if exists (select * from sys.objects where object_id = object_id(N'[dbo]. [spTrapAlert_20]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1) drop procedure [dbo].[spTrapAlert_20] if exists (select * from sys.objects where object_id = object_id(N'[dbo]. [spSetupAlertsTrap_20]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1) drop procedure [dbo].
SQL Sentry Quick Start 51 STORED PROCEDURES (MSDB): spGetBlockInfo_20 EVENT MANAGER WATCHED SERVER OBJECTS When a SQL Server instance is watched by SQL Sentry Event Manager, the below objects are placed on the target server. To remove these objects, see the Uninstalling SQL Sentry topic.
SQL Sentry Quick Start 10.5 Standard Vs Enterprise Editions Please see this link for a Standard Vs. Enterprise feature comparison. ©2015 SQL Sentry. All Rights Reserved.
SQL Sentry Quick Start 53 11 The SQL Sentry User Guide For advanced information on configuring and using SQL Sentry, please refer to the SQL Sentry User Guide. The User Guide is always accessible from the Help menu of the SQL Sentry Client. ©2015 SQL Sentry. All Rights Reserved.
SQL Sentry Quick Start 54 12 Contact Information CUSTOMER PORTAL Access to the SQL Sentry Customer Portal is available around the clock, allowing you to retrieve a backup license key, expand your enterprise by adding more licenses or even modify an existing license key in the case of hardware changes. In addition, the Customer Portal is where product updates and documentation can be found. The page http://www.sqlsentry.com/portal can be used to activate and log into your account.
SQL Sentry Quick Start 13 Index Add Users and Groups, 20 Additional Tasks Overview, 20 Appendix, 34 Client Security, 29-30 Contact Information, 54 Cover Page, 1 Customize Global Settings, 21-22 How to Configure Actions , 22-24 How to Configure Settings, 24-26 Important Concepts, 5-6 Installation and Setup Steps, 12 Installation Recommendations, 7-8 Introduction to Actions and Settings, 21-22 Least Privilege General Performance Monitoring, 32 Monitor Additional Connections, 20-21 Monitoring Service Credentia
SQL Sentry Quick Start System Requirements, 9-11 Reference Standard Vs Enterprise Editions, 51-52 Removing Watched Server Objects, 39-40 Security and the SQL Sentry Client, 29-30 Security and the SQL Sentry Server, 27-29 Security in non-Windows Network Environments, 32-33 Security Overview, 27 SMTP Settings, 39 SQL Sentry Performance Advisor, 34 SQL Sentry Security Overview, 27 Standard Vs Enterprise Editions, 51-52 Starting the Monitoring Service, 27-29 Step 1: Install SQL Sentry, 12-16 Step 2: The Setup W
