Alter View, Create Function: SQL Server Best Practices Analyzer Analyzed
By Kent Tegels, SSWUG.ORG Columnist.
Introduction
Welcome to the first installment of "Create View, Alter Function." I have a pretty simple purpose: to talk about the problems we face in our careers as Database Administrators, Developers and Analysts. Hopefully, we can talk about solutions, too! Most of the content will be about the things I think are really important as we look to the future, mainly:
- Newly introduced SQL Server tools and technologies
- XML and related technologies and issues
- The convergence of declarative and procedural programming
- The evolution and expansion of data-centric systems
- Our personal and technical development as the engineers and architects of the information age
Sometimes the column will be an inch-wide and mile-deep on about some highly technical topic. Most of the time, these will be presented in a tutorial like style. Other times, I will be writing about things at a one-inch depth trying to provide some mile-wide prospective on the issues. Sometimes the discussion will be very much about getting your hand dirty and working with the idea, while other times – and please forgive me in advance – the topic is going to lend itself to a lot of discussion about theory.
And some times, I will just go off on a rant. Deal with it.
One last thing – about the title – yes, I hope it will come to signify a lot about what this column is about: By altering your view, you create new function. It is not "thinking outside the box" as much as it is thinking without any box to at all.
Best Practices Analyzer Analyzed, Part I
The SQL Server Best Practices Analyzer (or simply BPA) is a tool for analyzing SQL Server against known sets of Best Practices, and reporting the results along with recommendations. In a sense, it is like having an SQL Server expert spending time examining the configuration of a server and the databases that it hosts, then having those experts offer you ideas and suggestions for making improving the security, efficiency and reliability of the server. Best practices, though, having the same problem as "one-size fits most" T-Shirts. That is, what may be a best practice for many SQL Server installations is not always a best – or even necessarily good – practice for you as an individual. That is likely why the BPA stops its work after making recommendations. It does not offer nor can it by itself change anything in your configuration. It is up to do evaluation the recommendations made for suitability to your environment and leaves the making of the required changes up to you. Some key points to keep in mind about the recommendations: they are not "must do," rather, they are "should do." The BPA is not designed to fix broken servers, either. While running it against a server that is experience problems might help you find and solve those problems, BPA is more a problem detection and prevention focused tool. Finally, the BPA should not be looked at as beginning canonically ultimate. While it can test for many things, it is not expected or intended to take the place of your best judgment about how its recommendations apply in your situation.
Installing and running BPA
One step I would recommend before asking the BPA to look at any SQL Server is to decide on one or more servers where you would like to keep the data which it collects. This database is called the BPA Repository. As part of the installation process, BPA will create a database on a given instance of SQL Server. This database will mostly consist of tables for storing the results of scans against your servers. It also has tables for which servers are "known" to the BPA, making them candidates for scanning. There are also tables for the best practice rules themselves. I bring this up because if you are going to scan a number of servers, you should have sufficient file and log disk space available for these operations before engaging in them. That said, I have scan a few servers with the tool and the total database size is still less than ten megabytes.
After downloading the BPA Microsoft Installer package from Microsoft's SQL Server Web site, all you really need to do is execute it. It will prompt you for credentials to log into a SQL Server where it will create the database discussed above. Obviously, that log-in will need the right rights to at least create a database. I would recommend installing the BPA on an administrator's workstation rather than directly on the server to be scanned since the program can be a bit CPU intensive during execution. When you run the BPA, I suggest doing so with a log in that is that has system administrator rights. The BPA does not do any data destructive tests so it should be a safe operation. That said and if you are a bit paranoid, there is nothing wrong with running the BPA after making back-ups of the databases.
After this, you are on your own as to how to use the tool. That should not a problem since it is fairly straight forward. Your next task will be to create a test suite, called a Best Practice Group, from sets of existing tests. This organization allows you to customize the testing to suit your needs. The existing test groups include: Backup and Recovery; Configuration; Database Design; Database Administration; Use of depreciated features; Use of the Full-Text indexing features; General administration; T-SQL usage and SQL Server 2005 readiness. Note that you can get a detailed description of each test group while you are constructing your own Best Practices Group. I believe you should run each test within a best practice group, take your time and read the test descriptions as you building the test. This will help you best understand the analysis provided and what the recommendations provided relate to.
Let us summarize. The BPA is a tool for analyzing SQL Servers. It analyzes the general configuration of the Database configuration, the configuration and settings of hosted databases and how users and programs and using that those services. It suggests actions for you to consider implementing. It creates a database to store all of this information in called the BPA Repository. All of this is valuable because it helps you detected and prevent problems proactively.
In the next installment of "Alter View, Create Function," we will actually do an analysis of the analysis provided by BPA. However, if you are eager to begin using the BPA, I would recommend watching this Webcast, then have a go.
