In my last post, I talked about why we can’t use open file backup tools with SQL Server, and today I’m going to discuss SQL Server’s built-in solution: native database backups.  The word “native” means it’s an option that ships inside the SQL Server box.

SQL Server’s BACKUP DATABASE Command

SQL Server has a BACKUP DATABASE command that…

Wait, don’t close that browser!

I know – you don’t want to learn a command, and don’t worry, this isn’t going to be a session on how to use BACKUP DATABASE.  I’m not going to teach you any of that.  In fact, I’m pretty much going to teach you how to avoid writing any commands whatsoever.  But in order for me to explain the pros and cons of SQL Server backup solutions, I’m going to have to teach you the bare minimum of what BACKUP DATABASE is doing behind the scenes.  Now back to the story….

The BACKUP DATABASE command runs for just one database, and it produces a single flat file that contains enough data to reconstruct a perfectly synchronized data and log file for that database.  It can put that file on local disk, on a network file share, or on a tape drive.

How SQL Server Backs Up Open MDF and LDF Files

SQL Server doesn’t need to pause activity while it makes a backup.  As SQL Server makes a copy of the data file, it uses the log file to track what’s happening inside the database.  Every transaction is logged as it occurs, so SQL Server has a running list of changes by time.  When the data backup is complete, SQL Server also copies the log file data into the backup file too.

When it’s time to do a restore, SQL Server:

• Opens the backup file
• Reads out the data contents, and writes those to the data file (MDF)
• Reads out the logged changes that happened during the original backup, and writes those to the log file (LDF)
• At this point, the restore process is basically done.  We’ve got complete copies of the data and log file – but that doesn’t mean the data is consistent, so the next step kicks in….
• The recovery process reconciles the data and log files to put the database at a point in time where no transactions have left records halfway modified.

The recovery process only results in a perfect copy of the database when it has log file records that match up to how the database looks.  It can fix some things by rolling transactions forwards or backwards, but it can’t fix data records that don’t match up to anything resembling what’s in the log.  This is the problem with the open file backup scenario – if SQL Server isn’t keeping track of transactions in the log file (LDF) when the data file is copied, or if the transactions have been cleared out of the log file before we take a copy, the recovery process may not work.

Sounds great, right?  What’s the catch?

The Drawbacks of BACKUP DATABASE

Unfortunately, the BACKUP DATABASE command does just that – it backs up a single database.

Sysadmins want to back up the entire server, or all of the databases on a server.  There’s a few ways to schedule this command to happen regularly for all of the databases on a server, but none of them are really point-and-click easy for Windows administrators who’ve never opened up SQL Server Management Studio before.  Even if you manage to do it successfully, you’re going to have a tough time restoring some things.

Security data about logins is stored in the master database.  While this is technically a database and BACKUP DATABASE does back it up, most people don’t restore this database.  When disaster strikes, they build a new server, install SQL Server on it, and SQL Server lays down its own new master database.  Restoring the master database isn’t impossible, but it’s just difficult enough that it freaks people out when they’re under duress.  They decide to just start restoring other user databases instead, and deal with logins later.

Similarly, information about scheduled jobs, linked servers, and server-wide tweaks like configuration settings are not easy to restore.  They’re all possible – but they’re not intuitive for sysadmins facing an outage.  They’re also uncommon enough that full-time DBAs aren’t even familiar enough with these processes to do them by heart.

The Easier Way Out

As a result, many Windows administrators take the easier way out – they buy an add-on product for their file backup software so that it’ll also handle SQL Server.  Backup products like Acronis, Backup Exec, NetBackup, Tivoli, and the like offer options to back up SQL Server.

Some of these products simply call SQL Server’s native BACKUP DATABASE command behind the scenes.  They intercept the backup output and direct it to the backup software rather than a flat file.  These products claim to back up all of the databases, but at restore time, they feature the same challenges I discussed above.

Other products take a snapshot backup of the entire server by integrating with Microsoft’s Virtual Shadow Copy Service (VSS).  Some of Microsoft’s enterprise products (including SQL Server and Exchange) have VSS support so that the snapshots will be consistent enough for the recovery process to work.  These products can restore the entire server quickly – but they have other drawbacks, like not having an easy time integrating with log shipping or development servers.

Is Easier Better?

In my next post, I’ll explain why easier isn’t necessarily better, and why you might want to pay a little more attention to backing up specialized applications like SQL Server and Exchange.

Not going to TechEd, but you wish you could pick up some of those cool vendor freebies?  I got your hookup right here.

Register for a free Quest t-shirt and you’re also entered into a drawing for a $200 AmEx gift card.  Rules and restrictions apply.  Drawing not open to my family members, people who live in other countries, or people who dilly-dally – time limited….

