Sometimes, its just the smallest of details that can make all the difference. For example, on my test system (see the end of this posting for a description), I created a new 50GB database. The database creation process took about 5 minutes and 50 seconds to complete.

Next, I populated the database with over 61 million rows of data, which virtually occupied all of the available space within the newly created database. After that, I backed up the database using SSMS, and then I deleted the original database.

At this point, I restored the database from the backup using SSMS. Below, you see the typical Restore Database screen.

 

At the bottom, right-hand side of the screen in the Progress box, notice the “Executing (0%)” indicator. Between the time I clicked the OK button to begin the restore, and when the “Executing (0%)” counter began to move, it took about 5 minutes and 50 seconds. At that point, the counter began to increment and the database was restored.

Now I make one very small change to my SQL Server instance (I’ll describe it in just a moment), and then I repeat the above steps (after deleting the database I just restored). First, I created a new 50GB database. This time, instead of taking 5 minutes and 50 seconds to create the database, it takes just under 2 seconds, a savings of about 5 minutes and 48 seconds. Next, I populated the database with the same amount of data as before, backed it up, and then deleted the original file. When I restored the database this time around, instead of having to wait 5 minutes and 50 seconds before the backup began to restore, I only had to wait just under 2 seconds. In both of these cases, I saved a significant amount of time.

So what was the very small change that I made, and why did it radically reduce the amount of time for database creation and database restoration to occur? I turned instant file initialization on.

What is Instant File Initialization?

In my first two examples, before instance file initialization was turned on, the reason it took so long for the database to be created, or the database to be restored (before a database can be restored, its space must first be pre-allocated, much like creating a new database), SQL Server had to go to every page in the 50 GB database and zero each one of them out. It can take a lot of time for SQL Server to go to every 8K page in a file (especially very large files) and physically zero out each page. When instant file initialization is turned on, SQL Server doesn’t have to zero out every 8K page that has been allocated. Instead, the space is just allocated to SQL Server by the operating system in one fell swoop, which is a very quick process, potentially saving you a great deal of time.

How Do You Turn Instant File Initialization On?

Unlike most configuration features in SQL Server, there is no on/off switch for instant file initialization. Instead, you have to assign a specific user right to the SQL Server Service (mssqlserver) account. Here’s what you need to do to turn on instant file initialization.

First of all, to use instant file initialization with SQL Server in a production environment, you must be using some combination of:

• Windows Server 2003 or
• Windows Server 2008 or
• Windows Server 2008 R2

and using:

• SQL Server 2005 (any edition) or
• SQL Server 2008 (any edition) or
• SQL Server 2008 R2 (any edition)

Second, you must assign the SQL Server Service (mssqlserver) a special user right called “Perform volume maintenance tasks”. To do this, start the Local Security Policy tool (you must be a local administrator to perform this task), then drill down to Security Settings | Local Policies | User Rights Assignment | Perform volume maintenance tasks, as you see in the screenshot below.

Once you have located “Perform volume maintenance tasks”, right-click on it and select “Properties”, and the “Perform volume maintenance tasks Properties” screen appears. Click on “Add User or Group” and then proceed through the remaining screens until you select the account that is being used as the service account for SQL Server. In the screen shot below, notice that I have added the BRADMCGEHEE\sqlserverservice account to this user rights assignment. This is the user account I use on my test server to run my SQL Server instance.

Once the SQL Server service account has been assigned this user right, you will have to restart the SQL Server service (of course, only when it is not being used), and from this point forward, instant file initialization is turned on for all MDF files in your SQL Server instance.

Note: If your SQL Server service account is a member of the local administrators group, then the account already has the “Perform volume maintenance tasks” user right and you don’t need to assign it again.

Why Isn’t Instant File Initialization Turned On by Default?

When a SQL Server instance is first installed, one of the things you must enter is a SQL Server service account. If you follow the best practice and select a domain user account to be used as the SQL Server service account, the setup process automatically assigns the domain user account with only just enough rights and permissions to run SQL Server. The “Perform volume maintenance tasks” user right is not automatically assigned during installation because it is not required to run SQL Server, and because allowing the service account to have this additional user right introduces a very small security risk.

Oh no, a security risk! Well, not really much of a security risk. Here’s the possible security risk scenario. The disk that is being used to create the new database on has been used for storing data that has been previously deleted. As you may know, when data is deleted from disk by the operating system, it really is not physically deleted; the space holding the data is just marked as being available. At some point, the older data will be overwritten with new data. This occurs all the time on millions of computers throughout the world every day. And as such, any data that has been marked for deletion, but not yet overwritten, is potentially available for access if you have the right tools and know what you are doing. In fact, undelete software uses this to recover data that has been accidently deleted.

When instant file initialization is not turned on, and when SQL Server allocates space for an MDF file, each of the pages allocated for the database is zeroed out, which removes the older data, in theory, preventing it from being accessed. I say “in theory” because there are computer forensics techniques that can even recover data that has been overwritten, but that discussion is really not applicable here.

So if instant file initialization is turned on, there is a very slight risk that someone could go to the pages allocated for the new database and read any older data that still may exist there. This is essentially a non-issue in virtually every organization, other than those that require very high security. But because of this potential security issue, instant file initialization is not turned on by default.

If instant file initialization is turned on, and pages are not zeroed out when the database is initially created, SQL Server will automatically overwrite any data that might have been on those pages when SQL Server needs that space.

When Is Instant File Initialization Used?

If instant file initialization is turned on, it is used in all of these cases:

• When a database is first created
• When a an existing database’s size is manually increased
• When tempdb is recreated each time SQL Server is restarted
• When autogrowth kicks in
• When backups are restored (as the space has to be pre-allocated before a restore can occur) 

Instant file initialization only affects MDF and NDF files, not LDF files. In other words, transaction log files can’t take advantage of instant file initialization. This is because log files are circular in nature and must be zeroed out, as random data in transaction log pages can be problematic. In my earlier test, when I created a new 50 GB database, the MDF file was 50 GB and the log file was only 1 MB. If I had created a large log file (which is not uncommon), it would have taken awhile for the log to be created, although the MDF file would have been instantly created. This is also true when you manually increase the size of a log file, or when log file autogrowth occurs. In other words, don’t expect to have all of your databases (MDF and LDF files) created in less than 2 seconds like in my test. While the MDF will be created virtually instantly, the log file may take awhile to be created.

When I was working with SQL Server 2000 a few years back, which does not support instant file initialization, one of the things that annoyed me the most when restoring large databases was waiting for the database space to be allocated before the restore actually began. During emergency database restores, this wasted a lot of precious time, preventing me from getting the database back into production as fast as I would have preferred. If you aren’t using instant file initialization today, you are facing this same problem. That’s why I recommend all SQL Server 2005/2008 instances have instant file initialization turned on. The time saved when restoring databases is the best reason to use instant file initialization.

Check to See if Your SQL Server Instances Have Instant File Initialization Turned On

Hopefully, by now, you see the benefits of using instant file initialization. Assuming that you don’t already know if instant file initialization is turned on or off on the SQL Servers your manage, I challenge you to check and see, and if you find it turned off, turn it on and reap its many benefits.

 

Test Hardware

• Dell T610 Tower, with a single, 6-core CPU (Intel Xeon X5670, 2.93 Ghz, 12M Cache, HT, 1333MHz FSB); 32GB 1333MHz RAM; a PERC H700 RAID controller; two 146GB 15K SAS Drives; one dual-port HBA (to connect to the DAS); and dual network connections. Hyper-threading turned off.
• One PowerVault MD3000 DAS with two, dual-port controllers, and 15 146GB 15K SAS drives. MDF files located on RAID 10 array with 10 spindles, LDF files on RAID 10 array with 4 spindles, backup drive on a single spindle.

 

 

 

 

The SQL Server DMV Starter Pack is a free eBook (84 pages) that describes 28 different ways that DMVs can be used with SQL Server. Not only is each way described in detail, but sample code is included so you can begin using the DMVs immediately. The book covers these uses of DMVs.

 

