I recently had the chance to go through a so called Risk and Health Assessment Program for Microsoft SQL Server (SQLRAP) at one of our clients. The SQLRAP assesses your SQL Server implementations to ensure proper productive use. It is an excellent opportunity to review the best practices and perform a validation of your client environment by a Microsoft SQL Server Engineer. The SQLRAP program will collect data from the SQL Servers, perform a very thorough analysis, and generate reports as well as recommendations. After that, you will be able to talk to the Engineer about the findings. Let’s see now in more details how it works.
To start our Risk and Health Assessment Program for Microsoft SQL Server (SQLRAP) we first have to download and run the scoping tool.
The scoping tool will verify connectivity requirements, permissions requirements, and other prerequisites for the successful execution of the SQLRAP toolset. Before running the scoping tool, Microsoft .NET Framework 4.0 and Microsoft Baseline Security Analyzer 2.1.1 have to be installed.
The scoping tool runs without making any changes to our environment, it simply uses standard operations such as WMI queries, LDAP queries, port queries and so on. It is completely read-only. It has minimal network impact and also doesn’t overload the target system. It may take it several minutes or even hours to complete depending upon the size of the environment.
The scoping must be run from a computer that has full network connectivity for all SQL Servers targeted. This should be the same computer that will be used during the SQLRAP engagement. It must be run using an account that has Enterprise Admin privileges and local administrative access on every SQL Server targeted. Ideally, it should be a dedicated account that will also be used during the SQLRAP engagement. The account does not need to remain after the engagement is complete.
The scoping tool runs through below areas to collect data and create report:
- Correct .NET Libraries Installed
.NET Framework version 4.0 (Full) must be installed to ensure Tools are able to perform properly.
- Operating System Memory
Checks to determine if the minimum recommended amount of memory (2GB) is installed on the Tool Server. Having less memory may result in the number of test cases fail with “Out of Memory” errors.
- Operating System Processor
Checks to determine if the minimum processor speed is available in the Tool Server.
- Latest Version of Windows Update Agent Installed
Windows Update Agent (WUA) framework must be installed on the target servers in order the MBSA test case could scan the target servers for missing updates.
- Wmi Check (Win32_ComputerSystem)
Validates the specified WMI class is available on each target system. Most of the data collection methods used during a RAP rely heavily upon WMI. If WMI is unavailable either because of lack of connectivity or permissions it is effectively as if the target system is completely unreachable from a data collection and analysis perspective.
- Port 135 (End Point Mapper)
Validates connectivity over TCP port 135 (End Point Mapper). This port is used during RPC communication to determine the listening port of an RPC service.
Performs an ICMP ping against the NetBIOS name of each target system. Certain tests during a RAP leverage ICMP pings to determine if a target system is available.
- Remote Registry
Validates remote Registry access is available by connecting to the HKEY_LOCAL_MACHINE Registry hive of each target system.
- Admin Shares
Performs a WMI query against the Win32_Shares class to enumerate the default administrative shares on each target system to validate they exist. This test only validates the shares exist, it does not attempt to connect to them.
- Query Logman
Logman.exe is used during the Risk Assessment to collect performance counters from the tools machine. This check ensures Logman.exe works against remote machines by running ‘logman.exe /query /s [machine]’.
- Windows Update Service Running
Validates the Automatic Updates/Windows Updates service is running on each target system. This service is required in order to evaluate the target systems for missing security updates. If this service is disabled intentionally for security reasons as of now, consider enabling the service for brief time during the SQLRAP engagement.
- MBSA Installed
Microsoft Baseline Security Assessment (MBSA) is used during the Risk Assessment to determine missing hotfixes. This check verifies that MBSA is installed on the local machine.
- DCOM Installed
Validates Distributed COM (DCOM) is enabled on each target system. Most of the data collection methods used during the risk assessment require DCOM be enabled.
- Tools Machine – OS Version
Determines the operating system version of the computer the scoping tool is run on. The tools machine must run Windows Server 2003 with Service Pack 2 or higher. Older server operating systems or workstation operating systems are not supported as the tools machine.
- Tools Machine – OS Language
Determines if the installed operating system language is English on the computer running the tool. Currently, the tools machine must run an English language Windows operating system. Target systems may be non-English OS.
- Tools Machine – OS Locale
Determines if the installed operating system locale is English (United States) on the computer running the tool. Currently, the tools machine must have its locale set as English (United States). Target systems may have non-English (United States) locale.
- Port 139 (NetBIOS) and 445 (SMB)
Validates connectivity over TCP port 139 (NetBIOS) and 445 (SMB). TCP port 139 is used during NetBIOS communications. TCP port 445 is used when remotely accessing the File and Printer Sharing (Server) service components. This check verifies access to at least one of these ports is available.
- SysAdmin on Databases
Checks to verify that the current user is a sysadmin on the content database instances. This is required in order to run the SQL BPA and a number of other test cases.
- OS Supportability Check
Determines if the target operating system supported or not. A RAP cannot be conducted against environments running unsupported products.
- OS and SP RAP Supportability Check
Determines if the target operating system and service pack have been tested for compatibility with the RAP. A RAP can be conducted against untested environments, however the RAP content is not validated or ensured to be functional. The delivery should proceed at the risk of the customer.
The result for our future RAP dedicated server is:
We have an error for Tools Machine – OS Locale here, so we can click the next button to see what it means and correct it. But it’s a good starting point to have just one error!
The local default language is set to Swiss French, code 100c, and it should be set to English (United States), code 0409, for the SQLRAP.
So, we have to change it to fullfill the requirement and rerun the scoping to have 0 failed and 0 warnings.
When all errors are resolved we have to generate a report for the scoring and mail it to our Technical Account Manager (TAM) for validation of our environment.
The first step of the SQLRAP, the scoring, is done.
RAP as a Service for SQL Server
RAP as a Service for Microsoft SQL Server is a proactive service delivered by a Microsoft accredited engineer to diagnose potential issues with your Microsoft SQL Server environment. This service is available for Microsoft SQL Server with up to five instances running SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012.
It will focus on the following aspects of your SQL Server environment:
- SQL Configurations
- SQL Database Design and Options
- SQL Security
- SQL Performance
- SQL Operations and Maintenance
- SQL Error Log Analysis
- SQL Instance Properties
- SQL Server 2012 AlwaysOn Health
- Cluster Resources
- Operational Excellence
Now that we have scoped our environment and installed the prerequisites tools, we can download, install and run the “RAP as a Service for SQL Server” from our Dashboard. Access to this page should be granted by your TAM.
After having installed the tool “RAP as a Service for SQL Server”, you can run it and access the frontend which will give you the possibility to assess up to five instances of SQL Server, collect data, and submit this data for analysis. The data is collected remotely and allows you to run the assessment on your own schedule.
On the first screen, click Next:
Choose the assessment file which was created during the installation process:
Click Next and confirm your assessment in the following screen to have the possibility to determine what to assess:
Enter an SQL server name and click “Discover my environment”:
The tool will check your environment configuration. When it’s done, you can click the Next button to start collecting data from your environment:
When the “collect data” is done, the Next button will be available and you have the possibility to submit your data after having entered your submission access key, which is available on your dashboard:
Data will go through the cloud via a secured transmission and we are able to view the result immediately on the dedicated dashboard.
View Collected data:
Or have a look at View Issues:
For each issue you have a description, all affected objects, recommended reading, and how to troubleshoot.
Now that our environment has been analyzed, a Microsoft accredited engineer will come to you to review the findings, provide recommendations on each issue, show you the configuration best practices, as well as for knowledge transfer, and to build a remediation plan with you.
He will also go over the SQL Server Profiler, the performance monitor, and some others tool. He will also make sure that you know how to use it to perform a self-analysis.
The result from your SQLRAP will remain available on your online portal for one year. You have the possibility to reassess your environment during one year to see if changes are beneficial or assess other instances (different from the first five ones).
- Result on an online portal
- Reassessment possible for one year, if wanted
- Access to best practice updates for one year
- Assessment of others SQL Server possible
- Reports online for issue findings
- Knowledge transfer
This experience was really meaningful and even if you are familiar with SQL Server best practices, you will probably discover new ones.
It is always good to refresh its know-how and have the possibility to discuss with an SQL Server Microsoft accredited engineer.
SQLRAP provides up-to-date recommendations and allows you to keep a healthy environment and be more proactive during the configuration of a new server. I will continue to assess my environments during the next year with this tool and see if there are new features or best practice updates.