Some facts about the Database Recovery Advisor and Restores


A new feature in SQL Server 2012 Management Studio is the Database Recovery Advisor. So why am I writing a post on a SQL Server 2012 enhancement which has been blogged already. Database Recovery Advisor There are some unique nuances that you need to be aware of while using this new utility. The Database Recovery Advisor facilitates constructing restore plans that implement optimal correct restore sequences.

Read on if you are interested in learning more about the Disaster Recovery Advisor. I will be referring to the Database Recovery Advisor as DRA in this post going forward.

The facts mentioned in this post are answers for frequently asked questions about DRA that I get from customers.

Continue reading

Advertisements

Concurrent backups: What is the big deal?


You are probably wondering why am I writing a blog post on concurrent backups! Well this is a topic that brings up a few questions during my customer visits. Apart from that the biggest reason, I want to talk about concurrent backups is because of backup softwares that you have active in your environments.

A lot of the backup softwares available in the market have the option of scheduling backups of the entire system. This involves backing up of SQL Server databases using the interfaces exposed through sqlvdi.dll. Ain’t your software really smart? However, sometimes the right hand doesn’t really talk to the left hand! It is quite possible that your server admins have scheduled backups of your systems around the same time you have scheduled a maintenance plan to take a backup of the SQL Server instance.

If you initiate two differential or full database backups concurrently, then you will experience a blocking condition as shown in the table below. SPID 53 was taking a VDI full-backup using the SQL Server Backup Simulator tool where as SPID 55 was also taking a native full-backup of the same database. In such a scenario, you will find that the backup which was running first will complete and only then will the second backup will continue. The second concurrent backup will be blocked during the entire duration of the first backup.

spid blocked waittime lastwaittype waitresource cmd dbid
53 0 6302 ASYNC_IO_COMPLETION   BACKUP DATABASE 8
53 0 0 MISCELLANEOUS   BACKUP DATABASE 8
53 0 412 BACKUPBUFFER   BACKUP DATABASE 8
53 0 412 BACKUPIO   BACKUP DATABASE 8
55 53 2386 LCK_M_U DB: 8 [BULKOP_BACKUP_DB] BACKUP DATABASE 8

This issue doesn’t manifest itself when you are you taking a volume snapshot backup of the database. The wait resource will always be DB: <dbid> [BULKOP_BACKUP_DB] with a wait type of LCK_M_U (update lock) for the second session that is running the concurrent backup operation. Concurrent log backups are allowed though starting from SQL Server 2005.

One of the situations where this can pose to be problematic is when the application that installed the database creates a maintenance plan during the application setup process. This can cause problems if you have concurrent full or differential backups scheduled from another software during the same time when your backup maintenance plan is running. Another big pitfall of concurrent backups running is that sometimes, the same job is responsible for both full and log backups of the database. If your log backup job doesn’t kick-off because your database backup is still being blocked, then you could run into a bloated transaction log issue for a database in full or bulk-logged recovery model. My colleague, Arvind, has also blogged about the same issue a few years back.

The following T-SQL script can help you determine if you have overlapping backups being taken for a SQL Server 2012 function. This script can also work for releases lower than SQL Server 2012 if you remove the LAG function as that T-SQL enhancement was introduced in SQL Server 2012.

select backup_start_date, backup_finish_date,

LAG(backup_start_date, 1) OVER (PARTITION BY database_name ORDER BY backup_start_date) as previous_start_date,

CASE datediff(mi,(LAG(backup_start_date, 1) OVER (PARTITION BY database_name ORDER BY backup_start_date)),backup_start_date) WHEN 0 THEN 'OVERLAPPING' ELSE '-' END as [Status] ,

datediff(mi,backup_start_date,backup_finish_date) as backup_time_mins, type

from msdb..backupset

where database_name = '<database name>' -- Replace with appropriate database name

and type <> 'L'

order by backup_start_date

Microsoft TechEd 2012–Multiple rows affected


WP_000363If you were at Microsoft TechEd India 2012 held at Bangalore, then you would agree with me without a shadow of doubt that it is quite difficult to write a wrap-up post of the entire event. The event for me was what I refer to as PPP. And by PPP, I am not talking about Point-to-Point Protocol. What I am referring to is a self-coined term…. People, Passion and Parallel.

People because it was great to connect a face to the twitter handle or online persona that you have interacted with in the past. What amazed me was some of the folks that I met at TechEd introduced themselves with their Twitter handle first and their actual name later! It is phenomenal to see how social media can empower collaboration between a technical community! The #TechEdIn twitter hashtag was trending on Twitter India in less than 15 minutes after the event started on Day 1 and continued to trend for the next three days!

Passion for technology that was evident from Day 1 to Day 3 where the rooms overflowed with people attending the sessions at different tracks. It was as if people were watching technical blockbusters at TechEd!

Parallel is the name of the game as everything that was happening at TechEd was in parallel… Whether it was the networking that was happening between folks or be in the technical sessions being delivered in parallel at the different tracks or the technology showcases displaying the several cool stuff from the sponsors at the venue. I heard multiple conversations in the hallways where debates were being raged (among the attendees) to decide on which session they should attend under which track! And obviously Twitter stands testament to the fact that such a dilemma existed since there were numerous tweets on this topic!

Since my daily bread and butter revolves around Microsoft SQL Server, it was quite obvious that I was lurking around the sessions which had remotely anything to do with data! On day one, I would be found mostly at “Data and Developer – Synergy with SQL Server” and on day three I was mostly at “Know Your Data – A Day in Life of a DBA”. Though there was a copious amount of information exchange in the session, there were also some really humorous moments which deserve a mention:

Vinod [B | T] and Pinal’s [B | T] session on “SQL Server Misconceptions and Resolution – A Practical Perspective” probably landed them as top candidates for a thin one and the fit one series in case they ever choose to pursue a media related career option!

Pinal Dave’s session on “Speed Up! – Parallel Processes and Unparalleled Performance” left people in splits…

Balmukund’s [B | T] session on “Keeping Your Database Available – ‘AlwaysOn’“ made learning fun and even though he had presented this in the past at other forums… This time around he came back with a totally new look and feel along with a high quality technical session on SQL Server 2012 AlwaysOn features with demos.

The VDI demo that I showed was using the “SQL Server Backup Simulator” which can be downloaded from here. The snapshot demo was shown using a modified version of the code present in the snapshot.cpp file from the Microsoft SQL Server Virtual Device Specification.

I had a lot of fun presenting my session on the “Lesser Known facts of SQL Server Backup and Restore” on March 23rd at TechEd. The slide deck used for the presentation is available below without a few situational humorous elements which wouldn’t really make sense to a person who had not attended the session. (Had to add the disclaimer before I got pulled up with comments that this was the incomplete slide deck!)

Here are some additional resources on VDI and VSS in case you want to indulge in some light reading Smile

IO Frozen messages while taking NT Backup for SQL databases
INFORMATIONAL- SHEDDING LIGHT on VSS & VDI Backups in SQL Server
Troubleshooting SQL Server Backup Issues
Incorrect Buffercount can cause Out of Memory Errors
VDI (VSS) Backup Resources
How It Works: SQL Server Backup Buffer Exchange (a VDI Focus)

The photographs from the session are available on TroubleshootingSQL’s Facebook page. Last but not the least, there needs to a be special vote of thanks to Pinal Dave who ensured that we had a seamless experience during the event.

The session recording is embedded below:

People… Passion… Parallel… @TechEdIn