Freigeben über

Introducing the SQL Server 2008 R2 Best Practices Analyzer (BPA)…

  • Artikel

Some of you may have noticed I haven’t posted a blog in some time. Well, I’ve been a bit busy working behind the scenes on a new tool we released this weekend, the SQL Server 2008 R2 Best Practices Analyzer (BPA). You may remember that I announced this new tool back in April at the PASS Europe Summit on this post.

This past week final development and testing were completed on the tool and it is now available for you to download at the following location:

https://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591

I will spend more time this summer posting a series of blogs on various aspects of the tool, the rules contained in it, and possible usages and benefits for you. While the tool was developed and produced by the product team, the CSS team was a major part of the project. The CSS SQL Escalation team provided much of the rule logic, design, and guidance about how the BPA tool would scan for known configuration settings or look for critical events to alert DBAs. In a way, running this tool is like getting CSS SQL Escalation advice without having to call CSS.

For now let me give you a few facts to help you get started:

  • Even though called SQL Server 2008 R2 BPA, it supports both SQL Server 2008 and SQL Server 2008 R2
  • Supports Windows Server 2003, Vista, Windows Server 2008, Windows Server 2008 R2, and Windows 7
  • Requires you to first install the Microsoft Baseline Configuration Analyzer 2.0 and Powershell 2.0 (only on WS03, Vista, or WS08).
  • Comes with about 130 rules including applicable rules from SQL Server 2005 BPA ported to the new tool plus many other new rules covering Engine, Security, Replication, Reporting Services, Analysis Services, Setup (Servicing), and SSIS.
  • Supports remote scanning through MBCA or through remote powershell (the help file explains all of this)

We have also created a series of KB articles that covers every rule in the tool with reference information in the article pointing back to the rule. The articles can be found as you use the help file or click on the More Information Link in the tool results.

However, you can also find our articles with some keyword searching on the internet:

Go to https://support.microsoft.com and search on “SQL Server 2008 R2 BPA”. You will find a bunch of results like this:

image

Here is an example of one of these articles that talks about a rule to check for a recent “clean” CHECKDB:

image

I invite you to download our new BPA tool and give it a spin. While formal feedback is done through Microsoft Connect (Use the “Send Feedback” link in the help file), I’m happy to take on comments and questions on this blog post once you download and try it out.

 

Thank you,

Bob Ward, Microsoft

Comments

  • Anonymous
    June 20, 2010
    Hi Bob I've downloaded BPA but each time I try to install on my machine (Windows Server 2008 R2 STD x64) I get an "Access Denied" error when the installer tries to run the "Set-WSManQuickConfig -force" command. Strange enough, if I run the program by myself It works perfectly. I've also tried to run the installer using an administrative account and I get the same error. Any idea that can help me to install BPA? Thanks Davide

  • Anonymous
    June 20, 2010
    We've seen this happen on non-domain machines.  We are going to have a blog post on the setup scenario later today in regards to this error.  The workaround you can do is to run the msi the following way to bypass teh PowerShell steps in the installer. msiexec /i SQL2008R2BPA_Setupx64.msi SKIPCA=1 This will skip the Custom Actions that run the PowerShell commands, and you will have to run them manually.  It has been working fine on our end in the different Domain scenarios we looked at. Thanks, Adam

  • Anonymous
    June 20, 2010
    Hi Adam THANKS! It now works :-) Davide

  • Anonymous
    June 20, 2010
    Btw, sqlblog.com/.../installing-sql-server-2008-r2-best-practices-analyzer-on-a-stand-alone-machine.aspx :)

  • Anonymous
    June 30, 2010
    When I try to install I get a message that says, "There is a problem with this Windows installer package. A program required for the install to complete could not be run" I am trying to install on Server 2008 x64.

  • Anonymous
    June 30, 2010
    Sorry After my first post I tried the install method posted in Davide Mauri's blog and it did work. sqlblog.com/.../installing-sql-server-2008-r2-best-practices-analyzer-on-a-stand-alone-machine.aspx

  • Anonymous
    July 12, 2010
    If your logins are mapped to AD groups instead of individual AD users, you may see this error when running the BPA: "Login does not exist or is not a member of the Systems Administrator role" I worked around this by creating a login explicitly for the AD username I was using.

  • Anonymous
    July 28, 2010
    Adam - Please ping me offline, this works for 2008 as well right?  It's not all that clear from the download center.

  • Anonymous
    August 09, 2010
    Running clustered SQl 2008 R2 on Windows Server 2008 R2, both 64 bit. I belong to the local windows administrator's group on both nodes of the (active/passive) cluster - both as an individual login and as a member of a domain group. My AD account is in the SQL sysadmin group.  Running BPA on the active node keeps telling me the login is not found in the local SystemAdministartor's group (but it is).  I noted that the local computer name is the name of the active node, however the instance of sql server is the virtual sql server.  Is this why it cannot find me in the sysadmin role?  We are listening on a non-standard TCP port only, with shared memory also enabled.  Any help appreciated. Thanks, Ken

  • Anonymous
    August 08, 2011
    I'm having the same problem as Ken.  Also have the exact same specs as described, anyone have any advice?  Thanks!

  • Anonymous
    November 14, 2011
    Good Day, I installed BPA , however I get the error that the login does not exist or the login does not have sysadmin privileges . Which id is BPA referring to ?

  • Anonymous
    November 21, 2011
    For users getting "not part of sysadmins" errors, you need to explicitly add your account to sysadmins, access via group membership is not sufficient.  So yes, you need to violate a Best Practice to run the Best Practice Analyzer.  Better yet, someone submitted a bug, and it was closed as "won't fix". connect.microsoft.com/.../user-running-sql-2008-r2-bpa-needs-to-be-explicitly-added-to-sa-role

  • Anonymous
    January 30, 2012
    Good Day, I installed BPA as instructed, however I still get HHTP errors (12152 errors ) when I tried to do a remote scan on one of my sql servers . Any ideas ?

  • Anonymous
    February 13, 2012
    TITLE: SQL Server Setup failure.

SQL Server Setup has encountered the following error: Object reference not set to an instance of an object..

BUTTONS: OK

i get this error when i install the software.  Any suggestons as to how to fix this.

  • Anonymous
    July 16, 2012
    I always got "Analysis Cannot be Performed message". Wondering how I can get tech. support for this tool, BPA.  Call me at 202-382-7221 or email: tom_an@hotmail.com

  • Anonymous
    January 14, 2014
    Good Day.  I need to be made administrator of SQL.  for the School Board Cost Report.  FLDOE recommends MySQL, and our district uses SQL 2008 R2 Enterprise.  What do you recommend would be easier to administer and do you have the training in using your SQL?