SolutionPack for Microsoft SQL Server 3.7 Summary Sheet – Service Assurance Suite 9.4.1
Table of Contents
- performance and availability of constituent databases
- datafile and logfile capacities
- performance metrics such IO, Wait, Memory pressure, and Host performance details
- End-to-End capacity reports (MS-SQL Server Application to LUN Detail)
Compatible EMC M&R versions
Supported Microsoft SQL versions
SQL Server 2008R2
SQL Server 2012
SQL Server 2014
1433 (This is the default port on which the MS-SQL instance is running on the MS-SQL Server. It is configurable.)
Data collection method
This SolutionPack uses these commands:
- CREATE TABLE
- This creates temporary tables used when gathering the capacity-related information of databases. The temporary tables are created in the tempdb table. These tables are later dropped.
- Executes a stored procedure that lists disks and their available free space.
- Removes temporary tables created earlier in the tempdb table.
- Instance Sizes
- SQL Server Instances Overview
- This includes reports for database sizes, database overview, and performance KPIs such as configuration parameters, IO, access methods, buffer manager, general statistics, and memory manager.
- Inventory of servers, instances, databases, and data/log files
- Situations to Watch
- This includes index fragmentation, data/log files out of space, inactive devices and components, and actual and forecasted SLA values.
- SQL Server to SAN Inventory
- Lists capacity reports per SQL Server instance
Support for Host Details report
- Host Details report supports virtual machines.
- Host Details report does not support physical hosts.
Install the latest core software update for your product suite. SolutionPacks distributed with core software have a 30-day free evaluation period. If you plan to use the software longer than 30 days, you must install a SolutionPack license before the trial period ends.
This 30-day free evaluation only applies to new installations and is not available for upgraded installations. If you upgrade the core software and want to try a new SolutionPack, you must request a license for that SolutionPack by completing a Support Request (SR) form, which is available on the EMC Online Support website at http://support.emc.com.Back to Top
Microsoft SQL authentication
- Server role of Public
- Securable grants for Connect SQL, View any definitions, and View server state
The collector must connect to each instance of MS-SQL Server and perform SQL queries. You can use either an administrator equivalent system account or create a dedicated system account for the collector. If you want a dedicated system account, please ask the DBA administrator to create a watch4net account with specific grants for the collector.
- Follow this example to create a
The default database must be used as the [master] database in this procedure.
USE [master] GO CREATE LOGIN [watch4net] WITH PASSWORD=N'<securepassword> ', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXECUTE master.sys.sp_MSforeachdb 'USE [?]; CREATE USER watch4net FOR LOGIN watch4net GO GRANT VIEW SERVER STATE TO watch4net GO GRANT VIEW ANY DEFINITION TO watch4net GO ;
Before you begin
- Make sure the core modules, such as the Module-Manager, are up-to-date on all servers since not all module dependencies are validated during the SolutionPack installation. The installation guide for your product provides more information.
- Ensure you create a watch4net user prior to installing the SolutionPack. You may need this to ensure correct credentials are added to Microsoft SQL Server Devices Management during the installation process.
- Log in to your product and click .
- Click SolutionPacks and select SOLUTIONPACK CENTER.
- Select the SolutionPack for Microsoft SQL Server in the Browse and Install SolutionPacks window.
- Read the summary information and click Install.
- Type the instance name.
- Assign a server for each of the three components.
The window displays the data collection details.
- Make a selection in the
Data Collection drop-down menu.
Select existing settings that have been specified for other components or select Add a new data collection.
- If you select
Add a new Data collection, type information about the data collection. In the
Hostname or IP address to send data field, use
localhost on default port 2020.
This is the Collector host where the Load Balancer Connector is installed.
- Select the
Frontend Web Service option.
Select existing settings that have been specified or choose Add a new Frontend Web Service.
- If you select
Configure Collector advanced settings, you have the option to select different polling periods.
The default polling period is 15 minutes. Consult with your database administrator to set the polling period based on the number of SQL Server instances that are being polled by this collector manager instance. For example, you have the option to select 5 minutes, 15 minutes, 30 minutes, or one hour.
- Click Next.
- If you select Add a new Web-Service Gateway, type information about the web-service gateway.
- In the Administration Web-Service instance, select an existing instance or create a custom instance.
- Click Install.
Monitor the installation logs to ensure that the installation completes successfully.
- Click .
- Click Microsoft SQL Server.
- Click Add new device.
- Select the server and collector instance where you want to store the configuration details for this device.
- Set these values:
- Enter the Hostname or IP address of the SQL server.
- Specify the Database Port and Database Instance name. For example, type Default for the SQL Server Instance name or type the named instance.
- Specify the mode of authentication used for the SQL Server: SQL Authentication or Windows Authentication
- If using SQL Server authentication, type the username and password for the SQL Server.
- If using Windows authentication, provide the Windows domain, username, and password. Use either the local or domain username.
- Click Test to check connectivity with the SQL Server.
- Click OK.
- Click Save.
Microsoft SQL Server is unable to connect to a Microsoft SQL Server that has SSL enabled. This is due to the inherent limitation of the OpenSource JTDS driver used to connect to the SQL Server.Back to Top
Report display problems
- What to do if data does not appear in reports
- Running a scheduled task to import data into reports
- Viewing collector errors in the Collector-Manager log files
- Do the following to check the Collector Manager logs:
- View /opt/APG/Collecting/Collector-Manager/microsoft-sqlserver/logs/collecting-0-0.log.
- Verify that the last message in the log states that a certain number of raw values were collected using nine SQL queries. This message indicates the collector is working.
For example: <instance_master> collect done: 2,735 new raw values collected in 0 seconds 527 ms using 9 SQL queries
- Check for any Java exceptions and warning messages.
- If collector errors exist, do the following:
- Ping the Microsoft SQL Server using the fully qualified domain name (FQDN).
- Replace the current user, which is the username and password set during SolutionPack installation to retrieve data from the Microsoft SQL sever, with a user with SYSADMIN rights.
- Use telnet to check if the Microsoft SQL port is accessible from the SQL collector server.
- Reconfigure the SolutionPack by navigating to .
- Under SolutionPackBlock, click the pencil next to Collector to reconfigure the settings.
- To edit advanced connection settings, you may need to manually edit the SQL collector configuration file.
- If no errors exist and the last message in the log indicates that nine queries were executed, do the following:
- Wait a minimum of three polling cycles. By default, one polling cycle is fifteen minutes.
- Navigate to .
- Under the Backend category, run backend/Default/import_variable_status.task.
- Refresh the reports after fifteen minutes.
- Make sure the Frontend is attached to the database used by the SQL collector.
Before you begin
Allow at least three polling cycles to pass before manually running the scheduled task.
- Click Administration.
- Click Centralized Management.
- Expand Scheduled Tasks.
- Click Database.
- Select the import-properties-Default task.
- Click Run Now.
- Confirm success in running the task in the Last Result and Last Result Time columns.
- Click Administration.
- Click .
- Expand Collecting.
- Click the Collector-Manager for your collector instance.
Collector-Manager::<Collector-Manager instance> - <host_ID>
- Expand Log Files and click the View File icon to review the error messages.