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.

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:

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 challenge@sqlmag.com 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

reduces the size of the database backups that SQL Server 7.0 performs during the week.

reduces the time SQL Server 7.0 would take to restore the database after a failure.

From the Blogs

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More