SP2, VLDB Maintenance and TechEd

OK…SP2, the SP2 refresh and then the parallel/subsequent GDRs has seemingly (and rightly so) confused some of us… However, thanks to the PSS Engineers blog (and specifically Bob Ward – Senior Escalation Engineer, Microsoft PSS), this blog entry clears up a lot of that confusion. The end result is that you should be at 9.00.3054 or 9.00.3159. 3054 is the correct one if you haven’t had any special hotfix/GDRs directly from Microsoft PSS and 3159 is for those of you that have. For me, I think the best part was the reiteration of the fact that “Microsoft Update will notify you of this” and the comments made that “Microsoft Update is smart enough to recognize you need this specific version of the GDR2 fix…“. The most interesting part of all of this is the reminder that SQL Server IS included in Microsoft Update. What’s the most interesting is that most people are still using Windows Update and Microsoft Update is DIFFERENT. You need to (essentially) replace Windows Update with Microsoft Update (although it’s not that simple – of course…). Basically, you need to install Microsoft Update and then remove Windows Update. So…. if you haven’t done this – you should. At least on your main desktop/laptop machine (at first) and then on other machines from there. I can’t remember when this originally came into place but a few folks asked me about the difference, etc. and how SQL Server fits in and well… it’s all about Microsoft Update now not Windows Update (however/fyi, Microsoft Update looks and feels exactly like Windows Update but it includes Windows, Office, SQL and Exchange). If you want to find out more, check out the Microsoft Update FAQ here.

And, along the lines of maintenance… Paul Randal (of the SQL Server Storage Engine blog) would like to know if you have time to fill in a survey on YOUR VLDB maintenance practices. This is pretty important for them to know. He explains what they’ll use it for and why it’s useful to them. Be sure to check out his blog entry here.

Finally……….. lots of final session writing/planning going on for TechEd. Bob Beauchemin and I are delivering a pre-conference workshop titled: Leveraging SQL Server Always-On Technologies to Achieve High Availability and Scalability. It’s on the Sunday prior to TechEd and it’s a new session for us. Here’s the abstract:

PRCN06 Leveraging SQL Server Always-On Technologies to Achieve High Availability and Scalability 
System down time and lack of scalability for mission critical applications can result in loss of revenue and business creditability. Planned downtime is typically caused by hardware upgrade, application or OS upgrade, applying a service pack, or performing routine maintenance task. Examples of unplanned downtime are hardware or software failure, natural disasters, and human error. In fact, human error has been identified as the number one cause of downtime. SQL Server 2005 Always-On Technologies provides a full range of options for achieving and maintaining appropriate levels of availability. Because the product offers so many choices, it is difficult to choose features that provide the best availability solution for a given application. In this session, we provide an in-depth description of these technologies and delve into scenarios and best practices in deployment of the availability technologies. The high availability technologies covered include Database Mirroring, Database Snapshots, Peer-to-Peer Replication, Clustering, Online Indexing, Online Restore, Piecemeal Backup & Restore, Partial Database Availability, Table and Index Partitioning, Snapshot Isolation, DDL Triggers, and others. The second part of this session focuses on scalability and building systems that scale-out to multiple servers. Building a scale-out application with SQL Server 2005 may entail using techniques and features that are unfamiliar, or are new. This session provides in-depth information about the internal implementation of scale-out features such as Service Broker, Query Notifications, Distributed Partitioned Views, Scalable Shared Databases, and Peer-to-Peer Replication. The session also includes troubleshooting techniques using Profiler and the new dynamic management views.

