Blog - comments

When I studied Oracle New Feature Guide "Media Failure: PDB SYSTEM Data File" , I was surprised tha...
Hayat Khan

Really a nice article to study.

Thanks,

Amol Bhoite

ana 100 goid

mokok

ana 100 gold

mokok

Bonjour,

Tout d'abord merci pour cet article. J'aimerai savoir si ACFS est gratuit ?

Chris

Chris
Blog Stéphane Savorgnano Risk and Health Assessment Program for Microsoft SQL Server

dbi services Blog

Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Risk and Health Assessment Program for Microsoft SQL Server

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.

 

Scoping tool

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.

  • Ping
    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.

SQLRAP dashboard:

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.

 

Benefits

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).

Keys benefits:

  • 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

Conclusion

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.

Rate this blog entry:
1

Stéphane Savorgnano is Consultant at dbi services. He has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. Stéphane Savorgnano is Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server implementation and maintenance. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. Stéphane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking / Financial Services, Chemicals & Pharmaceuticals, etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Monday, 21 April 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)
NewsOfficesContact

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter