Solved

Windows Server Backup causing SQL Timeouts

Posted on 2013-11-12
5
554 Views
Last Modified: 2014-03-14
We are running a Win 2008 Server SP2 32bit web server with MS SQL 2008. This server serves dozens of client websites. All website data and SQL DBs are housed on a data volume separate from the OS volume.

Yesterday I setup Windows Server Backup to backup the OS and Data volumes at 3am (about 65GB total). Then this morning I got hit with client calls saying their sites were down. Upon checking I found SQL server timeout errors on all websites with SQL databases. So, I interrupted the backup and all of the SQL connections went back to normal.

As a quick test I tried it again by initiating a one-time backup of the data volume. As soon as it started running the SQL connections began timing out and also got a couple of named pipe errors. As soon as I cancel the backup process SQL goes back to normal.

So, my question is, WTF? Isn't Windows Server Backup designed to backup live servers in real-time? Am I expecting too much from Microsoft here?

The server has plenty of resources. Upon looking at the resource monitor while backup is running, CPU, RAM, Disk & Network usage are all in the 5-20% range.

Any assistance toward a solution would be greatly appreciated.
0
Comment
Question by:worthyking1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 334 total points
ID: 39643642
Isn't Windows Server Backup designed to backup live servers in real-time?

The simple answer is no.

The long answer is that you need to exclude the mdf (SQL Main data files) the ldf (Log data files) and any ndf (exteNded data files) from the backups. Those files are opened by the SQL Server Service on the SQL Service startup. Those files are exclusively locked. Nothing else can really read or written them at the OS level.

The typical scenario is that you use a SQL Server Agent job to dump the DB(s) to a .bak file and then the backup SW grabs the .bak file. If you are using SQL Server Express, you can get a free workaround agent from Lazy Coding.

And I won't give my opinion on the Windows Server Backup software.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 166 total points
ID: 39643669
Ditto for what Jimpen said.

One caveat, I suggest adding a small text file to each leaf directory, and back up that text file. Filename to say something like - for backup. Contents to say: To make sure that directory tree is recreated on full restore of machine backup.

Then when you come to restore the database backups, the directory tree has been recreated for you.

Second caveat: I recommend after reading it somewhere else, that you create an offline copy of system data files. On full restore from tape, you can copy those files to the correct location, and SQL should start. Okay, most of the databases are missing, but you can fix that by restoring those databases one at a time. Restore Master and MSDB and Model first of course!

HTH
  David
0
 
LVL 6

Author Comment

by:worthyking1
ID: 39644580
Okay, so it appears that Windows Server Backup is not the right tool then because even its "custom" setup only allows the selection of disks and not inclusion/exclusion of directories on those disks.

So, what backup software should I be using that can automatically and seamlessly handle the task of backing up our data?

For example, on our Linux server the built-in backup tool in WHM backs up all websites, all account data, all MySQL DBs, and all system data every night without issue, without hiccups, nothing. Is there something similar for Win2008?
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 334 total points
ID: 39644872
I can't speak to the Linux file and OS but I believe that MySQL service only locks the MySQL DBs into read only mode. So that means a backup software, or even a DOS prompt can always make a copy of the file. The same with Firebird/Interbase. That isn't true of SQL Server.

I still haven't found a backup software that I really trust to take care of the backup of the OS and the SQL Server databases, especially if the SQL DB's aren't relatively quiescent. In addition the B/U SW companies usually charge extra for their SQL Backup Agent.

I generally never bother with backing up the system databases. I have built a series of scripts that create all the maintenance functions (including backups) that I need. And I also have a portion that runs the sp_help_revlogin sp every 8 hours and dumps the logins to disk.

So my recovery process is to (re)install SQL, restore the DBs, then recreate the logins from the revlogin output file, and recreate the maint jobs from my scripts.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you are a web developer, you would be aware of the <iframe> tag in HTML. The <iframe> stands for inline frame and is used to embed another document within the current HTML document. The embedded document could be even another website.
Many businesses neglect disaster recovery and treat it as an after-thought. I can tell you first hand that data will be lost, hard drives die, servers will be hacked, and careless (or malicious) employees can ruin your data.
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question