As for content, we’ll have our lecture content available to all attendees, we’re going to giveaway AlwaysOn DVDs (more info coming up) AND Bob, Paul and I are going to hang out after the workshop to answer even more questions… So, if you’re looking to burn budget for FY’07 AND you want to attend an information packed (and fun ;) pre-con workshop AND a great conference for breadth/futures (a bunch of Katmai sessions at the event too), then you should sign up for TechEd before it sells out……again. Also, there are a bunch of sessions at the conference that might interest you – Paul and I are doing a Chalk/Talk Q&A on VLDB Maintenance, I’m doing a demo fest on AlwaysOn, Paul’s doing a session on Corruption Detection and Recovery, Bob’s doing a session Windows PowerShell and SMO Together (oh, and he’s listed as Robert Beachemin…not sure why???) …and that’s just to name a few!

Oh, and the AlwaysOn DVDs are cool because:

  1. they have a setup.exe that runs to create vhd/vmc files that allow you to access a predefined VPC image.
  2. Virtual PC is free and Virtual Server is free… you can use EITHER for the Virtual Environment.
  3. the VPC is a Windows 2003 Server setup with SQL Server 2000 and SQL Server 2005 (multiple instances) and allows you to access an environment that’s excellent for learning and testing and…self-paced labs
  4. the DVD includes 9 lab manuals for roughly 16 hours of self-paced lab time AND they’re really good labs with multiple parts, excellent links and even useful undoc’ed commands too (if I might say so myself as I wrote most of them :)

    1. Database Snapshots – 4 Exercises, 75-90 minutes

      • Exercise 1: Repartition the SalesDB Database
      • Exercise 2: Create and Examine a Database Snapshot
      • Exercise 3: Working with Multiple Snapshots
      • Exercise 4: Creating a Database Snapshot on a Mirror Database

    2. Data Recovery & Preventative Techniques – 4, exercises, 75-90 minutes

      • Exercise 1: Examining Foreign Key Relationships between Tables
      • Exercise 2: Point-In-Time Recovery
      • Exercise 3: Using the tablediff.exe Command-Line Utility to Compare ALL Data Modifications
      • Exercise 4: Using DDL Triggers to Prevent Tables Being Dropped

    3. Instant Initialization – 2 exercises, 30-45 minutes

      • Exercise 1: Enabling Instant Initialization
      • Exercise 2: Security Vulnerabilities Created by Instant Initialization

    4. Peer to Peer Replication – 5 exercises, 75-90 minutes

      • Exercise 1: Implementing a Replication-Ready Schema
      • Exercise 2: Configuring and Implementing Peer-to-Peer Replication Configuration Using the Replication Wizards in SQL Server Management Studio
      • Exercise 3: Using the Dual Database Monitor
      • Exercise 4: Adding a new Peer Server
      • Exercise 5: Monitoring Peer-to-Peer Data Flow after a Fault

    5. Table and Index Partitioning – 4 exercises, 75-90 minutes

      • Exercise 1: Range Partition Function
      • Exercise 2: Partition Scheme
      • Exercise 3: Partitioned Table
      • Exercise 4: The Sliding Window Scenario

    6. Snapshot Isolation – 5 exercises, 75-90 minutes

      • Exercise 1: Pessimistic Locking
      • Exercise 2: Activating Snapshot Isolation & Read Committed with Snapshot Isolation 
      • Exercise 3: Using Snapshot Isolation (SI)
      • Exercise 4: Using Read Committed with Snapshot Isolation (RCSI)
      • Exercise 5: Monitoring Snapshot Isolation & Read Committed with Snapshot Isolation 

    7. Online Operations – 2 Parts, 75-90 minutes

      • Part 1: Online Index Operations

        • Exercise 1: ONLINE Index Move (for better isolation)
        • Exercise 2: Partition an Active Table ONLINE

      • Part 2: Partial Database Availability and Online Piecemeal Restore

    8. Database Mirroring – 2 large sesions with TONS of exercises, 4+ hours

      • Part I: Database Mirroring in Action

        • Exercise 1: Configuring and Implementing the High Availability Database Mirroring Configuration – using Transact-SQL through a SQLCMD master script
        • Exercise 2: Using the Dual Database Monitor and Transparent Client Redirect
        • Exercise 3: Initiating Failover in the High Availability Configuration

      • Part 2: Understanding and Implementing Database Mirroring

        • Exercise 1: Configuring and Implementing Database Mirroring using the SQL Server Management Studio
        • Exercise 2: Configuring the Database Mirroring Monitor, Mirroring Threshold Alerts and WMI Event Alerts
        • Exercise 3: Converting to the High Protection Configuration and Comparing Performance between Synchronous and Asynchronous forms of Database Mirroring 
        • Exercise 4: Configuring and Implementing the High Availability Database Mirroring Configuration – using Transact-SQL through a SQLCMD master script 
        • Exercise 5: Initiating Failover 

          1. Part I: Manual and Automatic Failover in the Synchronous forms of Database Mirroring Configuration 
          2. Part II: Preventing “split brain” in the High Availability configuration 

        • Exercise 6: Converting to the High Performance Configuration and Forcing Failover with Potential Data Loss 

    9. Service Oriented Database Architecture – 5 exercises, 3+ hours

      • Exercise 1: Setting up simple Service Broker messaging
      • Exercise 2: Setting up Inter-instance Services
      • Exercise 3: Setting up dialog security and encryption
      • Exercise 4: Setting up application-specific functions
      • Exercise 5: Using Query Notifications

