ToolboxNew Products for IT Pros
Greg Steen
Monitor Your Servers
Mastering SQL Server 2005 Reporting Services Infrastructure Design
The more complex our infrastructures become, the more inundated with information we administrators are—with most of that information being logged into files or databases. But to make sense of all that information, you need an analytic engine and a tool to present the analyzed data in a meaningful form. One very handy tool to do this is Microsoft® SQL Server® 2005 Reporting Services. This tool can help you get all that data into an aggregate form and then present it in a human-readable format. And it can deliver the information to disparate locations automatically or on demand, effectively giving your business users the near real-time data they need to make smarter business decisions.
Planning for, deploying, and managing a successful Reporting Services infrastructure (or any new server infrastructure, for that matter) that both serves your company's needs and grows as your company grows will require preparation, know-how, and a bit of grit. If you are thinking about deploying a Reporting Services instance, Mastering SQL Server 2005 Reporting Services Infrastructure Design, by Joseph L. Jorden (Sybex, 2007) can help build your understanding and guide you through the keys to a successful operation.
The book does a good job of covering the sometimes forgotten aspects of systems implementation before delving into the technical details of a Reporting Services deployment. These rudimentary aspects involve determining why you are implementing the system and the requirements of that business use, and defining how to manage expectations of your end users. There's a chapter on strategies for change management in your reporting system. This section covers different approaches to change management and provides a template and guidelines for change management request forms. I'm not one to proclaim the advantages of bureaucracy, but a little structure here will ensure a much more stable environment.
After covering the business end of Reporting Services, the book dives into deployment, going into the basics of installation, scaling, automation, and configuration. It also offers tips on how to integrate with Microsoft Windows® SharePoint® Services (WSS). It gives you a good start on a deployment checklist to help you avoid stumbling blocks.
One of the cool new features of SQL Server 2005 Reporting Services is ad hoc reporting capabilities. This feature lets your users create reports on the fly. The book explains how to enable this feature and set up the Report Builder tool. Next the book jumps into how to create subscriptions for reports, which allow you to generate and distribute your reports on a schedule via e-mail, file share, or integrated SharePoint delivery.
All systems need maintenance over time, and the next chapter covers essential tasks such as backing up your instance, managing report content, and troubleshooting problems with the available logs. There is also a good chapter on security, which covers authentication and authorization options for your deployment.
Programmers will appreciate the chapter on extending Reporting Services as well as the chapter on accessing and manipulating reports through applications that are based on the Microsoft .NET Framework. The Report Definition Language (RDL) is one of the fundamental aspects of Reporting Services, and the book dedicates a chapter to some of the key elements available to you, the structure of an RDL file, and the basics of creating and deploying those files.
The book finishes up with a few tips and tricks for performance tuning your Reporting Services deployment. You'll find information on measuring performance, making modifications to configuration files, and using the Performance Console. There is also a nice appendix on how to integrate Reporting Services with SharePoint. If you are looking to roll out SQL Server 2005 Reporting Services in your environment, you should take a look at this book. It can really help you get off the line running.
Price: $49.99 (direct).
Audit Your Environment
Alloy Discovery Professional Edition
Being able to retrieve information quickly for a particular system in your coverage area is critical to performing your job, troubleshooting issues, and verifying that systems are up to date and in compliance with security and software policies. And having that information in a format that is friendly to the business user, and that allows for at-a-glance evaluations of your infrastructure, can be a great advantage (and, not to mention, can make you look good to management). If that isn't enough, this quick access to information can also help you isolate who is utilizing what software to ensure your users are properly licensed. One such inventory and audit tool you might want to take a look at is Alloy Discovery Professional Edition from Alloy Software.
This application is very easy to set up, and you can start the audit on a group or range of machines on your LAN very quickly. The UI groups your machines into an organized view much like that in Internet Explorer®, and once you have your group or groups of auditable machines set up, performing an audit on a group is done with a simple click. The information collected is quite extensive, ranging from hardware, software, and devices to printers, shares, and even events. You can also view asset tags, serial numbers, uptime statistics, environment variables, network configuration, and local user accounts. Basically anything you need to know about your environment you can gather without ever leaving your desk (though I do recommend taking a walk every once in a while for your personal health).
Aside from auditing machines on demand, you can also schedule audits. For machines that are outside your direct LAN and for those that you cannot remotely connect to with enough privilege to perform the audit task, Alloy Discovery provides an agent-based method of auditing target machines. This type of audit can be scripted and then scheduled to run on a periodic basis or run via the agent and then e-mailed from the agent to your main Alloy Discovery repository. Another option is to put the agent on a flash drive and walk around to the machines to initiate an audit. After the audit, you can then sync up the information automatically or manually depending on the method used with the main repository.
Alloy Discovery also has filters that you can apply to your audit results to analyze the overall inventory. For example, you can see how many machines would need a memory upgrade to support an application or which machines in your environment are running Windows® XP Professional versus Windows Vista® Business edition.
Once you have your data, you'll want to create reports. The app can publish reports to HTML pages into which you can drill down. And you can set up a scheduled task to generate these reports on an ongoing basis, a great option for your Network Operations Center. In addition, you can export all of the audit information to XML, CSV, or Microsoft Excel® to, for example, deliver proof to auditors.
Price: $795 (direct) for the base product, plus $3 for every node.
Keep tabs on your environment with Alloy Discovery (Click the image for a larger view)
Manage Your Passwords
Password Corral
Passwords are a fact of life in today's interconnected world. (At least until everything is biometric-based or until we create some form of all-trusting utopia, but neither of those scenarios will be happening in the imminent future.) If you are like me, you probably have different types of passwords for different types of environments, and you vary those passwords across different mediums.
Beyond the normal mainstream uses, there are also all those system passwords for administrator accounts, service accounts, and various domain accounts. And, of course, there are different user names associated with all those different passwords. The list goes on and on. So how do you best keep track of all those passwords securely and easily?
One way is to use a password repository that locks up all the data into a strongly encrypted file, and a good solution to do this is the Password Corral. This easy-to-use and strongly encrypted application, which utilizes Diamond2 or Blowfish encryption to encrypt all your passwords into a file, has been around since 2001. It has been tried and tested over time and is still under active support and development for current and future releases.
After installing the program, you get your new password file going by creating a new user and selecting where you want the password file to be located on your system (or network drive). Now, it should go without saying that if you are going to keep all your sensitive password information for all of your accounts within a single Password Corral file, your Password Corral password should be incredibly strong and changed often. This will be the one password you will really need to remember and protect, so be smart about it.
Once you have authenticated, Password Corral provides a UI much like the one in Internet Explorer, with a hierarchical folder structure on the left and a details pane on the right. You can organize your passwords into a tree-like structure of folders. Each password can have a description, user name, password, reference URL, reference e-mail address, and comments about the account. In addition, you can tell the application what the password policy is for each account, specifying the number of days in the expiration cycle and the number of days remaining until a given password expires.
One of the view options includes filtering by expired passwords so you can see which of your accounts will require a reset on next use. Another cool feature is that you can have the program generate a password for you, saving you from having to invent your own strong passwords for every account. That password generator is customizable as well, allowing you to set it to your desired length, complexity, and character composition.
Another nice feature is that once you have entered all your passwords, you can easily search for them by any of the fields you have entered data for, which is great for finding those infrequently used accounts in a deep folder structure. You can also set a time-out on the application so that if you forget to close Password Corral, the application will lock automatically, protecting your sensitive information. The application will also default to clearing up your clipboard automatically upon exit in case you left the password you copied in your buffer after using it.
If you need constant access to your passwords, you can use the application's system tray icon option, which allows you to simply double-click to launch the application from there. If you want to use Password Corral and store your accounts elsewhere, the application also lets you import them from a file, saving you from having to do all the extra data entry. And if you choose, you can even export and print your passwords—just be sure to eat the paper when you're finished! All in all, this is a very useful free tool.
Price: Free.
Keep track of all your passwords with Password Corral (Click the image for a larger view)
Audit File Usage
File System Auditor
Maintaining an audit trail on shared file systems can save you a lot of headaches. More important, however, it is a necessity for meeting certain information retention policy standards. To help you find out who did what and when, you might want to check out File System Auditor from ScriptLogic. This application is a centralized management system that audits the use of files on target systems in near real time and stores the information in a SQL Server database for usage reporting.
File System Auditor supports Microsoft SQL Server Desktop Engine (MSDE), SQL Server 2000, and all versions of SQL Server 2005 (including the free SQL Server 2005 Express). Each monitored system will require an agent to be installed, which is used to monitor file usage and report back to the SQL Server. After installation, you'll use the Database Wizard to create the database repository where all the audit information will be recorded.
As a systems administrator, you are probably now thinking about bandwidth and verbosity on a heavily used system, but the application purportedly uses an intelligent auditing system that combines different actions into a single audit entry. In terms of bandwidth, File System Auditor uses only 100 bytes of data for the first access to a file and 10 bytes to 20 bytes for each access thereafter.
You may wonder why not just use native Windows auditing to do this, but the centralized administration, event coalescing, and reporting features of a dedicated monitoring app are a bit more flexible and usable. Plus, having all that information in a SQL database makes it much easier to reference from other systems.
After configuring your database, set up your Agents on the target servers via the Agent Configuration Console. You can either type in a server's name or browse for it using Active Directory®. File System Auditor also supports clustered file servers, so you can type in the name of your cluster instance. Next you install the agent service (with the click of a button if your account has remote access) and pick which Windows or SQL Server account the agent should use to connect to the File System Auditor database.
On a per-server basis, you can set up three different types of monitoring exclusions: Path Filters, Process Exclusion Filters, and User Exclusion Filters. Path Filters let you pick a path and choose whether to include or exclude certain folder and file events—such as permission changes, creation, and deletion—for either all files or files of certain types. Process Exclusions and User Exclusions let you do the same for file and folder events pertaining to a particular process on the target server, but don't support the file type filters.
After you set up and start collecting auditing events, you then launch the Report Configuration Console to put that audit data to use. To set up a report, you can set a number of filters on the data including users, certain file or folder events, paths, servers, processes, and date ranges. In addition, you can then schedule the report and have the app e-mail the published report via your SMTP server. Finally, to see what is happening to files on a system in real time, you can launch File System Auditor's Real Time Viewer to watch the raw events scroll by like "tailing" a log file.
Price: Starts around $800 (direct) for the program and one server license.
Audit file usage with File System Auditor (Click the image for a larger view)
Greg Steen is a technology professional, entrepreneur, and enthusiast. He is always on the hunt for new tools to help make operations, QA, and development easier for the IT professional.