• DMV#1: Are you Connected?
• DMV#2: Session Ownership
• DMV#3: Current expensive, or blocked, requests
• DMV#4: Query Stats – Find the "top X" most expensive cached queries
• DMV#5: How many single-use ad-hoc Plans?
• DMV#6: Ad-hoc queries and the plan cache
• DMV#7: Investigate expensive cached stored procedures
• DMV#8: Find Queries that are waiting, or have waited, for a Memory Grant
• DMV#9: Monitor long-running transactions
• DMV#10: Identify locking and blocking issues
• DMV#11: Find Missing Indexes
• DMV#12: Interrogate Index Usage
• DMV#13: Table Storage Stats (Pages and Row Counts)
• DMV#14: Monitor TempDB
• DMV#15: Investigate Disk Bottlenecks via I/O Stalls
• DMV#16: Investigate Disk Bottlenecks via Pending I/O
• DMV#17: Why are we Waiting?
• DMV#18: Expose Performance Counters
• DMV#19: Basic CPU Configuration
• DMV#20: CPU Utilization History
• DMV#21: Monitor Schedule activity
• DMV#22: System-wide Memory Usage
• DMV#23: Detect Memory Pressure
• DMV#24: Investigate Memory Usage Across all Caches
• DMV#25: Investigate memory use in the Buffer Pool
• DMV#26: Rooting out Unruly CLR Tasks
• DMV#27: Full Text Search
• DMV#28: Page Repair attempts in Database Mirroring

The books authors include Glenn Berry (Blog | Twitter), Louis Davidson (Blog | Twitter), and Tim Ford (Blog | Twitter), and you can download the eBook and scripts here.

Red Gate Software has recently opened up its SQL Response v2 Early Release Program (EAP), and is inviting anyone who is interested to download the application, test it, and provide feedback. This is your opportunity to help SQL Response’s developers to create a great product that every DBA will want to use.

For those of you who are not familiar with SQL Response, it is a web-based SQL Server monitoring and alerting tool that can help reduce much of the drudgery of routine DBA tasks. Here are a few examples of what it can do.

• Monitor your SQL Server’s health and performance locally from your desk, or remotely from most any computer with a web browser, including many mobile devices.
• Your SQL Server instance’s health can be monitored at a global level (all instances at once), at the individual instance level, or on a per database level.
• Monitoring screens not only provide a real-time overview of SQL Server’s health, it collects historical data so you can go back into time and check out a server’s health at any point in time. This feature is great when you are trying to track down problems and need historical diagnostic data to help you better troubleshoot problems.
• Although SQL Response has the ability for real-time monitoring, you probably won’t be spending a lot of time watching the monitoring screens. Instead, you will be working on other projects. So if a problem should occur on a SQL Server instance, and you are busy doing something else, SQL Response can send you alerts vie e-mail so you always know if there are any problems with your servers, allowing you to be a proactive DBA.

This is just a few of the things that SQL Response does, and I don’t want to waste your time describing the product in detail. If any of the above features have grabbed your attention, then download the EAP and see for yourself how the application works.

As you might imagine, the SQL Response v2 EAP is not feature complete, nor is it bug-free. If you decide to try out the software, be sure that you install in on a test SQL Server box. On the other hand, you can use the EAP to monitor production servers, without risk, because SQL Response doesn’t install any software on the SQL Servers being monitored. Instead, it polls the servers remotely, which greatly reduces the overhead of collecting monitoring and alerting data.

To download the SQL Response v2 EAP, visit this webpage and sign-up. Once you sign up, you will receive an email with a link to download the software, in addition to other links you can use to provide feedback on the release. In fact, if you provide lots of feedback on the EAP, you may even be able to win some prizes, which are described in the email you will receive. So check out the EAP today and see how you can contribute to its success.

November will be a busy time of the year for many SQL Server DBAs as they head off to the PASS Community Summit (November 8-11) or SQL Server Connections (November 1-5) for training. One of the things I have noticed, after attending the PASS Summit for 9 years now, and SQL Server Connections for 4 years, that there is very little overlap between those who attend each conference. Each conference has its strengths, and attendees select one or the other that they feel best meets their training needs.

In the past, and still today, SQLServerCentral.com has been a strong supporter of the PASS Community Summit. But because of the lack of overlap among those who attend each conference, we felt that we had been missing out on those DBAs and developers who chose to attend the SQL Server Connections event. Because of this, starting last year, SQLServerCentral.com began to offer a SQLServerCentral.com track at SQL Server Connections.

SQL Server Connections (which is a part of the larger DevConnections conference), will be held November 1-5 in Las Vegas at the Mandalay Bay Hotel and Conference Center, and will follow this schedule:

Monday, November 1, 2010: Two SQL Server pre-conference workshops:

• “Database Best Practices for the Involuntary DBA” with Paul Randal and Kimberly L Tripp
• “Plumbing the Depths of SQL Server / PowerShell Integration” with Bob Beauchemin

Tuesday, November 2, 2010: Microsoft Day

• There will be three different tracks (DBA, DEV, BI), all presented by members of the Microsoft SQL Server Team

Wednesday and Thursday, November 3-4: SQL Server Connections and SQLServerCentral.com tracks:

• There will be three DBA tracks that are managed by Paul Randal and Kimberly L. Tripp, which includes a wide variety of speakers
• There will be a single SQLServerCentral.Com track, that is managed by Brad M. McGehee, which includes four different speakers
• Attendees will be able to attend any of the sessions in these four concurrent SQL Server tracks.

Friday, November 5, 2010: One SQL Server post-conference workshop:

• “Indexing Strategies and Analysis” with Paul Randal and Kimberly L Tripp

For a list of all the speakers, you can visit this webpage, and for a list of all the sessions, you can visit this webpage.

The SQLServerCentral.com track includes four speakers presenting a total of nine different session over two days. If you recognize any of the speakers, that is because you have probably seen their contributions at SQLServerCentral.com or Simple-Talk.com.

Glenn (blog | twitter) works as a Database Architect at NewsGator Technologies in Denver, CO. He is a SQL Server MVP, and he has a large collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests. His expertise includes DMVs, high availability, hardware selection, full text search, and SQL Azure. Glenn is also an adjunct faculty member at University College – University of Denver, where has been teaching since 2000, and also where he recently completed the Master Teacher Program. He is the author of two chapters in the book SQL Server MVP Deep Dives, and blogs regularly.

 

Brad (blog | twitter) is the Director of DBA Education for Red Gate Software, and is an accomplished Microsoft SQL Server MVP with over 15 years SQL Server experience. He is a frequent speaker at SQL PASS, European PASS, SQL Server Connections, SQLTeach, devLINK, SQLBits, SQL Saturdays, TechFests, Code Camps, and SQL Server user groups. Brad is the author or co-author of more than 15 technical books and over 250 published articles. His most recent books include How to Become an Exceptional DBA (2nd Edition), Mastering SQL Server Profiler, and Brad’s Sure Guide to SQL Server Maintenance Plans. His website is at http://www.bradmcgehee.com.

 

Allen (blog | twitter) is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He’s spent over 35 years in IT and has been using SQL Server since 1992, and is certified as both a SQL Server MCITP and Microsoft Certified Trainer (MCT). Allen has been awarded Microsoft’s MVP Award for the last three years. He’s active in the Ohio North SQL Server User’s Group and contributes in the MSDN Forums, answering questions about SMO and PowerShell, and blogs regularly.

 

 

Buck (blog | twitter) is a SQL Server Senior Technical Specialist for Microsoft, working with enterprise-level clients to develop data platform architecture solutions within their organizations. He has over twenty years professional and practical experience in computer networks and database design. He is a popular speaker at TechEd, PASS and many other conferences; the author of over 400 articles and five books on SQL Server; and he teaches a Database Design course at the University of Washington.

 

If you plan to attend SQL Server Connections this year, be sure to stop by and meet our speakers, if you don’t already know them.

I finally received all the components of my SQL Server test cluster (pictured left), which includes:

–One PowerVault MD3000 DAS with two, dual-port controllers, and 15 146GB 15K SAS drives.

