Best Practices Analyzer for SQL Server 2005


I’ve seen a few posts in forums and newsgroups requesting Best Practices Analyzer support for SQL Server 2005.  Good news… we just started developing a new version that will work with SQL Server 2005!  If you aren’t familiar with this type of utility, check out Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0 or Microsoft Exchange Server Best Practices Analyzer Tool v2.5.


It is very early in the development cycle and we’d like to hear your thoughts on the following questions:


1) Is a Best Practices Analyzer tool something that you feel is important for SQL Server 2005?
2) What versions of SQL Server should this support? (SQL Server 2005 only, SQL Server 2000+)
3) What components should this support? (SQL Server Engine, Analysis Services, Reporting Services, Notification Services, Integration Services)
4) What features would you like to see in it?


I’ll post the results of our plans once things become clearer.


Thanks,
-Paul

Comments (36)

  1. It has been a crazy, hectic week.  I haven’t been able to make any posts this week, but I will…

  2. Mr_T says:

    You are looking from some customer feedback so here it is. I thought the SQL Server 2000 BPA tool was great. I rallied to have it included as part of our SDLC. This ensured that no new developments would need revisiting in the near future due to depreciated functionality. It also ensured that the code review process was less work through ensuring everyone had qualified schemas etc. The area where this fell over however was to do with formatting and naming conventions. Most dev shops would have some form of standard naming convention and SQL layout. It would be great if the BPA tool could have done this as well. Probably a difficult task given that dev shops do things differently.

    1) BPA for 2005 is definitely something I feel is important and look forward to seeing a new one.

    2) Supported versions: it would be good if the tool supported 2005 & 2000 however if that meant that extra functionality culled I would be happy to stick with two tools.

    3) Supported components: all would be good, but at a minimum you really need to look at the engine & reporting services. You probably need to consider the main components in SQL server express edition as they are the ones which will most likely be used the most.

    4) Static analysis type things: naming conventions, complexity factors (sp’s), optimization hints, general best practices, depreciated functionality (ie where is the next version of SQL server heading – maybe a little difficult to determine at this stage?)

    Oh yeah, one more thing which was a bit annoying in SQL Server 2000 BPA. If you ran the tool to check the compatibility of an SP, you had check through each category searching through the list to find if your object was listed. It would be GREAT to list violations by object as well rather than just category!!! The data was all in the db so we simply created a sp which did it for us. Would have been nicer in the tool.

    Tim (aka Mr_T)

  3. TiVoTim says:

    I would still like a T-SQL best practise check on the use of formal parameters.

    exec sp_help @objname = ‘tablename’ — This would check as OK

    exec sp_help ‘tablename’ — This would check as BAD missing  formal parameter name

    Tim S

  4. baddy says:

    it would be nice if the BPA warns about implicit type conversions within procedures and statements.

  5. MikeA says:

    I tried out the best practices utility for SQL Server 2000 and found it informational, but of course never had the time to go back and actually update my code. It may have been more useful during the development cycle. I think if I had the title of DBA I may have been more inclined to use it also.

    Either way, I think the tool could be very helpful moving forward. Here’s a few additions to the previous suggestions:

    1) Include a ‘create script’ option which would script out changes to fix issues

    Search for code which use table.* instead of descrete column names

    2) Objects which the definition does not match the object name. For example, the object name is dbo.usp_getinfo but the definition scripts it as user.usp_getinfoNEW because you created the view/sp as user with a name usp_getinfoNEW, then used sp_rename to rename it and sp_changeobjectowner to change the owner

    3) Views which are no longer valid (contain underlying tables/views which have been deleted or which contain columns not in a table/view)

    4) Supported versions: No need for SQL Server 2000 support, the previous tool works fine for that. I would focus efforts on SQL 2005

    5) Supported Components: all would be nice, but it would be much more beneficial to get the SQL Engine one soon rather than waiting for all services and delaying the release IMO

  6. sideshow says:

    I think that the BPA is a fantastic tool!

    i woud love to seeeeeee…..(and maybe i have just missed it)

    1) 2005, 2000 Supported, especially if major new functionality implemented, i think that there is a lot of life in 2k yet.

    2) A command line interface so that it can be included in an automatic build process. ie BPA check fails database build step fails.

    3) Related to above output to xml, database, n-format.

    4) Sooner rather than later for releases. (is "yesterday" a realistic timeframe for you :)), DB Engine first then Other Services.

  7. TiVoTim says:

    When you create a temp table it should warn you if you don’t specifiy NULL or NOT NULL for the columns.

    Tim S

  8. Thank you to everybody who posted comments in newsgroups, forums, and on the first SQL Server™ Best Practices…

  9. Phil S says:

    Have just found this great tool, would like to see some rule customization and adding new rules to make it fit our standards more closely.

  10. DavidB says:

    Personally, I am really looking forward to this product being released. We have integrated SQLBPA 2000 into our normal processes for configuration monitoring and reporting and have implemented Reporting Services around the output to make things easily reportable. A great tool for a great price : ). Recommendations as follows;

    -Needs to support 2000 and 2005 with the repository capable of being housed on either platform.

    -It would be nice if it would support the scanning of other services in addition to the base engine but if that is going to hold up the initial release then I would say pass by those components to get the initial release of the engine scan out there and available. Could allow for some great testing for a follow-up release with enhanced scanning and fixes.

    -I would like to see integration of the Microsoft Baseline Security Analyzer in this product as well. That would provide a great view into standard configurations as well as security considerations AND would allow for the auotmation of the runs and reporting of the Security Analyzer which is not available today (to my knowledge).

  11. Stephen Rosenthal says:

    I’ve been a regular user of the sql bpa on 2000 for some time. I have well over 100 databases and the tool is great for getting an overview of security & compatiibility issues. I think it’s great and would be interested in seeing a beta for 2005 when available. Like folks above I would love some suggested fixes, but even a report that could be sent to developers with in depth info on the various issues would be much appreciated. Especially if that report is sorted by database. sqlbpa scans rules, then for each rule give a breakdown of exceptions I would love to see an option to sort by server then database then rule-results. If you are to take action on an item it’s likely that you will do this on an aplication by application basis.

  12. Carmen Lipscomb says:

    I have just started using the DBA as part of our change management process, and I find it very useful.  So, yes, I think we need an analyzer tool for SQL 2005.  Additional features that I would like to see in the 2005 version include the abilitiy to specify individual database objects to analyze and, like Mr. Rosenthal, a configurable report to send to the developers.  For example, when Developer X sends me a script for deployment that contains one create stored procedure and two alter databases, I would like to run the analyzer on just the three affected objects and generate a report that can be sent back to Developer X with the good and/or bad news.

  13. The Aethyr Dragon says:

    I love to see the new version support both SQL 2000 and 2005. One of the things that I’d appreciate is a script/utility that can be run on a remote server so that the resulting data can be imported into a local repository for processing and reporting. It’s not always feasible to run any kind of  installation on a remote domain. We are often asked to do once-off checks for clients and this function would be invaluable.

  14. Denis the SQL Menace says:

    Is the SQL Server 2005 Best Practices Analyzer available yet?

    I noticed the following webcast:

    TechNet Webcast: Using the SQL Server Upgrade Advisor and New SQL Server 2005 Best Practices Analyzer Tools (Level 200)  (http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032306503&EventCategory=4&culture=en-US&CountryCode=US)

  15. Svetlana says:

    "SQL Server Best Practices Analyzer " and "SQL Server Health and History Tool (SQLH2)" are my favorite SQL Server tools. They helped me a lot with my DBA tasks.

    Now I work in a company where we have SQL server clusters. SQLH2 doesn’t work properly in clustered environment. I have not had chance to test sqlbpa on cluster, but I think it’s important to have these great tools in clustered enviroment. May be there could be some "best practices" for SQL Servers on clusters.

  16. Jim Curry says:

    I’d like to see the command line functionality expanded. I am currently running the analyzer daily and publishing the reports via SQL reporting Services. I have launch the Analyzer to manually drop the compliance reports. I’d like to create a SQL Agent job to automate the process.

  17. Swanie says:

    I’d love to see the equivalent in Analysis Services.  Is this on anyone’s radar?

  18. PaulMest says:

    Swanie said: "I’d love to see the equivalent in Analysis Services.  Is this on anyone’s radar?"

    Yes, we are working with the BI team to provide some rules for SSAS and SSIS.  This was covered in the webcast which you can view on demand at microsoft.com/webcasts/.

    Paul A. Mestemaker II

    Program Manager

    Microsoft SQL Server Manageability

  19. sharond says:

    Yes, I would like to see the BPA work with 2005.

    Should support 2000, 2005, and future versions.

    Should analyze all components.

    Should be more widely publicized. I use it in my classes and find that most students don’t know about it.

  20. Ian Ippolito says:

    Paul,

    YES, YES, YES! 🙂 This is something that is desperately needed as the product gets more complicated and easier for a person to inadvertently "screw up".   It’s now 7 months since this was posted announcing it would be coming.   How are things going…and is there an anticipate release date?  

    Ian Ippolito

  21. Joel Cabot says:

    Do you plan to have a beta or CTP version of BPA for SS2k5?.  Joel Cabot

  22. Glenn Berry says:

    I used the SQL Server 2000 BPA a lot, and got a lot of value from it. Any update on when the 2005 version will be available?

  23. Rustom says:

    I believe Microsoft should put together a best practice tool which will focus on SQL Server 2005 including all of the components.  This would be a great tool to add to the aresenal escpecially that we have now been teased with this tool from the SQL 2000 days.

  24. Tim Hatcher says:

    Hi there, SQL 2005 has been here for a while now, is there any movement on the release of the Best Practices Analyser that you mentioned was "in production" ?

    Thanks

  25. Granted says:

    Any word at all on this tool becoming available. A beta? A CTP? We’ve got months old announcements. Those of us who found the tool useful as a mechanism for cleaning up databases under development would sure like to see a new version for 2005. Anyone from MS listening to this one any more?

  26. twtterence says:

    I think it should be support both SQL Server 2000 and 2005.

    As I want to enjoy the following features from the new version of Best Practices Analyzer:

    1. It should support "Customed Rule" defined by DBA. Just like FxCop.

    e.g. The current rule of user object naming could cover more areas: constraints naming standard, all objects must have a customed name instead of using a irregular name generated by SQL Server.

    If the rules can be defined by DBA, then the Best Practices Analyzer would become more flexible.

    2. It would be better if it supports .sql or .txt file types. As our office use weekly release, the DB scripts (.sql files) are centralized in a repository for each release. We would like to automate the best Practices Analyzer for reading the .sql files before each release. Reading .sql files could help us a lot on the automated process.

    Best Practices Analyzer is a great tool for database quality improvement process. I look forward to enjoying the new release features in the near future.

  27. twtterence says:

    As I may interesetd in looking at the SQL query provided in a SQL Trace file only, if the Compliance Report supports filtering criteria, e.g. by database/file, it would be great!!

  28. Stephen says:

    Does anyone know if the SQL 2005 Best Practices Analyzer has been lanuched yet?

  29. Greg says:

    I think the SQL BPA is a great tool! But the latest release I’ve come across so far, is the February 2007 CTP. When will a release that can be deployed in production be released?

  30. disney Ben barnes <a href= http://ben-barnes.barerube.cn >Ben bell barnes</a> [url=http://ben-barnes.barerube.cn]Ben bell barnes[/url]

  31. Kevin Edwards says:

    I can’t seem to get the BPA to work with a clustered sql 2005 resource – is this supported?

  32. I’ve seen a few posts in forums and newsgroups requesting Best Practices Analyzer support for SQL Server 2005. Good news… we just started developing a new version that will work with SQL Server 2005! If you aren’t familiar with this type of utility, chec

  33. RednivaR says:

    i just need to enquire if SQL 2K5 BPA is Cluster Aware , i installed on a Cluster but it didnt Worked , Please assist

  34. Rafael says:

    I have a problem.  Server 2003 R2, SQL 2005 SP3, Installed.  When I get to the screen "Configure Database Engine" I can’t select a DB, I see the 4 defaults and the one I have for SCCM however, there are NO CHECKBOXES like on this person screen shot example:  http://www.mssqltips.com/tip.asp?tip=1302

    I’m stuck because I can’t select the DB, clicking "Select All" doesn’t work because I have no check boxes.  Any help would be appreciated.