When Windows administrators and network administrators first start trying to back up SQL Server databases, they don’t understand why they can’t just use normal backup tools.  Today, I’ll talk about what’s going on behind the scenes.

Why is is So Hard to Back Up SQL Server Databases?

Windows administrators are used to doing backups by simply backing up the files.  After all, if there’s anything useful on the computer, it’s stored in a file.  By backing up the files, their work is done.

The difficulties start with open files – files that are currently locked by programs that are reading or writing to them.  When you open a Word document on a network drive, and someone else tries to open that same file, they get an error that the file’s already open by someone else.  Windows doesn’t have an elegant way of letting multiple people access the same file at the same time if they both might make changes to it.

While SQL Server is running, it keeps the data and log files open so that if anyone runs a query or updates data, SQL Server can dive into the right place to store it with a minimum of waiting time.  You could theoretically work around this by using the AUTO_CLOSE switch on databases.  This tells SQL Server to automatically close database files when nobody’s using them.  Unfortunately, this switch has some severe side effects on performance, and it’s rarely enabled.  Therefore, if we use normal file backup tools to back up a SQL Server, it will simply skip the data and log files – which are the most important ones we need to back up!

Can We Use Open File Backup Tools on SQL Server?

Most file backup tools offer an option to back up open files.  They’ll just go ahead and copy the data, keeping their fingers crossed that nobody will write to the file during that particular time.

The problem arises when backing up larger files at the same time.  SQL Server databases are usually tens of gigabytes in size or much larger, and each database has at least two files – a data file (MDF) and a log file (LDF).  They’re both dependent on each other being in sync.  If we’re trying to back them up by simply copying the files, it’s crucial that we get both files as of the exact same moment in time.

Open file backups, however, usually work on a file-by-file basis.  They’ll first back up one of the two files, trying to catch changes as they happen, and then they’ll back up the second file.  The files might be individually consistent – but when used as a pair, they won’t be synchronized.  If we’re lucky enough to capture a small database when absolutely no activity is happening, we might be able to restore it just fine.

This is where “accidental” database administrators get themselves into trouble – they test open file backups with a small database on an isolated system.  The restore works, the database attaches fine, and they decide to proceed with this approach on progressively larger production systems.  The backup software never throws errors because it thinks everything is working fine – each file is successfully backed up.

When disaster strikes and they have to restore, they restore files from the most recent backup, but when they try to attach those databases to SQL Server, SQL says they’re corrupt.  They restore older and older backups, trying to find a copy that’s in sync.  If they’re lucky, they find a copy – but even then, they lost all the data that was written to the database in the meantime.

What About SAN Snapshot Backups for SQL Server?

Open file backups are slow because it’s hard to move so much data around, but Storage Area Networks (SANs) have the ability to grab very fast snapshots.  In a matter of milliseconds, they can make virtual copies of terabytes of data all at once.  The secret is that they’re not actually making copies; they’re just tracking all changes AFTER that point to a separate place.  They make a note of any new changes that come in.

While these are faster, they’re still not a solution if they’re not perfectly managed.  For example, databases often have their data and log files on separate volumes (LUNs in SAN-speak), and the SAN may not be able to take snapshots of two different LUNs at the exact same time.  They might take a snapshot of the data drive, and then take a snapshot of the log drive, but if those two aren’t taken simultaneously, we’re right back where we started.

Furthermore, SAN snapshots have a weakness; they’re still inside the SAN.  If we’re trying to protect ourselves from SAN failure or from a datacenter failure, we still have to get that data off the SAN as fast as possible.  Another common need is to create a copy for development or QA purposes on a different server that isn’t connected to the SAN, or send data offsite for auditors to access.  In these cases, we still need to be able to get the SQL Server backed up.

This is where SQL Server native backups come in, and I’ll explain these in my next post.

Continue to Part 2

Yes, VMWare ESX support is now in Spotlight on SQL Server Enterprise (SoSSE)!  If you’ve joined the ranks of the believers, or the of coerced non-believers in some cases, and are running SQL Server in a virtualized environment, Spotlight will help you to identify whether virtualization overhead is sapping your CPU.

VMWare ESX Homepage

VMWare SQL Server Homepage with Virtualization Overhead

WMWare Windows Homepage with Virtualization Overhead

I mentioned it earlier, but seeing is believing.  SQL Azure support is now part of Spotlight on SQL Server Enterprise (SoSSE)!  Our axis of diagnosis now goes from relational, to cubed, to cloud.  Just like with our other connection types, our SQL Azure homepage displays a visual representation of the underlying technology so you can understand how your traffic is flowing from user requests through to your database storage.

