Practical App

Top 10 Secrets of a SQL Server Expert

Maintaining a SQL Server environment is a potentially complex endeavor. Here are 10 tips for ways you can minimize complexity and reduce stress.

• By Paul S. Randal
• 12/06/2010

Based on my SQL Server consulting practice, here are the top 10 ways an IT pro who works with SQL Server can take control of his environment.

10. Take Inventory
It's critical to have an up-to-date inventory of what instances and databases you have in your enterprise and under your control. This is the only way you can properly manage them, consolidate where necessary, and correctly scope and plan projects and upgrades. It also helps you establish limits to your responsibilities by publishing a list of known instances for which you accept responsibility, with agreement from the various teams in your organization. You can define support policies for known instances and insist that new instances adhere to your configuration guidelines before you support them.

9. Standardize Configurations
If the number of databases and SQL instances for which you're responsible is growing all the time, you'll know that the number of different configurations grows in a similar fashion. It's extremely hard to work efficiently when moving from instance to instance if you have to constantly remember the configuration details for different instances.

The solution is to standardize your configuration to the greatest extent possible in terms of drive letters, server configuration options, database settings, database maintenance, security settings and so on. SQL Server 2008 introduced the Policy-Based Management feature to help define and enforce policies.

8. Understand the I/O Subsystem
There are several factors related to the I/O subsystem that can affect your SQL Server instances. You need to be aware of these, as well as their potential impact:

• The capacity of the I/O subsystem in terms of read/write throughput and disk space. It must be able to cope with workload peak demands and still provide space for the data volume to grow before you have to purchase more capacity. By identifying I/O bottlenecks and moving data and/or log files to other portions of the I/O subsystem, you can more evenly balance the load.
• The redundancy capabilities of the I/O subsystem in terms of RAID level and whether it can do things like split-mirror backups and any form of mirroring/replication (at the I/O subsystem level, not the SQL Server level). It's important to protect your data and log files from drive failures and other potential problems. This is often a trade-off -- RAID-10 offers better redundancy than RAID-5, but is more expensive.
• The I/O subsystem is configured correctly in terms of RAID stripe size, NTFS allocation unit/cluster size and partition alignment.

7. Create a Customized Maintenance Plan
Whenever I teach database maintenance classes I always start off by saying, "You can't just put a database into production and walk away." Indexes become fragmented over time, which leads to performance degradation. Statistics become out-of-date, which leads to bad query plans and poor performance. I/O subsystems can get corrupted, and there's the ever-present need for backups.

You can tackle all these problems by having a comprehensive maintenance plan tailored for your databases. A customized plan is far better than a generic plan that doesn't adequately address your needs.

6. Ensure the Security of Your System
Investing time in proactively discovering security problems is essential to preventing incidents and not having to deal with them later. Another one of my TechNet Magazine features, "Common SQL Server Security Issues and Solutions," lists the 10 most common security problems and how to avoid them. Also, don't forget to stay on top of patching your systems as you discover vulnerabilities.

5. Get on Good Terms with Your Developers
One of the major tension points in any IT department is often between the DBA team and the development team. The two groups typically don't understand each others' priorities and concerns -- from development deadlines to SQL Server design decisions. Differing opinions about behavior and performance issues and responsibilities around deployment and support are relatively common.

You can make your job a lot smoother by engaging proactively and productively with the development team. Organizing mutual education sessions works well, especially when done in a non-accusatory manner. Conduct design reviews with someone from the DBA team present, and test code adequately before being put into production -- hopefully avoiding damaging errors that can further erode inter-team relationships.

4. Develop a Comprehensive Disaster Recovery Strategy
No matter how bulletproof your infrastructure may be, you must have a contingency plan for when disaster strikes. You can't predict corruption, power outages, fires, accidental data loss or a host of other potential problems.

Work with management to define down-time and data-loss software license agreements for your databases, plan how to recover data from various kinds of data loss, determine how your databases and all SQL instances figure into your company's business-continuity plan. Work out the relative importance of all the databases and instances so you can prioritize disaster recovery.

You'll also need to implement technologies to help you know when problems occur, such as page checksums, consistency checks, SQL Agent alerts and System Center Operations Manager alerts. This disaster recovery infrastructure will help you protect data with backups, log shipping, replication and database mirroring; and potentially failover to a redundant system with database mirroring or failover clustering.

3. Take and Test Regular Backups
No matter how good your high availability and disaster recovery planning is, you cannot avoid taking regular backups of your databases. If your database is destroyed or fatally corrupted, your only recourse may be to restore from your last set of backups, so if you don't have any backups your company could suffer major consequences. Not only do you need to take backups, you also need to regularly practice restoring from them so you know they'll work when needed.

You can find more information in two of the articles I wrote for TechNet Magazine in 2009: "Understanding SQL Server Backups" and "SQL Server: Recovering From Disasters Using Backups."

2. Monitor and Maintain Performance
Performance tuning takes up the majority of a DBA's time, but there are plenty of ways that you can streamline the process:

• Establish a performance baseline so you can see whether performance has really changed.
• Break the system up into primitives you can measure in isolation without the uncertainty of external factors.
• Use the waits-and-queues methodology to quickly pinpoint performance problems.
• Monitor performance with system primitives, performance counters and wait statistics. This way you'll know when performance starts to degrade. Use the performance Data Collector feature in SQL Server 2008 and the Performance Dashboard for SQL Server 2005.
• Establish a maintenance plan.
• Carefully plan and execute your indexing strategy with tools such as the Database Engine Tuning Advisor, or DTA, missing index Dynamic Management Views (DMVs) and index usage DMV.

1. Know Where to Find Information
With a never-ending to-do list, it's vital that you know when to call it quits and look for help. You have to know your limitations and accept that you can't know everything about SQL Server. There's no point in beating your head against a wall and wasting precious time when someone out there can help with your task or problem.

Your No. 1 source of information about SQL Server is SQL Server Books Online, which you can download and install locally or search online on MSDN. SQL Server Books Online is great for looking up syntax, but if you have a more involved how-to question, or are trying to troubleshoot a problem, the best thing to do is post a question on an online forum. There are lots of SQL Server forums on MSDN and popular community sites like SQL Server Central.

Another fast way to find help is to tap into the SQL Server community on Twitter. Post your question with the #sqlhelp hash-tag, which many SQL experts (including me) monitor.

Attend a SQL Server-specific conference such as the annual PASS Community Summit, the bi-annual SQL Server Connections or more frequent SQL Saturdays. Follow some of the many blogs run by SQL Server experts in the community. You can get a good idea of which blogs are active and worth following from the blog rankings maintained by fellow-MVP Thomas LaRock.

You may be overloaded and overwhelmed, but if you can put in some extra effort to work through these suggestions, you should find you get huge benefits. Your systems will run smoother, you'll be better organized, you'll have more peace of mind -- and you'll be a more proficient DBA.