I had started to write this blog post when we (Paul and I) were on our way back from Zurich on November 21. We had been in Zurich presenting a TechNet DeepDive on Database Maintenance Best Practices...after presenting at ITForum in Barcelona...after presenting at SQLConnections in Las Vegas (well, we did spend 30 hours at home in between those last two conferences :)). Once we returned to the US on Wednesday night, it was just before Thanksgiving and well, a few personal things prevented me from getting as much work done as planned in December... and then the holidays hit... and then I realized I was horribly behind and so I've been playing catchup ever since. Ah, Happy New Year. 

Now, I'm really back in the swing of things and I wanted to let you know what we've done!

First things first - we've posted almost all of our resources from our conferences in November (we still have a few more to tweak/post). We'll catch up with the remaining scripts this week! Check them out on our Past Events page here: http://www.sqlskills.com/pastConferences.asp.

And, now, here are my thoughts as I wrote them on November 21 - with a few updates along the way in this font:

**********

It's been a great 2.5 weeks with 4 full-day workshops, 17 sessions, 3 interviews and 8 flights to conferences/events in 3 countries. OK, Paul has been with me too and that hasn't stopped him from blogging (I know you're all thinking this :) but, he blogs shorter blogs posts and I go for quality rather than quantity (TOTALLY kidding...lol...I am SO in trouble for that one (update: I'm glad I didn't post this until after the holidays!)). And, well I've also been pushing through a bit of a cold (and, Paul doesn't sleep ;=).

So, here's a long post to catch you up on all the travels and even some of the great questions we've been asked while running around from conference to conference. First, it was a great week in Vegas for SQLConnections. We ended that week with a full-day workshop that was all hands-on on SQL Server 2005. Our 80+ attendees downloaded a VPC image and used that for the base environment for labs on Database Mirroring, Database Snapshots, Partitioning, Partial Database Availability and Online Piecemeal Restore... (Update: We received our evaluations from this session (only 37 evals were submitted) but they really seemed to enjoy it! Our reviews were fantastic (literally 4 of 4) and so, we're planning to do this again for the Spring SQL Connections. More details coming.)

And, speaking of the VPC/DVD... We've had a lot of requests for these resources (the DVD, the lab manual, the utilities, etc.) and we're already working on an updated version of this for SQL Server 2008 CTP5 which just came out on Friday (November 16) (update 1/13/2008: we've finished the November CTP update and it will be available on the SQL Server 2008 Readiness Kit). And, that's just a start! (update 1/13/2008: we've also released the DDM, check out Paul's post here). With 9 labs on the current SQL Server 2005 version, a few new exercises planned for the first 2008 version (the labs include exercises on automatic page repair for Database Mirroring AND updates for Peer to Peer including the Peer Topology Viewer, etc.) AND a second DVD on Manageability (already working on labs for SQL Server Policy-based Managment and Performance Studio (update 1/13/2008: these two are done too and they will also be available on the SQL Server 2008 Readiness Kit). I think we'll have a TON of resources to help you get started with SQL Server 2008 by the time it releases. We're also looking (for the first time!) to put together a way for you to access these resources more directly. I *promise* we'll keep you posted on that as soon as we have the final outcome! (update 1/13/2008: and, that's the Readiness Kit! Now, we just need to find out all of the details on how/when you can access it. We'll let you know as soon as it's available! It's likely that you will receive it as an attendee at a launch event - February 27, 2008. Check out the launch portal here and you can see when/where a launch event is coming to you: http://www.microsoft.com/heroeshappenhere/register/default.mspx. I can't promise that they'll all give out the Readiness Kit but, that's the most likely place (of which I'm aware at this point) where you'll receive one. If I find out any additional information, I'll post it on my blog.)

As for some of the favorites from the labs - people seem to love the Database Mirroring SQLCMD master script that sets up the High Availability Configuration for Database Mirroring. So, I thought that this might be an interesting script to post here: GenericDatabaseMirroringSetup.sql (16.73 KB). And, to make it even more flexible, I have modified this script quite a bit and made almost everything parameterized (PrincipalServer, PrincipalDNS, PrincipalPort, MirrorServer, MirrorDNS, MirrorPort, WitnessServer, WitnessDNS, WitnessPort, Database2Mirror, RestoreWithMove, BackupLocation). Also, you can decide to "move" the database being restored on the mirror (to the mirror instance's default data root) OR keep the database backup directories exactly the same (which is generally the recommended configuration). In our lab, we move the database to the instance's default directory when it's restored on the mirror because all three instances are on the same virtual machine. However, in a real-world database mirroring environment, you want to try and avoid moving the database to a different drive location on the mirror because future changes might cause the mirroring partnership to become broken. So, if you want to play with this script, be sure to modify the parameters at the beginning of the script and then make sure you test this in your environment (and, let me know if you run into any snags... I know this works in our VPC but I just whipped up the modified script relatively quickly so it's a *learning* script more than anything!). And, here are a few tips for successfully implementing a database mirroring partnership:

1. Make sure that the principal and mirror server are either identical to each other (in every way) from disk to memory to CPU, etc. When the principal and mirror are identical, you are more likely to minimize performance problems during a failover AND you're less likely to have additional slowdowns on the principal (in a synchronous mirroring configuration) by slower hardware on the mirror. If you don't have identical hardware then you want to either choose an asynchronous database mirroring configuration OR be sure to thoroughly test your before and after failover configurations to ensure that performance is "good enough" in a failover sitation. Some shops find it acceptable to run slower during a diaster (v. having downtime) and therefore they choose a secondary server (for the mirror) that's not quite as powerful as the principal... However, beware that if you're in a synchronous mirroring configuration then your mirror's performance might affect the performance of the principal.
2. Test BOTH your OLTP activity load as well as your batch load AND be sure to test over an entire business cycle. You might be surprised to find that month end processing might surprise you AND/OR a batch process that runs maintenance (for example an index rebuild OR an index defrag over a heavily fragmented table). We've seen a few customers that had network problems when under a significant load and if not tested, this could compromise your mirroring partnership by not allowing the mirror to stay in sync OR it might cause "throttling" at the principal. If the database mirroring partnership has over 1MB of of unsent log waiting, then performance on the principal will be slowed to try and help the mirror catch up.
3. Be sure not to make any assumptions about the way that database mirroring works - even if you have multiple databases being mirrored on the same server. Database mirroring is always between only ONE "principal" database and it's mirror copy. If you want to mirror multiple databases on the same server - to the same secondary server - that's possible. However, this presents additional problems in that a failover is between ONLY a single database and it's mirror. If you have an isolated database failure that causes one of (let's say four) related databases to failover, then three-part naming will suddently start failing as you have a combination of mirrors and principals on the same server and local (three part naming) won't work against a mirror database as the mirror database(s) cannot be accessed directly. As a workaround, you could create an alert on the WMI event: database_mirroring_state_change which then forces a failover for the remaining databases... Effectively all four would then failover. While this will work - and, the alert will fire relatively quickly after the first database fails over, it's important to realize that alerts (and this WMI event) are all *after* the actual failover. The secondary failovers would be asynchronous, response-based events. As a result, there could be a few transactions that fail between the first database failing over and the remaining databases failing over. In a diaster case however, this might be acceptable and if your application is well designed, you might be able to make this relatively seemless...

Also, within the SQL Server 2005 Always On VPC, we found a bug (yes, I know... the shame of it!! but (and this is our saving grace...) it's fairly minor and only requires a couple of quick changes to database mail configuration options. We decided late in the game (which is always a bad mistake) that we needed wanted to change the Windows Server name (and we all know what a pain that is!). In fact, there have always been problems associated with a server name change (at least problems for SQL Server picking up this change):

• The server's @@servername setting will not be correct. There are actually a few problems that can occur as a result of this but it's also very easy to fix. The steps are quick and Tibor (an MVP in Sweden) already blogged them here: http://www.karaszi.com/SQLServer/info_change_server_name.asp so I won't repeat.
• Jobs will have problems when edited. The problem with jobs is that they have a different "originating_server" than the new (when changed) servername and so when you open a job and try to modify it, SQL Server thinks that the server is a target server in a master/target environment. And, when you're just a target server receiving jobs from a master, those jobs cannot be modified, they can only be executed. As a result, the job cannot be changed. If you're going to change your servername, you need to make sure that your jobs change with it. Tibor also blogged the update to msdb.dbo.sysjobs that's necessary.
• This one is NEW for SQL Server 2005 - The Windows groups that SQL Server creates on non-failover cluster servers (if you're on a failover cluster, you must create the security groups manually - check out this whitepaper for more details on failover cluster setup: http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en) will no longer correspond to the server name (after the change). So far, I have not seen nor heard of a case where this creates a problem - but I won't be surprised if one of you responds with an issue or two! SQL Server 2005 uses security GROUPs to manage security and service/component-level permissions so that service account changes don't require permissions to be removed from that account name (in 2000 they did this and the side-effect was that permissions that had already been granted for some other reason - and just happened to be duplicated with the required SQL Server permissions - were removed when you changed the service account. As part of the service account name change and cleanup, they removed all of the permissions needed to run an instance of SQL Server. In SQL Server 2005, they changed this model solely to put you into the correct server group (like SQLServer2005MSSQLUser$servername$instancename) and take the former service account out.

And, of course, there are potentially a lot of [additional/other] external dependencies when you make a servername change... and, well, that's where we missed one. Part of it is because we also setup a POP3/SMTP mail server inside the VPC and when we changed the servername, we also changed the mailserver name (and then forgot to change the Database Mail settings, Outlook Express Account and SQL Server Agent Operator settings). So, you need to change the references to it. The servername was SQLHASP1 and in the June edition of the Always On DVD, we upgraded to SP2 and changed the servername to SQLHAVPC (notice the more generic name... duh!). For completeness, I'll put a bit more detail here:

Database Mail - Manage account, View existing account, check correct domain and server name
SQL Agent - Ensure operators have correct domain and NET SEND address
Outlook Express - Ensure correct server name + domain name

And, now, you should be able to sucessfully use this POP3 mail server in the lab exercises on WMI Events with Database Mirroring. For more details and references (even if you don't have the lab content) check out the links to all of the Database Mirroring whitepapers here: http://www.sqlskills.com/whitepapers.asp. (update: Paul blogged about a bunch of new whitepapers here and some of these are very new!)

**********

And, that's all I had in my November 21 version of the blog post (see, this is what happens, I write a huge post and then start researching things and put the post on the side to post later... and, later is WAY later in this case.... Sadly, I have a bunch of half-finished posts like this. More posts than time :)

So, a lot more to post over the coming days/weeks as the SQLskills team is heads down in SQL Server 2008 working to create some great content (for example, the labs mentioned above).

And, HAPPY NEW YEAR! (at least I said this before January was even over :)

Cheers,
kt

Over the past couple of years, we've had *tons* of requests for the DDM (the Dual Database Monitor - which is a little utility for testing and checking out a mirroring partnership...) and finally, we've (ha, pretty much only Paul :)) made it more flexible (it was hard-coded with specific insert procs). He's created an ini to allow implicit or explicit client redirection (to see the benefits of Transparent Client Redirect), written a help file, and he'll keep you posted with updates/bug fixes...

If you're interested, Paul's blogged about it here: http://www.sqlskills.com/blogs/paul/2008/01/13/DualDatabaseMonitorNowAvailableToOrder.aspx

Have fun and HAPPY NEW YEAR!
kt 

Every time we're at a conference, we get asked whether we're going to write any books on the kinds of things we talk about. Well, at present the answer is no - we just don't have time unfortunately. And, honestly, we like doing smaller chunks of more timely content rather than a complete book which takes a lot longer to "get to press". However, we have a plan that we'd like to gauge interest in to see whether or not it's worthwhile (Paul already blogged about this).

Basically, there are two full-day workshops that we presented this year, Database Maintenance: From Planning to Practice to Post-Mortem and Disaster Recovery: From Planning to Practice to Post-Mortem. Each of these has 100+ slides in them and we add *a lot* of content while we're presenting...just looking at the slides really isn't enough. However, our plan is to take each of these slide decks and then add comprehensive notes for each slide. Once complete, we'll create a full printout of the entire deck - with notes - for US $99.99. In addition to the printout, we will create a section on our website that includes relevant whitepaper links, KB article links, and blog post links - corresponding with specific slides/topics from the deck. Additionally, as we'd be shipping the book to you anyway, we'll throw in a free copy of the AlwaysOn DVD and the Manageability DVD, with around 20 hours of self-paced labs on them. Shipping and handling would be included for domestic US orders, and US $10 extra for non-US orders.

So, if there's enough interest in this, then we'll go forward and try to make these available for ordering by January/February 2008. So - if you're interested in this (no obligation) please drop Paul a mail.

Thanks,

kt (& pr)

Donald Wayne Tripp
Donald Wayne Tripp was born on August 6, 1944 in Fort Madison, Iowa. After fighting a long and courageous battle with cancer, Don passed away peacefully on November 28, 2007 at home in Redmond, Washington, with his daughter and son-in-law by his side.
 
Don is survived by his daughter, Kimberly L. Tripp; his son-in-law, Paul S. Randal; his grandchildren Kiera and Katelyn Randal; his Mother, Doris McDonnell; his brothers Jon McDonnell and Wayne Tripp; his sisters Linda Fussell and Bonnie Bostick; his brother-in-law, Ted Fussell; his sister-in-law Valerie Tripp; his nephew and many nieces; and his wife of 22 years, Ursula Tripp. He will be greatly missed by these and many other relatives and friends.
 
Don was very charismatic, always quick to lighten the mood with his humor - lately he'd say that "I'm in great shape - besides a little cancer". He was an avid sailor and loved nothing more than being on the water - especially Lake Michigan - where he first sailed "Flutterby", an 18 foot SolCat, and then a variety of sail-boats (and ice-boats in WI) over many years. When not on the water somehow, he loved his local haunts and enjoyed an occasional Meyers-and-tonic with friends.
 
By far his greatest pleasure was introducing friends, family, and even acquaintances to sailing - especially as a Regional Sales Manager for Hunter Yachts - which he gladly took on as his second career after working for Moore Business Forms for almost 30 years. For Moore he received numerous "Achievement Awards" in Sales but he was most rewarded by sales that lead people to the boat of their dreams. He was a graduate of the University of Iowa in 1968, a member of the Army and Air Force Reserves for six years and took his first sky dive in 1962, the year he graduated from Fort Madison High School. He loved adventure and had it not been for a hurricane that washed ashore his 40-foot trimaran in 1978, he would have raced in the (London) Observer's Singlehanded Trans-Atlantic Race in 1980.
 
Over the past few months, Don greatly enjoyed bird watching from the deck of our house. He especially enjoyed torturing the squirrels by spraying “Pam” on the poles of the many bird feeders. Don’t worry; it didn’t really stop them from getting the food – but it did provide for many hours of entertainment as they slid down the pole after lunch or on their way to having lunch.
 
A "Celebration of Life" Memorial Reception is being planned for Memorial Day weekend. Please send email to KimberlyLTripp@hotmail.com for more information.
 
In lieu of flowers, donations should be made to a Cancer charity of your choosing. For example, consider donating to the Cancer Research Foundation at http://www.cancerresearchfdn.org/donate/.

OK, well, the first day is over and we're starting to relax... just had a nice meal in our room and we're off to each do a blog post (or what might turn into a couple :) regarding the things we each discussed in our full day workshop today.

So, it was a great day and a great way to start the Connections event. We had roughly 170 people attend our Sunday workshop that started at 9am which is especially impressive in Vegas!! And, it was a great way to start because it felt like everyone was ready with questions and a few folks (especially those with jet-lag) said that they didn't think they'd make it through the day but then were surprised at how fast it went and that we managed to keep them awake :)...

As for the questions, we answered a lot of them during the session but as it always happens, we each remembered additional references, sites, and/or details that we always want to post after the fact - this post is the result. I'll try to put headers on the sections but I think this will be a long one!

Here's a link to Paul's Q&A blog post from today's session:
http://www.sqlskills.com/blogs/paul/2007/11/05/ConferenceQuestionsPotPourri1IndexesStatsCorruptionAndEnterpriseonlyFeatures.aspx

So, have fun and we both look forward to seeing you tomorrow in the pre-conference workshop on Disaster Recovery - from Planning to Practice to Post-Mortem. The rest of this post contains random but helpful stuff (and in NO particular order at all). And, there's at least one more post coming after this one as I still have more to add!

Resource Reference List

• KB#909369: SQL Server 2000 Bug where checkpoint is not appropriately clearing the inactive portion of the log
• KB#329526: File allocation extension in SQL Server 2000 (64-bit) and SQL Server 2005 (this allows 256KB block allocations)
• KB#328551: PRB: Concurrency enhancements for the tempdb database (this refers to trace flag -T1118 for SQL Server 2000 AND how to create tempdb on multiple files when on multiproc machines). A good and very complementary read is the "Working with tempdb" whitepaper. AND, the primary author of this whitepaper (Sunil) will be here at Connections this week, so there's another session to visit.
• KB#224071: How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
• Performance Dashboard Reports (only in SQL Server Management Studio for SQL Server 2005 SP2)
• sysinternals Zoomit tool: this is what I was using to magnify various parts of the screen, etc.
• SQL Server Customer Advisory Team Blog and specifically the entry on DMVStats. Also, here's an excellent whitepaper called Troubleshooting Performance Problems in SQL Server 2005 as this has numerous DMV queries and helpful details on troubleshooting.
• SQLskills Whitepapers list

Progress Report: Online Index Operation
As for the details on this - you want to capture these events: EventClass, TextData, EventSubClass, ObjectID, ObjectName, and BigIntData1

And, for the EventSubClass there are multiple values that will be returned. What I think is the most interesting is that many of these events return only one of the values for either ObjectId or ObjectName...so, beware of filtering!

  1-Start (both ObjectID/ObjectName)
  2-Stage1 start (null for both ID/Name)
  6-Insert row count... (ObjectID only)
  3-Stage1 end (null for both ID/Name)
  7-Complete (both ObjectID/ObjectName)

EventSubClass 6 is definitely the most helpful. In the BigIntData1 column, they will show the current row number being processed (essentially) and so, you can guage roughly how far you have gone as well as how long you have to go. Here's a screen shot to help you get some insight into what you will see: OnlineProgressReport.pdf (690.78 KB).

Some operations (e.g. DBCC CHECKDB (and related), SHRINKFILE, ALTER INDEX...REORGANIZE) produce a value for the percent_complete column in sys.dm_exec_requests. Both of these are helpful for assessing where you're at...

Index Creation/Rebuild Order
Paul's written about this AND he's linking to some relevant posts in his blog post from today's session but here's a VERY quick highlight of a few things we chatted about:

Index CREATION order DOES matter (you should always create the clustered index first and then create the non-clustered (as non-clustered indexes depend on the clustering key)
Index REBUILD order does NOT matter as the physical location of the data isn't interesting to the non-clustered indexes as they reference the data by the row's clustering key

VLFs - Virtual Log Files... too many (and typically very small) OR even possibly too large of VLFs....
We had some great discussions around VLFs and I know I've posted a bit on this in the past but I'm not sure I've posted this much detail. So, here are a few relevant points.

First, the size and number of VLFs is determined by the size of the "fragment" added to the transaction log. The "fragment" is added at the time the log is created or anytime the log grows (either manually or automatically). The number of VLFs can be predicted from the following quick guide:

• If the fragment is less than 64MB, then up to 4 VLFs will be added (the reason I say "up to" 4 VLFs are added is because really small fragments - fragments under 1MB - will actually add even fewer VLFs and I can't remember (nor do I care :) what the exact cut-offs are for < 1MB.
• If the fragment is greater than or equal to 64MB and less than 1GB, then 8 VLFs will be added.
• If the fragment is greater than or equal to 1GB, then 16 VLFs will be added.

So, if you create a database with a 100MB log (just as a simple example), you will get 8 - 12.5MB logs... if you then increase the log to 150MB then you will add 4 more VLFs. This really isn't all that bad and in general, I recommend that you have less than 100 VLFs. Often I'll find folks that have thousands and this results in VLF fragmentation. I blogged about this originally here: http://www.sqlskills.com/blogs/kimberly/2005/06/25/8StepsToBetterTransactionLogThroughput.aspx and the steps to help minimize it are there as well. Also, as an update to that post - use DBCC SHRINKFILE with the NOTRUNCATE as it appears to have special meaning on the transaction log... but, I still need to investigate this one a bit more.

Finally, if you pre-create too large of a transaction log then you might have new/different problems... the most likely is that the log doesn't clear as often (because you haven't spilled into a new VLF OR you have a transaction that happens to span two VERY large VLFs) and the effect can be that performance is slowed by the less frequent clearing that has to happen on a very large log... As a way to minimize this, the best way to create larger logs (logs in the 10GB+ range), is to create them in multiple chunks (for example 4-8GB chunks so that you end up with VLFs that are 256MB-512MB instead of 4GB because you created a 48GB log to start).

Installation Instructions for the post-conference workshop AND the HOLs DVD
And, for those of you who want to play with the DVD we handed out today... here are the "generic" setup instructions:
Generic HOLs DVD SETUP Instructions.pdf (20.31 KB)

OK, that's it for today and we look forward to another infomation/question packed session tomorrow!

Cheers,
kt

OK, so for those of you who watch our (Paul's and my) blogs... you know it's time for SQLConnections. We're in Vegas (and it's Paul's first time here!) and the conference is the largest it's ever been with over 5000 people!!! We arrived a couple of days early so that Paul could experience a bit of Vegas... last night we went out to dinner with Gert and Karen (Gert's wife) and Michele and Andres (Michele's husband) and we went to Mix. I have to admit that I didn't like my main course all that much (I had only one bite of the Cod dish) but everyone else loved theirs (especially the steak/foie gras special)... the 24,000 sphere chandelier was VERY cool and the views of the Strip are outstanding. Tonight we're (MLB/Andres/us) off to see Elton John. Gert and Karen are off celebrating their anniversary... CONGRATS!!

And today, we're getting ready for the conference - having just picked up our shirts (which Paul - yes PAUL - is about to iron...lol) - we swung by to checkout how things are going in terms of setup. It's always amazing to me to see how much goes on behind the scenes of some of these big events (I blogged about some of the behind the scenes of TechEd here) and well, we caught the 30+ people who were setting up and "building bags" for the 5000+ attendees.......

And, so begins our first conference as a married couple... and all of our sessions (for the most part) are together. It's actually really fun to do sessions together as we're able to bounce things off of each other, take notes on things we want to change and/or questions that are asked AND it allows us to keep things moving without a lot of stops. And, to be honest, it's not quite as tiring... but, with 2 pre-conference workshops, 5 sessions at the conference, multiple meetings/dinners/side-events and then a hands-on post-conference workshop on Friday - we'll both be pretty tired!!! We get back home late Friday night and then we have about 30 hours in Redmond to relax (NOT!) before we head to TechEd ITForum in Barcelona next week. From Barcelona we head to Zurich for a TechNet DeepDive session on Database Maintenance...... November is quite a month for getting around!!

Hope to see you here (or in Spain... or in Zurich... or next year),
kt

OK, I still have a way to go in learning about data compression in SQL Server 2008 but one thing that I do know is that nothing is free. So, the trade-off will be performance (i.e. CPU) v. space. And, that's not really a new trade-off wrt to compression. Sometimes that trade-off has other benefits that still minimize the overall cost (for example, backup compression compresses in-memory and before it goes to disk... this actually makes the overall backup process faster because the actual backup written to disk is smaller). However, if we're talking about data and data access, then we need to think more about how the data is going to be used as well as the impact on performance. I can definitely think of many reasons to compress older (and read-mostly, if not read-only) data (mostly due to volume) but depending on the queries and the impact to uncompress it (based on the volume of data being accessed), I'm going to do a lot of testing before I compress high performance/OLTP data. To help estimate the savings on space, SQL Server 2008 offers a stored-proc: sp_estimate_data_compression_savings.

Compression in SQL Server 2005
SQL Server 2005 offers the ability to have read-only data compressed using Windows NTFS file compression. File compression is only supported for secondary non-primary data files and only when they're set to read-only. If the entire database is set to read-only then all files (incl. the primary and log) can be on compressed drives. While supported, and it can make sense to do this when you have large amounts of historical data, it's still not very granular.

The other form of compression in SQL Server 2005 was introduced in SP2 as data compression for the decimal/numeric data types, called vardecimal. First, you enable compression at the database level and then you turn it on at the table level. The primary form of compression used by vardecimal is when your actual values are generally much smaller than the defined/declared decimal/numeric column. For example, if you've chosen to define a lot of columns as precision/scale (38,4) then as a decimal column each value (per column, per row) will take 17 bytes whether you use all of it or not. If you only store the value 87.5 (which would normally take only 5 bytes as a decimal(3,1)) then you're wasting 12 bytes. This form of compression will still be supported in SQL Server 2008 so if you're interested in how the vardecimal type works, check out this whitepaper. As for the new forms of compression... row-level compression is similar to vardecimal, but the other forms are quite different, and very interesting (especially the page-level dictionary compression)!

Compression in SQL Server 2008
In addition to offering support for NTFS file compression and vardecimal, SQL Server 2008 offers row-level compression or page-level compression (which includes row-level compression) AND it offers the ability to turn these on at the partition-level or at the table-level for all partitions. While I think the per-partition option is excellent, you might still want to separate your OLTP and read-only data into separate tables for other benefits (like online index operations which I mentioned here) but, the "table-level only" options are certainly starting to decrease! And, more granular options always means better manageability.

So, how does compression work in SQL Server 2008:
   Paul wrote about backup compression here.
   Sunil wrote about data compression here and here.
   Chad Boyd wrote about both here.

Paul and I will post more on compression... I really want to get some numbers regarding performance and Paul will dive into all of the internals using DBCC PAGE (go figure! :).

Enjoy!
kt

OK, so Paul could be doing worse things while I'm away... but, what was he doing? I guess you'll have to read here and listen here.

Enjoy!
kt

Does it sound too good to be true: transparent data encryption? Well, it kind of is and kind of isn't. Let me explain. Transparent means that the application developer doesn't do anything (and if you have a third party app in which you can't do anything anyway, then this is even more important). Once enabled, the data is seemlessly protected.

But, protected from what? That's the part that needs a bit of explaining...

We all talk about the importance of physical security (I hope this doesn't sound new)? We all talk about the importance of protecting the physical media to which you've backed up your database(s)? We all talk about protecting the server/machine from physical access and of course, protecting the mdf/ldf files from unauthorized access if we copy them to other machines for attach. And, we all know that with all releases of SQL Server, up to and including SQL Server 2005 (with one exception - details coming up), that our databases are susceptible to unauthorized access if someone gains access to the physical files (whether the actual data files and/or the backup). The one exception is if data within the database has been encrypted using SQL Server 2005 column level encryption. As long as your database master key (created to allow encryption within the database) is strong and protected, then the data that is encrypted with it is also protected (you're only as good as your key protection mechanism). However, any data that is not encrypted is fair game to any system administrator who solely attaches the database or restores it. This is not good. However, it is reality in general. Physical security is incredibly important.

Now, having said all of that, SQL Server 2008 is going to change the landscape a bit. SQL Server 2008 is going to offer a way of protecting this "resting" data - transparently. Basically, once this is turned on, your data is protected from an unathorized attach OR when backed up, unauthorized restore.

This feature is not in the current CTP but I hope to see it in the next publicly available CTP (which will hopefully be out soon). So, when we can finally see it, I'll give you more details such as how to implement it, whether or not it negatively/positively impacts other features and the impact on performance.

Thanks for reading!
kt

With the November 5-9th SQL Connections conference in Las Vegas around the corner, Shirley Brothers, the Connections Conference Manager, would like to start planning the Spring show. And, for the Spring show forward, Paul and I will be the co-chairs of the SQL Connections conference. We would like to invite you to submit abstracts for the Spring 2008 SQL Connections conference, to be held in Orlando, April 20-24th, 2008.

The conference will take place shortly after the SQL Server 2008 launch (Feb 27, 2008), and will focus on best practices for SQL Server 2005, how to upgrade and migrate applications from SQL Server 200x to SQL Server 2008 and new features to leverage in SQL Server 2008. However, we will consider all topics - from design to performance to troubleshooting to architectures to programming. Also, we're likely to closely follow our past conference format with three primary themes/tracks: Infrastructure and Scalability, Programming, and Business Intelligence. You should aim for the intermediate to advanced audience for any SQL Server 2005 materials and for SQL Server 2008 - we'll consider any level - especially depending on the newness of the feature.

For submitting session abstracts, please use this URL:  http://www.deeptraining.com/devconnections/abstracts

Please keep the abstracts under 200 words each and in one paragraph. Please do not use bulleted items or line breaks, and please use a spell-checker. If you have an issue with the site, please follow up with an email as well to paul@sqlskills.com.

Please create a new speaker record (if you haven't submitted before) and submit at least 3 abstracts; however, it will help your chances of being selected if you submit 5 or 6 abstracts. We need to have all your abstracts by October 26th. 

Finally, please see some important notes about your sessions at Connections conferences. These comments are from our Connections Conference Manager - Shirley Brothers:

=========================================================================
I would like to thank each and every one of our Connections speakers for helping us make this event so successful. Last year in Vegas we had just over 4,700 attendees; we hope to do that or better this fall. By the end of September we had over 3,000 registrations for our Fall show.

A successful show happens when you have a combination of things come together: great speakers, good venue, great partners, fun events, and ongoing relationship building. I hope we can all continue to work together to make Connections the very best event outside of Tech Ed, for years to come. As a more successful show, our competitors are trying to compete by moving their shows closer to our dates and in some cases, to the same cities.

It’s very disheartening for me to see our speakers presenting similar topics at competing shows that are scheduled so close to our own shows. So I want to make a small change in how we handle speakers. Essentially, I don’t want to schedule a speaker at a Connections show who is also presenting at one of our competitor’s shows, in the same state, within 30 days of a Connections show. Many of you have known me for years and you know that I would never discourage a speaker from doing something that is good for his/her career and company. I have never asked a speaker not to speak for a competitor and I am NOT saying that now. I am saying that if another show sits within 30 days of ours in the same state, that the speaker should choose to speak at one or the other conference, but not both. If for whatever reason a speaker does not choose Connections during that time period, there will NOT be hard feelings and they can speak for one of our shows at another time. 

I want Connections conferences to be different and unique in the minds of our attendees, our sponsors, and our speakers. I have given this a lot of thought and I think the best way to produce our Connections shows is not to have any of our speakers presenting at our show and at a competitor’s show a few weeks earlier or later in the same place.

Shirley
=========================================================================

OK, so thought I'd do a follow up to the post I did a couple of days ago titled: The perils of case-insensitive data (and our life in tangent-land). The reason I'd like to followup on it is that I received some excellent comments and I want to make sure that you're all aware of the tips/tricks and recommendations that there were (some of you may not have returned to see all of the comments). Really, I was impressed by the speed at which people responded as well as the great comments (and things I learned!). It just reminds me of the fact that none of us can know everything AND that our SQL community is awesome in its willingness to share and communicate.

As for the tips/tricks and "yes, duh!" realizations I came to... here are the interesting points from the comments:

First - why did my comparison work for a single character (e.g. '%A%') but not when I did a character range (e.g. '%[A-Z]%')? Well, it was because it was unicode! This was a "right! duh!" realization that I think I dreamed after I wrote this BUT, Hugo Kornelis is exactly right in his comment. Thanks Hugo! Here is a direct cut/paste of his comment:

The reason [A-Z] doesn't work, is that a collation doesn't just govern case sensitive vs case insensitive but also (amongst others) the sort order of letters. And most case sensitive collations sort like A - a - B - b - ... - Z - z. So [^A-Z] would include all letters except the lowercase z.

You can use [A-Z] to find uppercase characters in a binary collation (since all uppercase characters are in one range of ASCII, and all lowercase characters in another), but not in any other collation.

And, you can check out more from Hugo on his blog: http://sqlblog.com/blogs/hugo_kornelis/default.aspx

Second - the comparison query that I wrote all together (where I stated each letter individually in the WHERE clause) only took a few seconds to write (thanks to cut and paste :) AND it did work...And, sometimes getting something to work and moving on is all we can do (come on - you've ALL been there, eh? :). However, my main comment was that "it wasn't pretty". A much more elegant and unbelievably simple solution came from David R Buckingham (aren't the great answers always the really simple ones :)). Here is a direct/cut/paste of his comment:

The following query will return any fully lower case names in the table:

SELECT LastName
FROM Person.Contact
WHERE LastName COLLATE Latin1_General_CS_AS_KS_WS = LOWER( LastName ) COLLATE Latin1_General_CS_AS_KS_WS

I don't believe that David has a blog... maybe he should :).

Third - a very cool and clever trick that came in from Denis Gobo is related to the performance of repeatedly doing case-sensitive searches on a case-insensitive column. I suggested that creating an additional column (preferably a computed column that uses the case sensitive collation) would be an easy and optimal solution. This is still definitely true when the case-insensitive values are NOT selective enough to warrant using an index and the case-sensitive values are... However, if both the case-sensitive AND the case-insensitive values are reasonably selective then the trick that helps is from his comment. Here is a direct cut/paste of his comment:

Kimberly, the way to force an index seek is to do this

SELECT *
FROM MyTestContacts
WHERE Lastname = N'adams'
AND Lastname COLLATE Latin1_General_CS_AS_KS_WS = N'Adams'

The WHERE might return more than one row but the AND will return only the case sensitive one

I wrote about that a while back here:
http://sqlservercode.blogspot.com/2007/05/make-your-case-sensitive-searches-1000.html 

And, you can check out more from Denis on his blog: http://sqlservercode.blogspot.com/

Now, as for the issues related to creating a view in a database that has a different collation from the server's collation... Here, I'm fairly certain that there's still a bug. However, I'm happy to say that I don't think that it's the most likely situation that exists for collations. I think the two most likely situations are:

1. The server has one collation. The database inherits that collation. The database developer makes column level collation changes throughout the db. This seems to work well. OR
2. The server has one collation. The database has a different collation. The database developer consistently uses that collation throughout their app. A good example of this is where people have case-sensitive databases on case-insensitive servers. This works fairly well (although there are some issues wrt to temp tables, etc. and default_collation is a good thing to know).

I guess there's even a third one where column level changes are made in a database whose collation is different from the server but where there aren't any views that also change the collation to yet a different collation (and this is where there seems to be a bug).

So, this was an excellent (and reasonably fun :) :) exercise to go through wrt collations. And, this is how I (we?) learn! I really want to thank everyone for reading - and commenting/sharing! - the things they learned/knew. That's part of why I love the SQL Server community. And, speaking of which, I thought I'd end this entry with a few community links - as a reminder to everything that's out there:

• SQL Server "Community" page on TechNet
• SQL Server MVPs
• Microsoft Regional Directors
• Blogs from the SQL Server team (and, it's Paul who originally blogged this when he was still with the SQL Server Storage Engine team)

Thanks for reading! Thanks for commenting!
kt

OK, so after SQL Connections in Las Vegas, Paul and I head off to Barcelona for the second week of TechEd's two week event (week one for developers and week two for IT professionals). November's definitely a busy month. So, if you're in the US - we hope to see you at Connections and if you're in Europe, we hope to see you at Microsoft TechEd ITForum 2007.

Here's what we're doing:

Sessions

• (DAT205) The Next Release of Microsoft SQL Server: Manageability Overview

The next release of SQL Server will contain exciting new manageability features targeted at reducing total cost of ownership. Come learn more about what's in store in areas like policy-driven administration and performance data collection and analysis. The session focuses on the database engine.

• (DAT301) SQL Server Indexing - Unravelling the Unknown

Knowing tips and tricks to indexing is extremely helpful and will help you to solve "known" query problems. But what's lurking in the unknown? Is SQL Server using your indexes? Or, do you have a bunch of indexes sitting around wasting space and negatively impacting performance? Finally, SQL Server 2005 has an answer! SQL Server 2005 DMVs (Dynamic Management Views) can provide you with valuable information about your current indexing strategies, what should be removed, and even what's missing. Do you know how to find this information, leverage it, and then programmatically respond to it? Come to this session to learn a few tips and tricks as well as how to figure out the unknown indexing problems!

• (DAT305) Secrets to Fast Detection and Recovery from Database Corruptions

How can you tell whether your data is corrupt? If you have corruption, how do you work out what’s wrong with the database? How do you ensure you have a valid backup? If you don’t have a valid backup, how and what do you repair? If you do have a backup, how do you work out whether you should restore or repair? And at what granularity? How do you go about determining what went wrong in the first place? It’s all about limiting downtime and data-loss when a corruption occurs - from knowing the tools to understanding the choices to planning a successful strategy. Some of the features discussed:

• Torn-page detection and page checksums
• IO read-retry
• Backup checksums
• Consistency checks (DBCC CHECKDB and related commands)
• Database repairs

Facing database corruption is almost inevitable in every DBAs career - make sure you're prepared when it happens to you.

Chalk-talks

• (DAT01-IS) SQL Server Upgrade Best Practices, Tips, and Tricks

Even though SQL Server 2005 has been out for a while, many companies are only just getting ready to upgrade. Come to this session to learn best practices, tips, and tricks distilled from two years of customer experiences. We'll also touch on some issues you'll face going to SQL Server 2008 when it's released next year. Come to this session to learn and share - bring your questions and experiences!

• (DAT02-IS) SQL Server 2005 Database Mirroring: Setup to Implementation to Monitoring

Database Mirroring is one of the most exciting technologies in SQL Server 2005. With more and more people including it in their Disaster Recovery Strategies, it's important to know when to implement Database Mirroring as well as the implications of the architecture you choose. In this session there will be no slides just demos that will explore how Database Mirroring works in its various configurations and how that may effect your performance. Join us and see database mirroring in action and get your questions answered!

• (DAT07-IS) DBCC Internals

All DBAs should have heard of (and used) DBCC – especially for consistency checking. Get down deeper than 400-level with this session on how some of the most important DBCC commands work. Topics covered include CHECKDB, SHRINKFILE, INDEXDEFRAG, and more.

Instructor-led labs

• (DAT03-ILL) SQL Server Always On Technologies Instructor-Led Lab: Part 1 - Database Mirroring

See Database Mirroring in action! From implementation to monitoring to failover, database mirroring provides an ideal solution for many disaster recovery scenarios and this session will prepare you to handle them with minimal downtime or data loss.

• (DAT04-ILL) SQL Server Always On Technologies Instructor-Led Lab: Part 2 - Database Snapshots

Database Snapshots are useful in many situations: database maintenance, data recovery, and point-in-time data access. You can even create a snapshot on a mirror database to get better return-on-investment (ROI) on your high-avaialbility (HA) investments. In this session we will explain how database snapshots work as well as go through several exercises, including working with multiple database snapshots and creating database snapshots on a mirror database.

• (DAT05-ILL) SQL Server Always On Technologies Instructor-Led Lab: Part 3 - Online Operations

The bane of any DBA's life is to have to take data offline to perform maintenance or recover from a disaster. The various Online Operations in SQL Server 2005 alleviate much of this frustration. This session will show you how to move a table online for better isolation and control, partition a table online, access a database that's partially damaged, and perform online piecemeal restore.

Lunchtime Demos

• (DAT01-PD) Database Recovery Techniques

In this fast-paced demo session nasty things will be done repeatedly to database. Then the methods and approaches to recovery will be shown. Not for the faint-hearted!

So, just like SQL Connections the week before, serious amounts of info with tips and tricks for you to take home and implement!

It's going to be a great week. We hope to see you there!!

Kimberly (and Paul)

OK, it's about that time again - the Fall conference season - is here! Building on our co-presented Database Maintenance workshop at SQL Connections in Orlando, Paul and I are doing a *ton* of stuff at SQL Connections this Fall. The conference is back at the Mandalay Bay hotel and officially runs from November 5^th to 8^th, with pre-con workshops on the 5^th. But, after Spring, we decided to significantly add recovery content to our maintenance content that we decided to have our maintenance content as a pre- pre-conference workshop on the 4th and then on the 5th, we have all new content on Disaster Recovery and Lessons Learned. AND, after the week of sessions, we decided to add a HANDS-ON (bring your own laptop) post-con workshop on the 9^th! Our day off is Tuesday the 6^th as it's Microsoft day... with a session line-up looks great with lots of juicy details about SQL Server 2008 - as well as some best-practices sessions for those of you who are happy with SQL Server 200x for now.

Here's what we're doing:

Workshops

• November 4^th - Pre-pre-con: SPR301: SQL Server Database Maintenance: From Planning to Practice to Post-Mortem

No matter how much effort you spend on the design of your database, if you don't maintain it in production then it will suffer from performance and manageability problems. The key to continued performance and smooth operations is a well thought-out and automated database maintenance plan. This full-day workshop has three sections: planning, practice, and post-mortem. Planning for database maintenance actually starts with database design, so one of the things covered will be how to avoid design choices that limit database maintenance or contribute to maintenance problems. We'll discuss a laundry-list of maintenance problems and then explore how to tell if you need to mitigate them, strategies and best-practices for doing so, and how to avoid having your mitigation choices cause unforeseen and undesirable side-effects. Topics covered will include database files (shrink, grow, virtual log files, log size/management), consistency checks and corruption detection, fragmentation, statistics, backup/restore (options, granularity, strategies) and recovery models. The workshop will vary between 200-400 level covering ALL the key concepts of maintenance features. Finally, we'll spotlight some real-world examples where people made good and bad choices and discuss how you can repeat or avoid them, respectively. If you're wondering how to bring your database back under control, and keep it there, then this full-day workshop will help you tame maintenance problems whether you're a full-time system administrator or a reluctant DBA.

• November 5^th - Pre-con: SPR303: SQL Server Disaster Recovery: From Planning to Practice to Post-Mortem

Every DBA's nightmare is having down time and data loss and not knowing how to recover. However, designing and implementing a successful disaster recovery strategy is easier said than done. It's about asking all the right questions and figuring out all the best answers for your situation. This full-day workshop has three sections: planning, practice and post-mortem. Planning is a critical part of disaster recovery, but the most-often disregarded. Topics we'll cover here include: How do you choose technologies to fit requirements and effectively use key features of SQL Server 2005? How do technology choice affect workload performance? Putting a well-thought out plan into practice requires even more planning and in this section we'll discuss technology implementation, building step-by-step recovery/operation guides for when disasters happen, and, most importantly, testing your plan by simulating real problems. In the final section, we'll spotlight some real-world examples where people made costly mistakes and show you how they could have been avoided with a little planning and practice. If you've ever had nightmares about disaster recovery (or actually had a disaster!) and been at a loss for what to do, then this full-day workshop will give you the direction and technical details you need for success!

• November 9^th - Post-con: SPS302: SQL Server - Put Your Knowledge Into Action (Bring Your Own Laptop)

After a week of learning and watching demos - spend your last conference day putting your knowledge into action and diving deeper into the implementation details. Bring your own laptop to install our VPC environment setup with hands-on lab exercises to walk you through some of our most important features in Database Maintenance and Disaster Recovery. All labs will be ILLs (instructor-led labs) with supporting hands-on lab content *and* you will walk away with your own copy of the DVD to continue the exercises back at your office. You can attend without a laptop but your experience will be significantly better with one! This is meant as an advanced workshop and you're expected to bring a reasonable laptop configuration in order to participate: * Virtual Server or Virtual PC - already installed * At least 1GB of physical memory w/512MB dedicated to the VPC environment (2GB is preferred w/1GB dedicated to VPC) * 12 GB of physical disk space (20+ GB is preferred) * DVD Drive

Sessions

• SDB351: Follow the Rabbit - Interactive Q&A on Availability

In this session, Kimberly Tripp and Paul Randal will have only 5-10 slides. The focus of this session is on mixing availability technologies to create the best overall architecture to minimize downtime and data loss. In general, we're going to focus on best practices and then open up to your questions so that you can drive the discussion! This session might not seem as structured as other sessions, but you'll be surprised at how informative and fun it is! Grab your lunch and come back - we'll probably still be hanging out!

• SDB350: SQL Server Table Strategies - Designing for Performance and Availability

Often tables are designed based solely on the data that needs to be tracked (here's a column name, here's a data type - done!). Unfortunately, design does not usually take into account how the data is going to be used OR how SQL Server uses the data. Knowing the internals of table structures as well as the optmizations that come with good design will make your database truly scalable. Come to this session to learn some internals as well as various design strategies such as vertical and horizonal partitioning. Additionally, are there any other features that require changes in your design and thinking? For example, online index operations impact design because of the limitations that exist with partitioning and LOB columns. If you want to scale, you need to be here!

• SDB347: SQL Server Indexing for Performance - Finding the Right Balance

In terms of performance tuning, there are few silver bullets. If I had to choose ONE area that improves performance the most (when designed appropriately!), it's indexing. However, indexing strategies depend on the data and even more so, the usage of the data. Come to this session to see what indexing strategies help the base table the most as well as how to optimize your worst performing queries.

• SDB348: SQL Server Indexing Strategies - Are You Sure?

Knowing tips and tricks to indexing is extremely helpful and will help you to solve "known" problems. But what's lurking in the unknown? Is SQL Server using your indexes? Or, do you have a bunch of useless indexes? Finally, SQL Server 2005 has an answer! SQL Server 2005 DMVs (Dynamic Management Views) can provide you with valuable information about your current indexing strategies, what should be removed, and even what's missing. Do you know how to find this information, leverage it, and then programmatically respond to it? Come to this session to figure it out!

• SDB349: Follow the Rabbit - Interactive Q&A on the Storage Engine and the Relational Engine

In this session, Kimberly Tripp and Paul Randal will have only 5-10 slides. Each slide cover topics for discussion as well as the reason(s) for why something might be behaving badly and/or things to try to solve your problems. In general we're going to focus on best practices and then open up to your questions so that you can drive the discussion! Paul will focus on the SE (Storage Engine) and internals and Kimberly will focus on the RE (Relational Engine) and query tuning/performance. This session might not seem as structured as other sessions, but you'll be surprised at how informative and fun it is!

At this event, you'll be able to get what most conferences offer (and that's breadth - in terms of session choices, etc.) but with the large number of workshops and the detailed planning that went into sequencing the conference sessions, you'll also get depth that no other conference offers. Serious amounts of tips and tricks for you to geek-out on with us and take home to immediately apply.

It's going to be a great week. We hope to see you there!!

Kimberly (and Paul)