How to manage data on Cloud VMs: A conversation

We published another episode of “VM End to End,” which is a series of curated conversations between a “VM skeptic” and a “VM enthusiast”. Every episode, join Brian, Carter, and a special guest as they explore why VMs are some of Google’s most trusted and reliable offerings, and how VMs benefit companies operating at scale in the cloud. Here is a transcript of the episode:

Carter Morgan: Welcome to another episode of VM End to End, a show where we have a VM skeptic, and a VM enthusiast come together hash out all things cloud, computer, Compute related. Brian, thank you so much for being here today. I've got some questions for you.

Brian Dorsey: Hello. Hello. I'm excited today. Databases and VMs, it's my happy place. And because of that, I wanted to bring in Gabe Weiss, one of our colleagues and an expert in databases. Hi, Gabe.

Gabe Weiss: Hey everybody.

Carter: Yes. Happy to get into it. I guess I'm just going to dive right in if that's cool. I want to know when it comes to data on the cloud with Compute and whatnot, why not just let a managed service run it for me magically? Why would I want to run the database level at the Compute level? Why does that make sense?

Gabe: I mean, why not indeed, right? The deferral answer that we always give: it depends. There is nothing wrong with running a database on a VM in the cloud. Let's be clear right away. Cloud SQL, our relational database managed offering, runs in a VM. So whether you're running in your VM or our VM, you're still running in a VM. The differences and why you might want to run your own come down to one of the requirements. If you have a legacy system, you've got an application you've been running forever, and it has a set of requirements, then a managed database might not satisfy those. One of the things with managed databases is we manage them for you, which means we restrict what you can do with them. Right?

Brian: Okay. So let's be specific. What are the kinds of things that might push you into running it yourself on a VM?

Gabe: Right. Like if you've got this legacy system and an application that you've been running for a couple of decades, it builds up over time. You add to it when you have requirements. So an excellent concrete example of this is SQL Server. One of the big things with SQL Servers is stored procedures. You've got these methods that run on the database itself. Stored procedures have specific requirements sometimes like maybe they require a superuser. If you're running on a managed database, we don't give you a proper superuser because we have to protect the database for you. We have an SLA, a Service Level Agreement, where we guarantee that the database will be up all the time for you, right? You hear some people talk about the nines. The nines are your sort of percent of how much uptime we guarantee. Cloud SQL, for example, our managed offering is 99.95% available, three and a half nines, which means it's like 20 minutes of downtime a month or something like that.

Gabe: With these extensions or the stored procedures, we have to guard that uptime, which means we don't give you everything. So if you've got a system that requires that, a managed system might not work for you.

Carter: Okay. That makes a lot of sense. And I appreciate you for bringing in that perspective. So what about someone who doesn't have their database in the cloud yet, but they're thinking about switching over. What's the process for that?

Gabe: Right. You're talking about what we call the Greenfield, where a new application, a new company is thinking where should I put my stuff? Everything's moving to the cloud. Let's be clear that what we see in the industry, right, is everyone wants their data in the cloud for a whole host of reasons. You want to run analytics on your data, you've got a global situation where you've got an application that needs global access, you're going to be in the cloud. So I always say, if you haven't got something already, start with a managed service, right? You don't want to set up all of the boilerplate to run your database like hardware management, operating system, and security updates, managing the database health itself, and ensuring that the machine is healthy. With a managed service, all of that's handled for you. You don't have to do any of that stuff. So if you're starting fresh, absolutely start with a managed service.

Brian: Well, you just said it right there. I love it when you're starting a new project, and you get to set everything up from scratch, but a lot of us are dealing with a lot of existing software. What do you do in that scenario?

Gabe: So if you've got one of those legacy systems, like I said, like the 20-year application you're working with, there are a few different ways you could do it. And, if your systems are all tightly integrated, and you've got it running in like a tight cluster of machines or something, you can just do the lift and shift, right? Just take the entire thing and throw it up in a VM. And now, instead of running in your closet, you're running in our closet, but we handle all of the machine health for you. So, that's absolutely a valid strategy. If you need to get out of where you're at and get to the cloud first, just put everything in a VM and get going.

Gabe: You do want to split the data out, though, right? So strategically speaking, if your requirements get to the cloud first, do that lift and shift up, then start working on splitting off your data. There are cases, though, where that might not be the case. If you've got an application that, for compliance reasons or other, you have to host the application locally, but your data has grown beyond what you can handle, and you need to get the data out. Splitting the data out is fine too, but there are some problems there, right? So the internet, the internet's a problem, right?

