SQL Server: Top 10 Secrets of a SQL Server Expert
Maintaining a SQL Server environment is a potentially complex endeavor. Here are the top 10 ways you can minimize complexity and reduce stress.
Paul S. Randal
Many companies have downsized their IT departments over the last few years. Many database administrators (DBAs) have ended up with responsibility for large numbers of SQL Server databases. Worse yet, often there’s no actual DBA. Someone is tagged as the involuntary or de facto DBA. In some cases, the DBA ends up in pure fire-fighting mode, moving from one crisis to the next. This sort of environment is difficult, toxic and unsustainable. No one likes being under constant stress and interruption.
One way out of this type of situation is to invest a little bit of time streamlining your SQL Server environment to make it easier to understand and manage. Based on my SQL Server consulting practice, here are the top 10 ways a SQL Server DBA can take control of his environment and reduce the overall potential for crises to occur. This list is presented in roughly increasing order of importance.
10. Take Inventory
How many times have you been requested to restore damaged data on a database you had no idea even existed? It’s easy for SQL Server databases to sprawl across an enterprise. The DBA team can lose track of what’s out there, resulting in unmanaged instances of SQL Server. This results in databases that aren’t backed up, aren’t patched, aren’t properly secured and missing out on a host of other necessary management tasks.
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.
There are a number of tools to help you create a SQL Server inventory—from simple tools such as SQLPing3 and SQLRecon to the Microsoft Assessment and Planning Toolkit and the Quest Discovery Wizard.
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. Lara Rubbelke, a SQL Server technology specialist at Microsoft, has also produced the Enterprise Policy Management (EPM) Framework, which easily extends the capabilities to SQL Server 2005 and SQL Server 2000 instances. You can find the EPM Framework on CodePlex. Figure 1 shows a sample EPM Framework report.
.jpg "Figure 1 The Enterprise Policy Management Framework report")
Figure 1 The Enterprise Policy Management Framework report
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 and 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. Read the white paper, “Physical Database Storage Design” for more guidance.
- The I/O subsystem is configured correctly in terms of RAID stripe size, NTFS allocation unit/cluster size and partition alignment. Check out this blog post, “Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly?” for more details.
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. My August 2008 TechNet Magazine feature, “Top Tips for Effective SQL Server Database Maintenance,” tells you how to construct a good maintenance plan. The best starting point for building your own maintenance plan is the comprehensive and free script from Ola Hallengren. That’s what I recommend to my clients.
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 bullet-proof 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. You need a plan to cope with and recover from these 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. There are two Microsoft white papers that can help you with this: “High Availability with SQL Server 2008” and “Proven SQL Server Architectures for High Availability and Disaster Recovery.”
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 to 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.
.jpg "Paul Randal")
Paul S. Randal* is the managing director of SQLskills.com, a Microsoft regional director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. He wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development. Randal is an expert on disaster recovery, high availability and database maintenance, and is a regular presenter at conferences worldwide. He blogs at SQLskills.com/blogs/paul, and you can find him on Twitter at Twitter.com/PaulRandal.*