Reading the Registry

Congratulations to Reza Sordi, consultant for California-based S.C.E. Corporation. Reza won first prize of $100 for the best solution to the February Reader Challenge, "Reading the Registry." Phill Wade, senior technical analyst at Norwich Union Life Services, Bristol, UK, won second prize of $50. Here's the solution to the February Reader Challenge.

Problem

David manages several SQL Server 2000 and 7.0 installations for his company. Often he dials in from home to troubleshoot pesky problems. He typically uses Enterprise Manager to view installation settings for configuration attributes such as backup directory, SQL Mail Profile, sort order, character set, case-sensitivity, and physical startup. But he notices that when he uses Enterprise Manager, response time over the dial-up link is slow because of large data transfers between client and server. To speed up his dial-in sessions, David wants to write a stored procedure that retrieves configuration details and the runtime information that SQL Server stores in the registry. He knows he must provide the server name and login information to obtain the registry values. Help him write the stored procedure using only documented system procedures.

Solution

To obtain the installation and runtime parameters for a SQL Server installation, David can use SQL Distributed Management Objects' (SQL-DMO’s) Registry object for SQL Server 2000 and 7.0. SQL-DMO's Registry object reveals such information as the SQL Server installation's registered owner, character set, sort order, SQL Mail account information, backup directory, default data path, and default error log path. David can create the SQL-DMO Registry object in T-SQL by using the OLE Automation system stored procedure sp_OACreate. The stored procedure call for creating the Registry object is:

EXEC sp_OACreate 'SQLDMO.SQLServer' , @SQLServer OUT 

After David obtains the Registry object, he can use the sp_OAGetProperty system stored procedure to loop through the registry object’s properties collection to obtain the individual registry keys and their values. The necessary sp_OAGetProperty stored procedure calls are as follows:

- Get the server’s Registry object.EXEC sp_OAGetProperty @SQLServer , 'Registry' , @Registry OUT

- Get each property from the properties collectionEXEC sp_OAGetProperty @Registry , 'Properties' , @Property OUT , @Counter

By using the sp_OACreate and sp_OAGetProperty stored procedures and SQL-DMO's Registry objects, David can create a stored procedure that produces the details he needs without using Enterprise Manager. He can also easily modify his stored procedure to retrieve only specific properties and speed up the data transfer even more. Listing 1 shows the complete stored procedure for retrieving configuration and runtime information.

March Challenge

Now, test your SQL Server savvy in the March Reader Challenge, "Scaling Back the Schedule" (below). Submit your solution in an email message to [email protected] by February 15. Umachandar Jayachandran, a SQL Server Magazine technical editor, SQL Server MVP, MCDBA, and MCSE+I will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here's the challenge: Mark is the DBA of a large SQL Server 7.0 installation. The installation's database size has rapidly reached its current 10GB, and Mark expects that figure to triple by year's end. Backups for a database of this magnitude command valuable resources, so Mark wants to pare down the backups in both size and duration. The current backup schedule consists of a nightly database backup and hourly transaction log backups during the day, 7 days a week. Help Mark design a backup schedule that