Best Practices Analyzer for SQL Server 2005

Best Practices Analyzer for SQL Server 2005

Rate This
  • Comments 36

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

Leave a Comment
  • Please add 7 and 4 and type the answer here:
  • Post
  • It has been a crazy, hectic week.  I haven't been able to make any posts this week, but I will...
  • 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)
  • 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

  • it would be nice if the BPA warns about implicit type conversions within procedures and statements.
  • 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
  • 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.
  • When you create a temp table it should warn you if you don't specifiy NULL or NOT NULL for the columns.

    Tim S
  • Thank you to everybody who posted comments in newsgroups, forums, and on the first SQL Server™ Best Practices...
  • 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.
  • 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).
  • 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.
  • 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.
  • 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.
  • 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)

  • "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.
Page 1 of 3 (36 items) 123