![[]Oracle Enterprise Manager Microsoft SQL Server Plug-in User's Guide .](/img/51/sqlpg.jpg)
Transcription
Oracle Enterprise Manager1[]Microsoft SQL Server Plug-in User’s GuideRelease 13.1.0.1E65825-01December 2015
Oracle Enterprise Manager Microsoft SQL Server Plug-in User's Guide, Release 13.1.0.1E65825-01Copyright 2015, Oracle and/or its affiliates. All rights reserved.This software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverseengineering, disassembly, or decompilation of this software, unless required by law for interoperability, isprohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing iton behalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software,any programs installed on the hardware, and/or documentation, delivered to U.S. Government end usersare "commercial computer software" pursuant to the applicable Federal Acquisition Regulation andagency-specific supplemental regulations. As such, use, duplication, disclosure, modification, andadaptation of the programs, including any operating system, integrated software, any programs installed onthe hardware, and/or documentation, shall be subject to license terms and license restrictions applicable tothe programs. No other rights are granted to the U.S. Government.This software or hardware is developed for general use in a variety of information managementapplications. It is not developed or intended for use in any inherently dangerous applications, includingapplications that may create a risk of personal injury. If you use this software or hardware in dangerousapplications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and othermeasures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damagescaused by use of this software or hardware in dangerous applications.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks oftheir respective owners.Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarksare used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD,Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of AdvancedMicro Devices. UNIX is a registered trademark of The Open Group.This software or hardware and documentation may provide access to or information about content,products, and services from third parties. Oracle Corporation and its affiliates are not responsible for andexpressly disclaim all warranties of any kind with respect to third-party content, products, and servicesunless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and itsaffiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use ofthird-party content, products, or services, except as set forth in an applicable agreement between you andOracle.
ContentsPreface . viiAudience. viiDocumentation Accessibility . viiRelated Documents . viiConventions . viiWhat’s Changed . viii1 Microsoft SQL Server Plug-in Overview and 1.51.61.71.8Microsoft SQL Server Plug-in Overview and Feature Summary .What’s New in This Release .Added Support for Microsoft SQL Server 2014 .Updated User Interface.Business Intelligence Publisher Reports.Chargeback Functionality.SQL Server Compliance Standard.Supported Versions .Prerequisites.Downloading the Plug-in .Deploying the Plug-in .Upgrading the Plug-in .Undeploying the Plug-in .1-11-21-21-31-41-41-51-51-61-71-71-81-82 Configure Microsoft SQL Server for 32.4.42.4.52.4.6Enabling and Finding TCP/IP Port Information .Enabling TCP/IP Port .Finding the TCP/IP Port.Modifying the Permissions for Database Authentication.Enabling SQL Authentication or Mixed Authentication.Authentication Configuration Scenarios .Example 1: Local monitoring with SQL Authentication.Example 2: Local monitoring with Windows Integrated Authentication (WIA).Example 3: Remote monitoring with SQL Authentication.Example 4: Remote monitoring with Windows Integrated Authentication (WIA).Example 5: Cluster remote monitoring with SQL Authentication .Example 6: Cluster remote monitoring with Windows Integrated Authentication .2-12-12-12-22-42-42-52-52-52-52-62-6iii
3 Discovery of the Microsoft SQL Server Target3.13.23.3Discovering Targets . 3-1Adding Targets with EMCLI. 3-3Verifying and Validating the Plug-in. 3-44 Configuring Connections4.14.2Configuring Remote Connections to Monitor Targets . 4-1Configuring Connections to Execute Jobs . 4-25 Creating, Editing, and Using Jobs5.15.2Creating and Editing Jobs. 5-1Using the Backup and Restore Jobs. 5-46 Using Reports and Monitoring Templates6.16.26.3Using the Microsoft SQL Server Plug-in Reports. 6-1Deploying Reports After BI Publisher is Configured. 6-3Using the Microsoft SQL Server Plug-in Monitoring Templates. 6-37 Chargeback Functionality7.17.27.37.47.57.67.77.87.9About Chargeback . 7-1Chargeback Plug-in Deployment . 7-1Configuring Global Settings for Chargeback. 7-2Configuring a Charge Plan . 7-3Revising Extended Charge Plans. 7-4Configuring a Cost Center. 7-5Configuring an Entity. 7-6Generating and Distributing Chargeback Reports . 7-9Additional Information . 7-108 Compliance Management8.18.28.38.48.58.68.78.88.98.10About Compliance Management.Managing Compliance Framework.Configuring the SQL Server Configuration Compliance Standard."Create Like" Compliance Standard.Editing a Compliance Standard .Evaluating Compliance.Using Trend Overview.Using Compliance Reports .Managing Compliance Violations .Additional Information .8-18-18-28-38-48-48-48-58-58-79 Inventory and Usage Details9.19.2ivInventory and Usage Details Page Feature Summary. 9-1Accessing the Inventory and Usage Details Page . 9-2
9.3Additional Information . 9-2Indexv
vi
PrefaceThis document provides a description about the Oracle system monitoring plug-in forMicrosoft SQL Server, details on the versions the plug-in supports, prerequisites forinstalling the plug-in, and step-by-step instructions on how to download, install,verify, and validate the plug-in.AudienceThis document is intended systems and database administrators tasked withmonitoring Microsoft SQL Server through Enterprise Manager Cloud Control 13c.Documentation AccessibilityFor information about Oracle's commitment to accessibility, visit the OracleAccessibility Program website athttp://www.oracle.com/pls/topic/lookup?ctx acc&id docacc.Access to Oracle SupportOracle customers that have purchased support have access to electronic supportthrough My Oracle Support. For information, visithttp://www.oracle.com/pls/topic/lookup?ctx acc&id info or visithttp://www.oracle.com/pls/topic/lookup?ctx acc&id trs if you are hearingimpaired.Related DocumentsFor information about the troubleshooting scenarios that you might encounter whileworking with the System Monitoring plug-ins, see the Oracle Enterprise ManagerSystem Monitoring Plug-in Troubleshooting Guide for Third-Party Database Plug-ins:ConventionsThe following text conventions are used in this document:ConventionMeaningboldfaceBoldface type indicates graphical user interface elements associatedwith an action, or terms defined in text or the glossary.italicItalic type indicates book titles, emphasis, or placeholder variables forwhich you supply particular values.vii
ConventionMeaningmonospaceMonospace type indicates commands within a paragraph, URLs, codein examples, text that appears on the screen, or text that you enter.What’s ChangedThis table provides a brief overview of the document changes for the latest publicationof the Oracle Enterprise Manager System Monitoring Plug-in Installation Guide forMicrosoft SQL Server:viiiPart NumberChange SummaryE65825-01Initial release in support of Oracle Enterprise Manager Cloud Control 13c.
1Microsoft SQL Server Plug-in Overview andPrerequisites1This chapter describes the system monitoring plug-in for Microsoft SQL Server andprovides a list of available features. Review the summary of prerequisites requiredbefore configuring Microsoft SQL Server for monitoring by Oracle Enterprise ManagerCloud Control.[2]The following topics are provided: Microsoft SQL Server Plug-in Overview and Feature Summary What’s New in This Release Supported Versions Prerequisites Downloading the Plug-in Deploying the Plug-in Upgrading the Plug-in Undeploying the Plug-in1.1 Microsoft SQL Server Plug-in Overview and Feature SummaryThe system monitoring plug-in for Microsoft SQL Server extends Oracle EnterpriseManager Cloud Control 13c to add support for managing Microsoft SQL Serverinstances. By deploying the plug-in within your Cloud Control environment, you gainthe following management features: Monitor SQL Server instances. Supports both SQL Authentication and Windows Integrated Authentication. Gather configuration data and track configuration changes for SQL Serverinstances.Raise alerts and violations based on thresholds set on monitored metrics andconfiguration data.Provide rich out-of-box reports through Enterprise Manager’s BI Publisher reportsfeature based on the gathered data.Support monitoring by a local or remote Windows Agent. Local Windows Agent isan agent running on the same host as the Microsoft SQL Server. Remote WindowsAgent is an agent running on a host that is different from the host where SQLServer is running.Microsoft SQL Server Plug-in Overview and Prerequisites 1-1
What’s New in This Release Out-of-the-box monitoring templates for Microsoft SQL Server Cluster monitoringand Microsoft SQL Server AlwaysOn (HADR) monitoring.Oracle Enterprise Manager Jobs are made easy-to-access by being accessible fromthe plug-in's UI. These jobs allow for the following management of Microsoft SQLServer:–Backup, restore, schedule, and naming of Microsoft SQL Server databasebackups.–Start, stop, pause, and resume of SQL Server Instances.–Killing of sessions that are high in CPU or memory usage.Provide chargeback functionality for resource usage metering, consumptionreports, and charge plans to define the resources to charge for and their associatedrates.Provide compliance management to evaluate the compliance of targets andsystems.Provide inventory and usage details for inventory summaries of your MicrosoftSQL Server database.1.2 What’s New in This ReleaseThis release of the Microsoft SQL Server plug-in for Oracle Enterprise Manager CloudControl 13c includes many new features for better visibility into your Microsoft SQLServer environment. Highlighted below are some of the new features included in thisrelease: Added Support for Microsoft SQL Server 2014 Updated User Interface Business Intelligence Publisher Reports Chargeback Functionality SQL Server Compliance Standard1.2.1 Added Support for Microsoft SQL Server 2014Microsoft SQL Server plug-in for Oracle Enterprise Manager Cloud Control 13csupports a wide range of Microsoft SQL Server versions (Figure 1–1). This plug-inrelease adds support for the latest version of Microsoft SQL Server - SQL Server 2014.The plug-in supports not only standalone but also the cluster configuration of thisversion.This release provides support for JDBC 4.0. The sqljdbc auth.dll files are nowprepackaged with the plug-in.1-2 Oracle Enterprise Manager Microsoft SQL Server Plug-in User's Guide
What’s New in This ReleaseFigure 1–1Microsoft SQL Server Database Instances1.2.2 Updated User InterfaceThe user interface has been updated to add an index page and a new link to the homepage.The home page (Figure 1–2) has been enhanced with the addition of a link in theSummary section to the all new indexes page. This enables quicker navigationthroughout the plug-in.Figure 1–2Microsoft SQL Server Plug-in Target Home PageThe all-new indexes page (Figure 1–3) has been added to the plug-in to give greaterinsight into the monitored SQL Server indexes. This includes an indexes by usagegraph and table, index fragmentation table, recommended missing indexes, and a jobto create indexes.Microsoft SQL Server Plug-in Overview and Prerequisites 1-3
What’s New in This ReleaseFigure 1–3Microsoft SQL Server Plug-in Indexes Page1.2.3 Business Intelligence Publisher ReportsIn this release Information Publisher Reports included in the previous release of theMS SQL Server Plug-in have been migrated to BI Publisher Reports. BI PublisherReports eliminate complexity and simplify report development and maintenance.See Using Reports and Monitoring Templates for more information on using thesereports.1.2.4 Chargeback FunctionalityNew to this version is Chargeback functionality (Figure 1–4). Chargeback allows usersto gather data on resource use, allocate charges for the use of these resources, andpresent the results in an intelligible format. Using Chargeback assists consumers inmonitoring and controlling their IT costs.Figure 1–4Microsoft SQL Server Plug-in Chargeback FunctionalityFor more information on how to implement Chargeback services, see the OracleEnterprise Manager Cloud Administration Guide:1-4 Oracle Enterprise Manager Microsoft SQL Server Plug-in User's Guide
Supported Versionshttp://docs.oracle.com/cd/E24628 01/doc.121/e28814/chargeback cloudadmin.htm#EMCLO9171.2.5 SQL Server Compliance StandardAnother new feature in this release is the Microsoft SQL Server Compliance Standard(Figure 1–5). Associating a Compliance Standard with targets allows an administratorto track and monitor the adherence of managed targets. This will assist in revealing theleast compliant targets, so measures can be taken to correct them.Figure 1–5SQL Server Configuration Compliance StandardFor more information on how to implement Compliance Standards to targets, see theOracle Enterprise Manager Cloud Control Oracle Database Compliance Standards Guide:http://docs.oracle.com/cd/E24628 01/doc.121/e36074/db comp intro.htm#EMDBC1081.3 Supported VersionsThis plug-in supports the following versions of products: Enterprise Manager Cloud Control (Oracle Management Server and OracleManagement Agent):–Recommended: Enterprise Manager Cloud Control 13c Release 1 (13.1.0.1.0) orhigher–Enterprise Manager Cloud Control 12c Release 4 (12.1.0.4.0) or higherStandard, Enterprise, and Workgroup editions of Microsoft SQL Server 2005,Microsoft SQL Server 2008, Microsoft SQL Server 2012, and Microsoft SQL Server2014 as detailed below:–Microsoft SQL Server 2005 (32-bit or 64-bit).–Microsoft SQL Server 2008 (32-bit or 64-bit).–Microsoft SQL Server 2008 R2 (32-bit or 64-bit) including Failover Clustersupport.–Microsoft SQL Server 2012 (32-bit or 64-bit) including Failover Cluster andAlwaysOn Availability Groups support.Microsoft SQL Server Plug-in Overview and Prerequisites 1-5
Prerequisites–Microsoft SQL Server 2014 (32-bit or 64-bit) including Failover Cluster andAlwaysOn Availability Groups support.Note: Monitoring of Microsoft SQL Server Clusters are onlysupported with a remote monitoring configuration. The OracleManagement Agent used in monitoring cannot be installed to one ofthe cluster nodes.1.4 PrerequisitesThe following prerequisites must be met before you can deploy the plug-in. Patchesare available from My Oracle Support (https://support.oracle.com):1.Enterprise Manager Cloud Control (Oracle Management Server and OracleManagement Agent) must be installed. Choose from: Recommended: Enterprise Manager Cloud Control 13c Release 1 (12.1.0.1). Enterprise Manager Cloud Control 12c Release 5 (12.1.0.5). Enterprise Manager Cloud Control 12c Release 4 (12.1.0.4.0) or higher andinstall Patch 20870437 to upgrade to version 12.1.0.4.4.To correct a deployment issue, install system patch 20645335: On WindowsServer OMS side plug-in deployment was stuck at OUI step.2.The following patches must be installed: Configuration Management Patch 18140699. Inventory and Usage Patch 20692416. Chargeback Patch:–For Enterprise Manager Cloud Control 12c Release 5 (12.1.0.5.0), whichcontains Chargeback plug-in version 12.1.0.7.1, apply patch 21420915:Enterprise Manager for OMS Plug-ins 12.1.0.5.1.–For Enterprise Manager Cloud Control 12c Release 4 (12.1.0.4.0), whichcontains Chargeback plug-in version 12.1.0.6.2, apply patch 21415432:Enterprise Manager for OMS Plug-ins 12.1.0.4.14.3.The plug-in is only supported when running the Oracle Management Agent on32-bit or 64-bit Windows.4.Access privileges required for non-admin System user to perform RemoteMonitoring of SQL Server instance.For more information, see Configuring Remote Connections to Monitor Targets.5.Windows Management Instrumentation Service is up and running.6.Enable TCP/IP for the SQL Server instance. For more information, see Enablingand Finding TCP/IP Port Information.7.Enable SQL or Mixed Authentication on the SQL Server instance. For moreinformation, Enabling SQL Authentication or Mixed Authentication.8.Create a suitable DB user with a sysadmin fixed server role. To monitor the SQLServer instance using non-sysadmin user, create a user with non-sysadmin roleand provide the following access to it:a.Execute this command to give access to the user:1-6 Oracle Enterprise Manager Microsoft SQL Server Plug-in User's Guide
Deploying the Plug-inGRANT VIEW SERVER STATE TO "login name"9.b.Provide database access to the user.c.Provide SQLAgentOperatorRole fixed database role in msdb to the user.Preferred credentials are set and validated on all Agents where you want todeploy the plug-in.10. The OS privileges for the user (set in the Preferred Credentials for the Agent) mustmeet the requirements documented in the "Setting Credentials for the Job System toWork with Enterprise Manager" section of the Oracle Database Installation Guide forMicrosoft Windows available at:http://docs.oracle.com/cd/E11882 01/install.112/e24186/postcfg.htm#BABFAEIGNote: If you do not assign the correct privileges for users, thedeployment will fail.11. As part of JDBC URL, either IP Address or host name can be provided. Ensure thatthe host name can be resolved consistently on the network. Standard TCP toolssuch as nslookup and traceroute can be used to verify the host name. Validateusing the following commands on Management Agent where plug-in is deployed: nslookup hostname This returns the IP address and fully qualified host name. nslookup IP This returns the IP address and fully qualified host name.12. To enable the use of the Backup, Delete Backup, and Restore jobs, the followingSQL commands must be processed on the monitored SQL Server database:EXEC sp configure 'show advanced options', 1RECONFIGUREEXEC sp configure 'xp cmdshell', 1RECONFIGURE1.5 Downloading the Plug-inYou can download plug-ins in online or offline mode. Online mode refers to anenvironment where you have Internet connectivity, and can download the plug-indirectly through Enterprise Manager from My Oracle Support. Offline mode refers to anenvironment where you do not have Internet connectivity, or where the plug-in is notavailable from My Oracle Support.See the Managing Plug-ins chapter in the Oracle Enterprise Manager Cloud ControlAdministrator's Guide for details on downloading the plug-in in either mode:http://docs.oracle.com/cd/E24628 01/doc.121/e24473/plugin mngr.htm#CJGBEAHJ1.6 Deploying the Plug-inYou can deploy the plug-in to an Oracle Management Service instance using theEnterprise Manager Cloud Control console, or using the EM Command Line Interface(EMCLI). While the console enables you to deploy one plug-in at a time, the commandline interface mode enables you to deploy multiple plug-ins at a time, thus savingplug-in deployment time and downtime, if applicable.Microsoft SQL Server Plug-in Overview and Prerequisites 1-7
Upgrading the Plug-inSee the Managing Plug-ins chapter in the Oracle Enterprise Manager Cloud ControlAdministrator's Guide for instructions on deploying the plug-in:http://docs.oracle.com/cd/E24628 01/doc.121/e24473/plugin mngr.htm#CJGCDHFG1.7 Upgrading the Plug-inThe Self Update feature allows you to expand Enterprise Manager's capabilities byupdating Enterprise Manager components whenever new or updated features becomeavailable. Updated plug-ins are made available via the Enterprise Manager Store, anexternal site that is periodically checked by Enterprise Manager Cloud Control toobtain information about updates ready for download. See the Updating Cloud Controlchapter in the Oracle Enterprise Manager Cloud Control Administrator's Guide for steps toupdate the plug-in:http://docs.oracle.com/cd/E24628 01/doc.121/e24473/self update.htm1.8 Undeploying the Plug-inSee the Managing Plug-ins chapter in the Oracle Enterprise Manager Cloud ControlAdministrator's Guide for steps to undeploy the plug-in:http://docs.oracle.com/cd/E24628 01/doc.121/e24473/plugin mngr.htm#CJGEFADI1-8 Oracle Enterprise Manager Microsoft SQL Server Plug-in User's Guide
2Configure Microsoft SQL Server forAuthentication2This chapter provides the instructions for configuring Microsoft SQL Server forauthentication for access through Oracle Enterprise Manager Cloud Control. Startingfirst with enabling and finding TCP/IP port information, the chapter ends with a set ofauthentication configuration scenarios that you can modify for your ownenvironment.[3]The following topics are provided: Enabling and Finding TCP/IP Port Information Modifying the Permissions for Database Authentication Enabling SQL Authentication or Mixed Authentication Authentication Configuration Scenarios2.1 Enabling and Finding TCP/IP Port InformationThe following sections provide information you require to enable the TCP/IP port andto find the TCP/IP port for a particular SQL server instance: Enabling TCP/IP Port Finding the TCP/IP Port2.1.1 Enabling TCP/IP Port1.From the SQL Server Configuration Manager, select your appropriate SQL ServerNetwork Configuration in the left panel and navigate to the SQL Server instance.The right panel displays all protocols for the specified SQL Server instance andtheir status.2.Ensure that TCP/IP is enabled.3.If TCP/IP is disabled, right-click TCP/IP and select Properties. The TCP/IPProperties dialog box appears.4.In the Protocol tab, select enabled, and click Apply.5.Restart the SQL Server instance.2.1.2 Finding the TCP/IP PortAfter enabling the TCP/IP protocol, restart the SQL Server to apply the changes.Configure Microsoft SQL Server for Authentication2-1
Modifying the Permissions for Database AuthenticationFrom the SQL Server Configuration Manager, select the appropriate SQL ServerNetwork Configuration in the left panel and navigate to the SQL Server instance:The right panel displays all protocols for the specified SQL Server instance and theirstatus.In the IP Addresses tab, TCP Dynamic Ports row of IP All will give the TCP/IP port ofinstance.2.2 Modifying the Permissions for Database AuthenticationModify the permissions for database authentication so that you enable SQLauthentication or Windows authentication, and set sysadmin role for the database userthat you are going to use for discovering the target and running jobs.On the SQL Server, for the user you are going to use for monitoring and running jobs,set the write permissions by following these steps:Note: If you do not have a user for Windows Authentication, thencreate one. To do so, from the task bar, go to Start, select Settings, andthen Control Panel. In the Control Panel, double-click Users andPasswords and click Add in the Users tab.1.Log in to the Microsoft SQL Server Management Studio with a predefined useraccount, or if one was not setup for SQL authentication, use WindowsAuthentication (Figure 2–1):Figure 2–12.Log In to Microsoft SQL ServerRight-click Logins and select New Login (Figure 2–2):2-2 Oracle Enterprise Manager Microsoft SQL Server Plug-in User's Guide
Modifying the Permissions for Database AuthenticationFigure 2–
1.1 Microsoft SQL Server Plug-in Overview and Feature Summary The system monitoring plug-in for Microsoft SQL Server extends Oracle Enterprise Manager Cloud Control 13c to add support for managing Microsoft SQL Server instances. By deploying the plug-in within your Cloud Control environment, you gain the following management features:
