… is to use a SQL Server where a robot does it for you!
Let’s take a look at how you can do this in AmazonRDS’s hosted SQL Server these days.
Normally, restoring transaction logs is super tedious.
You’ve got to restore all the files in the right order, for every single database. Even if you script it, if you’ve got a bunch of databases you need to restore, it’s a giant pain. AmazonRDS has essentially done all the scripting for you.
Amazon RDS makes sure it can always restore your SQL Server to a point in time
When you set up an RDS SQL Server instance, it asks you how long you’d like to keep backups, and if you have a preferred full backup window. And that’s all it asks. Transaction log backups are taken every 5 minutes to support point in time restores, no matter what you want.
So I wondered, what happens if someone creates a database in the wrong recovery model by accident? Does this break the transaction log backups?
Nope! I created a database named DB1_CreatedInSIMPLE using the SIMPLE recovery model. Check out what showed up in the RDS logs right afterward:
How Recently Can I Restore?
You can see log backups in the “Recent Events & Logs” above, or you can just look at the latest restore time for your instance. Since transaction log backups are every five minutes, you can’t always restore to one minute ago.
OK, Let’s Restore This Thing! How Easy Is it?
I wasn’t lying when I said it was easy. You just highlight your instance in the console and select “Restore to Point in Time” under instance actions.
This is going to create a whole new instance using the backups they’ve been taking. You get to pick the time you want to restore from. This will vary depending on how long the instance has existed, and what your backup window is set at. (Mine’s at 7 days, it can go up to 35 days.)
You also get to pick the instance name you’re going to restore to, along with all sorts of properties and security settings:
Setting up the restored instance isn’t immediate. RDS has to set up a new Windows instance and apply a whole lot of your backups, and that takes time.
After you get all your settings done, you click Launch to get your new RDS instance in motion to be created:
Our instance is Cooking
The instance goes through multiple phases. It automatically backs up the instance as part of the process if you’ve got backups enabled, just like any other instance.
Once that completes, I can connect
Here I am, connected to my original and restored instances via SSMS on my desktop!
SQL Server in RDS Isn’t Perfect, But It Does a Lot For You
I am not a founding member of Team Cloud. But I meet a lot of people who struggle making sure that backups are being taken properly and that they can restore. They’re frequently teams of developers who don’t want to learn to deal with backups and restore. They also want things like database mirroring without having to learn it, and RDS handles that for them, too.
This isn’t for everyone. RDS limits the number of databases you can have on an instance. It doesn’t do all maintenance for you — you have to set up your own CHECKDB jobs, for instance, which you can do in the SQL Server Agent. You don’t get tools like Database Mail inside the SQL Server, or the ability to see the SQL Server Error log via SSMS (don’t worry, you can get to that and other logs in the AWS Console). You can’t use every version of SQL Server out there (2008R2 and 2012 only, currently). You can’t do Availability Groups in Amazon RDS, either, just database mirroring. (I’m a big fan of mirroring, so I’m not all that sad about that.)
Putting it All Together
Database as a service is a pretty compelling option these days. It doesn’t take away the role of the DBA, and it doesn’t handle everything for you — but the flip side of that is that you get a lot of flexibility and customization.
And dang, I love the ease of restoring that instance to a point in time.
Brent says: what I love about the cloud is that it takes away the parts of the DBA job that I never really liked in the first place.
Jeremiah says: I’m with Brent – I love getting rid of the parts of the job I never liked. Cloud lets me do that. And RDS lets me ease up management on a certain class of servers even more
Erik says: I, for one, welcome our new Robot DBA overlords.
28 Comments. Leave new
I’m having trouble seeing how this is any easier than using a script that creates the T-sql for the restore, except your pushing different buttons. And if it still takes the same amount of time, what are you actually gaining by this “hands-off” approach? Even so…not everyone is using this type of service, so there’s that. Whether you like them or not, being able to manually backup, access, and restore as well as setting it all up are still skills that need to stay current.
That’s my $.02…now I’m broke.
Let’s see what’s different: The person doesn’t have to fumble around looking at different scripts from the internet, debate internally about whether or not enabling xp_cmdshell to run the query against msdb is a grievous sin or not, settle on a PowerShell script and then find it doesn’t work with their version of PowerShell, figure out that their script generated a lot of extra commands, and then accidentally restore over the production database.
There are a lot of DBAs out there who are really resistant to running transaction log backups more often than every 30 minutes because managing and restoring all those log files is such a pain. And there’s a lot of companies who don’t even *have* DBAs, and restoring logs is a deep, dark, mystery. Managed systems like this are a huge feature for all those folks.
Ok Ok, I see the differences. I guess I’m just so used to managing all of those aspects I don’t think about it. Fumbling shouldn’t be an issue – design it, run it, test, automate, test test test, and check it in. I’ve only recently learned PowerShell and that would be a good way around xp_cmdshell, but in the past I just checked whether or not it was enabled and set it back to where it was before I started.
But you make a very good point on the accidental or lack of DBA for smaller shops. These should be the first tasks that get nailed down and once they are, except for when we actually need the backups – oopsies or DR testing – I don’t think much of it. But doing it, especially for a non-DBA type can be a daunting task.
Go figure…this article comes out, a bunch of uppity DBAs dis it and then I run across a question on serverFault.com regarding an easy way to configure backup and restore operations for a CAD shop without a dedicated DBA. Life is funny like that sometimes.
Of course, I mentioned this article and your points have all been hammered home.
Can I assume that AmazonRDS does not support tail-log backup.
TM
Correct – the only way you can “run” a backup is the five minute schedule. To avoid more data loss than that, the other option is to use mirroring (realize it’s not the same thing as a backup, totally agree).
Neat feature but taking away database email for instance is a GIGANTIC big deal for our company. I definitely can see value though. Does M$ have anything similar in Azure?
Azure SQL Database (Azure’s relational database service) has a Point in Time Restore capability. For Azure SQL Databases backups are automatically taken, and with these backups you can restore to any point in time with the database’s retention period. In addition to this, every hour a backup is geo-replicated to another region, so in the event of a regional disaster, you will have at most a 1 hour old backup to recover from.
There is a blog post on Azure SQL Database Point in Time Restore here: http://azure.microsoft.com/blog/2014/10/01/azure-sql-database-point-in-time-restore/
There is also a post on Geo-Restore here: http://azure.microsoft.com/blog/2014/09/13/azure-sql-database-geo-restore/
Neat! I built a thing that can restore databases backed up to Azure Blob Storage, to the very latest backup you have, only knowing the database name.
Not quite the same thing, but also leveraging cloud technology to make our lives easier.
I love living in the future!
We have an app in C#.Net that automates all our backups (full and log) and restores them so they are continuously done and tested… the app does a bunch else too. I’m going to add this little feature into the app to restore to a point in time… the best thing about this post is “the concept”. Of course the concept does not have to be on an instance of a virtual machine… just a on a separate real server.
Hi Richard,
Is it possible for you to share details on this app. I have the same need. I appreciate your help.
I have heard that with great power comes great responsibility or so I am told. One of the great responsibilities that a DBA has is ensuring that the data and the databases are recoverable based on the recovery point objective (RPO) of the organization. Just because this is a mundane task (backups and restores have always been in this realm) does not make them any less important. I have dealt with 3rd party vendors in my long and illustrious career as a Microsoft SQL server DBA (all the way back to the SQL server 6.5 days) and I have learned that you can trust them about as far as you can throw them when it comes to service level agreements (SLA’s). Has anyone stopped to read the SLA for this? Has it been properly tested, say at 3:00 AM on a Sunday morning? When it comes to database continuity in the face of a disaster (caused by a badly written SQL script or the world around us) I would rather ‘roll my own’ than depend on a 3rd party vendor. If it is that important, either buy the knowledge (hire someone you can trust), rent it (bring in a consultant who actually knows what they are doing) or learn it yourself. Get you hands dirty for change. Have we become so dependent on this nebulous thing called the ‘cloud’ that we are willing to give up the training, knowledge and expertise that make us who we are. I say Bah Humbug to bringing on the Robots and let us do what we studied, trained and worked our behinds off for, it may not be pretty but it is more than worth it in the end.
Times are changing fast. Not every company wants to hire a specialist who’s had the time to learn these things, and give them time to test them. And I’m actually OK with that, because technology is changing too.
Microsoft SQL server is not cheap, in the cloud or not in the cloud. I know I have built my share to 6 figure failover cluster servers with warm standby servers. If one is going to plunk down that amount of money for a software product and the infrastructure to support it, the amount of money plunked down should include a specialist who knows these things that you can trust. If an institution has gotten so far in their business that is requires a database engine of SQL servers magnitude then I believe they would also need to invest money and resources in personnel with the expertise to perform disaster recovery operations. That is what we are really talking about. I may be as old as my ideals but I am still reliable. That is what is needed in the market place today, stability and reliability, not quick and cheap. You get what you pay for, nothing is free.
I agree Terry. And I don’t think it would take a company long to realize. But to Kendra’s point, Database as a service is something that could be used by a DB savvy developer or SysAdmin. Neither is a substitute for a DBA and the lack of architectural, security, performance, and administrative skills will soon become apparent.
Then again, it might not. Maybe I’m a little Mom and Pap shop that wants to cut out Etsy and needs a simple inventory/pay system. This might work nicely for something like that. I could even see a new market niche developing for AWS jockeys that setup and service these to make it truly “hands off” for Mom and Pop.
Terry – let’s flip this around and think of it differently for a minute.
Let’s say a group of developers was trying to decide where to store their data for a brand new web app. Is it possible that Amazon RDS might open up the possibility of them using SQL Server, when they couldn’t have afforded it before?
Or is your belief that they shouldn’t even consider Microsoft SQL Server without a qualified DBA such as yourself holding their hand?
Brent,
To answer your question simply, no I don’t think anyone needs hand holding if they know what they are doing. This statement was in my first comment, “If it is that important, either buy the knowledge (hire someone you can trust), rent it (bring in a consultant who actually knows what they are doing) or learn it yourself”. Now being a DBA for the last couple of decades has led me to not let anyone that has the word ‘developer’ in their title anywhere near my databases or servers, but if they want to use Microsoft SQL server and are willing to ‘Learn’ how to use it properly, then more power to them. I am all about empowerment and letting people do as much as they can so long as they do not mess up to much. You have to break things sometimes while learning to use them. Also, it would teach the student the importance of backups. You yourself were ‘self-taught’ if I am not mistaken?
Terry – great, I think we’re closer to agreement than you might even be aware!
You wrote: “If it is that important, either buy the knowledge (hire someone you can trust), rent it…”
Is it possible that these developers might rent that knowledge from Amazon in the form of RDS?
Sure, times are changing fast. I’ve heard this ever since I started, in 1983. The times are always changing. But, some things come back around because it is discovered how “sound” “something given up” is, in the real world. With all of this current “fast change”, we see huge security breaks and loss of data, with only the biggest making the news. So many other crisis moments go unheard of. All of these, because there’s a petty bean-counting stunting of skill and infrastructure. Ignorance in the guise of “we are adopting the latest-greatest whiz-bang” (replace with “cloud-based whiz-bang”).
Sure, Amazon and Google and others are putting out a lot of really cool services. They do have their place in the “new world” (a world that recycles), but there yet remains core requirements best addressed in tried-and-true “conservative” ways.
That’s just my thought. Hard work never hurt anyone.
Cheers…. Tomas @NW7US
To be completely honest, I have never seen the product that is being touted here but if you call pressing a button ‘renting knowledge’ then so be it. This product could be better than the ball point pen or sliced bread but I do not like how it takes functionality away (no simple mode, no email capability). This in my opinion, it lessens your options and does not expand upon them. Also, those whom you rent your knowledge from (in the form of a human consultant who is worth his or her salt) the consultant should ensure that the ‘rented’ knowledge is past on to the buyer. I have been a consultant in my day and a regular Joe employee as well and live by the mantra of ‘Leave it a little better than you found it’. For any person or thing that you rent ‘knowledge or expertise’ from, a part of that should be some form of knowledge transfer. Teach a man to fish and all that…
Terry – gotcha. So if you’ve never seen a product, it’s tough for me to take your criticisms seriously.
Products can indeed have knowledge built in, such as built-in tools that take actions for you. They can even pass it on to you in the form of documentation and training videos, too.
Just as Kendra is passing knowledge on to you in the form of this blog post – even though she’s not physically there to teach you how to fish. See, it’s actually working! Keep up with that learnin’.
Just because I have never seen the product we are discussing does not mean that I cannot comment on what was described in this blog post, which is what I have limited my comments to in fact. Products do have built in knowledge in the form of help files and on-line documentation but the ‘student’ has to ‘read and absorb’ the built in ‘knowledge’ to learn from it. That is a one way street and if the product documentation is overly confusing (I have seen my share of overly confusing application documentation). Microsoft SQL server’s documentation stands as an example. I have found that most people are like electricity and take the shortest route to ground. What I am saying is that if you provide some one with a button that purports it will backup all there databases and you never need to worry about losing your data, I believe the user is somewhat gullible and the provider somewhat shady in their business practices. Another part of this is the loss of knowledge. Lets say we all rush to this product and we all start backing up our data with it (this is an extreme example, I am aware of that), then the only ones who know how to do this type of operation would be Amazon and they would have everyone’s data. Now with all of the computer hacking that has been going on lately, Amazon would be a big target, to big to leave alone. When the breach occurs then no one would trust their product and use would plummet. Everyone would be forced to store all their data under the mattresses at home because they have lost the knowledge of how to do it wisely and safely. I am trying to stay on point but when I am told that I am not being taken seriously, then this whole blog post should not be taken seriously until everyone goes and looks at the product and then we can reconvene and have an adequate discussion about it.
Terry – ahhh, I see the problem. You think that Kendra is writing about a backup product.
Amazon RDS is a different way of hosting databases. It’s not just backing up your transaction logs – it’s also setting up SQL Server, managing Windows, hardware, etc. It’s not like you install Amazon RDS on your database server – you install your databases IN Amazon RDS. They’re like a hosting provider.
So I hate to break your heart on this, but it’s even worse than you’d feared. If people use Amazon RDS, not only will we lose the knowledge of how to do backups, but also how to set up SQL Server, configure Windows, rack hardware, and procure it. We’ll be nothing more than babes in the woods, helpless rubes.
I’m with you! I hear your call to action, and let’s take it a step further. It’s not enough that we all remember exactly how to back up our logs (and of course, configure Windows, rack hardware, buy it, and so forth), but we should all never forget how to lay the transaction log down on disk. I propose that all database servers go back to handwritten log files so that we don’t lose this knowledge. Nay, not log files on disk – that would make us rely on hardware vendors. Let’s chisel these things down onto stone tablets, Terry. You and me – we can take back the world and make people remember this cherished knowledge!
Ahhh – and so it goes. Are we going to go all the way back to the Big Bang in this blog post? I am sure the Romans, Ancient Greeks, Aztecs, Mayans and Sumerians felt just like you do about the advent of new technology and that new is better than existing or old knowledge. When the knowledge is lost by the masses and only held by a certain few, then lost forever, who is at fault then? I guess we just need to agree to disagree and be done with it. I’ll chisel that onto my stone tablet after I ride my dinosaur back to my log house.
We have stopped talking about the actual product and have started arguing about what is right and wrong and whose technology is better. This is where I will end it, we are not doing anyone any good anyway.
Terry – yep, agreed. You can continue to change your own oil, and I’ll keep picking up a Zipcar when I need wheels. 😉
Don’t come knocking on my door when the zombie apocalypse starts 🙂 And BTW I pay to have my oil changed, but I still know how and can change it if I have too.
what if you have several databases on the RDS node but only want to restore one of the databases to a point-in-time? that looks like an instances restore.
Correct, you would want to restore the database to a new RDS instance, and then copy the data over that you want.