–Two identical Dell T610 Towers, each with a single, 6-core CPU (Intel Xeon X5670, 2.93 Ghz, 12M Cache, HT, 1333MHz FSB); 32GB 1333MHz RAM; a PERC H700 RAID controller; two 146GB 15K SAS Drives; one HBA (to connect to the DAS); and dual network connections.

Before I start assembling the hardware and installing the operating system, I am first setting up an older Dell tower computer I have as a domain controller. In addition, I am configuring a wireless bridge to my wireless network, as the location of my test system is not located near a convenient network connection. Once I have the domain controller and wireless bridge configured and working, then I will begin assembling and configuring the new hardware.

I’ll post regularly about the trials and tribulations of getting the system up and running.

In August, I will be presenting at the following events:

DevLINK, August 5-7, 2010

• How and When to Use Indexed Views
• Identifying SQL Server Performance Problems Using SQL Trace
• Essential DBA Skills: Introduction to Graphical Execution Plans

St. Louis SQL Server Users Group, August 11, 2010

• Identifying SQL Server Performance Problems Using SQL Trace
• Essential DBA Skills: Introduction to Graphical Execution Plans

Baton Rouge SQL Saturday, August 14, 2010

• Best Practices Every SQL Server DBA Must Know
• Database Maintenance Essentials

If you are attending, be sure to stop me and say hello.

I get a lot of emails from people who read my blog, books, articles, and from people who have attended my presentations. I recently received the following email, which I particularly appreciated. While he has kind words to say, what really impressed me about this email was that the writer had taken my recommendations, and then used them as a checklist to follow. While I am not suggesting everyone follow my checklist, I commend the writer on taking the initiative to advance his career by following a specific plan. I think everyone who wants to control their career needs to create and follow a plan, whether it is one created by someone else, or created by themselves. Good work Rodrigo!

Brad,

Today I got MCITP in BI and I finished the check list in your article: http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/.

Well, in the next month I want to start my first book, writing about replication. Do you have some tips on how to become a great writer like you?

Thanks for all,

Rodrigo Crespi
MCP-MCDBA-MCAD-MCSD-MCTS-MCITP
www.crespi.pro.br
www.blog.crespidb.com.br

Originally published in the Database Weekly newsletter.

I used to work at a large organization ($8 billion in revenues) as a SQL Server DBA. My immediate manager, who was a DB2 mainframe DBA, had worked for the organization for over 20 years, and he became a great mentor whom I grew to respect over the many years I worked with him.

While I learned a lot from him, one thing in particular that he taught me that has really stuck in my mind is that a DBA should be the “protector of the organization’s data”. By this, he meant that one of the most important roles a DBA should take on in an organization is to actively seek out the organization’s mission critical data, wherever it was located, and take on the responsibility of managing it.

For example, if he heard about a user who was using an Excel spreadsheet or an Access database to store mission critical data, he would then contact the right people in the organization to ensure that this mission critical data was moved and properly stored in a database, so that it could be protected with proper security and backups. He didn’t do this because he wanted “power”, but because he really cared about the organization. As you might imagine, he ruffled a few feathers when he did this, but I really admired his devotion to the organization and the protection of its data.

So what do you think? Should DBAs take on the responsibility of actively seeking out an organization’s mission critical data in order to protect it, or should they leave that responsibility to someone else in the organization, such as the IT Director or the CEO? In other words, whose job it is to protect the organization’s data, and where does the DBA’s responsibilities begin and end?

Back in 2008, I wrote a blog post about version 1 of the Performance Analysis of Logs (PAL) tool. This free tool, available from pal.codeplex.com, has been enhanced, and Version 2.0, Beta 1.1, is currently available for download. I have just downloaded and installed it myself, although I haven’t had much chance to use it yet. When I have time, I will write a review of the new version. In the meantime, I recommend you download it and give it a try. It’s a great tool for analyzing Performance Monitor Logs, and it is also a great source for determining which Performance Monitor counters, and thresholds, should be used when analyzing SQL Server’s performance.

This is number four in a series of checklists that I am putting together for a new book I am writing. Previously, I blogged about my SQL Server Hardware Checklist, my SQL Server Operating System Checklist, and my SQL Server Instance Checklist. The goal of the SQL Server Database Configuration Checklist is to help DBAs document their databases, which in turn could reveal some potential changes that could be made in order to optimize their performance and availability.

Below is my current version of my Database Configuration Checklist, which is in the form of a spreadsheet that can be used to collect and store the database configuration information. The rows refer to various database configuration settings, and the columns can be used to list each of your databases by their logical names. I suggest that all the databases on a SQL Server instance be included in this checklist, including both system and user databases.

The Basics DB1 DB2 Etc. High Availability Class(1):       Owner:       Date Created:       Database State:       Recovery Model:       Compatibility Level:       Collation:               (1) High Availability Class Key:       Tier 1: >99.99% Uptime (1 Hour or Less of Downtime Acceptable Annually)       Tier 2: 99.9% to 99.99% Uptime (1 to 8.5 Hours of Downtime Acceptable Annually)       Tier 3: <99.9% Uptime (Hours to Days of Downtime Acceptable Annually)               Database File Information       Physical File Name:       MDF Location:       NDF Location(s) (add more rows as needed):       Filegroup(s) (add more rows as needed):       Includes Partitioned Tables/Indexes:       Part of a Federation:       Database Size:       Space Available:       Was Database Initially Pre-Sized:       Is Database Size Optimized for Future Growth:       Has Database File Layout Been Optimized:       Database Autogrowth Setting:               Log File Information       Physical File Name:       LDF Location:       Log Size:       Space Available:       Was Log Initially Pre-Sized:       Is Log Size Optimized for Future Growth:       Log Autogrowth Setting:       Number of Virtual Log Files:               Backup Information       Last Full Database Backup:       Last Database Log Backup:       Average Database Backup Time:       Database Backup Size:       Average Log Backup Size:       Number of Database Backup Copies Retained:       Backups Encrypted:       Backups Compressed:       Backup To Location:       Offsite Backup Location:       Backup Software/Agent Used:               List Applications Accessing Database       Application Name(s) (add more rows as needed):               Settings       ANSI NULL Default:       ANSI NULLS Enabled:       ANSI Paddings Enabled:       Arithmetic Abort Enabled:       Auto Close:       Auto Create Statistics:       Auto Shrink:       Auto Update Statistics Asynchronously:       Auto Update Statistics:       Close Cursor on Commit Enabled:       Concatenate Null Yields Null:       Cross-Database Ownership Chaining Enabled:       Data Correlation Optimization Enabled:       Database Read-Only:       Default Cursor:       Numeric Round-Abort:       Page Verify:       Parameterization:       Quoted Identifiers Enabled:       Recursive Triggers Enabled:       Restrict Access:       Service Broker Enabled:       Trustworthy:       VarDecimal Storage Format Enabled:               Full-Text Search       Is Full-Text Search Being Used:       Full-Text Catalog Name(s):       Full-Text Index Name(s):       Has Full-Text Search Configuration Been Documented:               Database Snapshots       Database Snapshots Used:       Existing Number of Snapshots:       Are Old Snapshots Being Dropped:       Total Sparse File Size:               Log Shipping       Log Shipping Used:       Instance Name(s) Where Log Shipped to:       Database Name(s) Where Log Shipped to:       Transaction Log Backup Schedule:       Has All Log Shipping Configuration Been Documented:       Has HA Documentation Been Created and Tested:               Database Mirroring       Database Mirroring Used:       Operating Mode:       Is Principal:       If Mirror, Instance Name of Principal Database:       Witness Instance Name:       Has Database Mirroring Compression Been Enabled (2008):       Has All Database Mirroring Configuration Been Documented:       Has HA Documentation Been Created and Tested:               Replication       Is Database Replicated:       Is Database a Publisher:       Is Database a Subscriber:       Is Database a Distribution Database:       Type of Replication:       Instance Name(s) Where Database is Subscribed:       Database Name(s) Where Database is Subscribed:       Instance Name of Distribution Server:       Is Replication Part of HA Strategy:       Has All Replication Configuration Been Documented:               SQL Server 2008 Change Tracking       Change Tracking Enabled:       Retention Period:       Auto Cleanup Enabled:               SQL Server 2008 Page/Row Compression       Row Compression Used:       Page Compression Used:       Compression Analyzed for Optimal Performance:               SQL Server 2008 Filestream       Is Database Using Filestream:       Filestream Filegroup:       Filestream Data Physical Location:               Is SQL Server 2008 Transparent Data Encryption Used       Is TDS Enabled?       Is EKM Used?       Is Certificate and Private Key Backed Up and Protected:      

