October 17, 2011 at 7:12 am
I have a SQL server 2008 running on a Win2008 server. Backup is dumped on another Win 2008 Server. When I do a backup to the "dump"-server the performance is slow.
I have doen serveral test to check how long backup takes on both MSSQL2008 on Win 2008 Server and MSSQL2005 on Win 2003 server. Here the results:
MSSQL 2008 on Win 2008 Sever backup to Win2008 Server : 4 MB/sek
MSSQL 2008 on Win 2008 Sever backup to Win2003 Server : 47 MB/sek
MSSQL 2005 on Win 2003 Sever backup to Win2008 Server : 21 MB/sek
MSSQL 2005 on Win 2003 Sever backup to Win2003 Server : 47 MB/sek
What could be the cause of the extremely slow performance from MSSQL2008 on Win2008 Server to a Win 2008Server?
Note: If I take backup to local disk, and copy It to the Win2008 Server, the transfer is around 47 MB/s.
--------------------------------------------------------------------------------
October 17, 2011 at 7:25 am
If I take backup to local disk, and copy It to the Win2008 Server, the transfer is around 47 MB/s.
Creating a backup on local disk & then copying it to 'DUMP' server is General Practice. May I know why you are not opting for it?
October 17, 2011 at 7:33 am
Thanks for the replay.
One of the reasons is diskspace on the Servers who run sqlserver.
Another reason is that we have approx 130 servers on 7 diff locations (writing to diff "dump" servers).
The thing is, it works when we "dump" to a server running Win 2003 server.
Testet another thing, restore. Here is the result;
RESTORE DATABASE successfully processed 139073 pages in 11.736 seconds (92.579 MB/sec).
That means that it is only the backup that is slow, not restore....
regards
Arne
October 17, 2011 at 7:44 am
That means that it is only the backup that is slow, not restore....
You WRITE on network drive when backup database and READ from network drive when restore database. READs are generally faster than WRITEs (also it depends on your network usage at the moment)
However I am not sure why you are seeing this difference in two different OS. It might be a result of better hardware.
October 17, 2011 at 7:55 am
Hi
Reads ARE faster than write, I know. But Write 4MB/sec and read 92 MB/s is a little too big difference 🙂
I don't think the old Win2003 servers are better than the new Win2008 Server
Haven't mentioned this before, but I see the same slow backup on other locations from SQLserver2008 to Win2008 Server.
October 17, 2011 at 7:55 am
Do you have compression enabled on the SQL 2008 instance? Writing to another server with compression could be what is slowing this down...
Thanks,
Jared
Jared
CE - Microsoft
October 17, 2011 at 7:57 am
Also, are the connections between the instances in all of your scenarios EXACTLY the same? Your network connections could be the issue, even in the same room if they are not configured the same. Have you tried pinging from each to the other?
Thanks,
Jared
Jared
CE - Microsoft
October 17, 2011 at 8:07 am
130 servers on 7 diff locations
I didn’t want to ask these but:
•What's the geographic distance between these servers? Is it same or relatively same?
•Are your databases same in terms of volume and type of data (character / numeric / image)?
•Are you sure on network configurations are same on all the servers (bandwidth distribution)?
•And many more......... ?
October 17, 2011 at 8:13 am
I would recommend you to consult with your System Administrators / IT Dept and verify UPLOAD (write)/ DOWNLOAD (read) quota on network.
Try it with simple (regular) files first. If you find they behave the way you are expecting, then it makes sense to continue the troubleshooting on SQL Server.
October 17, 2011 at 8:19 am
Dev @ +91 973 913 6683 (10/17/2011)
I would recommend you to consult with your System Administrators / IT Dept and verify UPLOAD (write)/ DOWNLOAD (read) quota on network.Try it with simple (regular) files first. If you find they behave the way you are expecting, then it makes sense to continue the troubleshooting on SQL Server.
First, because it is easy, verify if you are backing up with compression on your 2008 instances. This may make a substantial difference if enabled, and it is by default.
Thanks,
Jared
Jared
CE - Microsoft
October 18, 2011 at 3:34 am
Hi all.
Thanx for the replies.
We don't use backup compression.
Ordinary filecopy is fast. I also ran a restore, and that ran fast (only backup is slow).
I have serveral SQLservers and one (or two) dumpservers on each location, and I dont backup from one location to another.
I can't compare backup to Win2008 server and Win 2003 server on all locations, but I still see that backup to Win2008 Server i slow on all the locations where the "dump"(backup) server is WIN2008 Server.
Of course, slow backup on the different locations might bee different reasons, I just think they migt be the same reason.
October 19, 2011 at 5:51 am
Hi
Now I have testet backup "in every direction". I believe that the slow backup on the other locations is due to network/network card etc.
The backup on the location were I experience the worst backupspeed might be related to the "dump" server. I installed a new virtual Win2008 Server, and did not experience slow backup there. Not as fast as filecopy, but still fast.
So now I'm gonna kill a serverdude. 🙂
Thanx for all the replies and your patience.
Regards
Arne Holmen
October 19, 2011 at 5:55 am
Arne Holmen-458117 (10/19/2011)
I installed a new virtual Win2008 Server
Just to clarify... Was this strictly for your testing or are the instances you were referring to from your first post virtual?
Jared
Jared
CE - Microsoft
October 19, 2011 at 6:52 am
Hi Jared
The slow backup is from one virtual server which is going to be a testserver (for testdatabases, not for my own testing) and from av SQL2008 Failover Cluster with serveral instances which is not virtual.
I'm also experiencing the problem on other MSSQL2008 instances on other locations dumping to other "dump" servers running Win2008 Server. It's not as slow as the first location, but slow.
I now believe that the slow backup ont the other locations ar due to other thins(network, nic and so on).
That brings me back to location #1. I tested a "dumpt" to a new virtual server running Win2008 Server, and that was fast. I thenconcluded that there must be something wrong with my "dump"server, and I've asked the serverdude's to have a look at that.
Regards Arne
October 19, 2011 at 8:27 am
Arne Holmen-458117 (10/19/2011)
Hi JaredThe slow backup is from one virtual server which is going to be a testserver (for testdatabases, not for my own testing) and from av SQL2008 Failover Cluster with serveral instances which is not virtual.
I'm also experiencing the problem on other MSSQL2008 instances on other locations dumping to other "dump" servers running Win2008 Server. It's not as slow as the first location, but slow.
I now believe that the slow backup ont the other locations ar due to other thins(network, nic and so on).
That brings me back to location #1. I tested a "dumpt" to a new virtual server running Win2008 Server, and that was fast. I thenconcluded that there must be something wrong with my "dump"server, and I've asked the serverdude's to have a look at that.
Regards Arne
Good luck with that then! I am also now going to call our OPs people "serverdudes" 🙂
Jared
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply