August 2006 - Posts

My New SQL Server 2005 Administration Book Out

This weekend, I sent the final introduction out to the publisher on my newest book, Professional SQL Server 2005 Administration. It was a stressful but wonderful experience this time writing again for Wrox. This book is a pretty deep administration book. We had three MVPs, two very experienced authors and four Microsoft employees writing this book and I think you'll be happy with the coverage. You can order the book today on Amazon and it should be out within the next few months.


Professional SQL Server 2005 Administration


The first ten chapters of the book are about administering the various areas of SQL Server, including the developer and business intelligence features.  Chapter 1 briefly covers the architecture of SQL Server and the changing role of the DBA. Chapters 2 and 3 dives into best practices on installing and upgrading to SQL Server 2005. Managing your SQL Server database instance is talked about in Chapter 4. This chapter goes over also some of the hidden tools you may not even know you have.

Once you know how to manage your SQL Server, you can learn in Chapter 5 how to automate many of the redundant monitoring and maintenance tasks. This chapter also discusses best practices on configuring SQL Server Agent. Chapters 6 and 7 covers how to properly administer and automate many tasks inside of the Microsoft business intelligence products like Integration Services and Analysis Services. Developers will find that Chapter 8 is very useful, as it covers how to administer the development features like SQL CLR.  Chapter 9 talks about how to secure your SQL Server from many common threats and how to create logins and users. The last chapter in this part of the book (Chapter 10) covers how to create a SQL Server project and do proper change management in promoting your scripts through the various environments.

Chapters 11 through 15 make up the performance tuning part of the book. Chapter 11 discusses how to choose the right hardware configuration for your SQL Server to perform optimally. After the hardware and operating system is configured, Chapter 12 shows you how to optimize your SQL Server instance the best performance. Chapter 13 shows you how to monitor your SQL Server instance for problematic issues like blocking and locking. Chapters 14 and 15 discuss how to optimize the T-SQL that access your tables and then how to index your tables appropriately. 

Chapters 16 through 20 consist of the high availability chapters of the book. Chapter 16 covers how to use the various forms of replication while database mirroring is covered in Chapter 17.  Classic issues and best practices with backing up and recovering your database is discussed in Chapter 18.  Chapter 19 dives deeply in what the role of log shipping is in your high availability strategy and Chapter 20 presents a step-by-step guide to clustering your SQL Server.

-- Brian Knight

End to End SSIS Classes

Here's an updated schedule of the upcoming SSIS classes


Price: The classes run  $2,200 for 4 days.


Orlando Classes:(Orlando class information at

  Class Dates Cost Instructor Seats Available
  7/24/2006 - 7/27/2006 $2200 Brian Knight Sold Out!!!
  8/21/2006 - 8/24/2006 $2200 Brian Knight Sold Out!!!
Register 10/2/2006 - 10/5/2006 $2200 Brian Knight 1 left!
Register 12/18/2006 - 12/21/2006 $2200 Brian Knight Yes
Register 1/8/2007 - 1/11/2007 $2200 Brian Knight Yes
Register 2/5/2007 - 2/8/2007 $2200 Brian Knight Yes

How to Cluster Windows and SQL Server Videos

   I thought this might be useful for anyone who's looking at learning how to cluster. They're a series of mini-webcasts on how to create a SQL Server cluster from the ground-up (architecture, to windows, to SQL Server 2005).

Description: Before you start learning how to cluster, this video will show you the basics on how clustering in Windows 2003 and SQL Server 2005 works. Brian shows the basic architecture on clustering as well as the checklist that you would want to follow before starting to cluster.
Description: In this presentation, you'll learn step-by-step how to cluster Windows 2003 R2. Brian shows you how to configure and cluster a two node cluster, preparing it for any future services like SQL Server or Exchange.
Description: With Windows 2003 now clustered, you're ready to begin to clustering SQL Server 2005. In this presentation, you'll see how to cluster SQL Server 2005 and some best practices in how to configure the SQL Server cluster after the fact.
-- Brian Knight


Automating SQL Express Backups

SQL Express allows you to distribute a free edition of SQL Server with your applications. There are an enormous amount of 3rd party applications that use it and MSDE (it's SQL Server 2000 equivalent) for their data repository such as Sharepoint (by default). Generally speaking, the user may not even know they have the software on their workstation since it doesn't install anything on their Programs menu. The main weakness in SQL Express is that it doesn't have a scheduling tool. In this set of scripts, you'll receive a canned solution for automating backups of SQL Express. The same logic can be applied to MSDE by changing SQLCMD to OSQL in the batch file. No other syntax used is SQL Server 2005 specific.

The script contains three files:

  • BackupExpress.sql - Does the bulk of the work, backing up every database on the instance other than tempdb.
  • BackupExpress.cmd - The batch file that executes the script. Must pass in the instance name.
  • ScheduleBackups.cmd - Schedules the job by using AT. Must pass in the instance name to schedule like ScheduleBackups.cmd .\SQLExpress.

To do this, make sure you have the Task Scheduler service started. Uncompress all three of the files into the root of your C drive and run the schedule file from the command prompt.

I tried to keep the solution simple since most people that have SQL Express on their workstation may not be technical. So with that said, there is very little configuration or options. The backup solution keeps 7 days of history and constantly overwrites the previous week's backup. So, there's lots of room for improvement if you're feeling adventurous. Please send me any updates and I'll post them for all, giving you credit. The main thing missing from this solution is the index rebuilds, which I'll probably add in the next release.

Download the package here: Enjoy!

Watch the deployment video on JumpstartTV here:

-- Brian Knight