I would like your feedback on my SQL Server Database Configuration Checklist. For example, I would like your input on:

–What is missing from the list? Keep in mind that I will be creating additional lists to cover more specific topics, and that I can’t include every possible variation.

–What on the list could be removed because it is not very important? Keep in mind that the list is designed to be generic, so there will be items on the list that will not be applicable to all SQL Server environments.

–Does my wording make sense, or should I change any of the wording so that it is more understandable or more accurate?

Please add your comments below, and as I get feedback, I will update the checklist. Thanks!

This month’s question of the month was:

What is your best advice for boosting index performance?

As usual, selecting the winning entry was difficult as there were many great tips, but this month’s winner is Robert Miller. Robert has won a US $50.00 Amazon.com gift certificate and a single license (including one year of support and upgrades) for HyperBac for SQL Server, worth US $994.00.

To check out his response, and the responses of the other entries, click here.

Thanks for everyone who participated in this month’s contest, and don’t forget to enter the July Question of the Month.

Post your responses to the above SQL Aloha Question of the Month in the comments section below (at www.bradmcgehee.com if you are viewing this from a syndicated newsfeed). And don’t forget to enter your e-mail address when you post your response, so I can contact you if you win.

This month, because of the nature of the question, which has no right or wrong answer, the winner will be selected randomly from all the entries that are received. I have talked to many DBAs over the years, and nobody I have talked to started out their career wanting to be a DBA. They all seemed to have started one career, and then eventually moved into the role as a DBA (part-time or full-time). If you are not a DBA, but would like to be a DBA, you can still enter. Instead of telling us your story of how you became a DBA, tell us why you want to become a DBA.

For more information, click on the Question of the Month tab above.

This Month’s Prizes

The prizes this month are an Amazon.com voucher worth US$50.00 and a license for Red Gate’s monitoring and alerting tool, SQL Response, with one year of support and upgrades worth US$619.00. Not familiar with SQL Response?

Here’s what one customer has to say:”SQL Response enables you to monitor, get alerted and respond to SQL problems before they start, in an easy-to-navigate, user-friendly and visually precise way, with drill-down detail where you need it most.” John B Manderson

Several weeks ago I wrote a blog post called “What is the Ideal SQL Server Test Box?” At that time I was putting together the specs for a test SQL Server box I hoped to be able to get for my home office. While a laptop is great for giving SQL Server presentations, it’s not the greatest for testing and stressing SQL Server. And while I do have access to some large SQL Server boxes via remote access, I am limited to what I can do on them. So what I really wanted was my own test box that I could reconfigure as needed for testing and benchmarking.

While I was at TechEd, I had a talk with the head of the DBA Tools division at Red Gate Software, and we were talking about software testing when I mentioned that I would like to have my own test hardware at home. And a little to my surprise, he agreed with me. That’s when I started to do some research on what I thought would be an ideal (and still affordable) test box, and that’s when I wrote the above mentioned blog post.

As I was corresponding with the head of the DBA Tools division, he suggested that I also spec out a two-node cluster with Direct Attached Storage (DAS) so that I could test even more scenarios. That was a little more than I was expecting, but I went ahead and put together two proposals: one for a single tower test box, and another with two towers plus a DAS that was configured for clustering. To my surprise, the cluster configuration was approved and I placed the order for it yesterday. Hopefully, in about 30 days, all the hardware will arrive and I can begin to build the cluster and to begin some testing and benchmarking that I have been wanting to do for a long time.

Here is what I ordered:

–Two identical Dell T610 Towers, each with a single, 6-core CPU (Intel Xeon X5670, 2.93 Ghz, 12M Cache, HT, 1333MHz FSB); 32GB 1333MHz RAM; a PERC H700 RAID controller; two 146GB 15K SAS Drives; one HBA (to connect to the DAS); and dual network connections.

–One PowerVault MD3000 DAS with two, dual-port controllers, and 15 146GB 15K SAS drives.

In order to have a proper cluster, I also need a third box to act as a domain controller. Fortunately, I already have a small Dell tower that will now be devoted as my domain controller and be configured so that I can access my network remotely.

While not a huge system, it should more than meet my needs as a home test server, and give me plenty of flexibility to experiment with different configurations. Of course, it is not as big a test system as Paul Randal has, but I think it will do just fine. When I get the equipment and start building the cluster, I’ll blog on the experience. It has been a few years since I built a cluster from scratch, and this should be a good learning experience.

PS: I also want to think the head of the DBA Tools division, and Red Gate Software, for my new test system.

This past month I ran a poll asking this question: “Does your company have a formal, tested, disaster recovery plan for your SQL Servers.” While this was not a scientific poll, nor do I purport that it is representative of the real world, the results are still scary.

As you can see, only about 9% of responders said they have in place a complete, up-to-date, and tested DR plan. And another 9% said that they didn’t even know what a DR plan was.

About 45% said they “sort of” have a DR plan, but it is not complete and/or up-to-date, nor is it regularly tested. And then about 27% said that they wanted to put a DR plan in place, but that they just didn’t have time to complete one. The 9% who answered “other” were consultants, and the question didn’t really apply to them.

It’s disconcerting to think that only about 9% of companies have a valid DR plan, 45% have a “sort-of” plan, and another 36% have no plan at all.

I wonder if the owners of the companies that have “sort of” or no DR plan know about this. If they don’t, then it’s the responsibility of the DBA to inform them. If the owners do know, and have chosen not to invest in the resources to put a DR plan in place, I wonder if they understand the potential consequences of their choice. In any event, I am really dismayed by these results.

For those DBAs who don’t have a complete, up-to-date, and tested DR plan, you might want to consider checking out some of the following resources.

Disaster Recovery for SQL Server Databases

SQL Server Disaster Recovery

How to Write a Disaster Recovery Plan

So, You Want to Write a Disaster Recovery Plan?

Sample Disaster Recovery Planning Guide Provided by Michigan State University

Description of Disaster Recovery Options for Microsoft SQL Server

If you have any more links you would like to suggest, please do so below.

This is my third in a series of checklists that I am putting together for a new book designed for DBAs to help audit their SQL Server instances. Previously I blogged about my SQL Server Hardware Checklist and my SQL Server Operating System Checklist. The goal of this checklist is to help DBAs document how their SQL Server instances are configured, which in turn may reveal some potential changes that could be made in order to better optimize their performance and availability.

Below is my current version of the SQL Server Instance Checklist, which is in the form of a spreadsheet that can be used to collect and store the information. As you review this checklist, you may notice that I have excluded some instance-related information, such as that relating to databases, security, database maintenance, SQL Server agent jobs, and high availability. Because each of these topics are large, I will devote separate checklists for each of these topics. In essence, this checklist covers generic instance configuration settings.