Gabe: So if you are in a situation where you want to keep your application, but you need to move the data, that's fine, but you should be aware that you'll have to handle that on the application side. So great connection to your data center, maybe you've got a 30 millisecond ping time round trip, right? Your ping to your database is less than a millisecond for a well-tuned relational database if you're running locally. So all of a sudden, it's taking 30x as long. But it's not even that fast, because let's say, for example, you're only allowing SSL connections to your database; the TLS handshake to establish that connection takes two round trips back and forth to establish the handshake before you get to the data. So now it's not 30 milliseconds to get your answer; it's 100 milliseconds to get your answer.

Brian: And then a bunch of apps make a bunch of queries in a row. And so that just like every one of those extends it out. Like, what do you do about that?

Gabe: I mean, you can't fix the internet. But the tons of connections and queries you talked about? Because they're used to a quick [local] turnaround time, some applications will just spin up a connection, ask their question, and shut down the connection. But if you do that a couple of hundred times and your round trip is now not so quick, that's going to wreck your response of UI. So instead, the first thing that I tell people is that the first thing you want to do is client-side connection pooling. Most libraries that you connect with, so there's like for Python, we recommend SQLAlchemy is a great library to use for connecting to your relational databases.

Gabe: There are objects that you use to create the connection. And there's a connection object. And there's a pool object. That pool is a connection pool that hands you a connection to the database and holds it open for however long you specify. That means if you're making these hundreds of queries every second, that's great. It'll establish one connection, run all your queries until there's some idle time, then shut it down. So that cuts down your round trip time because the round trip time for that security is only on that initial connection. And then after that, you're good to go.

Carter: Right. I like that you said earlier that if you're starting Greenfield, choose the managed service. That's the easy move. And then I can see how it gets a lot more complicated depending on your needs. The one thing I think that shouldn't be complicated is actually moving to the cloud. And so are there tools or anything to help make that a smoother process?

Gabe: As you said, it shouldn't be, and there are absolutely cases where it is, right? There are totally cases where just moving the data is hard. The boilerplate way to do this in many cases is what they call the export/import, which is you go to your database, you hit a button, and it dumps all of your data to this giant file. And then, on the new instance, you hit import, and it sucks it all into the new one. And you're good to go. That's like the old-school, classic way that you do this stuff. But if you've got terabytes of data, it's kind of tough to do that. And while you're doing the import, well, now what? Unless you shut everyone down and say, okay, no one touches the database. Google's doing the migration. Now you've got data that continues to come in while you're importing. So the gap between when you've hit the export button and when you do the import, there's this data that you're missing, that you have to catch up still.

Gabe: There are other ways you can do it. You can set up replication between two servers, which means the current database you're moving away from is set up as the primary. You set up a replica, and it streams your data across the pipe. It's a pain to set up; There's a bunch of little knobs. To make that easier, we've created a service. It's called Database Migration Service, DMS, which basically handles the move. It still uses the database native ways to do it. For example, on PostgreSQL, we use a different underlying technology to do migrations; it will handle all the connections. You define a source object or source database in the UI, and then it creates a cloud SQL instance for you. And then it moves it.

Gabe: Now, keeping in mind, this is only for a managed service. So if you're moving to a managed service, this is the tool to do it. If you want to run your own in a VM, you're kind of on your own because it's not managed for you. So you have to do all of these pieces. You can still do it. There are still ways to set up external replicas and external primary instances to do it, but you're on your own. So DMS is that easy way to move to a managed service.

Brian: Okay. That's super cool because I have tried to schedule a window where you can shut everything down with all the people involved and then hope everything works right, and you don't have to revert or do everything. And that's hard. So it's nice to know that there are some tools to make that easier now.

Carter: All right, to summarize what we learned today, just to recap everything you came and taught, I still want to hold up that my managed databases can and should be used in a lot of instances, but there are other instances where it doesn't make sense to use them, where there's some capability that you need that's not provided yet. Or maybe just the way your system is set up. But if you're trying to transfer over, then there's Database Management System, which makes it a lot smoother of a process. Did I get that?

Gabe: Exactly. And it all comes down to your requirements, right? As you said, there is nothing wrong with running a database in a VM, as long as you're comfortable managing it. If you don't want to worry about that, managed services are great.

Brian: So, if people want to dig in and learn more about this, do you have any recommendations?

Gabe: Oh yeah. The DMS, the Database Migration Service documentation on our site is a great place to start. There are a lot of good links there. It talks a lot about the strategies that I talked about. And it will lead you to get comfortable with the idea of moving your data around. For other stuff, there's a ton of blog posts. We got a lot of blog material on our GCP blog around managed service versus running your own. What others? Troubleshooting. There are a couple of ones that are like troubleshooting. I wrote one on connectivity. Networking is hard just in general, so I have a good post on networking between different services.

^{Special thanks to Gabe Weiss, Senior Developer Advocate at Google, for being this episode's guest!}