And…….. if that doesn’t motivate you – we might also giveaway a Manageability DVD that’s packed with Tools demos/labs and some SP2 specific stuff such as customized reports (which we’ll talk about in the last part of our pre-conference workshop). OK, so I hope to see you at TechEd………. the pre-conference alone is worth it!


11 thoughts on “SP2, VLDB Maintenance and TechEd

  1. I did more checking, and it is because the URL reference to blogs.msdn.com is so long it won’t fit on the line. It appears IE will wrap a long URL, but firefox won’t. Why even show a URL that long — the web is supposed to be hyperlinks.

    Anyway, not a big deal, really.

  2. There is a small (but important) typo in the version numbers you mentioned: if you have applied the GDR2 Hotfix, you will have 9.00.3159 (not 9.00.3059).


  3. Kimberly,
    Is there any way for me to get the AlwaysOn DVD if I’m not attending TechEd? It looks like a great set of labs for learning.


  4. I’d love to go to TechEd, but can’t fit it into my budget.

    Will the CD be available for purchase afterwards? How can I get a copy?

  5. Hey there everyone – We’re looking into a mechanism to deliver some of this same content to a larger group… but there aren’t any definitive answers as of yet. What will likely be the case is that these will be converted into virtual labs in the not too distant future.

    I’ll keep you posted!


  6. Hi KT

    We met in South Africa at Tech-ed end of last year and with all your knowledge, I have many questions.

    What are your feelings about the following hardware configuration for SQL 2005? The hardware team plan to install HP servers with Enterprise Virtual Machine where we run SQL 2005 in this Virtual machine with / or on blade servers that are added to the VM ware environment. The data would be stored on a SAN. I have read about SLQ 2005 using a SAN for data storage, but not the use of VM ware and Blade servers to run a large system. Your thoughts on this would be appreciated. We are also thinking about the 64 bit option with mirroring this to a disaster recovery site. I hope this is enough information for you.

    Kind Regards

  7. Well… I know everyone is wanting to get copies of the DVDs and I know this as a few of you have also emailed me. So, the good news is that we’re printing more – for upcoming conferences, etc. BUT the bad news is that we don’t yet have a great way (other than handouts at conferences) of getting materials like this to large numbers of people. So, the point, there are still some discussions of options, etc. and I’ll keep you posted for sure! THANKS for all of your interest though as it helps us to know what’s desired/appropriate. Thanks!!

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

SQLskills SQL101: Partitioning

Continuing on our path to understanding the basics and core concepts, there’s a big topic that’s often greatly misunderstood and that’s partitioning. I know I’m


Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.