Backups 1: 3 Common Strategies

now in this module we're going to talk about the big picture options for database backups you're probably used to doing file server backups vm server backups app servers web servers but database servers are a little bit different with file and vm servers people are usually okay losing a few hours of data and people are usually okay being down for a few hours whenever we need to go do a restore well if people are willing to lose up to a day's worth of data it's totally normal to only do backups once a day we're used to installing backup agents on servers and having them run backups and we don't really care the exact moment that a file server or an app server or a vm server goes and does backups whenever they happen to hit customers are usually okay with that with sql servers though it's totally different people don't want to lose any data they think that they're going to be able to recover every single insert update and delete they think that no matter what people are changing inside of the database that they're going to be able to recover to an exact point in time and then also whenever the database server's down it's usually a big huge emergency people are completely freaked out so they want that server up and running as quickly as possible database is right up there with email in terms of things that people don't really realize how mission critical it is until it actually goes down so the way that you would back up a database server is fundamentally different than the way that you would back up a file server or a vm there are three common strategies to how you back up database servers and i'm going to go through and spend some time talking about each one of them in detail number one things that are a silo a backup that's only good for vm administrators or server administrators taking a snapshot but doing it very frequently as opposed to regular file and print servers that you would just maybe back up once a day number two a solution that's easy to implement but involves a lot of data loss that people aren't usually okay with daily sql backups but only done once a day and then number three daily full sql server backups plus transaction log backups being done throughout the day let's start with the easiest one vm snapshots now you probably have a server level backup program or a vm level backup program that uses vss volume snapshot service and what that does is it just tells the whole or tells backs up the whole entire server regardless of what applications are running on it these are relatively inexpensive there are a lot of vendors out there that do it these aren't listed in any kind of order or preference it's just the kinds of ones that i see out in the field so i just started typing whenever i had to go and take the right out the slide these work really well for most kinds of applications these work really well for most kinds of servers databases are a little different database servers need special handling what we have to do is we have to make sure that sql server isn't in the middle of writing something when you're dealing with a file server or an app server it's pretty easy to know whether or not it's in the middle of writing something because it'll hold a lock on a file if someone has an excel document open if someone has a word document open they'll have that file open but snapshot backup software is really good at saying all right i'm just going to take a snapshot of where this file is at this moment in time i'm not going to worry about any changes there in the middle of hitting save on databases are different databases are essentially saving stuff to files all the time there are constantly inserts updates and deletes going in people are logging sales logging human resource application changes so what we got to do is we got to get sql server to stop writing for a second or else the snapshot isn't going to be quite as crisp and clean as we want it to be we're going to have what's called a dirty snapshot or a crash consistent snapshot if you don't tell sql server to time out and stop doing writes what's going to happen is you're going to get a picture of the i o your picture of your drives and your storage that's consistent as if you'd have yanked the cables out of the back of the server at any given moment in time if all of a sudden you just yanked the storage cables out of the back of the sql server most of the time sql server can actually recover sql server does a pretty good job of recovering from crashes this is one of the things that i just love about sql server it's so phenomenal at handling unpredicted crashes i've seen so many people just force restart a server kill a vm have sql server crash have windows crash outright and then when windows comes back up and sql server comes back up most of the time it recovers well enough that it can figure out what was happening inside the database and rewind the changes most of the time isn't good enough for database servers for database servers this is usually the thing that your company runs sales on it runs payroll on it ships things on and if you try to bring this up and it doesn't recover and the database is offline that's usually when all hell breaks loose so what we have to do this bottom line on here we have to configure our snapshot server so that it's application aware and it tells sql server i want you to quiesce your rights but i don't want you to just believe it for me though i want you to also believe it from microsoft this knowledge base article sums up sql server support policy on virtual machines sql server supports virtualization where backup solutions that use vss but virtual machine snapshots that do not use vss snapshots are not supported by sql server any snapshot technology that does a behind the scenes save can leave sql server in an inconsistent state so what does application aware snapshot mean what does it mean to tell sql server to take a timeout say cheese and prep for the snapshot there are a handful of things that have to get done and i'm even simplifying this a lot here what's going to happen is that the snapshot software is going to say all right everybody i'm getting ready to take a backup sql server i want you to stop writing things to disk sql services what what's that you say okay hold on a second let me just clean up this data and lock this log file okay they're ready to go i've stopped taking rights he doesn't let go of the file he still owns the file locks on it but at least he said okay i've cleaned up my rights on here i'm going to stop all user transactions that would write to the disk while you go do your thing then the virtual snapshot software says okay great i'm going to go ahead and take a copy of everything that's going on here on the storage great off we go now the cool part about vss is it doesn't actually copy everything if you have a 100 gig database file for example vss snapshots don't copy the whole hundred gigs what they do is just get a really nice map of where the storage lives right now and then tell sql server okay bud you're good to go start doing rights again sql server says okay great off we go let's go insert a few rows and from that point forward the storage knows that every change sql server makes needs to be written to a new place so that we can keep track of what the data looked like before and when the data looked like afterwards this is miraculous when it's working well it's so cool and so fast regardless of how large your databases are your vss snapshots plus sql server can get the backups done in less than like 10 seconds max but there is a hiccup what you should see is up in the top event log up in the top event log sql services okay they they told me to freeze io on the master database and i froze io at give but get out my magic pointer here at 12 31 and 48 seconds then i thawed it again after you know however many seconds if it's thawed quickly however if it's not working well 1204 am i o is frozen on the database no user action is required however if no i if i o is not resumed promptly you should cancel the backup microsoft i love how they just assume that you're in there watching even informational alerts in the error log this isn't an error mind you this is just an informational thing they're expecting you to just be patrolling this log 24 7. in this case a full minute later io resumed on the master database no user action is required now if this happened at midnight who cares if you only dealt with i o that froze for 60 seconds around midnight most of us would probably be okay but the more databases that you have the worse that this becomes because remember how i said that the vss provider tells sql server hey go stop rights on all your databases what sql server does is it cleans up its rights on all of the databases on the volume where you're taking a snapshot in order serially one at a time it freezes io on the first database freezes i on the second database freezes i o on the third database all the way through until it's frozen i o on all the databases on that volume then tells the snapshot provider hey go ahead start taking a snapshot and then whenever the snapshot provider comes back sql server thaws all of the i o one database at a time that's why in this knowledge base article microsoft says we recommend that you create a snapshot backup of databases fewer than 35 databases at a time it's not a hard limit you could certainly go higher than that if you wanted to your experience would suck if your vss provider wasn't configured to go quickly enough because that person with the poor say 500th database who gets frozen they're screaming bloody murder going oh my god it's taking absolutely forever and it caught my backups whereas the people who have to deal with only a handful of databases don't really care this also affects how frequently you can take backups if you talk to businesses businesses usually say things like i want a up or i want no data loss when i ask a business hey how much data are you willing to lose they always go how about none is not an option because i would sure like to lose none and there's this huge disconnect between us as administrators and the end users who think that we can somehow magically provide zero data loss the closer that i want to get to zero the more frequently i have to take backups i want maybe the users say they'd be okay with 15 minutes of data loss that means i got to take snapshots at least once every 15 minutes if not more frequently if i had my vss backups were pausing i o for 60 seconds that would be a show stopper for me i can't freeze i o for 60 seconds every 15 minutes now i go in warning you about this stuff because i want you to go in with open eyes i still love this as a backup option it's listed first inside my list for a reason it just means that you have to balance how frequently you take those backups and how poorly they're configured if you're just the kind of person who just next next next your way through a wizard and you assume that the backups are working okay they are it's just that your users are being frozen in time every 15 minutes for however many seconds it takes your application to finish the backups if i'm doing snapshots every 15 minutes and io is freezing for 15 or 30 seconds people are going to get kind of angry about them so vss snapshot backups advantages number one they're really easy to configure even when you're dealing with application awareness because these are so widespread the documentation is really good they're stable they've been out there for years they let you manage your sql server under one pane of glass you just get one backup product that works for all your vms all your physical servers cloud servers these days some vss backup partners are saying that they work with cloud software although usually they they don't work very well they're good enough to meet most business requirements like if you can tune it to the point where you can take backups every 15 minutes that's usually good enough for most businesses disadvantages when they're configured poorly they freeze i o for tens of seconds at a time it's easy for me to just say this and go hey you should configure it correctly i don't teach any classes on that because of course every vss provider's backups are different like veeam is going to be different from netapp snap manager is going to be different from emc all those different companies they're not useful for data related tasks if you work for larger and larger shops your end users are going to start to say things like hey can you give me a copy of the database hey can you export a copy of the data for our data quality partner hey can you keep a copy of a reporting server in sync and now's a good time to stop just a second and talk about security so when i think about security and backup so backups are are kind of a surprisingly big deal around database security as a systems administrator or developer so you're in this class because you are in the fundamentals of database administration class you haven't been to an official like senior dba class or the mastering dba classes yet so i got to scope how much i tell you inside this class versus how because i'm trying to teach you all i can teach you inside like the span of a day i want you to just be aware that when someone says hey can we get a copy of the backups they may be getting your customer list your employees your payroll your profit and loss statements how much you charge for products it may have personally identifiable data that data may need to be encrypted and it's probably not as you first get started taking over people's sql servers and looking at what's happening inside the backups later on as you get through your career you're going to be kind of horrified by what stuff is sitting inside the database unencrypted and anyone who gets a copy of the backups gets to query this stuff there's no security inside the backups just by themselves so more on that when we start to talk about encryption here in a minute so who are vss snapshot backups great for they're great for standalone sql servers with less than 35 databases because microsoft's recommendation on that 35 database is max that's not a hard and fast number that's microsoft's number you could go higher with a well configured vss snapshot you could go worse if you have a poorly configured vss snapshot provider if you have third-party apps accounting apps crms intranets whatever where none of the developers work for you and you don't have to worry about keeping a dev server in sync or keeping a reporting server in sync and the sql server is just infrastructure that's sitting over in the corner vss snapshot backups are great for that just make sure that you do application aware snapshots uh just because of course that if they're not showing up in sql servers backups uh backup logs things like that then they're not quieting sql servers rights and you're getting a crash consistent backup sometimes sql server can't recover from that so that was the first option which was vss snapshot backups now we're under the second option which is daily full native sql server backups before there were vss snapshot backups which were kind of the standard now across around the world for uh for server level backups before that we needed a way to backup sql server now i'm old so i can actually tell you about this back in my day any kind of a server level backup type program had a really hard time backing up the sheer quantity of data that we would get from sql server plus sql server never closes its data or log files it always keeps locks on them because of course an insert update or delete could come up with and fire up and dump data into those files at any given time so sql server had its own backup software sql server has its own backup software that starts with a query when you run backup database and then you get all kinds of options you can tell sql server hey backup this database use compression only backup this file encrypt databases on the way out you get all kinds of options that you can use around backup database this module of this class is not how you run that command first in this module we're just going to talk about high level architecture choices what this does is it just shoves all of the data pages out across over to wherever we're doing our backups as quickly as possible it just reads the data out of the data and log files dumping them across over to wherever their destination is and you don't have to really worry too much about how much of the transaction log is included but some of that is included as well it's pretty easy to configure because it's been stable it's been the same for like two decades now there's been small changes to backup database but it's mostly around how it's implemented internally it's not as much about how how you have to execute the commands it's pretty easy to configure it is at the per database level though you'll notice that that command said backup database it didn't say backup server so you have to iterate through that for every single database that you care about classic classic configuration problem you run into is that someone specifically picked a list of databases and they didn't say all databases and then over time their users added more and more databases and they forgot to go back in and go add those additional newly created databases works the same exact way whether we're talking about a vm or a physical server which is wonderful you can configure things just once and then use the exact same process all throughout your entire environment and it's super easy to get backups to developers to reporting servers to sync your disaster recovery servers instead of having to copy the entire server from one place to another we can just copy specific databases and we can copy them on a different frequency than we would the whole entire server the disadvantage though is backup database backs up the entire database if you have a 100 gig database you probably don't want to read a hundred gigs worth of data and then shove a hundred gigs of data across the wire wherever you're writing your backups to because it takes a long time to run not catastrophically long but just think like the same length of time it would take to write out the contents of the server because it takes so catastrophically long when people do this technique they tend to say i'm going to run daily backups there's certainly no reason that you couldn't run them more frequently than that but i'm going to show you a much better alternative to that here in a few minutes this does add another layer of complexity too because i can't tell you how many times i've seen people set up backup jobs and then assume that they're working okay without actually checking and they think that as long as the backup software isn't throwing an error that the backups must be working okay and of course a lack of failure emails does not indicate success and then it's also painful to do restores because you're going to have to write out restore database commands for every single database that you have you'll need to learn to automate that either with third-party scripts utilities or starting to do things like powershell using the dba tools framework so given those advantages and disadvantages who's it for well if you're only doing daily full backups that's good for servers where you don't mind losing a whole day's worth of data and your first reaction is probably brent on what planet are people okay losing an entire day's worth of data and you'll be surprised there are actually servers like data warehouses where people say oh it's okay if we lost a whole entire day's worth of data we would just reload it from scratch from somewhere else servers where we don't have the licensing for snapshot backups and the data isn't as critical and i'll talk about this in more detail when i step through the maintenance plans demo so we've covered two out of the three common ways to back up your sql server the third way to do it now we're getting into more complex ways the vss snapshot backup option the very first one was super simple because you could use the same tools that you use for all of your file servers vm servers app servers you name it the second one started talking about using skills that were specific to sql server now the third one also involves losing less data but working harder you're going to have to work a little harder in order to get this to work to explain it i need to talk about the two ways the sql server store stuff sql server puts things inside the data file and inside the log file and in order to understand what that is let's say that we're going to go re up to run an update against our customer table to set a new balance if i run this transaction the first thing that sql server does is it logs what it's about to do inside the transaction log file second it edits the data page in the data file but it doesn't write that to disk yet and you're like brent wait what how can it edit something in the file but not make the change inside of disk well what it does is it pulls the data page up into memory that it's going to change remember we talked about earlier everything in sql server it does in 8k pages it pulls the relevant 8k page up into memory and it changes it in memory but doesn't write it to disk yet and immediately tells the application okay your change is done and that sounds a little weird to tell people that their change is done before that change has actually been written to disk but it was written to the log file so what's awesome about that is say that sql server crashes in between step three and four after it's told your application that the change has already been written to disk but before it's been actually written to disk how does sql server recover remember how earlier i talked about how we could pull the cables out of the back of the sql server we could just yank the storage cables out if they would ever allow you to get near the sql server without being shot with a rifle you just yank the cables out of the back in the sql server and the reason why it can recover is the first thing it does on startup is it says okay right what was i doing well i see here in the log file it says that i was updating this customer's bank balance to 500 bucks or euros or lira or whatever he was updating this customer's balance to 500 oh i'm looking over in the the data files that change hasn't been made yet i'll go ahead and read the change that i need to do from the log and then write that over against the data file that's why even crash consistent backups usually work okay not always but usually work okay will that log file of the things we're going to do to the database the changes that we're going to make is relatively small imagine that you have three years worth of sales inside your database three years times 365 days a year yeah maybe one of them's a leap year it's about a thousand ninety five days that's why i say about i always weird out whenever i use the word about well a log file for today's activity only has one day in it if you do the math on there and i had to use a calculator because i suck at this really bad that's hardly any percentage of the data file size so what the log files make an amazing difference with is we're only writing down if we just write out whatever changes we made inside the log file that's a very tiny percentage so we could take a full backup every day at midnight just to say you don't want to do this i'll talk about why in a second but every day at midnight you could back up each of your sql servers and then every hour on the hour you could tell each sql server hey you know that log file you have where you've logged out the changes that you made to the data file go back that up take out the list of transactions that you've written out in the last hour and just go write that tiny part out oh my god it's going to be so tiny it's going to be nothing it's going to hardly take any time at all because you're not really doing that much work i know because you're watching this video rather than doing real work so every hour on the hour we could be writing out whatever tiny transaction log changes we were making and then let's say at 4 30 p.m the sql server crashes all you have to do all you have to do said the consultant said the manager from out of town all you have to do is restore the last full backup and all of the transaction log sense and you're right up to a point in time recovery that is way better than daily backups but honestly it's it's it's really only kind of competitive with vss snapshots it's not the same yet because you know what's awesome about bss snapshots as they run nearly instantaneously this is not nearly instantaneously this is going to take a little bit longer because after all we're writing out a full backup we're writing out all the transaction log changes so why would i argue for this over vss snapshots what's better than vs snapshots because i can give somebody the data without giving them the server and this is the point where if there are any vss vendors out in the audience they immediately go brent brent wait i would like to file an objection wait trust us we have this really cool feature where you can spin up your server in an isolated environment you can extract the databases out uh yeah yes and what do you extract the databases out to too go back up why oh come on now what's the point of that you know at this point you're really doing the same thing that we were talking about earlier well here instead all you can just do is just go point them over to your fold file share where you've got your log file backups your full your database backups and just hand them those backups and say go with no additional work plus other sql servers can go and pull that data they can go restore that data and keep the development servers in sync you can keep reporting servers in sync disaster recovery servers and sync and a whole lot more this lets you lose as little data as possible without taking those hiccups that via poorly configured vss snaps will take if you've got the kind of crappy vss app that's freezing your i o for 10 seconds 30 seconds 60 seconds this isn't going to freeze i o so you can do backups as often as you want true story i have a couple of clients who just do backups while one equals one they've written up their own little script and they just say while one equals one go take a transaction log backup so that they're just constantly streaming the log files out somewhere else this technique works exactly the same way whether it's a vm a physical box something up in the clouds work the same way for decades it's super rock solid so it's easy to get into you can find documentation on this it's been exactly the same for decades disadvantages you can imagine at those four clients where they're going well one equals one take a log back up my god they're going to have thousands of log backups for every single database multiply this times lots of databases even if you're only taking one log back up every five minutes i suck at math but five that's what one it's fifth time a lot it's 12 backups an hour times 24 hours of days like 288 backups a day per database you can imagine if you've got 30 databases you ain't going to t sql your way through this the first time a disaster strikes you have to rehearse this ahead of time you have to use tools like powershell or sp database restore to quickly restore lots of them just by pointing at a folder if you want disaster recovery it's kind of like a bunch of tools and you have to figure out how to use those tools to copy data from one place to another we're not going to talk about that inside the fundamentals of database administration class i do talk about it inside the mastering database administration classes and then the last one i'm frustrated that i even have to mention this in the year 2020 it's a super bummer that i have to tell you that microsoft sql server doesn't clean up the log file by itself and it doesn't tell you whenever there's a problem cleaning out the log file the log file is only made available for reuse whenever you take a log backup and push that data out somewhere else but if for some reason something happens that where sql server has to keep stuff inside there like someone starts a transaction someone configures replication and then doesn't uh go back and clean it up we can run into a situation where the log file grows and grows and grows and then we have to get involved in a you know disk space out of use type scenario so where's this option good for this option is good for mission critical servers where you want to lose as absolutely little data as possible now we're in the fundamentals of database administration class here and now is a good time to talk about something about that first line everybody believes that their stuff is mission critical everybody believes that their server should never go down and it should lose as little data as possible but the less data you want to lose the more it costs the harder it is for you to achieve near zero data loss so in here inside this class i tend to focus on stuff where people are willing to lose up to about an hour's worth of data it's not their goal they're not asking to lose up to an hour's worth of data but to them it's like a worst case scenario we're willing to lose up to about an hour's worth of data and beyond that we're going to start to get a little angry if you want to lose less than that you have to do more advanced techniques just beyond the fundamentals class that's the only reason that i mention it so it's also good for servers where shops where you have a really crappy vss backup application and it thaws for thoughts and freezes for ios take longer than 10-15 seconds and your users aren't happy with that slow long delay between vss snaps i do talk about how to configure this over in the transaction logging demo and i'll expand on it over in the mastering classes so those are the three ways that you can they're the three most common ways that people back up sql servers in that are 100 gigs or less there are a few things to keep in mind regardless of which one of those three options that you choose first off is how you configure the database when you right click on the sql server which i'll show you in the maintenance plans module when you right click on the sql server's databases and go into properties you're going to see that each database has a recovery model either simple or full there's another option but we won't cover that inside this class in both of these options see or but whichever regardless which one of these two you're in sql server still writes down everything that it's going to do to those databases sometimes you'll hear misconceptions that people will think if your database is in simple recovery model the sql server doesn't log transactions it still does because remember the yanking cables problem if someone yanks the cables out of the back of the server we have to be able to recover so even in simple recovery model sql server still logs the changes that it's about to make the difference between these two is that in full recovery model the transaction log is considered cleared or the space is considered cleared as transactions finish now i'm simplifying the bejesus out of this there are a whole lot of gotchas around things like availability groups database mirroring long-running transactions replication and so forth i feel like i would be a bad teacher if i didn't at least mention that but generally speaking the transaction is cleared as our transaction log space is made available for reuse as transactions are committed in simple recovery or i'm sorry i've got the backwards simple recovery model tran this transaction log space is made available whenever transactions are committed in full recovery model your transaction log space isn't made available for reuse until you clear it by doing a transaction log backup so which one of those you choose depends on how little data you're willing to lose if you're only doing backups once a day if you're just doing native full backups choose the simple recovery model so the sql server can kind of sort of manage the transaction space for you inside the log if you decide to do transaction log backups you have to choose the full recovery model and when you do that just means you're going to have a little bit more work involved in terms of maintenance and then if you're doing vss backups it's going to depend on how your backup vendor wants the stuff configured for example with veeam backups you can choose whether or not you're going to be in simple or full recovery model either they'll take transaction log snapshots for you or they'll require you to do transaction log backups and they'll take snapshots of the wherever the log files go teaching you how to use each of the vss snapshot backup vendors is obviously way out of scope for this particular module the reason why i mention this though is the classic problem that i worry about is you choose the full recovery model not understanding what's going on there and then you don't do log backups which means that sql server's keeping all this stuff in the log file for you hoping that someday you'll do a log file backup which you have no intention of doing and then your log file just starts to grow and it's a ticking time bomb it's just a matter of time until you run out of space available wherever your log files are and the sql server grinds to a halt as a side note this is the kind of reason why you'll sometimes hear old grumpy database administrators say things like you should never put databases on the c drive it's not as much of a performance thing these days a lot of us have virtual machines and all of it's just one big pool of storage anyway but it's also a management piece because if you put all the databases on the c drive and you configure it the way that i just described where sql server's logging everything and never freeing up the space your log file is going to grow and grow and grow until the point when all of a sudden you run out of drive space and if you run out of drive space on the c drive you're going to have a really bad day the second thing that i'm worried about is encryption by default sql server doesn't encrypt anything in the data file sql server doesn't encrypt anything in the log files you can go read these files with things like a hex editor you can see their contents and we talk about this in the corruption module where i corrupt a database right in front of you this means that anybody who gets a copy of your backups can query anything inside the database and the first time that i i have this discussion with managers whenever i go to a new client we're talking about hey looks like you've got social security numbers hey it looks like you've got customers addresses hey it looks like your price list is inside the database hey here's your profit and loss statement would it be a problem if any of this stuff got out to your competitors you're like oh my god i need something better you can think that you're protected if you do stuff like encryption at rest if you do encryption at rest sql server depending on where you implement it either whether you implement it inside the storage layer or if you do it inside the vm layer if you do it outside of sql server sql server doesn't know and it'll just happily write unencrypted stuff that your drives are supposed to be intercepting and taking care of well that works when the date is at rest in the places that you expected it to be at rest but if mr diabolical evil co-worker comes in and says oh i've had my eyes on a new portion 911 and i know just how to get it and they go write a backup command they can craft their own backup command to write the databases anywhere that they want to write them like the cloud yes these days sql server will even back up directly to the cloud if you have someone diabolical who asks for that right and of course i know lots of people inside your shop have that right because people have been given free rein inside the databases to do anything that they want to that database looks like a hot mess so i would be a bad trainer and a bad consultant if i didn't at least say the bottom stuff you either need to encrypt personally identifiable data inside the database and often personally identifiable data isn't even enough like your list of co-workers your list of employees your list of competitors there can be all kinds of things inside of the database that cause executives to freak out and or encrypt the backups themselves and just remember that even if you encrypt the backups if you leave permissions loosey-goosey other people may still be able to take their own backups and not specify encryption your job is never done so to recap what we talked about inside this module i covered the three common ways to back up sql server now you're at the point where starting where you can have a good conversation with your coworkers about which one of these makes the most sense for you this is really just a starting point as is most of my fundamentals classes with backups you can spend days even weeks of your life learning about the intricacies of how they work and how you need to protect yourself better whenever any of these things go bump in the night

Channel: Brent Ozar Unlimited

Views: 6,156

Rating: 4.9666667 out of 5

Keywords: sqlserver, brentozarunlimited, brent ozar, mssql

Id: NuwDF6kzONs

Length: 41min 57sec (2517 seconds)

Published: Thu Mar 11 2021