sqlCrunch.com :: SQL Server Administration Links

A fantastic database maintenance solution from Ola. Takes care of "The Big 3" maintenance items in one hit. Backups, Indexes and DBCC Checks. Providing far more flexibility than Maintenance plans, the solution dynamically detects databases, has excellent error handling, is very robust and well documented. Best of all, has been used for over 6 months in a large production environment. Great Stuff Ola, and thanks to Kevin Kline for the heads up on the link ... Source : Ola Hallengren - Blog Post

Automate your Database Maintenance using SMO

A fantastic 3 part series from Allen White on using SMO to automate backups and integrity checks ... Source : Allen White - simple-talk.com - Article

Script to determine permissions in SQL Server 2005

A great article from mssqltips.com outlining the usage of the fn_my_permissions function to return the current permissions of a specific user at various levels in a given database or at the server level ... Source : MSSQLTips.com - Article

The DBA's Guide to the SQL Server .NET Common Language Runtime (CLR)

This whitepaper helps the DBA determine appropriate use of this new feature and guidance on when other alternatives may provide better performance, flexibility or capabilities. As well as offering guidance on suitable uses for the Database Engine .NET Framework Programming API this whitepaper also suggests code, change and release management processes that should be tailored to each DBA’s individual circumstances to ensure a professional and safe deployment ... Source : SQLskills.com

Quick list of VLDB maintenance best practices

A quick and dirty (and excellent!) list of VLDB (Very Large Database) maintenance tips from Paul Randall ... Source : Paul Randal - SQL Server Storage Engine - Blog Post

Schema Comparison with Visual Studio Team Edition for Database Professionals

A great article from Dinesh Priyankara that highlights the Schema Compare feature in Visual Studio Team Edition for Database Professionals (Data Dude) ... Source : sql-server-performance.com - Article

Setting Advanced Configuration Options in SQL Server 2005

This article discusses some of the advanced configuration options in SQL Server 2005 and their associated best practices and limitations ... Source : Dell.com

How to Clone a Database

The ability to clone a database i.e.; take an exact copy including statistics but excluding data, is a crucial part of any good development process. Using this technique, you can examine execution plans for poorly performing queries in a test environment that takes hardly any space at all, as the data itself is removed, but the statistics for the query optimizer remain. In this blog post, Kalen talks about the process of cloning a database ... Source : Kalen Delaney - SQLblog.com - Blog Post

Auto-shrink - turn it OFF!

In this blog post, Paul talks about the dangers of AutoShrink - among others, unexpected performance hits and index fragmentation ... Source : Paul Randal - SQLskills.com - Blog Post

Understanding/Diagnosing SQL Server Scheduling Problems

Thanks to Kevin Kline for this link. This white paper thoroughly documents SQL Server scheduler health details and associated troubleshooting techniques. As Kevin comments in his blog, this paper will also cover reading and understanding SQL Server dump files ... Source : Microsoft.com - Technet Article

How can I limit the number of reboots when applying hotfixes ?

This Technet Q A article discusses the need for reboots when installing SQL Server hotfixes and service packs and how to avoid them ... Source : Microsoft.com - Technet Article

Capacity Planning for TempDB

TempDB capacity planning and concurrency considerations for index create and rebuild ... Source : Microsoft.com - Technet Article

SQL Server 2005 Deployment Guidance for Web Hosting Environments

This paper provides best practices for configuring SQL Server 2005 to optimize security, tenant isolation, and the performance of your hosted SQL Server 2005 deployment. Sample scripts for provisioning users and databases for use in shared hosting are included ... Source : Microsoft.com - Technet Article

Top 10 Best Practices for SQL Server Maintenance for SAP

The following recommendations provide an outline of best practices for maintaining SQL Server database for an SAP implementation ... Source : Microsoft.com - Technet Article

The Impact of Changing Collations and of Changing Data Types from Non-Unicode to Unicode

When a business grows internationally, its database system must support multilingual characters in tables through the use of Unicode data types. This white paper explores the amount of time that it takes to change a table column from a non-Unicode data type to a Unicode data type, along with changing the collation of the database. Use this information to calculate how long it takes to alter large tables ... Source : Microsoft.com - Whitepaper

The Sad State Of Programmers Part 2 : The In Person Interview

Preparing for an interview ? This great series of blog posts from Denis Gobo provides sample questions, general advice and various resources to prepare and improve your skills ... Source : Denis Gobo - SQLblog.com - Blog Post

SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Performance, and Scalability

Whitepaper covering recommendations for SAP based SQL Server systems ... Source : Microsoft.com - Download

SQL Server 2005 for Oracle Professionals

