10 errors that SQL Server DBA can easily commit

10 errors that SQL Server DBA can easily commit

Translated from: http://sqlsentry.tv/top-10-administrative-mistakes-on-sql-server/

In addition to the top ten mistakes, other top-ranked errors

Aside from SQL Server errors, these errors are mainly in development or design:

1, unreasonable norms and unreasonable database design

2, there is no need to design a good scalability

3. No database performance baselines or benchmarks

4. Problems with indexing

5. Insufficient attention to statement tuning

Error tenth bit (disk-disk IO is ignored as long as disk space is sufficient)

Often only consider disk subsystem disk space, ignoring the IO load

Without sufficient expertise, the following issues may arise:

Inappropriate fault-tolerant mechanism selected

Insufficient IO performance:

OLTP systems require a higher TPS

OLAP requires high transfer speed

Select a poor RAID type, controller, channel

Not enough disk spindles

SSD can change the rules of this IO game

Error Nineth bit (not understood by business rules)

As an IT PRO, you should know how SQL Server works inside

What is checkpoint? What is lazy writer?

How is tempdb used? What's in plan cache?

You know, DBAs are the protectors of corporate asset data.

The connection between business and it, you should know how and in what ways to use your server

Who cares when the program goes down, how much does the company need to lose per minute of downtime?

What is the business cycle?

When is the best time to crash?

What baselines and benchmarks are normal?

error Eighth digit ( no set of own troubleshooting methods )

In critical situations, DBAs need a strong, step-by-step approach to root cause analysis.

If not, you will:

1, missed the database errors and problems

2. Data loss due to errors or catastrophic problems

3. Poor response times may violate SLA service level agreements

4. Loss of credibility

If you do not yet have a methodology, you can refer to

End-to-end Troubleshooting on Http://SQLSentry.TV

SQL Server Troubleshooting Guide by J. Kehayias on http://www.simple-talk.com

error Seventh digit (basically using default values )

Install SQL Server with default values the purpose is to have the server up and running as soon as possible

However, this can cause the runtime to be suboptimal, such as the following settings:

Automatic database growth, automatic shrinking

The size of the automatic database growth

Default file group

Some small problems can also become big problems

1, the degree of parallelism

2. Fill Factor

Other settings options for servers and databases

Error Sixth (the security of the database is remembered afterwards )

Now the SQL injection vulnerability above the Internet becomes the first

It is worth noting that a lot of the methods of anti-SQL injection ten years ago, until today we still continue to use

Plan ahead to minimize problems:

Make sure that the application running on your server has minimal permissions, and that this permission ensures that your program will function properly

How many exposed surfaces do you have on your server? The more exposed, the wider the attack surface.

Who has access to your server?

How do you find out who has some inappropriate authority when something goes wrong?

Error Fifth digit (no automation is fully used )

Automation can alleviate much of the DBA's work, and Ironically, at first the DBA needs to automate a lot of work

Without automation, DBAs must face the following issues:

If it's all about people, it's possible to be error-prone and missing.

Your work will be doubled when the number of servers increases

Examples of using automation:

Automatic error notification

Maintenance Plan Jobs

is basically a script, without the need to use a GUI

Error Fourth digit (improper function or technique used at work )

DBA is the "performance engineer" for it programs in the company

His job is to use the most appropriate features for each business requirement

Otherwise, you will:

Make your application complex

Excessive resource consumption

There is a theorem: No IT projects, only the use of it to solve business projects

third-Lowest error (very indifferent to management changes )

change management is very important! without management changes, DBAs will face:

If it doesn't change, then what they do will be worse.

Change control to change management

A reasonable management change means:

Plan your time in advance in a prescribed time limit

In a production environment, the quality of management change will be verified and tested

The change is isolated, atomic, reversible.

second-lowest error (Inappropriate maintenance plan )

Proper preventive Maintenance (PM) can help you:

Catch a problem before it happens

Can determine the direction of optimization

Users performing resource-intensive operations on the system will reduce

Preventive maintenance should be included in SQL Server

Database consistency check and DBCC CHECKIDENT

Use the CHECK option when backing up and restoring a database

Index fill factor, defragmentation

Index statistical information

Do not rely on the Database Maintenance Plan Wizard!!

You don't have to do the wheels, there are a lot of maintenance plans, and the Immortals have helped us write.

Www.sqlfool.com www.olahallengren.com

Error countdown First (backup and restore )

DBAs do not often verify the availability of backups

This can lead to some problems:

Your SLA for the customer is not guaranteed, and RTO and RPO are not guaranteed

No one can make sure the backup is available

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o