Ten secrets of SQL Server experts

Over the past few years, many companies have streamlined their IT departments. Many database administrators (DBAs) have to manage a large amount of SQL Server databases. What's worse is that there is often no real DBA, but you can find someone to act as a real DBA. In addition, sometimes DBAs become fire fighters and are exhausted in the ever-increasing crisis. Such a harsh environment is not normal and persistent. No one is willing to be under such continuous pressure and interference.

One way to get rid of this situation is to spend some time simplifying your SQL Server environment to make it easier to understand and manage. Based on my practical experience as an SQL Server consultant, I have summarized the following 10 methods to help SQL Server DBA improve environmental control and reduce the possibility of crisis. These methods are listed based on their importance. The more important they are, the more important they are.

10. Prepare a list

How many times do you even have no idea about the existence of damaged database data? SQL Server databases can easily flood in the company. The DBA team may not know which data is in the database and thus lose control of the SQL Server instance. In this way, databases that are not backed up or repaired may not be properly protected and many other necessary management tasks are missed.

Therefore, it is imperative to master the latest list of controllable company instances and databases. This is the only way to effectively manage them, merge them when necessary, and properly define the scope and plan the project and upgrade. The configuration also helps you clarify your responsibilities by publishing the list of known instances you are responsible for after negotiating with various teams in the company. You can develop a support policy for a known instance and require the new instance to strictly comply with your configuration rules. Otherwise, the policy will not be supported.

There are many tools that can help you create an SQL Server LIST, such as simple tools such as SQLPing3 and SQLRecon, Microsoft Evaluation and Planning toolkit, and Quest Discovery wizard.

9. Standardized Configuration

If the number of databases and SQL instances you are responsible for is growing, you will find that the number of different configurations is also growing in a similar way. If you have to remember the configuration details of different instances, it is difficult to achieve efficiency when facing different instances.

The solution is to standardize the configuration information as much as possible, such as the drive letter, server configuration options, database settings, database maintenance, and security settings. Policy-based management is introduced in SQL Server 2008 to help define and implement policies. In addition, Lara Rubbelke, a Microsoft SQL Server Technical Expert, developed the enterprise policy management (EPM) framework, which can be easily expanded to SQL Server 2005 and SQL Server 2000 instances. You can obtain the PEM framework from CodePlex. Figure 1 shows a report example of the MPs framework.

Figure 1 enterprise policy management framework report

8. Understand the I/O subsystem

Several factors related to the I/O subsystem may affect the SQL Server instance. You need to realize these factors and their possible impacts:

The read/write throughput and disk space capacity of the I/O subsystem. You must be able to meet peak workload requirements and provide sufficient space before you have to purchase more capacity for the increased data volume. You can determine the I/O bottleneck and move the data and/or log files to other parts of the I/O subsystem to balance the load more evenly.

I/O Sub-system RAID-level redundancy and the ability to perform operations such as splitting image backup and any form of image/copy at the I/O Sub-system level, rather than the SQL Server level ). It is important to protect data and log files to avoid losses due to drive failures and other potential problems. However, this often requires compromise-RAID-10 redundancy is more expensive than RAID-5. For more information, see the White Paper "physical database storage design ".

Check whether the RAID strip size, NTFS allocation unit/cluster size, and partition alignment of the I/O subsystem are correctly configured. For more information, see my blog post "Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly? Is the disk partition offset, RAID strip size, and NTFS allocation unit set correctly ?)".

7. Create a custom maintenance plan

When I teach Database Maintenance Courses, I always start with "You can't just put the database into production, and then let it go. Indexes become increasingly fragmented over time, resulting in lower performance. Statistics gradually become obsolete, leading to poor queries and deteriorating performance. The I/O subsystem may be damaged, and there is no end to the backup requirements.

You can customize a comprehensive maintenance plan for the database to solve all the above problems. A custom plan is far better than a general plan that does not fully meet the requirements. In August 2008, I published the article "Key Skills for efficient maintenance of SQL Server databases" in TechNet magazine, which introduced how to create a good maintenance plan. The best way to start creating your own maintenance plan is to use a free script written by Ola Hallengren. I always recommend that you use this script.

6. Ensure system security