This white paper explains the key differences between Microsoft SQL Server 2005 databases and Oracle databases. It compares SQL Server and Oracle database architecture and provides information on the newest features in SQL Server 2005. This paper is intended for Oracle professionals who want to leverage their Oracle knowledge to manage SQL Server databases or prepare to migrate an Oracle database to SQL Server 2005 ... Source : Microsoft.com - Technet Article

Email Table as HTML

Tony Blogs about a proc he wrote to email the contents of a table in HTML format. Very nice ... Source : Tony Rogerson - sqlBlogCasts.com - Blog Post

Snapshot Troubles

Paul addresses some of the problems you can run into with snapshots ... Source : Paul S. Randal - sqlskills.com - blog post

Upgrading to SQL 2005 at Microsoft

This article covers the process followed by Microsoft to upgrade their internal database systems to SQL Server 2005 ... Source : Microsoft.com - Technet Article

2005 Upgrade Technical Reference

Comprehensive upgrade guide to upgrading to SQL Server 2005 ... Source : Microsoft.com - Download

Best Practices for Migrating Non-Unicode Data Types to Unicode

This paper summarizes best practices for migrating database systems from non-Unicode to Unicode data type ... Source : Microsoft.com - Whitepaper

SQL Server Page Allocations

From the PSS Engineers. 'When I drop an object why does SQL Server not immediately re-use those extents for a new object?' ... Source : PSS SQL Server Engineers - Microsoft.com - Blog Post

Drive Letter Limitations

Linchi blogs about Drive Letter requirements for SQL Server Instances and its potential effect on large consolidation projects in the future ... Source : Linchi Shea - SQLblog.com - Blog Post

Multi-Cores and MAXDOP

Linchi addresses the generally accepted practice to turn off Parallelism when unexpected problems arise, and what this means for the future with multicore processers becoming the norm ... Source : Linchi Shea - SQLblog.com - Blog Post

Procedures & Management Tools

SQL Server Operations Guide

Based on SQL Server 2000, but easily adapted to 2005. This guide covers the operations tasks and life cycle of SQL Server systems and, where appropriate, relates tasks to the Microsoft Operations Framework (MOF) ... Source : Microsoft.com - Technet Article

MOM 2005 / Dell Case Study

A case study of a Dell based SQL Server implementation using Microsoft Operations Manager (MOM) 2005 ... Source : Dell.com

Backup/Restore

VLDB Backups: Filegroups vs Multiple DBs

Backing up very large databases (VLDBs) presents various administrative challenges. In this post, Paul addresses a suggestion that splitting a VLDB into multiple smaller databases can help, and why this idea is not recommended given the alternative, and much better approach; filegroups ... Source : Paul S. Randal - sqlSkills.com - Blog Post

Best Practices for Recovering a Database to a Specific Recovery Point

This topic presents some best practices for restoring a database to a specific recovery point ... Source : Microsoft.com - Technet Article

Determining the amount of database change since the last full backup

Paul Randal provides the source code for custom proc to determine the percentage of a database that has changed since the last full backup. Amongst other things, this would be useful for determining the merits of a differential backup compared to running another full backup ... Source : Paul S. Randal - sqlSkills.com - Blog Post

Successful Backup Messages No More

In this blog post, Andrew talks about an undocumented trace flag that supresses the successful backup messages that are written to the event logs. A handy feature when you have regular backups of many databases ... Source : Andrew Kelly - SQLblog.com - Blog Post

When Bad Things Happen to Good Projects

This is not specific to SQL Server, but a great article none the less. It's a case study of a failed SAP implementation at HP and highlights the importance of contingency planning, part of which is obviously developing and testing a good backup/restore plan ... Source : CIO.com

Debunking Backup Myths

Myth 1: A full database backup only contains the transaction log from the start of the backup to the end of the backup - Myth 2: It's possible to do a STOPAT with only a full database backup ... Source : Paul Randal - SQL Server Storage Engine - Blog Post

Using the CONTINUE_AFTER_ERROR restore option to restore a corrupt database

If the only available database backup is corrupt, this option allows the restore to complete for further analysis. This blog post from Paul Randall discusses this option in more detail ... Source : Paul Randal - SQL Server Storage Engine - Blog Post

Implementing Restore Scenarios

An overview (and examples) of the different restore scenarios available under SQL Server 2005 ... Source : Microsoft.com - MSDN Article

Working with Restore Sequences

This article describes how you can combine multiple RESTORE statements into restore sequences that copy data, roll it forward, and bring it online at the end of the rollback phase ... Source : Microsoft.com - MSDN Article

Partial Database Availability