SQL Azure Homepage

SQL Azure Database

What’s a SoSSE?  Why, our pet name for Spotlight on SQL Server Enterprise, of course!

I wanted to show you some of what will be coming in the 7.0 release before it’s available.  First up, replication support.  In keeping with the ease of use that SoSSE brings to other aspects of SQL Server management, just identify your distribution server and SoSSE will discover your publishers and subscribers, show you the health of individual replication jobs and the overall replication scheme, and allow you to configure alarms and automated responses to them.  Add to that other key features in SoSSE like playback for up to two weeks of data (presented in the same interface and not a feature-limited report view), long-term reporting for trending and analysis, and the ability to manage your entire environment via alarm logs, timelines, or a graphical enterprise view and you can rest assured that SoSSE will have the data you need, when you need it.

Replication Home Page

Replication Drill-down

If you read my last post about the upcoming Spotlight on SQL Server Enterprise 7.0 release and wanted to know when you could get your hands on all of the latest updates, your answer is here!  I am pleased to announce that the product will be generally available on Thursday, May 6th.  For those of you on the East coast or on Central time, please note that Quest puts products live on our corporate headquarters’ schedule, so our website should show the release as generally available at 9:00 AM PST.

Spring is upon us and proper care and feeding have cultivated a new crop of performance management releases.  First up is Spotlight on SQL Server Enterprise 7.0.  I will be following up this post with more details on the final GA date and more detail on the individual features, but for now, here is a high-level overview of what to expect:

• Fully remote collection – that’s correct, no more work database or stored procedures, and because we’re proponents of eating our own proverbial dog food, we have tuned the queries to be even more efficient than the stored procedures that preceeded them.  This means that Spotlight on SQL Server Enterprise has a zero-install footprint in your monitored environments and a lower collection overhead overall.
• Comprehensive Replication Monitoring – if you had the opportunity to participate in our Spotlight on Replication beta program, thank you for your time and your feedback! We have implemented the full complement of replication data and implemented it via a new connection type in Spotlight on SQL Server Enterprise. Just connect Spotlight to your distributor, and we’ll show you your entire replication environment with comprehensive visualizations, drill-downs, alerts, and emails and automation.
• Support for Database Mirroring – SQL Server high availability management isn’t complete without support for Database Mirroring, so we’ve created a new High Availability drill-down and included mirroring visualization. Whatever your mirroring configuration and however many databases you’re mirroring in your environment, we have payed Spotlight’s architecture visualization paradign forward to help you identify the health of your mirroring configuration at a glance.
• Support for SQL Server Running on VMWare ESX – Are you virtualizing your SQL Servers and wondering whether you’re seeing the 30% overhead the experts warn you to plan for?  Our integrated virtualization overhead metrics will help you determine if that’s the case. Do you worry that another image might be causing issues with your SQL Server or the applications that rely on it?  Well now with version 7.0 all you have to do is register your ESX server and we can show you the guests running on that host, and the resource consumption for each.  Couple that with our Playback and Reporting and Trending capabilities and you have the peace of mind you’ve been seeking to ensure the health of your virtualized SQL Servers.
• Support for SQL Azure – yes, in the same product and in the same release (it’s been a long winter).  Just register your SQL Azure database with the address and login information, and then specify whether yours is Web, Business, or Enterprise edition and you’ll be monitoring the health of your SQL Azure database alongside your other SQL Server or SSAS instances.  One thing of note: Microsoft has a bug in the DMVs that they should fix any time now where it’s only possible to see one active request at a time.  Once this is fixed in SQL Azure, Spotlight will show you all of the requests.
• Other product enhancements – For those who have already used Spotlight on SQL Server Enterprise’s previous versions, we’ve taken your feedback and made the changes you’ve been asking for, including:
  • Enterprise View enhancements: Longer server labels (so you can see your fully-qualified or otherwise many character server names), layout customization persistence, group name visibility.
  • Disk Queue Length has been replaced by Max I/O Wait: Unfortunately there is still a lot of advice out there pointing folks to disk queue length to identify I/O bottlenecks.  I won’t say there’s no value in that approach, but the advice is meant to be applied in conjunction with other metrics and investigations.  To avoid confusion and to improve the data we’re presenting we’ve replaced Disk Queue with Max I/O Wait.  Now, SAN, RAID, or otherwise, the I/O information on the instance home page will indicate whether you have an I/O bottleneck or not…and then you can use the:
  • New I/O Diagnostic Drill-down: We added the CPU diagnostic drill-down in version 6.0, and now in 7.0 we’ve added a simliar dashboard to identify I/O bottlenecks. Spotlight has always been great at helping you to identify resource bottlenecks, but these diagnostic drill-downs go a step further by pre-analyzing data for you to show you areas we’ve already analyzed, a confidence factor relative to the information presented, and data in a single location to identify what you should address to resolve the issue.
  • Playback up to 14 days and do so more simply than before: you wanted the ability to playback over long weekends and holidays, so unless you’re taking a sabbatical we should have you covered. Also, starting with version 7.0, when you navigate through Playback data you’ll see that we only list the times when alarms were generated to make it simpler to find the performance issues you’re looking for. If you want to navigate to a specific period of time, just enter the date and we’ll take you there. Simple!
  • Faster and lighter than ever before: this is most noticeable in Spotlight Today and in the Alarms by Time and Alarm Log views when we’re processing all of the alarms in your environment. From front to back though, version 7.0 is the fastest, most efficient Spotlight on SQL Server to date which translates into a better experience and more time savings for you.

