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.
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: http://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 rulesThe 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 XMLIn 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
Interactive ReportingAfter 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 UIYou 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 FrameworkBest 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 ExperienceYou 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 IIProgram ManagerMicrosoft SQL Server Manageability
SQL Server 2005 Compact Edition has been RTM'ed (formerly known as SQL Server Everywhere). More information...
I'm glad to see that the tool Best Practice Analyzer is finally coming in a SQL Server 2005 version.
A pity the don't provide slip-streamed SQL 2005 release, which would save many hours of unnecessary work.
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:
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.
SP2の残はSQL Server 2005 BPAとPerformance Dashboard Reports
Recently Microsoft has release the much awaited SP2 for SQL Server 2005. Guys go and grab it – it has
Since the T-SQL checking is being removed from the final product, can this be factored out into a separate tool? I found it very useful for enforcing ANSI compliance, proper DB options, etc. with developers.
I still have to deal with SQL 2000 instances occasionally so if it can have separate compatibility levels that would be great. A command line tool using a configuration file would be excellent for QA teams.
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 ?