The 20-page whitepaper I wrote this Spring for the SQL team has just been published. This whitepaper is titled Proven SQL Server Architectures for High Availability and Disaster Recovery and ties in with the longer whitepaper I wrote last year on High Availability with SQL Server 2008 (see here). This new whitepaper describes the five […]
Paul S. Randal
In Recovery...
Whitepaper on high-availability architectures published
Posted on: June 4, 2010 11:00 am
A SQL Server DBA myth a day: (30/30) backup myths
Posted on: April 30, 2010 9:19 am
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) The month is finally over so time for the grand […]
A SQL Server DBA myth a day: (23/30) lock escalation
Posted on: April 23, 2010 12:14 pm
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Another really commonly-held belief… Myth #23: lock escalation goes row-to-page […]
Tracking expensive queries with extended events in SQL 2008
Posted on: October 15, 2009 4:13 pm
As part of the 2008 DBA class we’re teaching down here in Melbourne, I did a demo of using predicates and file targets with extended events, so I want to blog the script for people to play with. For background info on extended events see: My TechNet Magazine article from 2008: Advanced Troubleshooting with Extended […]
SQL Server 2008 High Availability whitepaper published on MSDN
Posted on: September 28, 2009 12:42 pm
The 35-page whitepaper on high availability I wrote for the SQL team over the summer has been published on MSDN. It’s a 2-300 level whitepaper that describes the various high-availability technologies in SQL Server 2008 and how they can be used to mitigate disasters. It’s chock-full of links to other whitepapers, technical articles and Books […]
DBCC CHECKFILEGROUP bug on SQL Server 2008
Posted on: September 23, 2009 1:54 am
Here's an interesting bug that surfaced recently, first reported by Bryan Smith on the MSDN disaster recovery/HA forum three weeks ago. One of the mechanisms I advise for running consistency checks on VLDBs with multiple filegroups is to run successive DBCC CHECKFILEGROUP operations on the filegroups holding the partitions (see CHECKDB From Every Angle: Consistency Checking Options […]
New whitepaper on Resource Governor
Posted on: July 18, 2009 12:19 pm
A new whitepaper has been published that comprehensively covers the Resource Governor feature of SQL Server 2008, written by fellow MVP Aaron Bertrand and SQL PM Boris Baryshnikov. I read through this a while back (but didn't have the bandwidth to do a full technical review) and it's an excellent whitepaper with lots of code […]
Scripting schema AND data with SSMS in SQL 2008
Posted on: June 26, 2009 10:16 am
It's very commonly known that you can use the Script Wizard in Management Studio to script out tables or even an entire database. It's NOT commonly known that in SQL Server 2008, the wizard was upgraded and you can now script out the data too. The feature isn't enabled by default, which is why not […]
SQL Server 2008 Data Compression whitepaper published
Posted on: May 29, 2009 12:59 pm
The SQLCAT team have published another excellent whitepaper – this time the long-awaited one on the SQL Server 2008 data compression feature. Thirteen people inside and outside Microsoft (including me) provided technical reviews and the authors (Sanjay Mishra along with Marcel van der Holst, Peter Carlin, and Sunil Agarwal) did a great job. I remember […]
Importance of having a manageable environment
Posted on: May 10, 2009 11:22 am
Last week's survey was two-fold – what's the largest SQL Server database you manage, and how many SQL Server databases are you responsible for managing. Here are the results as of 5/10/2009. As far as the database sizes are concerned, the distribution curve isn't surprising, but the average size of databases *is*. 70% of […]
What is FSAgent.exe and why does it have lots of waits?
Posted on: May 6, 2009 4:21 pm
Another quickie today. If you're using Activity Monitor in SQL Server 2008, or Performance Data Collection with the Server Activity system data collection set, then you may see a constant, and large number of 'Other' SQL Server Waits. When you drill into these, you see that the highest number of waits are for FSAgent. The FSAgent is […]
Misconceptions around FILESTREAM storage
Posted on: May 6, 2009 1:47 pm
This short post is prompted by a question that came in through Twitter – I *knew* it was worth joining and spending time on it (http://twitter.com/PaulRandal). The (paraphrased) question is "can FILESTREAM data be stored remotely?". This has been confusing people, and neither FILESTREAM BOL nor my FILESTREAM whitepaper (see here) explicitly answer the question. […]
Misconceptions around TF 1118
Posted on: April 23, 2009 9:19 pm
[Edit 2016: This trace flag is required in all version of SQL Server up to and including SQL Server 2014. Every instance of SQL Server in the world should have this trace flag enabled. In SQL Server 2016, the behavior enabled by the trace flag is the default, so the trace flag is no longer […]
FILESTREAM garbage collection
Posted on: April 21, 2009 9:29 pm
In my previous posts on FILESTREAM I discussed the directory structure of the FILESTREAM data container and how to map the directories to database tables and columns. In this post I’m going to explain how and when the FILESTREAM garbage collection process works as that doesn’t seem to be documented anywhere (even in the FILESTREAM whitepaper […]
SQL Server 2008 SP1 has been released
Posted on: April 7, 2009 7:17 pm
See http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19 for the download, release notes, etc
Importance of choosing the right LOB storage technique
Posted on: April 3, 2009 8:46 am
Last week’s survey was on how you should store large-value character data in SQL 2005+ (see here for the survey). Here are the result as of 4/3/2009 – and I think my favorite answer is starting to catch-on: My favorite answer is, of course, it depends! For all those who didn’t answer ‘it depends’, your […]
TechNet Magazine: feature article on Common SQL Server Security Issues and Solutions
Posted on: April 3, 2009 7:35 am
The May 2009 TechNet Magazine is now available online, and it's the annual security issue. In there is an article I wrote highlighting 10 common security issues (and solutions) you should worry about if you're not a security-savvy DBA. It covers: Physical security Network security Attach surface minimzation Service accounts Restricting use of administrator privileges […]
How to change a default constraint
Posted on: March 22, 2009 8:04 am
While I was teaching the MCM-Database class last week, we were discussing fragmentation and the effect of a high-order GUID key on an index. Without going into too many details, having a random GUID – as generated from the NEWID() – function is bad, but having one generated by NEWSEQUENTIALID() isn't anyway near so bad (I'll […]
IAM page corruption examples
Posted on: March 22, 2009 6:14 am
A thread cropped up on SQLServerCentral involving IAM chain corruption (see Inside the Storage Engine: IAM pages, IAM chains, and allocation units for details of IAM chains). The error from DBCC CHECKDB was: Server: Msg 2576, Level 16, State 1, Line 1 IAM page (0:0) is pointed to by the previous pointer of IAM page (1:394336) […]
Importance of index maintenance
Posted on: March 21, 2009 3:50 pm
Last week’s survey was on what kind of regular index maintenance you perform (see here for the survey) as a way of kicking off a new series I’m writing around index maintenance. Here are the results as of 3/21/09 – I find them very encouraging: As you can see, about 2/5 of respondents are performing some form […]
Ghost cleanup redux
Posted on: March 19, 2009 10:44 am
Way back at the start of me blogging here I wrote a comprehensive description of ghost records and the ghost cleanup process – see Inside the Storage Engine: Ghost cleanup in depth. A question came up in the class I’m teaching this week that’s worth answering in a blog post – do ghost records occur […]
FILESTREAM directory structure – where do the GUIDs come from?
Posted on: March 17, 2009 4:19 pm
I’m teaching the Microsoft Certified Masters – Database qualification this week here in Redmond, and in part of day one I discuss the FILESTREAM directory structure. I was asked the question where do the directory name GUIDs come from? so I started digging around in the system tables while Kimberly was lecturing. Take a look […]
Importance of validating backups
Posted on: March 13, 2009 5:17 pm
Last week I kicked off the first weekly survey – on whether you validate your backups or not (see here for the survey). The results are very interesting (as of 3/13/09): As you can see, almost 25% of respondents never validate their backups! And a further 25% only validate them occasionally, with 30% doing some […]
FILESTREAM directory structure
Posted on: March 11, 2009 3:50 pm
After writing the FILESTREAM whitepaper for Microsoft, I’ve had lots of questions about the structure of the FILESTREAM data container. The FILESTREAM data container is the technical term for the NTFS directory structure where all the FILESTREAM data is stored. When you want to use FILESTREAM data, you first add a filegroup (during or after […]
Our book has been published!
Posted on: March 11, 2009 3:24 pm
Microsoft SQL Server 2008 Internals finally starts to roll off the presses at Microsoft Press today! For me this is the first time in print (in book form at least) and my consistency checking chapter (about 1/10th of the book) represents a complete brain-dump of everything I know about how DBCC CHECKDB works. Kimberly […]
New whitepaper on SQL Server Audit in 2008
Posted on: March 2, 2009 4:41 pm
One set of features i haven't blogged about yet in SQL Server 2008 are the new security features: SQL Server Audit, Transparent Data Encryption, and Extensible Key Management. I've just finished writing a security article for the May 2009 TechNet Magazine (the annual security issue) and while trolling around TechNet I found that the security […]
Rolling upgrade using database mirroring (and a bug in 2008 RTM)
Posted on: February 24, 2009 11:00 am
While trawling through the latest 2008 Books Online this morning to answer a question, I noticed a new section that I hadn't seen before, which explains in detail how to perform a rolling upgrade with database mirroring. The link to the MSDN page is http://msdn.microsoft.com/en-us/library/bb677181.aspx and below I've linked to the flowchart from that page. Books […]
Getting historical deadlock info using extended events
Posted on: February 23, 2009 1:49 pm
In the TechNet Magazine article I wrote on Advanced Troubleshooting with Extended Events, I mentioned the always-on event session called system_health. Jonathan Kehayias, a fellow MVP and blogging mad-man, posted a great article with SQL Server Central today about how to get historical deadlock graph info from it. His article explains some of the pre-reqs […]
Couple of nasty FILESTREAM bugs fixed in 2008 RTM CU1
Posted on: February 23, 2009 12:29 pm
Cumulative Update 1 for SQL 2008 RTM contains fixes for two nasty FILESTREAM bugs (among a lot of other bug fixes). The first one concerns restoring a 2008 database from a series of log backups when the database contains FILESTREAM info. It’s possible that a race condition can cause one of the log backups to […]
SQL Server 2008 system table map PDF
Posted on: February 16, 2009 4:34 am
Microsoft has released a PDF showing all the SQL Server 2008 system tables – very cool but you'll need a big printer to print it out – or go to Kinko's. Download it from Microsoft here.
TechNet Magazine: feature article on Advanced Troubleshooting with Extended Events
Posted on: December 5, 2008 6:31 am
The January 2009 issue of TechNet Magazine is now available on the web and has a new article I wrote on Advanced Troubleshooting with Extended Events in SQL Server 2008. The article covers: An overview of troubleshooting in SQL Server, along with links to a bunch of tools like DMVStats and RML An overview of […]
SQL Server 2008: Using Change Data Capture with SSIS
Posted on: December 3, 2008 6:27 pm
I was perusing the latest release of the SQL Server 2008 Books Online on MSDN (look, Kimberly’s in Dublin this week – what else am I supposed to do to amuse myself in the evenings? :-) and found a cool new section on change data capture in the SSIS section. It’s called Improving Incremental Loads […]
Life on SQL Server 2008 – PASS interview with a large-scale user
Posted on: November 25, 2008 10:23 am
During the PASS Community Summit, Rick Heiges did an interview with Thomas Grohser, the Senior Database Engineer at the bwin Interactive Entertainment AG, the world's largest online gambling company. This is the private client Kimberly and I were onsite with for a week last month in Vienna before heading to Barcelona for TechEd. In the […]
Conference Questions Pot-Pourri #9: Q&A around compression features
Posted on: November 11, 2008 9:29 am
Today's post is based on a bunch of questions I've had around the various compression features in SQL Server 2008. Does turning on data compression or backup compression compress the transaction log files on disk? No. The transaction log is not compressed in any way under any circumstances. Rows from tables and indexes that have […]
High-end backup compression numbers
Posted on: October 29, 2008 8:36 pm
On Tuesday we had a look around our customer's data centers here in Austria – hidden away in the labryinthine bowels of a very large building in Vienna. Typical data centre with a halon fire extinguishing system but exceptional in its neatness and organization. The star of the show was their new HP Superdome – […]
New minimally-logged insert functionality in SQL Server 2008
Posted on: October 28, 2008 1:26 am
During early SQL Server 2008 development, the Storage Engine team introduced additional minimally-logged functionality. And then it was removed again for RTM. Now it seems that they've bowed under customer pressure and have made the functionality available again (see Sunil's announcement here). Using trace flag 610 in the RTM build, you enable the potential for […]
Sparse columns: misleading info in Books Online
Posted on: October 19, 2008 3:30 pm
The sparse columns feature in SQL Server 2008 is generating lots of interest from people looking to deploy extensible schemas. I've seen a few questions from people that are confused by some of the info in Books Online, particularly about adding and removing sparse columns from a table. There's a section in BOL that states […]
TechNet Magazine: Article on Tracking Changes in Your Enterprise Database
Posted on: October 14, 2008 4:01 pm
The feature article on SQL Server 2008: Tracking Changes in Your Enterprise Database I wrote for the November issue of TechNet Magazine is live on the web. It also includes a 5 minute long screencast I recorded where I demo the change data capture feature. You can get to the article at http://technet.microsoft.com/en-us/magazine/cc987538.aspx. The topics covered are: The […]
SQL Server 2008 FILESTREAM whitepaper published on MSDN
Posted on: October 10, 2008 4:35 pm
Just found out that the 25-page FILESTREAM whitepaper I wrote recently for the SQL team has been published on MSDN. You can get it at https://msdn.microsoft.com/en-us/library/hh461480.aspx. Enjoy! Here’s the table of contents. Introduction Choices for BLOB Storage Overview of FILESTREAM Dual Programming Model Access to BLOB Data When to Use FILESTREAM Configuring Windows for […]
Dev Connections newsletter
Posted on: September 10, 2008 7:29 am
At the last Connections conference in April, the conference organizers tapped a bunch of speakers for interviews, articles, and other content to put into a free “newsletter” called MyDevConnections, and now it’s finally available. It covers all the Connections conferences, so isn’t just limited to SQL Server. As far as SQL is concerned, Kimberly and […]
RunAs Radio interview on FILESTREAM
Posted on: September 10, 2008 7:13 am
This is the second of the two sessions I recently recorded with Richard and Greg on RunAs Radio (the first one on being an “involuntary DBA” is here). I’ve just finished the final edited version of a whitepaper for Microsoft on the FILESTREAM feature of SQL Server 2008 and this session goes into details of why […]
SQL Server2008: New whitepaper on combining transactional replication and database mirroring
Posted on: September 2, 2008 8:05 pm
I just heard today that the first whitepaper I’ve written for Microsoft has been published! The abstract is: SQL Server Replication: Providing High-Availability using Database Mirroring This white paper describes how to use database mirroring to increase the availability of the replication stream in a transactional environment. It covers setting up replication in a mirrored […]
Better behavior with suspect databases in SQL Server 2008
Posted on: September 2, 2008 9:44 am
(Quickie post #1 while it’s Kimberly’s turn to lecture this morning…) I had a comment on my post dealing with suspect databases, saying that the 2008 behavior is much better. I hadn’t tried this so it was (really good) news to me. So, trying the same steps from that blog post on 2008 – everything’s […]
SQL Server 2008: How to rebuild the system databases?
Posted on: August 29, 2008 5:04 pm
When I started blogging, way back in 2006 :-), the third post I made on the old Storage Engine blog was about rebuilding the msdb database in 2005 (see here). This no longer works in 2008 (fellow MVP Tibor Karaszi explains why here), and in fact the information in Books Online about how to rebuild […]
High-performance FILESTREAM tips and tricks
Posted on: August 25, 2008 10:53 pm
Over the last few weeks I’ve had lots of questions about FILESTREAM performance and how to coax NTFS into scaling nicely. I just finished writing a 30-page whitepaper about FILESTREAM for the SQL Server team, which should be published before PASS in November (I’ll blog the link when I have it). Although my whitepaper isn’t […]
SQL Server 2008: Does my database contain Enterprise-only features?
Posted on: August 23, 2008 4:06 pm
Moving databases around is pretty common, as is moving databases between servers running different Editions of SQL Server, especially during a disaster recovery situation. You may not know this, but in SQL Server 2005, if you had partitioning anywhere in a database, you could only attach/restore that database using an Enterprise or Developer instance. I […]
TechEd Online Panel Video: Building a Solid High-Availability Strategy
Posted on: July 21, 2008 6:00 pm
While we were at TechEd in June, Kimberly and I participated in an hour-long discussion panel (Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy) that was video-taped by the TechEd Online folks. It’s now been edited and is available for download/viewing. We cover everything from requirements analysis to technology details in SQL Server […]
We’re writing a book – finally!
Posted on: July 10, 2008 10:37 pm
Well, almost… Kimberly and I have agreed to co-author the upcoming SQL Server 2008 Internals book with our good friend Kalen Delaney. This is the 2008 evolution of Kalen’s Inside SQL Server series of books and will be published by MS Press around February 2009. The other authors are (also our good friends) Adam Machanic […]
Dev Connections SQL Power Workshop on SQL Server 2008, October, San Francisco
Posted on: July 10, 2008 7:07 pm
People have been complaining that I’ve stopped blogging so much – vacation folks, vacation! Today I’ve got a few class and conference posts to get through and then I’ll get back to the technical posts. We’re doing a 2.5 day public class based on the SQL Server 2008 material we developed earlier this year. This will […]
SQL Server 2008: Sparse columns and XML COLUMN_SET
Posted on: April 28, 2008 11:05 pm
We’re sitting here in St. Pete Beach in Florida visiting some of Kimberly’s family and having some sun-kissed R&R before heading back up to Seattle on Wednesday, and I thought I’d get the next post in my sparse columns mini-series out. Before I start though, Kimberly just posted the resources for the Accidental DBA class […]
SQL Server 2008: Sparse Columns
Posted on: April 18, 2008 1:46 am
It’s been quite a while since I wrote a blog post on a flight but I happened to be playing with a CTP-6 VPC on the way down to Orlando and thought I’d do the first in a few posts on a cool feature of SQL Server 2008 – Sparse Columns. One problem in […]
New script: How much of the database has changed since the last full backup?
Posted on: April 8, 2008 4:49 pm
Over the weekend there was a question on one of the internal aliases at MS: how can I tell what percentage of a database has changed since the last full backup, so I can choose between a differential or full backup? No such code exists as far as I know – until now! I happened […]
SQL Server 2008: How to get FILESTREAM and partitioning to work
Posted on: April 7, 2008 11:07 pm
Now we’re back from Iceland and I have a week to catch up with some content development before the MVP Summit next week and then SQL Connections the following week. One of the things I struggled with earlier in the year while writing a SQL Server 2008 training course for Microsoft was how to get […]
SQL Server 2008: New (undocumented) physical row locator function
Posted on: March 20, 2008 12:25 am
One of the drawbacks of not being in the SQL team at Microsoft any longer is that I don’t know about all the undocumented features in the next release – I have to hunt around for them like everyone else :-( So I was poking about in SSMS in 2008 CTP-6 and noticed a function called sys.fn_PhysLocCracker […]
Available for download: Our High-Availability hands-on labs and SQL Server 2008 JumpStart materials
Posted on: March 12, 2008 5:23 pm
A couple of weeks ago I blogged about the three tracks of the SQL Server 2008 JumpStart course that SQLskills.com taught internally for Microsoft and some MVPs – see here for details. Well, the content is now available to download! Note that this was based on CTP-5 (November 2007 CTP) and there have been *lots* […]
TechNet Radio interview with Paul and Kimberly on SQL Server 2008 – Part 2
Posted on: March 11, 2008 11:45 pm
The second part of our radio interview with TechNet has been released (see here for part 1). In this installment we discuss troubleshooting and manageability in SQL Server 2008. You can get to it by going to the March 11th 2008 show here. Enjoy!
SQL Server Magazine Connections Fall 2008 Call For Abstracts
Posted on: March 10, 2008 9:29 pm
(Cross-posted on Paul and Kimberly’s blogs) With the Spring SQL Server Connections show coming up next month, its time to start planning for the Fall show. Once again Kimberly and I will be Co-Chairs of the Conference and we’d like to invite you to submit abstracts for the Fall 2008 SQL Connections conference, to be […]
SQL Server 2008: FILESTREAM performance
Posted on: March 9, 2008 5:45 pm
During the various courses I've been teaching, people are interested in how FILESTREAM performance compares with storing BLOBs in the database itself. I have some performance graphs based on measurements the dev team made – these have been presented publicly by myself and the dev team so I can share them with you here. There […]
SQL Server 2008: Changes to enabling FILESTREAM from CTP-6 onwards
Posted on: March 7, 2008 7:10 pm
Just over a month ago I posted on how to enable FILESTREAM in CTP-5 and the pre-CTP-6 build I was working with. Now that’s all changed! CTP-6 is a hybrid of the CTP-5 method and what will eventually be the methodology in RTM. The changes were made to separate the OS-level configuration from the SQL-level […]
TechNet Radio interview with Paul and Kimberly on SQL Server 2008 – Part 1
Posted on: March 5, 2008 2:31 am
Wow – almost 10 days without a blog post – that must be a record for me! :-) Never fear – I'll be posting more over the next couple of weeks. Kimberly flew off to India yesterday to teach some Microsoft classes and unfortunately I couldn't join her this time as I'm teaching 3 classes […]
Conferences coming up: SQL Connections in April and TechEd US in June
Posted on: February 25, 2008 4:11 am
SQL Connections is in less than two months now and our pre-con and post-con workshops are filling up fast – checkout my previous blog post here for the full list of what we’re presenting. Now we’ve finalized our TechEd US sessions with Microsoft and can let you all know that we’re going to be there […]
SQL Server 2008 JumpStart
Posted on: February 15, 2008 12:38 am
Phew – last week Kimberly and I spent 3 days teaching the ins-and-outs of SQL Server 2008 for DBAs/IT-Pros to about 130 Microsoft SQL Server experts and MVPs (like Kalen Delaney, Adam Machanic and Ron Talmage). This was the (95% complete) Beta delivery of a course we’ve been developing for the last six months for Microsoft […]
Updated aggregated feeds
Posted on: February 7, 2008 1:03 pm
After many reminders (thanks Adam Machanic!) I’ve added Conor and Simon to the two aggregated feeds over all the SQLskills.com blogs. There are two feeds: SQL Server 2008 Category Posts Provides previous 30 day’s posts http://pipes.yahoo.com/pipes/pipe.run?Age=30+days+ago&_id=mhCQs_B13BGv3jGgjtzu1g&_render=rss All posts Provides previous 20 day’s posts http://pipes.yahoo.com/pipes/pipe.run?Age=20+days+ago&_id=vv9PBOp13BGAc67kLO2fWQ&_render=rss The amount of content is really growing as Simon and Conor […]
SQL Server 2008: Enabling FILESTREAM through T-SQL and SSMS
Posted on: February 4, 2008 7:57 pm
The second post in my series on FILESTREAM (see here) deals with how to enable FILESTREAM through T-SQL and using SSMS. You can’t just create FILESTREAM data – you have to specifically enable it at the instance level and then possibly restart the instance or host computer. The reason for this is that there’s a […]
SQL Server 2008: RTM slips to Q3
Posted on: January 28, 2008 1:59 am
Microsoft has announced that the RTM date of SQL Server 2008 has slipped out to Q3 of this year. See this official blog post here. The official launch will still go ahead as planned on February 27th. While some people may see this as disappointing, I don’t see many large customers going straight into SS2008 […]
SQL Server 2008: FILESTREAM
Posted on: January 25, 2008 9:51 am
Well it’s the end of our first week in China – tomorrow we fly to Beijing for another week of teaching. Hopefully we’ll get to do some sightseeing on Sunday – the last time I was in Beijing (for TechEd ’06) the conference hotel was about 25 miles outside the city centre – so I […]
SQL Server 2008: Spatial indexes
Posted on: January 17, 2008 7:59 pm
One of the cool features of SQL Server 2008 for developers is spatial data support. There have been some great posts recently about using spatial (see Simon Sabin’s blog here), which is all developer stuff – but what I’m interested in are the implications of spatial support for DBAs, and they are focused on spatial […]
SQL Server 2008: Partition-level lock escalation details and examples
Posted on: January 14, 2008 10:10 pm
Back in October 2007 I blogged about partition-level lock escalation in SQL Server 2008 (see here) and I promised to do a follow-up once CTP-5 came out with syntax etc. So here it is. A brief recap – lock escalation in SQL Server 2005 and before only allowed table-level lock escalation. If you have […]
SQL Server 2008: Hot-Add CPU (and affinity masks)
Posted on: January 10, 2008 12:11 am
Quickie today as I’m preparing to speak at the Pacific Northwest SQL Server User Group meeting tonight on the MS Campus here in Redmond (my trademark Detection and Recovery from Database Corruptions talk). SQL Server 2005 introduced the concept of hot-add memory, to allow for dynamic workload handling. SQL Server 2008 increases these capabilities by […]
SQL Server 2008: Backup Compression CPU Cost
Posted on: January 9, 2008 10:26 am
Back in September last year I blogged about the native Backup Compression that is in SQL Server 2008 (see here) and promised to blog more when CTP-5 came out and I ran some tests. Well, it’s here and I have so here’s some data for you. I expanded the AdventureWorks database to be 322Mb (random […]
Enabling VS2005 support for SQL Server 2008 CTP-5
Posted on: January 4, 2008 6:05 pm
Hit another issue last night while playing with the latest CTP – VS2005 can’t cope with some of the changes in SS2008. The problem arose while defining an ASP.NET connection string that was pointing to a 2008 instance (while playing with query notifications and cache invalidation, but that’s unrelated) – it failed with a slightly […]
Katmai bug: SSMS doesn’t drop database locks when leaving the context of a database
Posted on: January 4, 2008 3:07 am
I came across an interesting bug in Management Studio in the latest Katmai CTP today – when a connection disconnects from a database, SSMS doesn’t release the shared database lock that it holds. This prevents any operations that need exclusive database access (like a RESTORE) and can be somewhat disconcerting if you don’t realize what’s going […]
Paul and Kimberly interview on RunAs Radio – What’s New in SQL Server 2008?
Posted on: December 12, 2007 6:07 pm
While we were in Barcelona we sat down with Richard Campbell and Greg Hughes from RunAs Radio to record a 1/2 hour interview on SQL Server 2008. We touch on a ton of different features (look at the number of Categories I’ve tagged this with!) and have a bunch of laughs along the way – […]
SQL Server 2008: Configuring Peer-to-Peer Replication
Posted on: December 7, 2007 12:31 am
One of the cool features added in SQL Server 2005 for scaling-out a workload was peer-to-peer replication. The major drawback was that to change an existing peer-to-peer topology, the entire topology had to be quiesced. In SQL Servr 2008, the Configure Peer-To-Peer Topology Wizard in Management Studio has undergone a major face-lift and a peer-to-peer […]
Kimberly & Paul interview with SSWUG TV
Posted on: November 18, 2007 2:49 pm
On the last day of SQL Connections a couple of weeks back we did a 20 minute TV interview with Steve Wynkoop of SSWUG. Apart from the mandatory mention of our favorite game Blokus, we discussed a bunch of new features coming in SQL Server 2008 while I struggled not to say ‘we’ instead of ‘they’ […]
Fed up with BACKUP success messages bloating your error logs?
Posted on: October 30, 2007 7:26 pm
There’s a well known problem that every time a backup operation succeeds a message is written into the error log and Windows event log. If you’re taking very frequent log backups (say every 5 minutes) of multiple databases, that’s a significant amount of clutter in the logs. Well – now there’s a fix! Kevin Farlee, […]
SQL Server 2008: What’s been removed in this version? BACKUP LOG WITH NO_LOG for one!
Posted on: October 12, 2007 7:26 pm
All the razzamatazz about new releases go on about what's new but hardly ever is there discussion of what's been removed. So that's the topic of this short post. In the Books Online that comes with the July CTP of SS2008 (here's a link to the download page), the topic Discontinued Database Engine Functionality in […]
SQL Server 2008: Performance boost for Database Mirroring
Posted on: October 11, 2007 12:06 am
As I mentioned in a previous post, one of the new features for database mirroring in SQL Server 2008 is log stream compression. There’s a good explanation of what this is (along with some example workloads and performance graphs) on the SQL Customer Advisory Team blog so I’m not going to duplicate all that here. Basically, the […]
New aggregate feeds for SQLskills blogs
Posted on: October 9, 2007 1:35 am
Lots of people have been asking for us to create some aggregate feeds of the various blogs on the SQLskills site – well, now I’ve done it. The three new feeds are: SQLskills BI Team Blog http://pipes.yahoo.com/pipes/pipe.run?_id=xMvfPeB13BGn_EL9iHrL0A&_render=rss or http://feeds.feedburner.com/SQLskillsBITeamBlog SQLskills SQL Server 2008 Category Aggregate Feed http://pipes.yahoo.com/pipes/pipe.run?_id=mhCQs_B13BGv3jGgjtzu1g&_render=rss or http://feeds.feedburner.com/SQLskillsSQLServer2008TeamBlog SQLskills All Blogs Aggregate Feed http://pipes.yahoo.com/pipes/pipe.run?_id=vv9PBOp13BGAc67kLO2fWQ&_render=rss. There’s no […]
SQL Server Magazine Connections Spring 2008 Call For Presentations
Posted on: October 5, 2007 1:27 am
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. From the Spring show onwards, Kimberly and I will be the co-chairs of the SQL Connections conference. We would like to invite you to submit abstracts for the […]
Coming to Connections in November? Send us your questions and win a prize!
Posted on: October 4, 2007 9:19 pm
Are you coming to SQL Connections in November? If you are, and you’re coming to either of our interactive sessions, we invite you to send us questions in advance that we will pick from and answer during the sessions. The two sessions are: SDB351: Follow the Rabbit – Interactive Q&A on Availability In this session, Kimberly Tripp […]
SQL Server 2008: Parallelism improvements for partitioning
Posted on: October 3, 2007 10:40 pm
In SQL Server 2005, queries over partitioned tables use a single-thread per partition. This can cause performance problems under certain circumstances: On systems with many CPUs, if the table schema has less partitions than there are CPUs, then not all the CPUs will be used to process the query. Some examples: On a 32-way box, […]
Coming up soon: TechEd IT Forum 2007
Posted on: October 3, 2007 3:58 pm
November’s going to be a busy month for Kimberly and I! Hot on the heels of SQL Connections in Las Vegas is the yearly pilgrimage to Barcelona for TechEd IT Forum and this year we’re packing in a ton of sessions in between us. Here’s what we’re doing: Sessions (DAT205) The […]
SQL Server 2008: Lock escalation changes
Posted on: October 2, 2007 10:06 pm
SQL Server supports lock escalation – when the server decides to move from a large number of row or page locks on an object to a table-level lock. Sunil Agarwal posted a great description of lock escalation in SQL Server 2005 on the Storage Engine blog so I won’t repeat it all here. The problem with […]
SQL Server 2008: New Performance Counters for Database Mirroring
Posted on: October 1, 2007 8:31 pm
Part of the improvements to database mirroring in SQL Server 2008 are the addition of 10 new performance counters under the Database Mirroring Performance Object. These add some cool troubleshooting capabilities. SQL Server 2005 provided the following 11 counters (from Books Online): Name Description Bytes Received/Sec Number of bytes received per second. Bytes Sent/sec Number […]
SQL Server 2008: Automatic Page Repair with Database Mirroring
Posted on: September 27, 2007 10:53 pm
One of the hottest features in SQL Server 2005 is database mirroring, and it’s helped many companies implement successful and relatively inexpensive high-availability strategies. In SQL Server 2008, Database Mirroring has been enhanced in several ways – one of which is the ability to automatically repair corrupt pages! This feature is based on the fact that the […]
SQL Server 2008: Backup Compression
Posted on: September 20, 2007 6:38 pm
Over the next few months I’ll be blogging a lot about new features that are coming in SQL Server 2008 for DBAs and ITPros. First up is Backup Compression. This has been one of most heavily requested features since before I joined the SQL team back in 1999 – for me it’s really cool that […]
Coming up soon: SQL Server Magazine Connections conference
Posted on: September 19, 2007 11:41 pm
The Fall conference season is on us again! Building on the great reaction to our co-presented Database Maintenance workshop at SQL Connections in Orlando, Kimberly and I are doing a *ton* of stuff at SQL Connections this Fall. The conference is in Las Vegas (where I’ve never been!) and runs from November 5th to […]