The Basics         Real or Virtual Machine:         Default or Named Instance:         Single or Multiple Instances:         Instance Name:         Port Number:         SQL Server Version:         SQL Server Edition:         32-Bit or 64-Bit:         Service Pack Level:         Version (Build) Number:         Server Language:         Server Collation:         Latest Updates Applied:         List Hotfixes Added:         License Type:         Is Properly Licensed?         Product Key:                   Aliases         Server Aliases & Settings:                   Network Protocols         Shared Memory:         Named Pipes:         TCP/IP:         VIA:         Protocol Encryption Enabled:                   Clustering         Is This Instance Part of a Windows Cluster:         Virtual Server Name:         Virtual Server IP Address:                   Type of Workload         OLTP:         OLAP:         Combination:                   File Locations         SQL Server Executables & Related Files:         Location of System Databases:         Production MDF/NDF Files:         Production LDF Files:         Tempdb:         BAK/TRN Files:                   Tempdb Configuration         Is Tempdb Pre-Sized to Optimal Size:         Current Size of Tempdb:         Is Tempdb Located on Its Own Array:         Is Tempdb Divided Into Multiple Files:         If Multiple Files, How Many Physical Files:         If Multiple Files, Does Each File Have Identical Size:                   Services Installed/Running On This Instance         SQL Server Database Services (MSSQLSERVER):         SQL Server Agent:         Integration Services:         Analysis Services:         Reporting Services:         Full-Text Search:         SQL Server Activity Directory Helper:         SQL Server Browser:         SQL Server VSS Writer:                   OS-Related Settings         Is Instant File Initialization On:         Is the "Lock Pages in Memory" Setting On (64-bit only):                   32-Bit Memory Configuration (If 32-Bit Memory)         How Much 32-Bit Memory is Available to the Instance:         Does Boot.ini File Have the /3GB Switch:         Does Boot.ini File Have the /PAE Switch:         Is the "awe enabled" Server Setting On:         The "max server memory" Server Setting Is:                   SP_Configure Settings Minimum Value Maximum Value Default Current Setting access check cache bucket count (2) (3) 0 16384 0   access check cache quota (2) (3) 0 2147483647 0   ad hoc distributed queries (1) (2) 0 1 0   affinity I/O mask (1) (2) (3) -2147483648 2147483647 0   affinity64 I/O mask (1) (2) (4) -2147483648 2147483647 0   affinity mask (1) (2) (3) -2147483648 2147483647 0   affinity64 mask (1) (2) (3) (4) -2147483648 2147483647 0   Agent XPs (1) (2) (3) 0 1 1   allow updates (5) 0 1 0   awe enabled (1) (2) (3) 0 1 0   backup compression default (2) (3) 0 1 0   blocked process threshold (1) (2) (3) 0 86400 0   c2 audit mode (1) (2) (3) 0 1 0   clr enabled (1) (2) (3) 0 1 0   common criteria compliance enabled (1) (2) (3) 0 1 0   cost threshold for parallelism (1) (2) (3) 0 32767 5   cross db ownership chaining (1) (2) (3) 0 1 0   cursor threshold (1) (2) (3) -1 2147483647 -1   Database Mail XPs (1) (2) (3) 0 1 0   default full-text language (1) (2) (3) 0 2147483647 1033   default language (1) (2) (3) 0 9999 0   default trace enabled (1) (2) (3) 0 1 1   disallow results from triggers (1) (2) (3) 0 1 0   EKM provider enabled (2) (3) 0 1 0   filestream_access_level (2) (3) 0 2 0   fill factor (1) (2) (3) 0 100 0   ft crawl bandwidth (max): (1) (2) (3) 0 32767 100   ft crawl bandwidth (min): (1) (2) (3) 0 32767 0   ft notify bandwidth (max): (1) (2) (3) 0 32767 100   ft notify bandwidth (min): (1) (2) (3) 0 32767 0   index create memory (1) (2) (3) 704 2147483647 0   in-doubt xact resolution (1) (2) (3) 0 2 0   lightweight pooling (1) (2) (3) 0 1 0   locks: (1) (2) (3) 5000 2147483647 0   max degree of parallelism (1) (2) (3) 0 64 0   max full-text crawl range (1) (2) (3) 0 256 4   max server memory (1) (2) (3) 16 2147483647 2147483647   max text repl size (1) (2) (3) 0 2147483647 65536   max worker threads (1) (2) (3) 128 32767 0   media retention (1) (2) (3) 0 365 0   min memory per query (1) (2) (3) 512 2147483647 1024   min server memory (1) (2) (3) 0 2147483647 0   nested triggers (1) (2) (3) 0 1 1   network packet size (1) (2) (3) 512 32767 4096   Ole Automation Procedures (1) (2) (3) 0 1 0   open objects (5) 0 2147483647 0   optimize for ad hoc workloads (2) (3) 0 1 0   PH_timeout (1) (2) (3) 1 3600 60   precompute rank (1) (2) (3) 0 1 0   priority boost (1) (2) (3) 0 1 0   query governor cost limit (1) (2) (3) 0 2147483647 0   query wait (1) (2) (3) -1 2147483647 -1   recovery interval (1) (2) (3) 0 32767 0   remote access (1) (2) (3) 0 1 1   remote admin connections (1) (2) (3) 0 1 0   remote login timeout (1) (2) (3) 0 2147483647 20   remote proc trans (1) (2) (3) 0 1 0   remote query timeout (1) (2) (3) 0 2147483647 600   Replication XPs Option (1) (2) (3) 0 1 0   scan for startup procs (1) (2) (3) 0 1 0   server trigger recursion (1) (2) (3) 0 1 1   set working set size (5) 0 1 0   show advanced options (1) (2) (3) 0 1 0   SMO and DMO XPs (1) (2) (3) 0 1 1   SQL Mail XPs (1) (2) (3) 0 1 0   transform noise words (1) (2) (3) 0 1 0   two digit year cutoff (1) (2) (3) 1753 9999 2049   user connections: (1) (2) (3) 0 32767 0   User Instance Timeout (6) 5 65535 60   user instances enabled (6) 0 1 0   user options (1) (2) (3) 0 32767 0   Web Assistant Procedures (1) 0 1 0   xp_cmdshell (1) (2) (3) 0 1 0             Key for Above         (1) 2005         (2) 2008         (3) 2008 R2         (4) 64-bit only         (5) deprecated         (6) SQL Server 2008 Express only                   Linked Servers         Is This Instance Linked to Other Instances:         Described How Instances are Linked:                   Instance Endpoints         Database Mirroring:         Service Broker:         SOAP:         TSQL:                   Replication         Is Replication Used:         Type of Replication Used:         Replication Role(s):         Names of Instances Involved in Replication:                   SQL Server 2008 Policy-Based Management         Is Policy-Based Management Being Used:         Is This Instance Used to Manage Policies:         List All Instances Managed by This Instance:         List Policies:                   SQL Server 2008 Resource Governor         Is the Resource Governor Being Used:         List the Workload Groups:         List the Resource Pools & Their Settings:         Has the Classification Function Been Fully Tested:         Has the Resource Governor Been Evaluated for Effectiveness:                   SQL Server 2008 Data Collector         Is the Data Collector Used:         Does This Instance Have the MDW:         Is the Instance With the MDW Dedicated:         Location of the MDW:         List Instances Monitored by the Data Collector:         Has the Data Collector Overhead Been Reviewed:         Has the Data Collector Retention Policy Been Reviewed:                   SQL Server 2008 R2 Features Used         PowerPivot:         Master Data Services:         Multi-Server Administration & Data-Tier Application:        

I would like your feedback on my SQL Server Instance Checklist. For example, I would like your input on:

–What is missing from the list? Keep in mind that I will be creating additional lists to cover more specific topics, and that I can’t include every possible instance variation.

–What on the list could be removed because it is not very important? Keep in mind that the list is designed to be generic, so there will be items on the list that will not be applicable to all SQL Server environments.

–Does my wording make sense, or should I change any of the wording so that it is more understandable or more accurate?

Please add your comments below, and as I get feedback, I will update the checklist. Thanks!

Starting today, you can vote for the 2010 Exceptional DBA of the Year. All of the nominations have been reviewed by a panel of judges, and six have been selected to be voted on by the SQL Server community. The winner will not only receive the recognition of being the 2010 Exceptional DBA of the Year, but will receive a full conference pass for the 2010 PASS Community Summit, most hotel and travel expenses, and a license of SQL Toolbelt.

The six nominees include:

–Kevan Riley

–Jonathan Kehayias

–John Burnette

–Tracy Hamlin

–Jorge Segarra

–Ted Krueger

To vote, and to find out more about this year’s six nominees, visit www.exceptionaldba.com. All votes must be in by July 23, 2010.

As a part of a new book project, I am putting together a series of checklists that DBAs can use to inventory and audit their servers. Previously, I blogged about my SQL Server Hardware Checklist, in this post, I am writing about my Operating System Checklist. The goal of the checklist is to help DBAs document how the operating system is configured, which in turn could reveal some potential changes that should be made to the operating system in order to better optimize it for SQL Server.

