"Continuous" backup via VSS? Performance Issues?
-
We are using Iron Mountain's backup service, LiveVault, for our SQL servers. It provides what they call continuous backup via VSS. They offer 15 minute restore granularity.
It backups up the MDF and LDF files directly, without using any SQL services other than the SQL VSS Writer.
From the little I know about SQL and VSS, I believe that a call is made to the VSS Writer to stop IO on the SQL server briefly while a copy is made.
What I would like to know, is whether it is possible for the VSS Writer to send only full copies to ShadowStorage, or is it able to make incremental copies?
The reason I ask is performance related. If they are having to dump full copies and then parse them to only send a delta back to Iron Mountain, then IO is going to have to be stopped for a good while every 15 minutes. We easily have 100 databases going to Iron Mountain through LiveVault and it make me verious curious as to the real impact this is having on our server's performance.
The end-user experience is very good in all line-of-business applications, so the only reason I am concerned is because if I can increase performance I can increase the density on our SQL servers, reduce server count and therefore reduce cost.
I'm sure one of the many great minds on this forum has some experience with this, and would be very appreciative for any feedback that could be given.
Thanks in advance everyone!
-
Oh, and I am ignoring the fact that this is in no way "continuous" backup, and therefore the reason it is in quotes. Please do the same for the sake of keeping to the intent of this thread.
-
There is no such thing as Incremental backups either. However, you could send Iron Mountain Differential backups, which is a list of all changes since the last FULL backup. Or you could use DeDup technology. I haven't personally implemented it, but the way it works is that it literally sends only changes once the first backup file is taken. We're talking individual records.
But you'd have to coordinate with Iron Mountain and a DeDep technology specialist to know if that'd work for you. Has anyone here actually used DeDup tech?
-
cashmanifesto (9/16/2010)
From the little I know about SQL and VSS, I believe that a call is made to the VSS Writer to stop IO on the SQL server briefly while a copy is made.Oh, and I don't know much about the VSS Writer, but I'm pretty certain that SQL doesn't stop writing records to the database just because a backup is going on. At the very least, it will write them to the transaction log.
-
Brandie Tarvin (9/22/2010)
cashmanifesto (9/16/2010)
From the little I know about SQL and VSS, I believe that a call is made to the VSS Writer to stop IO on the SQL server briefly while a copy is made.Oh, and I don't know much about the VSS Writer, but I'm pretty certain that SQL doesn't stop writing records to the database just because a backup is going on. At the very least, it will write them to the transaction log.
I read an article the other day about the difference between VSS Writers and VSS Subscrbers that said the difference is specifically that the VSS Writers are designed to suspend IO operation the application, allowing a copy to be made safely without stopping the service.
I can't find that article to show you right now, but I did find this diagram that specifically deals with the SQL VSS Writer. It definitely shows a Freeze/Thaw process.....
I understand that the purpose of VSS is to not put strain unduly on a server while performing backups, but if this is being done every 15 minutes, it can't be good.
If that is what's happening, it really makes me want to consider shipping my T-Logs and backing them up instead.
Here's the diagram:
-
Brandie Tarvin (9/22/2010)
There is no such thing as Incremental backups either. However, you could send Iron Mountain Differential backups, which is a list of all changes since the last FULL backup. Or you could use DeDup technology. I haven't personally implemented it, but the way it works is that it literally sends only changes once the first backup file is taken. We're talking individual records.But you'd have to coordinate with Iron Mountain and a DeDep technology specialist to know if that'd work for you. Has anyone here actually used DeDup tech?
I forgot in my last post Brandie: Thanks for jumping in and that's great information.
However, I'm specifically talking about what the VSS writer puts into ShadowStorage. I don't believe this uses the same mechanism as a SQL backup, as I think it is an actual binary copy of the MDF/LDF and not SQL data.
Assuming I'm right about that (big assumption), could is also possibly put a delta of the MDF/LDF image into ShadowStorage, or does it have to be the entire thing?
I could be very wrong about all of this though, as I am just trying to figure out how VSS actually works in SQL.
I do know that it only sends the changed information through a priopriety client-side dedup process, so our WAN consumption is not a concern.
-
Well, as I don't know enough to answer the question, I suppose I need to do some research. @=) Thanks for the diagram. That's good stuff. I'll see what I can find for you and post back.
BTW, you say you're log shipping but not backing up the transaction log? Or did I misread that?
-
Brandie Tarvin (9/22/2010)
Well, as I don't know enough to answer the question, I suppose I need to do some research. @=) Thanks for the diagram. That's good stuff. I'll see what I can find for you and post back.BTW, you say you're log shipping but not backing up the transaction log? Or did I misread that?
Sorry, what I meant was that if the backup agent we're using is really eating my resources and delaying IO as frequently as every 15 minutes, I may have to alter our backup strategy to use T-Log shipping instead and then configure our backup agent to back the T-Logs up instead of directly backing up our databases.
And thanks again for jumping in! This is my first time posting here and definitely a good experience so far.
-
Are you actually having performance issues? Because if you're not, I don't think you're going to have a problem.
Yes, VSS Writer does freeze SQL Server, but only for a max of 60 seconds while it's building the Shadow volume. I found more links for you, some really good information. But here's the most important article:
http://technet.microsoft.com/en-us/library/ee923636(WS.10).aspx
Other Links (which I followed to get to the above):
http://msdn.microsoft.com/en-us/library/ms175536.aspx
http://technet.microsoft.com/en-us/library/cc966520.aspx
http://msdn.microsoft.com/en-us/library/bb968832.aspx
Take a look at these and let me know what you think. It's interesting that the VSS Writer forces open transactions to commit or roll back before allowing the Shadow volume to be written.
-
If I'm not mistaken, this is the basis of Database Snapshot technology. Interesting. Perhaps even the Mirroring.
-
Firstly, I am not having performance issues. We have approximately 160 databases running on SQL instances ranging over 4 different versions of SQL on a single machine and performance in all of the applications is exceptional. (Based on end-user feedback)
I guess that is what really confused me. I can't understand how VSS can be SO efficient. It's having to snapshot each of those 160 databases and keep IO frozen for up to 1 minute out of every 15. This lead me to believe that I could possibly increase my capacity on that server by reducing overhead. Which we all know, makes us heros when we start throwing around ROI and TCO figures. (Or makes us look like brown-nosers...LOL)
I stumbled across the information about VSS when I had a problem with some Exchange Stores stuck in ShadowStorage. Exchange calls for the IO to be frozen in the same way, and the article specifically mentioned SQL also. Of course when I read that I was a) shocked b) worried c) curious.
But at the end of the day, there are no performance problems. So this really just becomes about finding out why not!
-
Brandie Tarvin (9/24/2010)
Are you actually having performance issues? Because if you're not, I don't think you're going to have a problem.Yes, VSS Writer does freeze SQL Server, but only for a max of 60 seconds while it's building the Shadow volume. I found more links for you, some really good information. But here's the most important article:
http://technet.microsoft.com/en-us/library/ee923636(WS.10).aspx
Other Links (which I followed to get to the above):
http://msdn.microsoft.com/en-us/library/ms175536.aspx
http://technet.microsoft.com/en-us/library/cc966520.aspx
http://msdn.microsoft.com/en-us/library/bb968832.aspx
Take a look at these and let me know what you think. It's interesting that the VSS Writer forces open transactions to commit or roll back before allowing the Shadow volume to be written.
Oh, and thank you very much for this information; the articles are fantastic. Specifically the bit about it only freezing for a max of 60 seconds. That really answers a lot.
It also presents some new questions, like what happens if your copy doesn't get finished in 60 seconds? And how do you get a copy of a 90GB MDF and 180GB LDF within 60 seconds? But that's good. I like the opportunity for growth! If I find the answers to these questions next, I will post them just for the benefit of anybody else stumbling across this thread.
-
cashmanifesto (9/24/2010)
thank you very much for this information;You're quite welcome.
cashmanifesto (9/24/2010)
It also presents some new questions, like what happens if your copy doesn't get finished in 60 seconds?And how do you get a copy of a 90GB MDF and 180GB LDF within 60 seconds?
You're thinking about this the wrong way.
Sure, the first backup is going to take a while, but later on, not so much. Look further down in the first article. There are different types of shadow copying that VSS Writer does. Depending on how Iron Mountain developed LiveVault, it could just be doing a complete copy, copy-on-write, or a redirect-on-write. My money's on the copy-on-write.
That's why I think this tech is the basis of database snapshoting and mirroring. It's also why I don't think you'll have performance issues. But if you want more details, contact Iron Mountain and talk to them. I'm sure your Sales Rep can put you into contact with someone.
-
Brandie Tarvin (9/24/2010)
That's why I think this tech is the basis of database snapshoting and mirroring.Snapshot, yes (copy-on-write). Mirroring, no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You're thinking about this the wrong way.
Sure, the first backup is going to take a while, but later on, not so much. Look further down in the first article. There are different types of shadow copying that VSS Writer does. Depending on how Iron Mountain developed LiveVault, it could just be doing a complete copy, copy-on-write, or a redirect-on-write. My money's on the copy-on-write.
Fantastic. That makes perfect sense, and what I'd hoped. I started the thread asking about whether the VSS Writer can do incrementals, and it looks like that's what the differential copy in the copy-on-write is doing. (since it uses a point-in-time reference instead of an archive bit, it seems like it would ACT like an incremental even though its technically differential)
Move over mythbusters.... :-p THANKS AGAIN! You guys are kickass!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply