Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Building a Database Documenter, Part 1
Last updated Mar 28, 2003.
In my storied DBA career, I've come into more than my fair share of shops that were in, well, a bit of a crisis. The former DBA had left suddenly, or the previous staff was a little less than fastidious about documenting the changes of their systems.
When you're thrown into a situation like that, you need to quickly assess the system, so you know what you're dealing with. It's important to act like a physician, and "first do the patient no harm;" make no changes until you know what the system looks like. Also, check the system backups before you do anything else. You'd be surprised to learn how many DBAs don't treat backups as seriously as they should.
You can get lots of system information about things like the backups using Enterprise Manager, Query Analyzer, and SQL Profiler. To do that, you need to understand just what you're looking for, and where to find it.
Another tack to take is to use a commercial product to document your system. There are plenty out there; one I'm fond of is Quest Software's Spotlight on SQL Server, which I've reviewed before. Embarcadero also makes a good suite of SQL products, but they can be pretty expensive.
While buying a product is the quickest route to database documentation, there are times when it doesn't suit the situation. For several years, I was a database consultant, and it wasn't legal for me to install software on my clients' servers just to document them.
Not only that, it's wise to completely understand what you're looking at, and why. Buying a product that has a green light on the database files object is great, but doesn't help you understand what that really means. Is the system OK for now (only), or will it last longer than ten minutes? For that, I've found the best thing to do is to ask SQL Server how it's doing.
Yes, you can do that. I'll show you the exact scripts in part two of this article.
Before you start writing code, however, you have to decide on the granularity of your documentation. You might need only a simple set of metrics regarding backups, maintenance, and database sizes. Or you may be looking for a lot more.
Let's begin with the basic information you might want to gather. Here are some items I normally want to know about right away:
Name of the server
Physical information (storage info, memory, CPU, etc.)
Backups and maintenance plans
And then there are all kinds of other objects I might like to know about:
There are a couple of ways you can get this information. One way is to write a compiled application in Visual Basic or C# using Data Management Objects (DMO) or the new SQL Management Objects (SMO) in SQL Server 2005. That's an article for another day, so we'll move on to the second method.
SQL Server comes chock-full of system tables. These are the tables in the master and msdb databases that it uses to track itself. These tables store everything from security information to database object names and descriptions.
Microsoft has made various views on these tables, since the tables themselves are subject to change without notice. While those views are very useful, they don't have everything we're after, so I'll show you the specific tables and columns on which the views are based.
Be advised that these tables and columns can be version and service pack-specific, so the queries might break at some point in the future. If you're really concerned about that, you can look up the Information Schema Views topic in Books Online and use those instead.
SQL Server also provides system variables that we can use to request information. And finally, SQL Server has several system stored procedures and functions to call for even more info. We'll use all these SQL Server constructs to find out what we want to know.
There are several ways within this method to display the results of the queries. You could use a high-level language like Visual Basic or C#, you could create a database-enabled Web page, or you could create a report using Crystal Reports or Microsoft Access. See the reference section at the end of this article to find out how you can even use Microsoft Word! You could also store the results in a set of tables.
I normally use this last method, since it allows me to collect the "static" information (such as server name) only once, and the other information on a more frequent basis. I store the information about all my servers in a separate database and then run reports from that to the Web. I use replication to roll the remote server information up to a central location, and then I use Analysis Services to cube it.
If you follow this method, remember that data about your server's OS and service pack level might be very useful to a hacker. Be sure you treat this information with the appropriate level of security.
One final word before I show you the queries. These aren't all the scripts that I have for this kind of thing. I've gathered these scripts over the years, and modified them to fit my needs. Some of this stuff I made up on my own, and other parts were given to me by my mentors years ago. I try to give credit where it's due, so if you see something here that you know someone independently wrote, make sure you respond to this article with that info. I've always believed in sharing, since I think it makes us all stronger. You certainly don't have to credit me with any of these scripts, but if they have a credit to someone else, please carry that along. That person deserves the mention.
In part two I'll show you the scripts, along with an explanation of what they do. See you then!
SQLDev has a lot of great SQL DMO programming examples.
Believe it or not, you can use Microsoft Word to document your SQL Server. This site shows you how.
Something no DBA should be without a system table map, compliments of Microsoft.
InformIT Tutorials and Sample Chapters
You can find out more about the system functions from our own Baya Pavliashvili in his article called SQL Server System-Related Functions.