Share via

SP2/BPA Frequently Asked Questions

  • Article

I have been getting a lot of questions and hearing a lot of speculation about SP2 and BPA. Let me address some of the most common questions.

SP2

When will SQL Server 2005 Service Pack 2 be available?

SQL Server 2005 SP2 is currently available as a Community Technology Preview. This is a pre-release (beta) and should generally not be deployed in production environments. You can find out more information here: https://www.microsoft.com/sql/ctp.mspx.

It will be officially released to market (RTM) in Q1 2007.

I see there are a lot of great features in the December CTP of SP2. I can’t upgrade my DB engine, yet. Can I upgrade only the tools on my client workstation? Will this still work with SQL Server 2005 RTM and SP1?

Yes. If you want to test out the functionality of the tools, you do not have to upgrade your database engine. There are some areas of the product that may have required changes to the engine or stored procedures. These features should display a friendly message indicating that they will not work on pre-SP2 instances.

Best Practices Analyzer

When will SQL Server 2005 Best Practices Analyzer be available?

It will ship in Q1 2007 along with SQL Server 2005 Service Pack 2 RTM. You will find a link to it from the SQL Server 2005 SP2 Feature Pack. ****

What are some of the new features in SQL Server 2005 Best Practices Analyzer?

**

High impact rules

The SQL Server product team has worked closely with the SQL Server customer support team to understand what issues customers are commonly experiencing. **

Store Scans Results as XML

In 1.0, you would store your scan results in a SQL Server repository. This was good if your SQL Server was functioning. But if you were having problems or want to quickly send scan results to a fellow DBA, this was somewhat annoying. **

Store Scan Settings as XML

You can setup scans of servers and store them in XML.  This can later be used from the GUI or the command line tool. **

Interactive Reporting

After completing your scan, you can see a concise list of potential issues in your environment. Each rule will be accompanied by an article describing how it was detected, why it is important, what you should do to resolve it, and where you should go for more information. The best part is that you can pivot the reports to view issues by issue type or severity. For those of you with larger environments, you’ll appreciate the search functionality. So you can filter your report to find a specific term. **

Common BPA UI

You can automate SQL BPA scans using XML and command line parameters. Using the command line mode, you have the most granular control over which rules you want to run. If you want to check each of your 500 servers for compliance of one rule, the SqlBPACmd.exe is the tool to use. **

Auto Update Framework

Best practices are learned over time. The more customers that use our products and give us feedback, the better we channel that information back to the masses.  So... based on your feedback, we will be able to ship you additional rules/functionality. **

Cross-box Experience

You can use SQL BPA to check for best practice violations in SQL Server Database Engine, Analysis Services, and Integration Services. ****

Will we be able to create our own rules?

No. Unfortunately, we cannot support this. The SQL Server Best Practices Analyzer is using an extensible platform that was built by the Microsoft Exchange team. It is fairly easy to extend, but it is not an expected part of SQL Server’s strategic future. Ultimately, this means that if we allowed you to write custom rules, you’d expect it to work with SQL Server "Katmai" and SQL Server "Katmai" + 1. We haven’t figured out a way for custom rules to be supported long-term… so, for now, we are not going to allow it. ****

Who is the target audience for SQL Server 2005 Best Practices Analyzer?

Database Administrators/IT Professionals ****

We used SQL Server 2000 Best Practices Analyzer 1.0 to check our T-SQL code. It is very useful in our development process. Why are you getting rid of it?

The best practices utilities across Microsoft are more focused toward operations/management. We are aligning SQL Server’s BPA to the corporate standard. We agree that there should be aids in checking best practices in code, but don’t feel that BPA is the right tool for the job. Software like FxCop or plug-ins to Visual Studio and Management Studio would be the ideal location for such checks.

If you have more questions/comments, feel free to post them here.  Blogging is not my day job... so it may take a while before I can post an answer to address them :-)

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability

Comments

  • Anonymous
    January 18, 2007
    SQL Server 2005 Compact Edition has been RTM'ed (formerly known as SQL Server Everywhere). More information...

  • Anonymous
    January 19, 2007
    SQL Server 2005 Compact Edition has been RTM'ed (formerly known as SQL Server Everywhere). More information...

  • Anonymous
    February 02, 2007
    I'm glad to see that the tool Best Practice Analyzer is finally coming in a SQL Server 2005 version.

  • Anonymous
    February 07, 2007
    A pity the don't provide slip-streamed SQL 2005 release, which would save many hours of unnecessary work.

  • Anonymous
    February 12, 2007
    You wrote: "Can I upgrade only the tools on my client workstation? Will this still work with SQL Server 2005 RTM and SP1? Yes. If you want to test out the functionality of the tools, you do not have to upgrade your database engine. There are some areas of the product that may have required changes to the engine or stored procedures. These features should display a friendly message indicating that they will not work on pre-SP2 instances." However, the following bug: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249725 can cause some real problems if the UI tools are on SP2 and the server is on SP1. There is no friendly message here, you just see misleading informations in the UI. Please consider fixing the bug for the final release of SP2. Razvan

  • Anonymous
    February 21, 2007
    SP2の残はSQL Server 2005 BPAとPerformance Dashboard Reports

  • Anonymous
    February 25, 2007
    Recently Microsoft has release the much awaited SP2 for SQL Server 2005. Guys go and grab it – it has

  • Anonymous
    March 28, 2007
    The comment has been removed

  • Anonymous
    April 30, 2007
    I hear what you are saying about the T-SQL checking section, and that FxCop is the way to go, do you know if there are any plans or roadmap to update it to check T-SQL ? Thanks Tim