Do you not have a full-time DBA to keep Microsoft SQL Server databases
running according to industry standards and best practices? Sometimes even DBAs
want to test their SQL Servers against various best practices. 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. 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.
Installation
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
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 (Figure A). This
information is used to create the Best Practices database in your SQL Server,
shown in Figure B as sqlbpa. After you enter this information,
the installer finishes the installation and you are ready to work with the
product.
Figure A
Figure B
Working with the BPA
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 (Figure
C) and it gives you a hyperlink to input your login information.
Click for larger image
Figure C
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
in Figure D.
Click for larger image
Figure D
Once you have entered your information, you are ready to log in and enter your
SQL Servers into this tool (Figure E). 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.
Click for larger image
Figure E