The intellectual property(IP) or other valuable information may be stored on SQL Server in your ISV product. Its value is more than credit card records (credit cards can be cancelled if lost, IP can’t). It may be the equivalent of a state secret for your firm. Microsoft provides two versions of its free Best Practices Analyzer Tools to check your SQL Server. One version is for SQL Server 2005 and the other version is for SQL Server 2008.

  • SQL Server 2005 Best Practices Analyzer. Download.
  • SQL Server 2008 R2 Best Practices Analyzer. Download.
    • SQL 2008 R2 BPA Whitepaper – Instructions on using SQL 2008 R2 BPA. Download.
    • SQL 2008 R2 BPA Demo -- Video on using SQL 2008 R2 BPA. Download.

Let us face the reality that you support a dozen different products and really do not have time to become a SQL Server expert – the Best Practices Analyzer saves you from having to learn a long list of items to review and how to check them. It provides information on a truly need-to-know basis so you can do your job well without needing to consume a lot of time.

SQL Server 2005 Best Practices Analyzer (BPA)

BPA does a whole system analysis of your SQL Server installation—including Microsoft Windows and SQL Server configuration and security settings—according to industry standards and best practices. BPA is very simple to use. It should be executed after the database is first created and then on a quarterly basis. Make sure you run BPA from your desktop machine and not on the SQL Server machine. You can use your desktop installation to do analyses of multiple SQL Servers.

Running SQL Server 2005 BPA

  1.  After downloading and installing BPA from the All Programs menu, select SQL Server 2005 BPA and then SQL Server 2005 Best Practices Analyzer to open the application.
    image
  2. The application Welcome screen will appear.
    image
  3. To begin, click Select options for a new scan.
    image
  4. The Start a New Best Practices Scan step will appear.
    image
  5. On your first run, select the Detail scan radio button and enter a name for the scan such, as “Initial Install”, then click Scan selected components.
    image
  6. A progress step will appear.
    image
  7. When the scan completes, a link called View a report of this Best Practices scan will appear. Click this link.
    image
  8. BPA will build the report and then present it.
    image     

Expected Exceptions

The BPA report may show an exception similar to that shown above;  some exceptions are by design for the product.  I will be following this post with additional short posts for each product, listing the expected exceptions. If I have not yet posted for your product, please email me with your results and I will provide personal feedback.

Either correct all other violations or document the reason for the violation. Running BPA at least every quarter is recommended to detect any changes that may have occurred.

 SQL Server 2008 R2 Best Practices Analyzer (BPA)

The SQL Server 2008 R2 BPA was released in summer 2010. SQL Server 2005 BPA functionality evolved into the Policy Management Framework in SQL Server 2008 SSMS to allow custom rules. SQL Server 2008 R2 BPA actually supports both SQL Server 2008 and SQL Server 2008 R2. It uses Microsoft Baseline Configuration Analyzer (MBCA) 2.0 and Powershell 2.0, which means that it is not a single download and install.

  • To install Powershell 2.0, you need to go to the support page, http://support.microsoft.com/kb/968929, and scroll down until you see a list of seven different installation packages, then select the appropriate one.
  • To install the Microsoft Baseline Configuration Analyzer (MBCA) 2.0, you need to select the appropriate version from its download page.

To illustrate this process and show some things that may complicate the process

Well, not entirely correct – the last step produced an error. Checking the application log I saw Microsoft SQL Server 2008 R2 BPA -- Error 1722. Fortunately, the BPA team created an auxiliary whitepaper cited above that identified the problem. Although I had installed Powershell 2.0, it was not configured. I executed the recommended command (verifying that it was being executed with Administrator privileges).

  • powershell.exe -NoLogo -NoProfile -Command Enable-PSRemoting –force

I tried installing once more. This time, I noticed that the second dialog told me about the above – but being human, I had ignored it as yet another EULA.
image

 

I encountered one more issue connected to my running SQL Server on an isolated network without a Domain Server (my recommended approach to reduce surface exposure of your SQL Server). This issue and its solution is also documented in the BPA whitepaper. I had to add a parameter to the command line.

  • msiexec /i SQL2008R2BPA_Setup64.msi SKIPCA=1

This resulted in a successful install. If you encounter additional issues, check the whitepaper – they did a good job covering all of the likely complications.

Running SQL Server 2008 R2 BPA

1. After downloading and installing the MBCA, you will find it listed under Programs. Click the Microsoft Baseline Configuration Analyzer 2.0 link to launch it.
image

  1. In the drop-down box, select SQL Server 2008 R2 BPA and then click New Scan
    image
  2. From the resulting Enter Parameters screen, check Analyze_SQL_Server_Engine and Analyze_SQL_Server_Setup at least. If you are using other components, you should check them also. If you are not using the default instance, then enter its name.
    image  
  3. The process may run for a few minutes.
    image  
  4. After its analysis, MBCA will present a report showing errors and warnings as shown below.
    image  
  5. Clicking an item will expand the details of the error or the warning.
    image
     
    1. The items listed may include issues that you are unaware of. The figure above could imply a failing hard drive – do you wait until it crashes or do you replace it in the next maintenance window?

  6. You should export the report and include it in your reports to management as an attachment to illustrate that you are doing due-diligence.
    image 

Expected Exceptions

The BPA report may show an exception similar to that shown above;  some exceptions are by design for the product.  I will be following this post with additional short posts for each product listing the expected exceptions. If I have not yet posted for your product, please email me with your results and I will provide personal feedback.

Either correct all other violations or document the reason for the violation. Running BPA at least every quarter is recommended to detect any changes that may have occurred. I recommend running SQL Server 2008R2 BPA on at least a monthly schedule.

Summary

Installing BPA can be a bit of an exercise but the benefits are huge. There are time savings from not having to do the checks manually and improved quality assurance by the thoroughness of coverage.

A simple illustration of an item affecting performance was found above, and is shown below:


In my case, I immediately knew how this happened. The machine originally had an early Windows OS on it and I did not redo the drives when I upgraded to Windows Server 2008. An easy human error to do, and one that BPA catches  clip_image021.

Running it on a weekly basis often will reduce your exposure risk. Often I have seen a critical problem fixed by doing ‘random’ changes that are not undone later. Your system may have been secure on the 1st of the month, but a support person may have accidentally made it vulnerable on the 7th. BPA will catch many of these human-ware errors.