Problem
Database Administrators are often asked questions like “are all the SQL Servers within an organization configured according to the industry standards?”. In this tip, you will see how a Database Administrator can quickly use Microsoft SQL Server 2005 Best Practices Analyzer to analyze a SQL Server instance to determine whether it is configured according to the best practices or not.
Solution
The Microsoft SQL Server 2005 Best Practices Analyzer (BPA) is an excellent tool which is available as a free download from Microsoft. Using the BPA tool, a DBA can quickly gather information from Microsoft Windows and SQL Server configuration settings. The BPA tool basically uses a predefined set of SQL Server 2005 recommendations and best practices to identify potential issues within the database environment. You can download the latest version of Microsoft SQL Server 2005 Best Practices Analyzer (BPA) for free from the following link.
1. Once you have installed Microsoft SQL Server 2005 Best Practices Analyzer (BPA). You can use the tool by clicking Start | Programs | SQL Server 2005 BPA | SQL Server 2005 Best Practices Analyzer. In Microsoft SQL Server 2005 Best Practices Analyzer, select Go to Welcome Screen to start using the tool as shown in the snippet below.
2. In Welcome to the Microsoft SQL Server Best Practices Analyzer screen, you need to choose the very first option Select options for a new scan as shown in the below snippet.
3. In Start a New Best Practices Scan screen, you will find different options available to scan such as the SQL Server Instance, Computer or a File. There are two different types of scanning available namely Limited or Detailed. I would suggest you to use Detailed Scan when you are running this tool for the first time on an Instance or on a Server to get the complete configuration information.
4. Once you have selected SQL Server Instance Name option as shown in the snippet above, you will be asked to provide information like SQL Server Component, SQL Server Instance Name, Authentication Mode, Login and Password. Once you have provided the entire set of information necessary, click Next to continue.
5. In the Configure Database Engine screen, you can choose the databases against which you want to run the BPA tool to analyze the best practices as shown in the snippet below. Click Next to continue.
6. In Start a New Best Practices Scan screen, you need to enter a name for this scan and click on Scan selected components to start scanning.
7. Once the scan has completed successfully, you will get the high level summary of the scan as shown in the snippet below. You can view the detailed report by clicking View a report of this Best Practices scan.
8. In the View Best Practices Report screen, you can see a very detailed report with respect to the scan which you have performed. There are three different ways by which you can view the report namely List Reports, Tree Reports and Other Reports.
9. If you want to view any report at a later time, you can do so by selecting Select a Best Practices scan to view from the right panel and then by selecting the report of your choice.
Next Steps
- Be proactive and check out your SQL Servers to ensure they are meeting the industry best practices, so you can confidentially answer your management when they come to ask you about the environment.
- Additional tips on SQL Server Configuration can be found here.
- Check out these related tips:
- Read all my previous tips.
