SQL Server monitoring tools help DBAs take back nights and weekends

Many DBAs have to work more and more nights and weekends to fulfill zero-downtime demands. But tools for monitoring and managing SQL Server might free up some of that time.

Minimizing weekend and late-night work by database administrators was a quest near and dear to the hearts of many attendees at PASS Summit 2014in Seattle. At the conference, Thomas LaRock, president of the Professional Association for SQL Server (PASS) user group, explained the dilemma of the modern DBA: Routine maintenance, updates, patches and hardware replacements all require database downtime — but that’s unacceptable except at night or on weekends.

And as systems have grown more and more efficient, La Rock added, many database administrators (DBAs) are being asked to take on workloads that used to be spread across multiple individuals, requiring longer working hours to get everything done. “It’s only ever more, more, more,” he said in an interview.

“I’ve seen a huge growth rate of after-hours work that people are being demanded to do,” agreed Carl Berglund, director of business development at DH2i Co., a vendor of SQL Server monitoring tools and management software in Fort Collins, Colo. But, Berglund said, working more nights and weekends results in tired DBAs — and tired people make more mistakes.

One thing that potentially can help SQL Server DBAs cut down on their off-hours workloads is the database performance monitoring and managementsoftware sold by Microsoft and various third-party vendors. For example, LaRock works as database management “head geek” at SolarWinds, a vendor in Austin, Texas, that offers a tool called Database Performance Analyzer (DPA). The product, which SolarWinds acquired when it bought Confio Software last year, tracks and analyzes the “wait time” in applications running on top of a database. DPA pinpoints processes that are causing holdups and provides guidance on how to alleviate the problems and speed up processing time.

DH2i has also developed tools specifically intended to combat lost nights and weekends for DBAs. Berglund presented on that topic at a SQLSaturday conference held by PASS in Orlando, Fla., in September. DH2i’s strategy is designed to provide application mobility and infrastructure independence, enabling SQL Server instances to be updated on a new virtual host with little downtime. DBAs “can do the majority of [updates and patching] in the daytime and just a stop and restart at night,” Berglund said.

New tool saves time — for other tasks

Cindy Osborn, SQL Server technology lead and SQL architect at International Paper Co. in Memphis, Tenn., is a user of the SolarWinds DPA tool. She started a trial of the software in June, when it was still known as Confio Ignite. Now Osborn uses it on a regular basis as she manages 100 instances of SQL Server for the global paper and packaging manufacturer. She found DPA while looking for a monitoring tool to help her analyze database performance and deal with code issues in applications.

Previously, whenever one of the multiple software development groups at International Paper had a problem, Osborn’s DBA team was forced to drop everything to “stop and dig,” as she described it. The developers couldn’t work on fixing the problems themselves without being given elevated access to the servers running the databases, which could cause security issues. In one case, Osborn had to do hours of code tweaking to get a homegrown incident tracking application to work correctly. At other times, she said, it took her “hours upon hours” to tell software vendors what was wrong with their applications.

With the SolarWinds tool, Osborn said her team can pinpoint problems more quickly and reduce interruptions to their usual DBA work. Another benefit, she added, is that graphs generated by DPA as part of reports on performance problems are easy for business users to understand. “Now, I don’t get, ‘Your server is slow,’ ” she said, describing phone calls with users.

Osborn is still working off-hours, but she said that DPA has helped her reduce her workload by enabling her to consolidate some SQL Server instances. Thanks to the database consolidation, she now can run SQL Server on fewer processor cores — and with fewer cores and instances to manage, Osborn has freed up some time. Much of it was taken up by other tasks, but she has noticed a decrease in night and weekend overtime.

On-call DBA hours hard to endure

Andrea Letourneau stopped working as a DBA for financial services technology provider Fiserv Inc. after her experience in an on-call position there. Letourneau, who now is a developer and database specialist at Viewpoint Construction Software in Portland, Ore., said that she was one of two people working as on-call DBAs at Fiserv, which meant she had to be available to take calls from its customers more than 50% of the time. “My husband got sick of the 3 a.m. phone calls,” she said.

While Letourneau has exchanged the duties of a DBA for writing custom code, she did offer some strategies for minimizing night and weekend work time. She said she checked system usage trends before the weekend so she could see if more hardware would be needed and put in a request to IT before it became a problem, thus cutting down on emergency calls. She also made manual checks of the database servers part of her daily routine and especially monitored disk space to make sure there was plenty of room.

Letourneau added that DBAs now are able to use SQL Server monitoring tools from vendors like SolarWinds, SQL Sentry and Idera to help them with that process. “It’s definitely come a long way,” she said. “If you have good monitoring software and a good DBA doing the monitoring, it helps.”


[Source:- techtarget]