Assess your System (Part 2of3) - SQL BPA

  • Article

 

As a second tool to assess your system configuration, today I am going to post about SQL Server BPA (another acronym!) aka SQL Server Best Practices Analyser (better).

 

We have 2 versions available:

 

- one for SQL Server 2000 (https://www.microsoft.com/downloads/details.aspx?familyid=b352eb1f-d3ca-44ee-893e-9e07339c1f22&displaylang=en)

- one for SQL Server 2005 (https://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en)

 

And no, I hadn't forgotten about 2008. In 2008 there won't be a standalone BPA tool but there will be something much much better, but for this you have to wait till next blog post (Assess your System (Part3of3)) :-)

 

 

1) Before we start talking about what is checked\performances impact etc etc I want to comment which version you should choose when assessing your system.

 

Given that:

- 2000 BPA requires a 2000 DB to store the data to be analyzed and can only assess a 2000 DB

- 2005 BPA requires a 2005 DB to store the data to be analyzed but can assess both a 2000 DB and a 2005 DB plus comes with additional features and checks

 

...it would be better to use the 2005 version even when assessing a 2000 implementation, by connecting remotely from another installation.

 

2) 2005 BPA new Features

 

- You can scan also SSAS, SSRS and SSIS on your 2005 implementations. You can only scan the DB Engine on your 2000 implementations.

- Auto Update Framework- Every time BPA is started, it cheks on the Microsoft website if rules have been updated or new rules added

- Command line version (not available in 2000 BPA)

 

Other new features can be found at:

SP2/BPA Frequently Asked Questions https://blogs.msdn.com/sqlrem/archive/2007/01/12/SP2-and-BPA-FAQ.aspx

 

3) What is Checked?

 

Best place to look at to have a comprehensive list of the checks done is the help tool which comes with BPA. There is all the information you might be interested in.

 

4) Performances Considerations

 

You are retrieving data from your target SQL Server instance, inserting this data on the BPA database and then analysing this data. Guess what? You need a bit of RAM and a quite CPU to perform calculations :-)

 

Therefore, as you can install the tool remotely, better having it on you local machine, together with the BPA database.

 

Hope this helps!

 

- Beatrice Nicolini -