Optimize SQL Server administration with Microsoft’s SQL Server Best Practices Analyzer (BPA)
Image 1 of 10
SQL Server Best Practices Analyzer
SQL Server Best Practices Analyzer
In the past, you would have to tweak your SQL Server settings manually to find the ideal configuration. Now, Microsoft has released a tool called the Microsoft SQL Server Best Practices Analyzer (BPA). With this tool, you can test and implement a combination of SQL Server best practices and then implement them on your SQL Server. I am going to go over how to install the tool and show you how it works.
After downloading this tool from Microsoft’s Web site, you simply run the BPASETUP Windows Installer Package. The installer does a check to make sure you have the correct .NET Framework version installed. If you do not have this installed, you will be directed to Microsoft’s Web site to download it. In addition to having the correct .NET Framework, you must also have Internet Explorer 6.0 or later. You can install the SQL Server Best Practices Analyzer on any of the following platforms:
- Microsoft Windows XP
- Microsoft Windows 2000 Professional
- Microsoft Windows Server
- Microsoft Windows Server 2003
- Internet Explorer 6.0 or later
This gallery was orginialy posted in Steven Warren’s TechRepublic blog – The Hot Button.
Select Repository Information
Select Repository Information
Once you begin the installation and verify the .NET Framework version, you will be prompted with the welcome screen and then asked to accept the license agreement. Once you have accepted the license agreement, you can choose your SQL Server name, database name, and authentication mode–shown in this screenshot.
Best Practices database
Best Practices database
This information is used to create the Best Practices database in your SQL Server, shown here as sqlbpa. After you enter this information, the installer finishes the installation and you are ready to work with the product.
BPA Welcome screen
BPA Welcome screen
Now that you have installed the Best Practices Analyzer (BPA), you are ready to open it from the Start menu by choosing Start | Microsoft Best Practices Analyzer | SQL Best Practices Analyzer. The welcome page appears and it gives you a hyperlink to input your login information.
Log in
Log in
In this window you can enter your SQL Server name and authentication type. You can use Windows Authentication or SQL Server authentication to log in as shown here.
Defining your SQL Server in the BPA
Defining your SQL Server in the BPA
Once you have entered your information, you are ready to log in and enter your SQL Servers into this tool. You can enter a SQL Server and a Friendly Name. The Friendly Name allows you to further identify your SQL Server, and if you change your SQL Server name, the BPA tool will still work using the Friendly Name.
Create a best practice
Create a best practice
Now that you have your SQL Servers defined in the BPA tool, you are ready to create best practices and scan the SQL Server(s). In order to create a best practice, click the Best Practices link on the left navigation bar and choose Create Best Practice (at the bottom of the screen).
Name and categorize the best practice
Name and categorize the best practice
In this screen, you can enter a Best Practice Name and choose the category of your choice. If you choose all categories, you can select any of the Rules (best practices) you desire. If you are unsure of which of these rules to use, you can click on the question mark (?) icon for a definition of each one.
Save and execute the best practice
Save and execute the best practice
Once you have selected all of your rules, click Save Best Practice. After you save it, you can execute it by clicking on the down arrow of each best practice and click Scan SQL Servers.
Review your best practices with BPA Reports
Review your best practices with BPA Reports
Once the scan is complete, you can review your best practices by viewing the Reports.
I have shown you how to harness the power of this Microsoft tool to analyze your SQL Servers against standard best practices. By testing each scenario on a SQL Server on your testing network, you can become familiar with this tool and how it works, prior to implementing it on your production SQL Servers to make them run at optimum efficiency.