We are very excited about this upcoming release and look forward to hearing your feedback! If you have any questions or concerns, please don’t hesitate to contact Quest via your account manager, our world class Support organization, or you can email me directly at ariel.weil@quest.com and I’ll be happy to help.

More, different, or better?  Whatever you need, our SQL Server community at http://sqlserver.quest.com is there to help.  Many of you already frequent our forum location to engage in discussions about our database management products, but to improve upon the experience we’ve recently added a sticky topic specifically for you to request videos and collateral.  Do you want to know how Spotlight on SQL Server Enterprise can help you identify the I/O latency for an instance using SAN storage, and feel that a short video would be more helpful?  Would you like a brief tutorial on how to make the most out of the History view in Foglight Performance Analysis for SQL Server?  If so, we want to hear from you here: http://sqlserver.quest.com/forum.jspa?forumID=888, which is the direct link to our forum area entitled “Request Product Videos and Collateral Here”.  We’re looking forward to hearing from you!

The television show Seconds from Disaster talks about how bad things happen when a chain of small problems add up to one very large one.  I highly recommend checking out the Wikipedia entry for Seconds from Disaster when you’re bored – they link to all the articles about each disaster.

In my role at Quest, I help out users in the community, but I also help out customers during very high-priority support escalation calls.  Sometimes it’s software, sometimes it’s hardware, sometimes it’s the user, but most of the time it’s a combination of lots of things.  And sometimes, support calls sound an awful lot like Seconds from Disaster.

What Happens If I Press This

In a recent episode – I mean, support call – a customer was having problems running LiteSpeed backups.  When they added the SQL Server service account to the Local Administrators group, everything worked fine – but not when they took it back out.  I started asking questions, and I’ll paraphrase them a little in the interest of readability.

Me: “What account are you using for the SQL Server service?”

Them: “A custom one.  We use the same one on other servers, and they work fine.  We’ve been hardening our servers with new accounts.”

Me: “How did you configure SQL Server to use this domain account?”

Them: “By going into the Services control panel and changing the one SQL Server uses.”

Me: “Ah, there’s the problem.  You need to use the Configuration Manager to change service accounts.”

Them: “We can’t.  When we start Configuration Manager on that server, it doesn’t show any SQL Server instances.”

Me: “Err – that’s a problem.  You need to call Microsoft to find out why.”

Them: “They said it’s fine, don’t worry about it.”

Ouch.  This puts me in a nasty situation.  The customer’s just admitted they’ve done something Microsoft doesn’t support, and that Microsoft definitely wouldn’t say was okay.  The server’s not just in an unsupported state for Quest, but for Microsoft as well.  If I start recommending changes to “fix” something that isn’t a Quest problem, I can easily make a bad situation even worse.

This is especially difficult when we might be pointing the finger back at the users.  It’s not always the DBA’s fault when they do something unsupported like change the SQL Server’s service account outside of Configuration Manager.  Even when Configuration Manager is working, people don’t know they can’t use the Services control panel.  There’s nothing in Windows to prevent them from doing it, and it’s not common knowledge.

I know how tough it is to be a DBA and hear vendors point fingers at each other.  I’d love to get my days back from babysitting vendors who blamed the SAN, blamed the hardware, blamed Windows, blamed the 3rd party addons, and so forth.  Some folks are just desperate to get off the support call and close the case, but even when a vendor has the best intentions, sometimes we have to draw the line and say we can’t support it until something else is fixed.

In a perfect world, every user would be a Microsoft Certified Master, and every piece of software would prevent users from making unsupported changes.  In this imperfect world, we have to do some finger-pointing now and then.