Unlike the Hardware Checklist, this list may seem somewhat unfamiliar to many DBAs. This is because many DBAs aren’t very familiar with the operating system that SQL Server is running on, and in many cases, the operating system was setup and configured by someone other than themselves.

Below is the current version of my SQL Server Operating System Checklist, which is in the form of a spreadsheet that can be used not only to collect and store the information, but it can also be customized as needed by DBAs to better meet their environment.

The Basics     Real or Virtual Machine:     Computer/Machine Name:     OS Version:     Windows Edition:     OS Build Number:     32-Bit or 64-Bit:     Service Pack Level:     Are OS Updates Current:     IP Address:     What Domain or Workgroup Does the Computer/Machine Belong To:     Is OS Properly Licensed and Activated:     Product Key:     Location of OS Files:           Clustering     Is This Computer/Machine Part of a Windows Cluster:     Cluster Node Name:     Cluster Node IP Address:           Does the OS Have the Latest Hardware Drivers     System:     Display Adapter:     DVD/CD-ROM:     RAID Controller:     HBA:     Network Interface Adapter:     Other:           Partitions     Are Partitions Properly Aligned:     Are Partitions Formatted Using NTFS:     NTFS Cluster Size:     Is NTFS Compression Being Used:     Is NTFS File Encryption Being Used:     How Many Logical Partitions are Available:     Size of Each Logical Partition (by Drive Letter):     Percent Free Space Per Logical Partition (by Drive Letter):           Page File     Location of Pagefile.sys:     Size of Pagefile.sys:     Percentage Free Space in Pagefile.sys (under typical load):           OS Settings     Is "Processor scheduling" Set to "Adjust for best performance of Background services":     Is "Visual Effects" Set to "Adjust for best performance":     Is "System failure" Set to "Automatically restart":     Is Windows Update Set for "Automatic Updates":     Is "Indexing" Running:     Is the Existing Power Plan Set to "High Performance":     Is "Remote Desktop" Enabled:     Has "Audit Policy" Been Enabled:     Have Only Necessary Network Protocols Been Installed:           Which OS Roles are Installed (2008 or 2008 R2)     Active Directory Certificate Services     Active Directory Domain Services     Active Directory Federation Services     Active Directory Lightweight Directory Services     Active Directory Rights Management Services (AD RMS)     Application Server     Dynamic Host Configuration Protocol Server     DNS Server     Fax Server     File Services     Hyper-V     Network Policy and Access Services     Print and Document Services     Remote Desktop Services     Web Server (IIS)     Windows Deployment Services     Windows Server Update Services           Which OS Features are Installed (2008 or 2008 R2)     .NET Framework 3.5.1 Features Overview     Background Intelligent File Transfer Service (BITS) Overview     Biometrics Overview     BitLocker Active Directory Recovery Password Viewer Overview     BitLocker Drive Encryption Overview     BranchCache Overview     Connection Manager Administration Kit Overview     Desktop Experience Overview     DirectAccess Overview     Failover Cluster Overview     Group Policy Management Overview     Ink and Handwriting Services Overview     Internet Printing Client Overview     iSNS Server Overview     Line Printer Remote Port Monitor Overview     Message Queuing Overview     Multipath I/O Overview     Network Load Balancing Overview     Peer Name Resolution Protocol Overview     Quality Windows Audio Video Experience Overview     Remote Assistance Overview     Remote Differential Compression Overview     Remote Server Administration Tools Overview     RPC over HTTP Proxy Overview     Services for Network File System Overview     Simple Mail Transfer Protocol Overview     Simple Network Management Protocol Overview     Simple TCP/IP Services Overview     SNMP WMI Provider Overview     Storage Manager for SANs Overview     Subsystem for UNIX-based Applications Overview     Telnet Client Overview     Telnet Server Overview     Trivial File Transfer Protocol (TFTP) Overview     Windows Internal Database Overview     Windows Internet Name Service (WINS) Overview     Windows PowerShell Integrated Scripting Environment Overview     Windows Process Activation Service (WAS) Overview     Windows Server Backup Overview     Windows System Resource Manager Overview     Windows TIFF IFilter Overview     WinRM IIS Extension Overview     Wireless LAN Service Overview     XPS Viewer Overview           User Accounts & Security     List Account(s) Used for SQL Server Services:     What Permissions Have Been Given to the SQL Server Service Accounts:     List Account Used for Clustering Service:     What Permissions Have Been Given to the Cluster Service Accounts:     Does the DBA(s) Have Local Admin Rights:     Is the Windows Firewall Running?           Physical File Fragmentation     What are the Fragmentation Levels of Existing Partitions (by Drive Letter):     Is Any Active Physical File Defragmentation Being Performed Regularly:           OS Task Scheduler     Are There Any Custom OS Jobs Running in the Task Scheduler?     Is "Customer Experience Improvement Program" Task Running:     Is "Defrag" Task Running"           Other     Is Computer/Virtual Machine Dedicated to SQL Server:     Is Windows Defender Running:     Is Anti-Virus/Spyware Turned On:     If Anti-Virus/Spyware is Active, Have mdf, ldf, bak, and trn files been excluded:     What Non-Default Software is Installed Besides SQL Server:     What version of PowerShell in installed:    

In the above list, there are three sections that many DBAs may not be familiar with. They include:

–Which OS Roles are Installed: When the Windows Operating System is installed, the installer can choose if this particular server has one or more roles. In some cases, combining some of these roles with SQL Server can significantly hurt SQL Server’s performance. In other cases, one or more of these roles may be required. If you install a standard instance of SQL Server, none of these OS roles are required.

–Which OS Features are Installed: Features are similar to roles, but they are smaller in scope. As with roles, some features can hurt SQL Server’s performance, while others might be needed, depending on how SQL Server is configured. If you install a standard instance of SQL Server, none of the OS features are required.

–OS Task Scheduler: Lists the jobs that the OS might be running in the background. For example, by default, every Wednesday at 1:00 the Windows “defrag” program is executed, assuming that the server has been idle for at least 3 minutes. Did you know that?

Which of the above roles, features, and tasks should be on or off is beyond the scope of this blog post, but this topic will be covered in the new book I am working on.

As with the SQL Server Hardware Checklist, I would like your input on my SQL Server Operating System Checklist. For example, I would like your input on:

–What is missing from the list? What OS configuration information should I add? I am especially interested in tracking any OS configuration information that could affect SQL Server’s performance and availability. Keep in mind that I can’t include every possible variation.

–What on the list could be removed because it is not very important? Keep in mind that the list is designed to be generic, so there will be items on the list that will not be applicable to all SQL Server environments.

–Does my wording make sense, or should I change any of the wording so that it is more understandable or more accurate?

Please add your comments below, and as I get feedback, I will update the checklist. Thanks!

I’m in the process of putting together a SQL Server hardware inventory/audit checklist for an upcoming book project. The purpose of this list is to provide a comprehensive listing of all the important information about the components of a physical server used to run SQL Server. Besides collecting hardware information, it is also designed to collect some configuration information that may be of importance for SQL Server performance and high availability.

Below is the current version of my SQL Server hardware inventory/audit checklist, which is in the form of a spreadsheet that can be used not only to collect and store the information, but it can also be customized as needed by DBAs to better meet their environment.

