July 19, 2018 at 7:51 am
Hi, I am not sure if this is possible but I wanted to check. We have a Dev SQL Server. We wrote a script to take backup files from prod server every night and restore it on the dev server. It is working fine but the problem is when the Restore script runs, we can't query anything from that server. It seems like SQL Server uses all the CPU and Disk I/O to restore the database as fast as possible. Is there a way to specify in the restore script or some where to use only up to specific CPU % or Disk I/O %?
If there is a better approach for this, please let me know.
Thanks,
Sridhar.
July 19, 2018 at 8:02 am
Sridhar-137443 - Thursday, July 19, 2018 7:51 AMIf there is a better approach for this, please let me know.
Presumably you have considered doing the restore in the middle of the night, when no one is working?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 19, 2018 at 9:34 am
we start the restore in the night but since we have several databases, it completes in the morning.
July 19, 2018 at 9:42 am
Sridhar-137443 - Thursday, July 19, 2018 9:34 AMwe start the restore in the night but since we have several databases, it completes in the morning.
If you are prepared to spend some money on a solution, SQL Clone might be an option.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 19, 2018 at 1:06 pm
It seems to be interesting but expensive. Are there any other solutions?
July 19, 2018 at 1:28 pm
Sridhar-137443 - Thursday, July 19, 2018 1:06 PMIt seems to be interesting but expensive. Are there any other solutions?
Make the DEV environment have more resources to allow it to restore faster. It will cost money though... and possibly as much or more than SQL Clone.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 19, 2018 at 2:15 pm
Another option would be to not work at night when the restores are running. That would be my first choice. 🙂
Sue
July 19, 2018 at 2:39 pm
The issue is since we have several databases, by the time it restores all databases, it will be around 9:00 AM next day.
July 19, 2018 at 2:51 pm
Sridhar-137443 - Thursday, July 19, 2018 2:39 PMThe issue is since we have several databases, by the time it restores all databases, it will be around 9:00 AM next day.
There really isn't a whole lot you can do if the server doesn't have the resources. Not real likely but maybe we can think of some other things if we knew a bit more -
How big are the databases you are restoring daily?
what does your dev server have in terms of CPU, memory, disks? Is in physical or virtual?
Any chance you can restore the databases from local drives?
What else is running on the dev server during the restores - in SQL Server and on the Server itself?
Sue
July 19, 2018 at 3:58 pm
Hi Sue,
Thank you for your response. The databases are different sizes. The biggest one is 100 GB. Lot of them are over 50 GB. we are restoring the database from files in another server. The Dev server is AWS EC2 Instance (m4.large - 2 CPU, 8 GB RAM). I can copy the backup files to local drives if you think it would speed up the restore significantly. It is a dedicated SQL Server. We are not running anything other than SQL.
July 19, 2018 at 4:46 pm
Sridhar-137443 - Thursday, July 19, 2018 3:58 PMHi Sue,
Thank you for your response. The databases are different sizes. The biggest one is 100 GB. Lot of them are over 50 GB. we are restoring the database from files in another server. The Dev server is AWS EC2 Instance (m4.large - 2 CPU, 8 GB RAM). I can copy the backup files to local drives if you think it would speed up the restore significantly. It is a dedicated SQL Server. We are not running anything other than SQL.
Thanks for providing that - it really helps. With those database sizes, it really shouldn't be running forever. and pegging the server so bad. How many databases? And do you run all of the restores individually or all/a few at a time?
You can ignore my comment on using the local drives - that would be more for an on-premise server.
Where are the .bak files when you are restoring them? Are the .bak files on Amazon S3?
Sue
July 19, 2018 at 8:55 pm
Sue_H - Thursday, July 19, 2018 4:46 PMSridhar-137443 - Thursday, July 19, 2018 3:58 PMHi Sue,
Thank you for your response. The databases are different sizes. The biggest one is 100 GB. Lot of them are over 50 GB. we are restoring the database from files in another server. The Dev server is AWS EC2 Instance (m4.large - 2 CPU, 8 GB RAM). I can copy the backup files to local drives if you think it would speed up the restore significantly. It is a dedicated SQL Server. We are not running anything other than SQL.Thanks for providing that - it really helps. With those database sizes, it really shouldn't be running forever. and pegging the server so bad. How many databases? And do you run all of the restores individually or all/a few at a time?
You can ignore my comment on using the local drives - that would be more for an on-premise server.
Where are the .bak files when you are restoring them? Are the .bak files on Amazon S3?Sue
I'm not sure why people would expect a server with only 2 CPUs and 8GB of RAM to do better than what it is even if it is in the cloud. You might be able to squeeze more speed out if you've got the VLFs in your log files under control and fiddle with the number of backup buffers and max transfer size but the machine is a bit pasty and I wouldn't expect to help a lot.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2018 at 9:00 pm
As of now, we have 127 databases. we typically add 1-2 databases every month. If clients have been with us for long time and depending on the size of the client, the size of DB varies. We run the restore one database at a time. We take a full backup every Sunday and transactional log backup every night. so, what we do in the restore script is take the last weekly backup and apply the transactional log backups that were taken during the week. So, as we get to Thursday or Friday, we can see the difference that it takes more time to restore.
The .bak files are stored in our mirror server. It is also an EC2 instance in the same network but much bigger than SQL Dev Server.
July 19, 2018 at 9:56 pm
Sridhar-137443 - Thursday, July 19, 2018 9:00 PMAs of now, we have 127 databases. we typically add 1-2 databases every month. If clients have been with us for long time and depending on the size of the client, the size of DB varies. We run the restore one database at a time. We take a full backup every Sunday and transactional log backup every night. so, what we do in the restore script is take the last weekly backup and apply the transactional log backups that were taken during the week. So, as we get to Thursday or Friday, we can see the difference that it takes more time to restore.The .bak files are stored in our mirror server. It is also an EC2 instance in the same network but much bigger than SQL Dev Server.
You have 127 database, mostly 50GB each and you want restores to run faster on a 2 CPU, 8GB server? I have to say, good luck with that! 😀
You can mitigate this a bit, though.
First, do all 127 databases actually suffer changes every day or are some of them temporal in nature where they become static after a month or two. If the later, consider NOT restoring them because nothing has changed.
Second, it takes a fair bit longer to restore a herd of transaction log files than it does to restore a DIF. Consider making a DIF backup each night to go along with your transaction log backups. The restore the full, restore the lastest DIF, and then restore the now much smaller number of log file backups.
You have said if you've examined the VLFs in the log files. If you have an inordinate number of VLFs in your log files, it will take a lot longer to restore because your log file is kind of like formatting a disk when you do a restore because it has to format the allocated space with VLFs. And, if you DO have static databases as cited above, consider reducing the log file to 0 bytes (or as close as you can get to it), set the static databases to READ_ONLY, and restore them 1 final time never to be restored again.
And for crying out loud, beef up that server. My bloody old S3 android phone has more memory than 8GB! Stop penny pinching in the wrong place. You folks have spent more money in manpower doing restores than what you'll need to spend on better virtual hardware.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 20, 2018 at 6:17 am
Sridhar-137443 - Thursday, July 19, 2018 9:00 PMAs of now, we have 127 databases. we typically add 1-2 databases every month. If clients have been with us for long time and depending on the size of the client, the size of DB varies. We run the restore one database at a time. We take a full backup every Sunday and transactional log backup every night. so, what we do in the restore script is take the last weekly backup and apply the transactional log backups that were taken during the week. So, as we get to Thursday or Friday, we can see the difference that it takes more time to restore.The .bak files are stored in our mirror server. It is also an EC2 instance in the same network but much bigger than SQL Dev Server.
As I already said when you don't have the resources on the Dev server there isn't much that can be done. But 127 databases and growing is a fair number of restores to be doing on that server. If you are adding databases everything month, the issues you face will only keep getting worse. At some point fairly soon, the resources will need to be increased on the Dev server. Your just about hitting a wall now and losing time, not being able to work yet...those are really business decisions. But at some point, holding off upgrading that server is going to cost the business more than what it costs to add more memory, CPU to the server.
Since you can get by in production with just one transaction log backup a day, there doesn't seem to be a lot of activity. Are all of the databases in Development being used daily? Is there much of a time difference in time to do the restores on Sunday night with the one full restore compared to Saturday night when you add in all of the weeks transaction log files? That will give you an idea of how much time gets added with all of the log files and how much the t-log restores plays into the time to restore.
If not all of those databases in dev are actually used, skipping some of the restores may be the best you can do.
You could do something like leave all of the databases in recovering state when you restore, if you used one in dev, pull it out of recovering state and then that is one database where you need to go through the whole restore process you have (last full and all logs). The databases that are left in recovering state, you can apply just the last log backup. You can check state_desc in sys.databases to figure out which restore process is needed - those not in recovering stat would need the fullback and the logs, in recovery need just the last log. But if every databases on the dev server is used daily for some reason or another, none of that applies
Sue
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply