Tim’s Insider Video on encryption can be downloaded
You can get the demo code here.
The video on backing up to Azure can be downloaded
Transcription: Advanced Backup Techniques
Carlos Chacon: Hello, and welcome to SQL Data Partners podcast. I’m your host, Carlos Chacon. Today were going to finish up our conversation with Tim Radney, a Microsoft MVP and SQL skills consultant. We’re going to be talking about backup and recovery specifically some advanced features, particularly around encryption and backup to Azure.
Before we begin that, I do want to throw out something. For those who maybe in smaller environments, who don’t have enterprise versions or can’t quite do encryptions yet, that is the SQL server booster by my friend Eric Velez. Eric is actually from Guatemala, and has put together a free tool called SQL server booster.
This is an application for DBAs, developers, or people that do management around Microsoft SQL server and that need to make backups automatically. Compress those and send them to different places, through FTP, maybe through Amazon, Azure, or even Dropbox, or through your network.
It just so happens Eric is looking for BETA testers for his newest version of the product. If you’re interested, you can take a peek at sqldatapartners.com/podcast for this episode, which is episode five. We will make sure you have the link to do that. This is going to be particularly interesting for those who are with the express editions.
Maybe you have a couple of those you’ve been playing around with, you don’t have yet even a full standard edition version, and need some help with assistance around those maintenance tasks. You might want to check those out.
At the end of the podcast, we will have some information on how you can win Tim’s book on backup and recovery strategies. Also, we will give you the information on how you can get some free Perl site, free training from the SQL skills folk over at Perl site. That information will be at the end of the show.
I cannot forget, we are now on iTunes. I didn’t realize quite the level of effort it will take to get on iTunes. We missed that, if you will, on opening week last week. Now we are there on iTunes, so if you seek for us SQL Data Partners podcast, you will find that. We will make sure, again, that you have the links. You can send those out. Again, welcome to the show.[music]
Carlos: Encryption, backup encryption, you want to talk about backup encryption?
Tim Radney: Sure, I was really glad to see backup encryption being added to SQL server 2014. Previously, we’d have to use third party tools. Some of those tools are still good for centralizing and managing your backup and things but with encryption being part of 2014, really helped add to in backing up the URL which was introduced in 2012 service pack and accumulated update.We gained the ability to backup URL, meaning windows server storage or Azure storage. Being able to encrypt natively our backups really eases the pain and concern about backing up across the wire up to Azure.
Working with banks, working with financial institutions, healthcare environments, we backup our databases. We are all good, we know that we need to have them somewhere else other than our SQL server. They need to be on a network share, a backup device somewhere. As soon as we place those backup files somewhere else, we don’t have that control anymore.
Sys-engineers, sysadmins, other folks that have access to those volumes could easily grab a BAK file, install SQL server express, SQL server developer edition. Restore the database, and they have access to all the goodies.
Having this encryption is very simple. We’ve had a SQL skills data video a few weeks ago, that includes the stepping through the process. You just need to create a server certificate, a master key, backup those two keys, then you can start backing up using compression. You can specify your algorithm from AES-256 and others.
It is very straight forward. You backup, you choose your encryption algorithm, you choose the certificate that you’re using it to encrypt it with, and it’s done. To restore to that server, is just like any other restorer. To restore to another server, you will need to have a master key, and then you will have to restore that certificate.
Restoring that certificate, you have to have the key you used to encrypt it. All that is documented in our video. You can restore those encrypted databases to another instance. It is really straight forward, it is super easy, just a couple little gadgets with the master key and the encryption cert. Having that encryption cert also backed up in stored, and keeping up the password that you used to encrypt it.
If you have those things in place, you can now encrypt with 2014 locally. You can encrypt and ship that off to your Azure. When it is up in Azure, that is really a nice thing if you’re a medium to small size shop, you don’t have a dedicated DR site, to get your backups offsite. To satisfy that concern and worry that if something happened to your data server, or someone stole your hardware or things like that, you have data you can recover from.
If you leverage Azure DR site, now you have your backups there. You can spin out a virtual machine with SQL server on it, that volume restore your databases. It makes for really good DR posture.
Carlos: One thing about those encryption keys, make sure you back them up, you don’t want to lose those. [laughs] Another advantage, which was available, I think it started 2012, I think there were some enhancements in 2014, and that is backing up to Microsoft Azure storage.You referenced that a little bit. This is one of the areas for those who are a little bit timid to take that jump into the cloud. Microsoft has definitely made a push into the cloud, they want you to be there.
For some folks it may be a little overwhelming. I think making your backup’s too, as your storage, fairly straightforward, fairly economical, and fairly straightforward.
Tim: Yeah, the storage is cheap, the bandwidth you pretty much have it. I really advocate for restoring the production database in Azure, with the idea of restoring from Azure back to your local data center if you have a problem. You’re still limited with your bandwidth but…
Carlos: Explain it a bit more.
Tim: Inexpensive off-site storage to meet regulatory and audit requirements, it is a simple, easy, inexpensive solution. Also like I have mentioned, if you want Azure as your potential DR site, you can spin off these VMs with VPUs low memory. Get you storage there, again storage is the cheapest portion of it. You can have store process in place, where it is taking your backups up there, basically like a poor man’s log shipping.You can have these databases ready to go. If you need them, and you’re setting up your app servers, and web servers and things as well, then you can turn up and choose instead of the one CPU six GB RAM machine, you’ve got eight CPU nine-something GB RAM, host as a server. You start paying for it when you increase the CPU in memory.
No real sense into paying the money for a high end server, just to be doing log shipping, or restore validations and things. Just turn it up when you need it, and when you get to your site backup, fell back over then dial it back down. I know several corporations that are leveraging that. It is an inexpensive insurance policy, rather than paying for this big iron sitting somewhere that’s not being used.
Azure is a really great option.
Carlos: Yeah, and you mentioned actually having that DR if you will, if it’s ready to rock and roll. I was referring to something even as simple as having your backups there, particularly if you’re in a pinch where you may not be able to be keeping quite as many as you’d like. There are ways to get around that, just like we talked about earlier.We’re going with differentials and making those backups a little bit smaller. Should you want to keep certain number of your full backups around, restoring an Azure can make a lot of sense.
Tim: Archive data up there, we’ve seen with SQL server 2016, the little consumer preview that came out with stretch databases. You can store archive information in Azure. It’s really starting to take off. Like you said, keeping older databases there, getting them off prim, in the off chance that you may need it, very viable option.I would still rather keep, if it’s dated, I think I would need for a production restore. I’m running tight on space, I’d be looking at really inexpensive onsite UT3 AES data, or something. Even a supposed spinning 5,400 RPM disc should still be faster than my connection up to the Internet. Pulling back gigs and gigs of data. It’s a really good safety net for sure.
Carlos: We have a couple questions we want to ask you. This is going to wrap up our back up and recovery section. As a reminder, Tim’s book is called “SQL Server 2014 Backup and Recovery Techniques.” It’s available out there on Amazon. We are going to give away five copies of the book. If you’d like your shot at winning the book, go to sqldatapartners.com/podcast.Under the information from today’s session, you can register. We will be announcing those winners on August 31st of 2015. Again, that is sqldatapartners.com/podcast, and you can register under today’s program information.
Tim, what’s your favorite SQL tool? This could be a paid tool or a free tool. What tool do you like most and why do you enjoy using it?
Tim: I would probably have to go with two of them. First is the SQL central plan explorer.
Carlos: That is a good one.
Tim: Just for diving into execution plans, from a production DVA, tuning, troubleshooting, that’s my go to must-have. The other is when I’m digging in routing a lot of T-SQL, is SQL prompt from Red-Gate. That tool saves me so much time from having to type, and formatting SQL, especially when I’m doing webcast or use a group presentation of things, and I’m writing new scripts.I could just use the SQL prompt feature to format my codes, so it looks like I’m really good at just sticking to standards, best practices, when I really just write really sloppy code.
Carlos: [laughs] It can make you look good. There you go. I think that the plan explorer, SQL entry plan explorer comes in two varieties. There is actually a free version available. Lets you look at one execution plan at a time. I don’t know that I can name all the features. I know one of them is if you want to open multiple execution plans, you need to go pro, and there is a small fee for that.
Tim: We joke about the free version, the regular plan explorer. It’s so free, they don’t even ask you for your email address.
Tim: You go to SQL sentry, click on “download the free tools,” It is straight up download. There is no login, no registration. There’s just click the link, and you’ve got it.
Carlos: There you go. That is a good one. The other one is the SQL prompt from Red-Gate, that is a paid tool variety. Correct?
Tim: Yeah, it is. It is one of the tools that I have always joke about. If I wasn’t a friend of Red-Gate, and get it through that program, and didn’t work for a company that would buy it, I would buy it myself. It’s that good of a tool.
Carlos: It is a good one.
Tim: It is relatively inexpensive, a couple of hundred bucks. The product median increase from having that is absolutely well worth it.
Carlos: I’ve been toying around with that same vein with the apex SQL tool, the apex SQL refactor. They have another tool very similar to the SQL prompt in that same vein as tools.Ultimately, as the folks listening to this podcast now, I didn’t go to school to be a DBA, wasn’t my intention. Lo and behold, here I am. What is your favorite DBA story? What is it that you enjoy about being a DBA? What keeps you here?
Tim: The SQL community is huge. Having the friends in the industry, it’s really tight knit, in your family. That definitely helps, keep you in the business and doing the data stuff rather than changing careers. For me, I just like fixing problems. I like making SQL Server run faster. I like the ability to educate others.The fact that I’m not really aware of any school that you can go to and come out and be a DBA. You can go get your computer science degree, you can get your information systems degree, you can get your engineering degree, but there’s not really a school for DBAs.
Most that are doing what we do are those accidental DBAs. They were either a sys-engineer, a sysadmin, an application developer that was told, “Oh, by the way, you now own the SQL Server instance.”
Tim: You’ve been voluntold. Very few actually volunteer for the job, we get stuck with it. Being able to dedicate my time just to DBA and being able to help clients all over the world, and stepping in and saying, “Here’s things that we have found that you can do improve your instance,” because they’re running a default install. That’s just so much fun.Now we’re on an every two year cycle with a new version of SQL Server. It’s just getting better and better and new features and technologies to work with. It’s just an incredible thing. If I was to have to sit down and write code for an application, and that’s all I’m doing, I think I would get really bored with it.
Carlos: Interesting that one that you mentioned, the community, you’re right. The SQL Server community does have a great environment, a great core group of people, very open, and that diversity in the database field. I agree with you there. The two-year cycle, I guess, is a nice thing because new technology is coming out. A little scary, too, right?[laughter]
Tim: Absolutely. As soon as you feel like, “I’m finally starting to learn 2014,” “Hey, guess what? Here’s 2016.”
Carlos: That’s right.
Tim: A whole bunch of new stuff. Just when I have clients getting on 2012, now it’s like, “You need to be looking for 2014.” “Well, we’ll wait for 2016.” It’s extremely challenging. I miss the days where SQL Server 2000 lasted for…it’s still out and running now 15 years later. It was the work horse, the tried and true.2005 came out five years later. Then 2008 three years later. It felt pretty good with that three-year cycle. Two years is a bit challenging.
Carlos: They keep us on our toes at Microsoft.
Tim: Absolutely. I don’t know if they’ll follow a Windows 10, where it’s the last version. We’ll have 2016, and then we’ll just have massive service packs to make us feel better. Who knows?
Carlos: It’ll be interesting to see what that projection is. Last question. If you could have one superhero power, what would it be and why would you want it?
Tim: Superhero power…I think flying would be cool.
Carlos: [laughs] You chose mine, too. Are you a pilot? Did I see that?
Tim: I’m not, but if I could fly, then I wouldn’t have to get on an airplane. I could clean my gutters much more easily.
Carlos: All the uses, right? The honey-do list could get done much more quickly.
Tim: Just be Superman for real. Have the laser beams, see through walls, be able to fly, superhuman strength.
Carlos: We only gave you one superhero power. [laughs]
Tim: Out of those, the flying, I can buy a tractor for the superhuman strength.
Carlos: Very good. Thanks again, everyone, for tuning into the “SQL Data Partner” podcast. Tim, thanks for coming and joining us. It was great fun. I do appreciate it.
Tim: Thanks for having me. This was a lot of fun.
Carlos: That’ll do it for our show today. Thanks again for tuning in. Again, if you’re interested in winning a copy of Tim’s book, you can do so at sqldatapartners.com/podcast. At the bottom there, there is a registration for that book. The raffle will be on August 31st of 2015.Also, wanted to remind you of the offer from the SQL Skills folks for a free 30-day trial of SQL Skills content on Pluralsight. What you need to do to get that is you can email Paul Randal at paul @ sqlskills.com with the subject line “User Group Pluralsight Code,” and you will get the free 30-day trial. No strings attached, to all of the content from the SQL Skills folks.
There is about 150 hours of training on there, and so we hope that you’ll find that useful.[background music]
Carlos: Thanks for tuning in today, and I’ll see you on the SQL trail.