Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

SQL Server Administration Best Practices

From Wiki

Jump to: navigation, search

This page contains a collection of best practices in regards to SQL Server administration. These best practices have been suggested by the members of LessThanDot, some of them are really common sense and most people will know them, there are of course always some that you didn't know of....hopefully you will find some of those in this list

There are also pages for SQL Server Programming Best Practices and SQL Server Data Modeling And Design Best Practices so check those out after you are done with this page. As always this is a work in progress, make sure to bookmark this page and come back often because we will add more content on a regular basis.


Log and Data files

Do not truncate your ldf files! by Ted Krueger

This post explains why this is a bad thing to do. There is this thing backups use called the LSN (Log Sequence Number). Very cirtical to the manner in which you can restore backups. In short when you truncated the log you essentially killed the LSN and broken the chain between the Full which is required for any differential or log backup to restore correctly. If you had access to your SQL Agent history for the job you would probably see an error for the log backup job after you truncated the logs stating something like, "BACKUP LOG cannot be performed because there is no current database backup".

Place log files and data files on separate physical drives * stub

Create one tempdb file per processor/core * stub

Backup and Restore

Maintenance

Cycle The SQL Server Error Log

Test your Full Recovery model by restoring backups by Ted Kruger

What do you need to run on a daily basis as a SQL Server DBA * stub

What do you need to run on a weekly basis as a SQL Server DBA * stub

What do you need to run on a monthly basis as a SQL Server DBA * stub

What do you need to run on a quarterly basis as a SQL Server DBA * stub

Monitoring

How to Monitor Database Mirroring by Paul Theriault
A post explaining how to use alerts to notify you of problems with database mirroring

Activity Monitor to gather statistics and health of SQL Server 2008 by Ted Krueger
This post explains how to use the activity monitor to gather statistics and health of SQL Server 2008.

Performance

What alerts do you need to setup to monitor performance? * stub

We Loaded 1TB in 30 Minutes with SSIS, and So Can You by Len Wyatt, Tim Shea, David Powell

In February 2008, Microsoft announced a record-breaking data load using Microsoft® SQL Server® Integration Services (SSIS): 1 TB of data in less than 30 minutes. That data load, using SQL Server Integration Services, was 30% faster than the previous best time using a commercial ETL tool. This paper outlines what it took: the software, hardware, and configuration used. We will describe what we did to achieve that result, and offer suggestions for how to relate these techniques to typical scenarios. Even for customers who don't have needs quite like this benchmark, such efforts can teach a lot about getting optimal performance.

Data Compression: Strategy, Capacity Planning and Best Practices by Sanjay Mishra

The data compression feature in SQL Server 2008 helps compress the data inside a database, and it can help reduce the size of the database. Apart from the space savings, data compression provides another benefit: Because compressed data is stored in fewer pages, queries need to read fewer pages from the disk, thereby improving the performance of I/O intensive workloads. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. Therefore, it is important to understand the workload characteristics when deciding which tables to compress.

Security

To SA or not to SA by Ted Krueger

Please Mr. DBA, Change default passwords and use strong passwords by Ted Krueger

656 Rating: 2.8/5 (81 votes cast)