Best Practices Analyzer for SQL Server 2005

Comments

# Jeff Barnes - MCSD said:

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

07 April 06 at 11:58 AM

# Mr_T said:

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)

07 April 06 at 9:40 PM

# TiVoTim said:

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

07 April 06 at 11:01 PM

# baddy said:

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

09 April 06 at 11:06 AM

# MikeA said:

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

10 April 06 at 9:21 AM

# sideshow said:

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.

18 April 06 at 8:58 AM

# TiVoTim said:

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

Tim S

20 April 06 at 4:35 PM

# SQL Server Relational Engine Manageability Team Blog said:

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

29 April 06 at 10:29 PM

# Phil S said:

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.

11 May 06 at 6:29 AM

# DavidB said:

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).

12 May 06 at 3:16 PM

# Stephen Rosenthal said:

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.

19 June 06 at 5:37 PM

# Carmen Lipscomb said:

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.

04 August 06 at 12:26 PM

# The Aethyr Dragon said:

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.

23 August 06 at 6:55 AM

# Denis the SQL Menace said:

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)

01 September 06 at 1:22 PM

# Svetlana said:

"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.

27 September 06 at 2:37 PM

# Jim Curry said:

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.

28 September 06 at 10:16 AM

# Swanie said:

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

03 October 06 at 6:21 AM

# Paul Mestemaker said:

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

12 October 06 at 4:23 PM

# sharond said:

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.

19 October 06 at 6:31 PM

# Ian Ippolito said:

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

22 November 06 at 5:07 PM

# Joel Cabot said:

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

07 December 06 at 9:47 AM

# Glenn Berry said:

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?

11 December 06 at 1:08 PM

# Rustom said:

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.

19 December 06 at 3:38 PM

# Tim Hatcher said:

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

10 January 07 at 6:45 AM

# Microsoft » Blog Archives » MSDE and SQL Server Manager - Easy Management of MSDE/MS SQL Databases said:

PingBack from http://chaespot.com/mssql/2007/01/11/msde-and-sql-server-manager-easy-management-of-msdems-sql-databases/

11 January 07 at 5:06 AM

# Granted said:

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?

12 January 07 at 8:13 AM

# twtterence said:

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.

30 January 07 at 5:42 AM

# twtterence said:

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!!

30 January 07 at 5:53 AM

# Stephen said:

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

03 April 07 at 6:30 AM

# Monte said:

Here it is guys!! (Ladies too!)....

http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

11 April 07 at 10:55 AM

# Greg said:

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?

07 June 07 at 2:55 PM

# ctl00$_$ctl00$_$ctl01$_$form$_$tbname said:

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

18 May 08 at 4:44 PM

# Kevin Edwards said:

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

20 May 08 at 12:22 AM

# Relationship Compatibility said:

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

07 June 08 at 10:44 AM

# RednivaR said:

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

24 July 08 at 7:18 AM

# Rafael said:

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.

10 August 09 at 4:52 PM

Leave a Comment

Comment Policy: No HTML allowed. URIs and line breaks are converted automatically. Your e–mail address will not show up on any public page.

Name (required) 

Your URL (optional)

Comments (required) 

  

Enter Code Here: Required

Remember Me?

About Paul Mestemaker