Moving away from the Cloud - T-SQL Tuesday #199

This month’s T-SQL Tuesday topic is an interesting one, Koen Verbeeck has posed the question of what would happen if you decided to move out of the cloud and return to on-premesis databases. What skills have we left behind that we would have to re-learn? What might we have taken for granted in the cloud that would be a lot more work on-prem?

I’m a performance tuner, I make servers go fast, there’s definitely things that I’d worry about with a cloud to ground migration.

Disks

Let’s use AWS EC2 as an example. What happens if I want a new disk added to my server? I just connect to the AWS Console, provision a disk, tell it what size I want and then attach it to the server. I can then monitor the io stall time on that disk and if it’s not performing as I need then I can just turn up either the throughput or the iops. I can play with these settings to balance the performance I need with the amount I’m willing to pay for that disk.

On-prem I don’t have as much flexibility. If I need a new disk for my SQL Server I first need to check if I’ve got it available in the SAN. If I don’t have space then I’ll need to go buy some new disks, wait for them to be delivered, then attach them to my server physically. It’s probably not even that simple, for a new drive on my server will one disk do? Do I have a RAID array and need more hard drives so that I can have redundancy in case of failure?

This is before talking about things like hardware lifecycles and availability, especially memory in this timeline where AI data centers are sending prices through the roof.

Disaster Recovery/High Availability

Something we all hope never happens but need to prepare for: What happens to my databases if a hurricane hits my office and takes the building down on top of my server? Do I have a replacement server that I could use instead?

Availability Groups are in a similar vein. Let’s say I have a 2 node AG for HA purposes, are those nodes both in the same physical location? If so, we run the risk of the situation above.

For all of this, we would need to consider how we off-site our backups. In the olden days we would send reels of tape through the post to a safe location. These days we would probably still use some cloud storage solution for both simplicity and cost, but if we have a multi terabyte database do we need to consider how long a full backup is going to take to upload every week?

Plus, I bet we’d all miss being able to point at an Azure status page showing red lights and say there’s nothing we can do about it and then going to make a cup of tea.

Maintenance

I’m not talking about things like indexes and CheckDB (you’re doing those now and you’d do them after too, right?), but for things like Windows and SQL Server Cumulative Updates. If you’re currently using a solution like Azure SQL DB that’s all just done for you. You’re just on the latest version of SQL Server and don’t have a Windows environment to worry about. Happy days! Moving back to on-prem and those will all be your concern again. It’s not a huge deal in most environments (especially if you have Availability Groups) however are you going to have somebody working on a Sunday morning at 2am during a maintenance window just to apply updates? Every single month?

We’d also have to consider when we need to upgrade to a new version of SQL Server (they don’t last forever). If you’re in the cloud, you can just provision a new server and do all of your testing and stuff there. You can even shut the server down when it’s not in use to save money. You don’t have that same option when it comes to on-prem. You need that physical hardware available and you’re paying for it regardless. You can still scale the instance size down/up in your virtualization environment (Hyper-V, VMware etc) but it just adds a level of complication.

Virtualization

Talking about Hyper-V and VMware, you’ll have to (re)learn that stuff real quick. How long has it been since you’ve had to work with SCSI controllers? What’s the best NUMA configuration for a SQL Server? Have we remembered to not soft provision our underlying hardware?

If it’s been a while, do you even remember the recommendations for server config? Have those recommendations changed over the last however many years since we’ve been in the cloud? Has the person since left the company that used to do all of this and we’re going to either need to train somebody or make a new hire so we don’t get bitten by unexpected issues? This is going to be a steep learning curve and essential if we go back to on-prem.

Application Vendors

Maybe you’re using a fancy cloud based application that your company has paid big bucks to implement. Does that support an on-prem database? Are you able to run the app on-prem at all? Even if your app can talk to an on-prem database, what’s the latency going to be like? Are users going to notice that the application is suddenly super slow and begin complaining about it?

The same goes for monitoring. Are you currently relying on the Azure Portal or the AWS console to give you performance insights and/or alerting? Those will be out of the window. If you’re lucky enough to have a monitoring solution in place then does that support on-premesis servers or are you going to need to rethink your approach to database monitoring and alerting?

Conclusion

It’s an interesting question for sure, but the main thing that would push people back to on-prem is going to ultimely be money. Being hosted in the Cloud can get expensive quick. I know people spending $20k/month for one server. However I think that the benefits to being in the cloud far outweigh the downsides of being up there.

Please, let me know your thoughts and if there’s something obvious I’ve missed from this list.