This white paper outlines the fundamental recovery and design patterns involving the use of filegroups in implementing partial database availability in SQL Server 2005. As databases become larger and larger, the infrastructure assets and technology that provide availability become more and more important ... Source : Microsoft.com - Technet Article

Overview of Restore and Recovery in SQL Server 2005

Summarises the different restore options available under each database recovery model ... Source : Microsoft.com - MSDN Article

COPY_ONLY Backups in SQL Server 2005

A new feature in SQL Server 2005 that allows an additional backup to be taken without effecting the existing sequence of backups ... Source : DatabaseJournal.com

Myths on Transaction Log inclusion in Full Backups

How much of a transaction log is included in a full backup ? All of it, none of it, or some? ... Source : Paul S. Randal - sqlskills.com - blog post

DBCC

Running DBCC on a VLDB

In this Blog post, Paul Randal discusses the options for running DBCC CHECKDB on VLDB's ... Source : Paul Randal - SQL Server Storage Engine - Blog Post

How to create a corrupt database

In order to test your processes when you encounter database corruption, you need to have a known corrupt database to test against. This article shows you how to produce a corrupt database for testing purposes ... Source : Tony Rogerson - Blog Post

Can DBCC CHECKDB repair everything?

In this Blog post, Paul Randal discusses the ramifations and possible outcomes from using CHECKDB to repair a damaged database... Source : Paul Randal - SQLskills.com - Blog Post

How long will CHECKDB take to run?

There are many factors that determine how long a DBCC check is likely to take on a particular database. In this blog post, Paul covers the top 10 ... Source : Paul Randal - SQLskills.com - Blog Post

Tips and tricks for interpreting CHECKDB output

Having trouble understanding the output of DBCC ? Paul wrote it, so he should know :-) In this post, he explains the output, and gives some insiders tricks to understanding it better ... Source : Paul Randal - SQLskills.com - Blog Post

DBCC CHECKDB: Last Success and Completion Status

Do you use SQL Agent Jobs to run DBCC CHECKDB? If the job completes without error, do you assume that the check was successful? In this blog post, Paul highlights the danger of this assumption, and tells us how we can find out the last successful CHECKDB operation for a given database ... Source : Paul S. Randal - sqlSkills.com - Blog Post

Running DBCC on a user defined snapshot

DBCC uses a snapshot to reduce the performance impact on users whilst running. The problem with this is that the snapshot is created on the same disk(s) as the database, and may lead to failure if all the disk space is consumed by the snapshot during the dbcc check. In this post, Kalen talks about running the DBCC check on a user defined snapshot, which allows you to define the disk location for the snapshot, and therefore have more control of disk space usage during the DBCC check ... Source : Kalen Delaney - SQLblog.com - Blog Post

Connectivity

Troubleshooting Kerberos Issues

In this article, Adam Saxton starts down the long road of double-hop troubleshooting by providing a gentle introduction to Kerberos, and how to recognize Kerberos+SQL related issues ... Source : Adam Saxton - Microsoft SQL Server Support Blog - Blog Post

Available TCP Ports On A SQL Server

Kevin Kline discusses situations where you may wish to use a static tcp port for your SQL Server and links to a site that lists application ports that you should avoid using ... Source : Kevin Kline - SQLBlog.com - Blog Post

Virtualization

Using SQL Server 2005 in a Virtual Environment

This paper discusses the overall concept of virtualization and factors to consider when deciding whether to use SQL Server in a virtual environment ... Source : DatabaseJournal.com

SQL Server Performance in a VMWare Infrastructure 3 Environment

This paper describes transaction processing workload performance in virtual machines using Microsoft SQL Server 2005 and VMware Infrastructure 3. This performance study was conducted at the HP Strategic Alliances Engineering (SAE) lab in Cupertino. The primary goal is to prove that Microsoft SQL Server 2005 can successfully handle enterprise-level transaction-processing workloads when running inside VMware virtual machines. Update; comments on this paper from Marco Russo's blog post ... Source : VMWare.com

Virtualization Troubles

Virtualize everything all the time! Perhaps not. In this post, Conor addresses some of the lesser known issues with Virtualization, particularly as they relate to SQL Server ... Source : Conor Cunningham - sqlskills.com - blog post

Filegroups

Filegroups - Dividing Data for Performance

In this blog post, James talks about the use of filegroups and discusses the way he typically sets up databases using 3 filegroups - Primary, Data and Indexes ... Source : James Luetkehoelter - SQLblog.com - Blog Post

How Many Data Files Should I Create for a User Database?

A common question and another great answer from Linchi Shea ... Source : Linchi Shea - SQLblog.com - Blog Post