The Basics   Hardware Manufacturer:   Model Number:   Serial Number:   Physical Location of Server:   Tower/Rack/Blade   Warranty/Service Contract Number:   Warranty/Service Telephone Number:       CPU   Number of CPU Sockets:   Number of Installed CPUs:   CPU Model:   CPU Ghz Speed:   Number of Cores per CPU:   Type of Hyperthreading:   Is Hyperthreading on or off:   CPU L2 Cache Size:   CPU Bus Speed:       Memory   Current Amount of RAM:   Additional RAM Capacity Available:   32-bit or 64-bit:   ERC Memory:       Network Adapter   Hardware Manufacturer:   Model Number:   Speed:   Number of Ports per Card:   Number of Cards:   BIOS Version Number:   Is BIOS Version Current:   NIC Duplex Setting:       Storage   Type: Local, DAS, SAN, Combo:   Number of Disk Controllers:   SAS, SATA, SCSI, iSCSI, Fibre Channel:   Controller Hardware Manufacturer:   Controller Model Number:   Controller Cache Size:   Is Their a Cache Battery:   Is Write Back Caching On:   Controller BIOS Version Number:   Is Controller BIOS Version Current:       Local Disk Configuration   RAID Configuration:   Number of Physical Drives:   Physical Dimension of Drives:   Drive Capacity:   Drive Speed/RPM:   Number of Logical Drives:   Total Available Disk Space:       DAS Disk Configuration   RAID Configuration:   Number of Drives:   Physical Dimension of Drives:   Drive Capacity:   Drive Speed/RPM:   Number of Logical Drives:   Total Available Disk Space:       SAN Disk Configuration   SAN Manufacturer:   SAN Model:   iSCSI, Fibre Channel:   SAN Cache Capacity:   SAN Software Version:   Is SAN Software Current:   Number of Attached LUNs:   RAID Configuration per LUN:   Number of Drives Used per LUN:   Capacity of Drives Used in LUNs:   Speed of Drives Used in LUNs:   Available Disk Space per LUN:   Are LUNs Shared or Dedicated:       High Availability   Redundant Power Supplies:   Redundant NICs:   Redundant Controllers:   All Components Connected to UPS:   Is Server Physically Secure:   If Cooling Required, is it Redundant:       Clustering   Number of Cluster Nodes:   Number of Active Nodes:   Number of Passive Nodes:   Type of Quorum:   Type of Shared Storage:   Are HBAs Redundant:   Are Storage Switches Redundant:   Are NIC Switches Redundant:   Are NICs Redundant:       Backup   Tape Drive: Internal/External:   Tape Drive Manufacturer:   Tape Drive Model:   Local Disk:   DAS Disk:   SAN Disk:  

I would really like your input on this list. For example:

–What is missing from the list? What hardware or configuration information should I add?

–What on the list could be removed because it is not very important? Keep in mind that the list is designed to be generic, so there will be items on the list that will not be applicable to all SQL Server environments.

–Does my wording make sense, or should I change any of the wording so that it is more understandable or more accurate?

If you are already using your own hardware inventory checklist, I would appreciate it if you could e-mail me a copy (empty of course) of it to bradmcgehee@hotmail.com. I won’t share the list, but I would like to see how you are keeping track of hardware-related information. Thanks!

SQLBits—The 7 Wonders of SQL Conference—will be held in York, England, September 30 through October 2, 2010. SQLBits is the largest SQL Server conference held in Europe, and this three-day event will be held at York University, allowing up to 500 SQL Server professionals to attend.

The first day of SQLBits will include full-day seminars; day two will include advanced technical sessions; and day three will be the traditional community conference day with sessions targeted towards all skill levels. The first two days are fee-based, while the community conference day on Saturday is free to all attendees.

For more information on this event, check out the SQLBits homepage. Registration is currently not open yet, but be sure to sign up for the SQLBits mailing list to ensure you are the first to know when registration starts. And if you are planning on attending, be sure to mark your calendars now.

The SQLBits call for speakers is also currently open, and you can find out more information on the here.

Recently, Buck Woody challenged myself, Paul Randal, and Brent Ozar to write a blog post on “How I Travel”. I have actually been thinking about blogging about this topic, but I wasn’t sure if anyone would be interested in hearing my travel tips, but since Buck put me up to this, I can blame him if you find this post boring. As I write this, I have purposely not read Buck’s, Paul’s or Brent’s blog posts, as I didn’t want to have their blog posts influence mine. So once I have finished writing this post, I will read theirs and find how many similarities, and differences, we have among our traveling styles. Hopefully I’ll be able to learn from them.

I’ll start off describing what I bring along with me on my trips. Then I’ll finish off the post with specific travel tips I find handy while I am on the road.

What I Bring

Last year I was away from home 125 days, which was just a little bit too much traveling for my tastes. This year I hope to keep it just under 100 days, which is still a lot, but its just part of the job. As you might imagine, I have gotten my packing list, and packing skills, down to a science. In fact, I can pack in less than 30 minutes because I know exactly what I am going to bring on each trip, which includes:

1) My most important piece of travel gear is my Red Oxx Air Boss carry-on travel bag. I have tried out many different kinds of luggage over the years, and this bag is the best. While expensive, it is ruggedly built, and after nearly four years of traveling with it, it still shows no signs of wear. In fact, it looks brand new. Unlike most luggage, this bag is made of fabric, is designed to be carried over the shoulder, is only 21”  x 8”  x 13”  (2,184 cu. in.) in size and weighs just under 4 pounds. The bag’s size is designed for international travel and will fit in virtually any airplane without needing to be checked. If you have ever compared other carry-on luggage sizes, you will find this one much smaller than typical, but its small size is why it’s so great. It is lightweight and I can carry it anywhere.

Now, you may be saying that this sounds like a great carry-on bag for carrying a few things you might need on the plane, but this is the only luggage I carry (other than my backpack which holds my electronics, which I will talk about next). In other words, this small bag holds everything I need to travel, including all my clothing, toiletries, and other miscellaneous items. In fact, I have used this bag for trips that have lasted 30 days, and I have found that everything I need can be carried in it, often with room to spare. I follow the packing philosophy of travel expert Doug Dyment, who runs the www.onebag.com website, which means that I pack very light. Since the topic of packing light is well-covered on his website, there is no need for me to repeat it hear. Check out the website for more information.

2) My second most important piece of travel gear is my MountainSmith Explore backpack, which I use to carry my electronics. While the exact model I own is no longer made, the current model is very similar. The bag is also tough, although it is beginning to show some wear, but that is to be expected after using it for 5 years. This small 18” x 13” x 8” (1,586 cu. in.) bag weighs just under 3 pounds and carries all of my electronics, which includes a laptop and a netbook computer (more on my electronics later). The backpack has many different sections and pouches that makes it easy to hold everything I need to bring along, and to keep it well organized.

3) I carry two computers, my primary and my backup. My primary laptop is a Dell 15” Studio XPS with dual-cores, 6GB RAM, and 256 GB SSD drives. It is relatively lightweight and powerful. My backup computer is a Toshiba NB-205 netbook. While the netbook is slow, it is lightweight and allows me to make my presentations should my primary laptop fail. I protect both computers using protective sleeves, and even with the sleeves on, they both fit comfortably in my MountainSmith backpack, along with my other gear.

In order to keep my two computers synchronized, just in case my primary fails and I need to use my secondary, I use the www.dropbox.com software. This free software (there is also a paid version), will automatically synchronize files between both laptops, in addition to making these same files available via the web. So in the worst case, and I somehow I lose both of my computers, I can still go to the web and download my presentations.

4) While I am not much of a cell phone user, I bring along my BlackBerry Bold 9000, which I mainly use for e-mail, Twitter, and for reading the news when I get bored at airports. I also use it’s built-in GPS and Google Maps to help me find my way about when I am walking. It comes with a leather case to protect it while it rides in my backpack, which is where is normally stays. I hate carrying a cell phone on my belt, unless I have no other choice.

5) Given that I have a GPS in my phone, it might seem redundant that I also carry a Garmin Nuvi 775T GPS that includes both U.S. and European maps. I use this when I am traveling by rental car, as the GPS in my phone is not really designed to be used when driving. I also subscribe to the Garmin map update service so I get updated maps every 3 months. The GPS is protected by a leather case, and the car mount and cable I carry in a plastic bag so they don’t get lost or entangled.

6) To help pass the time in airports and airplanes, and to help mask the incessant noise that pervades both, I bring along a Zune MP3 player; and my Shure SE210 Sound Isolating Earphones, which are much, much better than the standard earphones that come with MP3 players. These earphones are pricey, but worth every dollar.