It is necessary to take the time to actively discover security issues and prevent incidents from being handled afterwards. My other article TechNet, "common SQL Server Security Problems and Solutions", lists ten of the most common security problems and solutions. In addition, do not forget to fix the system in time when detecting the vulnerability.

5. Deal with the relationship with the development team

In any IT department, the relationship between the DBA team and the development team is often one of the main contradictions. These two teams usually do not understand each other's priorities and concerns-from the development term to the SQL Server design decision. In terms of behavior, performance issues, and deployment and support responsibilities, the two teams often disagree.

You can actively and effectively participate in the work of the development team to make your tasks more smooth. Organizing education courses together is a very effective way, especially when the atmosphere is very friendly. Prior to putting the design into production, review and fully test the Code with the participating DBA team members, which is expected to avoid destructive errors that may further damage the team relationship.

4. Formulate comprehensive disaster recovery strategies

No matter how strong your infrastructure is, you must have contingency plans when a disaster falls. You cannot predict damage, power outages, fires, unexpected data loss, or many other potential problems. Therefore, you need a plan to address these problems and recover them.

You can work out a database downtime and data loss software license agreement with the management to plan how to recover from various data loss types, and determine how to incorporate the database and all SQL instances into the company's business continuity plan. Determine the relative importance of all databases and instances to prioritize disaster recovery.

You also need other technologies to help you understand the time when a problem occurs, such as page checksum, consistency check, SQL proxy alarms, and System Center Operations Manager alarms. The disaster recovery infrastructure helps you protect data through backup, log transfer, replication, and database images, and possibly transfers faults to redundant systems through database images or failover clusters. The following two Microsoft whitepaper can help you: "High Availability with SQL Server 2008SQL Server 2008 High Availability) "and" Proven SQL Server ubuntures for High Availability and Disaster Recovery Proven SQL Server architecture with High Availability and Disaster Recovery features )".

3. Regularly back up and test

No matter how detailed your high availability and disaster recovery plan is, you must regularly back up your database. If your database is damaged or damaged, your only resource may be the final backup. If you do not have any backup, it may have extremely serious consequences for the company. You not only need to back up data, but also need to perform regular Recovery tests to ensure that these backups can be used properly as needed.

You can find more information from two articles I wrote for TechNet in 2009: "Understanding SQL Server Backups learn about SQL Server backup)" and "SQL Server: recovering From Disasters Using BackupsSQL Server: Using backup for disaster recovery )".

2. Monitor and maintain performance

Performance Tuning takes most of the DBA's time, but there are many ways to simplify this process:

Establish performance benchmarks to see if performance has actually changed.

The system is decomposed into isolated measurement elements without external interference.

Use the wait-queue method to quickly identify performance problems.

Use system elements and performance counters to monitor performance and wait for statistics. In this way, you will know when the performance will begin to decline. You can use the performance data collector function in SQL Server 2008 and the performance Dashboard of SQL Server 2005.

Develop maintenance plans.

Use tools to carefully plan and execute index policies, such as database engine optimization advisor, DTA, missing index dynamic management view (DMV), and index usage DMV.

1. Know where to find information

There are endless things to do. Knowing when to let go and asking for help is the best strategy. You should understand your limitations and understand that you cannot understand everything about SQL Server. If someone can help you complete the task or solve the problem, you do not have to struggle and waste valuable time.

Your primary source of SQL Server Information is SQL Server books online. You can download and install it locally, or search for it on MSDN. SQL Server books online is suitable for query syntax, but if you have more complex operation problems or are trying to solve a problem, the best way is to publish the issue to the online forum. On MSDN, there are many SQL Server forums and some popular community sites, such as SQL Server Central.

Another quick way to seek help is to use the SQL Server community of Twitter. Add the # sqlhelp hash tag when releasing the question. Many SQL experts, including me, can see your problem.

In addition, you can attend special SQL Server conferences, such as the annual PASS Community summit, the two-year SQL Server Connections, or more frequent SQL Saturday theme day. You can follow the blogs of many SQL Server experts in the community. You can use the blog rankings maintained by MVP Thomas LaRock to learn about the activity and value of these blogs.

You may have been overwhelmed by high work intensity, but if you can take some time to learn about these suggestions, you will find that you have benefited a lot. Your system will run more smoothly and you will be more organized and more peaceful-you will eventually become a more professional DBA.

Original article address

View more articles

Edit recommendations]