Effectively Manage Changes and Configurations on Your SQL Server

By : Dinesh Asanka Jan 24, 2007

Printer friendly

As a DBA or system administrator, you know how difficult it is to configure a database. It is even more difficult to maintain the configuration. In the case of SQL Server, as a DBA you have to monitor new database object changes. You have to monitor other system changes too. And if it is a dedicated SQL Server, you especially have to monitor the other applications that are installed.

All this will be a Herculean task if you are going to do this manually. And in today's dynamic IT environment, there will likely be more than one SQL Server to monitor. DBAs need a tool that can automatically monitor SQL Servers for changes. Idera, which provides various SQL Server tools, has a wonderful tool called SQLconfig to help you with this task.

Key Features

• Detects changes to individual SQL Servers and categorizes the changes into easily scanned reports.
• Presents data only about the instances and servers you want to monitor.

SQLconfig provides a clear and automated way for you to analyze and control changes to databases, SQL Server instances, and database server configurations:

• Create and maintain database and server configuration compliance standards.
• Compare grouped SQL Server instances and servers to a model server.
• Assess database server hardware configurations.
• Detect installed software on database servers.
• Analyze resource usage.
• Identify modifications made over time.
• Prepare for large-scale changes or data migrations.
• Detect SQL Server and database changes.
• Provides detail reports on SQL Server configurations.

Review Roadmap

In this review, here is what we are going to look at:

• What are the key benefits of SQLconfig, and are they actually met?
• How does it work?
• Is it easy to install and administer?
• Does it meet the needs of the typical DBA?

Features Tested

• Configuring SQLconfig.
• Setting up a configuration baseline.
• Viewing the configuration change.
• Acknowledge configuration changes.

Architecture

During installation, you will be prompted for a database in which to store all the configurations and data collected by SQLconfig, which can be easily backed up.

Data is collected by a windows scheduled task. It can be created during instillation or configured later.

You can set a baseline for your software and hardware configurations as a point of reference. After gathering data, the data will be compares with the baseline. Any differences will be shown on a special place. Legitimate changes can then be accepted or reversed.

Since a baseline will change over time, you are allowed to alter it.

Preparation

Hardware requirements:

• 300 MHz Intel Pentium processor or equivalent.
• 128 MB or more of RAM.
• The MMC interface requires a monitor capable of displaying 256 colors or more and a screen resolution of 800x600 or greater.
• 50 MB of free hard drive space for program files, 100 MB for the database, and 50 MB for the logs.

Software requirements (for the server on which it is installed):

Windows 2000 or Windows XP Professional using TCP/IP networking protocol. If you manage Microsoft SQL Servers running on Windows NT, ensure you support NetBIOS.

SQL Server version 7 or later SQL Management Tools. MSDE is included in the installation kit. If you install the database on a remote database server, it must be running MSDE or SQL Server version 7 or later. SQLconfig now supports SQL Server 2005.

Microsoft Word 97/2000 Viewer, available free on the Microsoft Office download Web site. The viewer is needed to view and print the SQLconfig documentation. The setup program installs the documentation to a subfolder of the main installation folder.

SQL Server permissions:

SQLconfig uses the logged-on user account to dispatch agents and collect database server configuration data. This account must have the following permissions:

• System Administrator role on the managed database server.
• Permission to write results to the SQLconfig database.
• Administrator permission on the managed database servers.

I experienced a problem on the computer on which SQLconfig was installed when the default browser was not Internet Explorer. As most of the SQLconfig interfaces are in HTML format, you need to set your default browser Internet Explorer.

Register SQL Servers

You need to register all SQL servers that you want to monitor. This can be accomplished from one page.

If your servers have SQL authentication, there will be an additional page to provide user authentications.