SP2/BPA Frequently Asked Questions
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