7) One very important accessory I carry, that is not a common travel item, is my SureFire U2 Ultra variable-output LED flashlight (2-100 lumens). It lasts up to 175 hours in low mode (which is the mode I use most), and I use it all the time to see in the dark, such as when picking up a rental car at night and I want to check the outside of the car for damage and to check the inside to see where all the controls are. It is very small, so it takes up very little space. It also comes in very handy when a building or hotel loses power. I highly recommend every traveler to carry a good flashlight.

8 ) To record my journeys, I bring along a Canon G11 digital camera. It is very small and creates great photographs, especially low-light photos, which I commonly take at events. It is protected by a case, just as it all my electronic gear.

9) Since I never know if it will rain or not, I carry an REI Travel Umbrella, which fits handily inside my backpack, so it is always with me in case of unexpected downpours.

10) While most hotels now have wireless Internet access, a surprising amount still don’t. Because of this, I carry a portable Linksys wireless router. The model I have is no longer made, but it work allows me to work with my computer in bed at a hotel instead of being tethered to a desk when wireless Internet access is not available. In some cases, I still use the portable router, even if wireless Internet is available because using it allows me to use both my computers if I want, all for the same cost. If you use a hotel’s wireless Internet access for two different computers, you have to pay two fees (unless you have free Internet access), one daily fee for each computer. But if you use the portable router, the hotel thinks you only have one computer, and you can connect as many computers to your wireless network as you want. Of course, I encrypt my connection so another person in a different room can’t use my bandwidth.

11) Some of you may be wondering how I can get away with carrying such a small bag for my clothing. This is because I only bring along four different sets of clothing, one of which I wear, which means I only need to pack three sets of clothing. I select clothing that mixes and matches, and is easily washable and wrinkle-resistant. Because virtually all of my trips are longer than four days, I wash my clothes when traveling, which is much easier than trying to bring along clean clothing for every day of travel. I generally use the hotel’s laundry room, or sometimes I wash my clothing in the sink of my hotel bathroom using special pre-packaged laundry soap. All the clothing I bring is lightweight and air dries quickly.

To help keep my clothes from wrinkling while in my Oxx Air Boss travel bag, I also carry along two 18” Eagle Creek Pack-It Folders, one for pants and one for shirts, which fit well within the travel bag’s two outer dividers. Depending on the clothing, I still might have to iron some clothing after unpacking it.

One piece of clothing I always bring along, no matter what the weather is expected to be where I am traveling, is a Patagonia R1 Full-Zip Jacket. This lightweight, non-bulky, warm jacket is more like a sweater than a jacket, and I use it when traveling on planes (most are too cold for me), during cool days, and in cold rooms when attending conferences. When I am traveling in cold weather, I also bring along an Arcteryx Gamma Softshell Jacket. This is a wind and water resistant jacket that I can wear alone, or with my Patagonia jacket if it really cold. Also, when traveling in cold weather, I bring along lightweight gloves and ear protectors, which I store in the pockets of the Arcteryx jacket.

Depending on where I am traveling, or at which event I am speaking at, I may bring one or two pair of shoes. Most of the time, I wear a pair of New Balance walking shoes, but I also sometimes carry along hiking shoes or dress shoes.

12) In my backpack a have a smaller bag that I use to carry small miscellaneous electronic gear, such as cables, USB sticks, batteries, a Kensington Notebook Lock, a Logitech VX Nano Laser Notebook Mouse,  a Logitech Professional Presenter, and electrical adapters if I am traveling internationally.

13) Because accidents happen when traveling (I cut my finger and got a terrible infection while in Australia), I carry an Adventure Smart Travel Medical Kit that I have modified slightly to carry a few more items than what comes standard with the kit.

14) Because I spend a lot of time sleeping on airplanes, I also carry eye covers and ear plugs to help block out the world while I am trying to sleep.

The above list covers most everything that I carry when traveling, although I have left out a few things that would be boring, such as my reading glasses, glass cleaners, toiletries, and so on. In the next section, I will offer some specific travel tips.

Other Travel Tips

1) I only bring along hand-carry luggage, the Red Oxx Air Boss bag and MountainSmith backpack, as I described above. I do my absolute best to not check in luggage. Both bags meeting airline size requirements, which means I rarely (New Zealand Airlines would only allow one carry-on) have to check in my luggage. Checking in luggage is time-consuming (both when checking in at the airport and when leaving the airport after arrival), and is prone to getting lost, especially if my flight connections are tight because of delayed flights. I much prefer carrying these two bags than dragging along luggage with wheels, because there are a lot of places where luggage with wheels doesn’t work well, such as on escalators, curbs, rough sidewalks, or no sidewalks, and getting in and out of taxies, and so on. Because the luggage is so light, and I keep my gear to a minimum, the weight is not bad. In fact, I have a bad back and I have had virtually no problems carrying these bags everywhere I go.

2) When I go through security at the airport, I am fully prepared before I even get to the security checkpoint. I take all metal out of my pockets and put it in my backpack, my liquid toiletries are in a 1 quart plastic bag, and both computers (which are in protective sleeve) are ready to come out in seconds. You can leave the protective sleeves on your laptops when they go through the screen machine, which helps protect them. About one out of three times I am asked to have one or both of my pieces of luggage to be hand inspected as all the electronics I carry might look a little suspicious. I am used to this and it has never been a problem.

3) I make all my own travel plans over the Internet. I have worked at companies in the past the had their own travel department which made all the reservations. I hated that as they always made mistakes. As I book my own travel, these mistakes don’t happen.

4) After traveling many different airlines over the years, I have chosen American Airlines as my main carrier. They have a very good frequent flyer program and treat their best customers (like me who travel 100,000+ miles a year, very well). I almost always get upgraded to business or first class, which is very advantageous for long trips. I avoid, as much as possible, mixing airlines in the same trip, as if you have a problem with your ticket, you often have to deal with both airlines to resolve it. It is much easier to deal with only one airline. I also book my flights so that I have at least two hours between connecting flights. This often makes my day longer, but helps to avoid missed connections. To help minimize the discomfort at waiting at airports, I joined American Airlines Admiral’s Club, which makes waiting much more tolerable. Membership is relatively expensive, but I get a discount for being a frequent traveler, and it is money well spent. I tend to book my flights as far ahead as possible in order to get the best choice of seat. I am very picky about which seat I am on the plane, and use www.seatguru.com to help me select the best available seat, assuming I am not already familiar with the plane I will be flying in.

5) My biggest travel expense are hotels, even more than airplane tickets. I generally stay at Marriott-brand or Starwood-brand (Sheraton, Weston, etc.) hotels. I always select a hotel that is closet to the venue I am attending to minimize the need for taking taxis or renting cars. I prefer to walk to the venue if I can. In most cases, I arrive a day earlier than I need to in order to recover from overnight flights and time zone changes. I also, if affordable, try to get a room with a separate room for the bedroom, which helps to avoid the noise that so often comes from the hallways. I also request a room on the highest floor, away from elevators, ice machines, and busy streets. This helps to minimize noise, which I find distracting when I am trying to work in the hotel room.

6) I try to avoid renting cars, but often I have no choice. I only rent cars from Hertz, and I joined their #1 Club Gold so that my rental car is already ready to go when I arrive at the airport. This way, I don’t have to stay in line waiting to pick up my car. I always reserve the least expensive car available, but in most cases, Hertz will upgrade me to a bigger car.

7) As those who have traveled with me know, I don’t like to spend a lot of time eating at fancy restaurants. To save time and money, I usually eat fast food, or buy groceries at a store and bring them back to the hotel. I also avoid room service (or eating at the hotel’s restaurant), unless I am checking into a new hotel at night, and I have yet to locate any fast food places within easy walking distance, and I am too tired to leave the hotel or room.

8 ) I am a member of the AAA, which helps me to get many discounts at hotels and for rental cars, or can help out if I run out of gas or lock myself outside of a rental car (although this had not happened yet). The discounts save my company a lot of money each year, although I pay for the AAA membership myself, as I also use their services for my own cars at home when not traveling.

While many people envy all my travels, it is actually quite stressful for me. It takes a lot out of you, especially when traveling overseas or when having to take redeye flights. If the travel wasn’t part of the job, I would prefer to stay home and work out of my home office.

I hope you find some of this information useful, and if you have any travel advice you would like to offer, please do so.