When the business decides it’s time to give up fixing the old server because it’s not fast enough or reliable enough, stop. The worst thing you can do is jump directly to the hardware vendor’s site, check a few boxes, and order a new shiny server that ends up with exactly the same problems as the last server.

1. Get the business’s RPO and RTO goals in writing. These two numbers determine whether you build a cluster, an AlwaysOn Availability Group, a log shipping backup, or a pair of tin cans connected with string. Learn what RPO and RTO are here.

2. Find the right SQL Server feature for your RPO/RTO goals. For example, if you’re not allowed to lose any data, and you need to fail over in less than 1 minute, then your options include failover clustered instances, AlwaysOn AGs with synchronous mirroring, or synchronous database mirroring. Use Page 2 of the HA/DR worksheet linked in the last post to figure that part out, or watch the HA/DR architecture modules of the Senior DBA Class.

3. Review the current server’s wait stats. To help figure out what kind of hardware you need for tomorrow, look at the pains you’re experiencing today. For example, if you’re suffering from PAGEIOLATCH% waits because you can’t cache enough data in RAM, the next server may need significantly more memory (or faster storage, or time spent tuning indexes & queries.) If you’re suffering from LCK% waits because the apps are holding locks on their side, though, you may not see relief no matter what kind of hardware you throw at it.

4. Benchmark your current hardware’s speed. I’ve written about how to check performance on a new SQL Server, but this is also really useful when you’re transitioning away from an old server. For example, take your backup & CHECKDB job runtimes, and the new server should be able to perform at least that quickly (if not more so, depending on what your bottleneck was in the above step, and which kind of bottleneck you’re trying to fix with the new server.)

5. Get the server’s proposed expiration date.  Servers are like milk cartons: both need an expiration date printed clearly on the outside. Ask the business users, “How long does this server need to last?” They usually say, “Forever,” and start laughing, but once the laughing stops, here’s how to handle the discussion.

6. Ask the developers, “What features are we adding during that time?” I don’t expect them to have specific line-by-line answers about which features they’re adding over the next 3 years, but I do expect them to have rough architecture ideas. Are we going to add some kind of big data analysis projects? Machine learning in the database? R, Python, or Java running inside it? Storing new kinds of data, like tracking every web site click?

7. Ask the business, “How much will load grow during that time?” Are we expecting to acquire our next several larger competitors, or maybe conquer sales in a new country? Or are we just expecting to tread water and only deal with incremental growth?

For all of the above questions, the more specific the answers are, the more precise your server sizing can be.

My new hardware

In reality, they’re very rarely specific.

And that’s fine!

But it just means that your server design needs to be more flexible: we need to design something that we expect will change rapidly over time as we learn more about the answers above. That’s one of the reasons why I really love building new environments as Availability Groups up in the cloud: it’s so much easier to add in new, more powerful replicas, join them to the cluster, and then get rid of the old ones. Your server design can leverage the flexibility of the cloud to handle new features or workloads, and you can throw cloud at performance issues.

In our last episode, we found out what your server was waiting on. In the closing remarks, I mentioned that you should find out which queries are causing the problem, and focus your mitigation efforts there. You might have circled back to the episode where we covered sp_BlitzCache, used a @SortOrder parameter that lines up with your top wait type, and…

Now you’re frustrated.

Because you’re looking at all these bad queries and thinking, “Isn’t there an easier way? Can’t I just throw hardware at this?”

Scaling out reads with replicas:
splitting queries between servers

When you want to copy the exact same production data to a second server to offload reporting queries, some of the costs are immediately obvious:

• Hardware and storage – even if you’re running it in a virtual machine, you need to account for the costs of say, 4 cores and 32GB RAM. Not only will you need storage for the databases, but you’ll also need to decide whether this server gets backed up, and copied to a disaster recovery data center.
• Software licensing – Standard Edition is ~$2k per core, and Enterprise Edition is ~$7k per core. Toss in Windows (especially now that it’s licensed per-core), your management/backup/antivirus tools, and your monitoring software.
• Project planning – you’ll need to design how to get the data from production to the reporting server, like with Always On Availability Groups, log shipping, or transactional replication.
• App modifications – the app running reporting queries will need a new connection string. Even with Always On Availability Groups, reads aren’t automatically offloaded to readable replicas – you have to use the connection string parameter ApplicationIntent = ReadOnly to tell SQL Server that you promise not to try to write anything. If you have a single app that does both reads and writes, and you only want to offload some of the queries, you’ll need to go through the code to switch those queries over to the new connection string.

But some of the costs come as cruel surprises:

• Adding a troubleshooting process – sooner or later, the data replication process will break. Depending on the method (AGs, log shipping, replication) and failure type, it’ll fail in different ways – maybe all of the data is old, maybe just some of it is, or maybe the reports aren’t accessible at all. You’ll want to list out the failure methods and explain what symptoms will look like. This helps business users recognize when their reports are wrong, and react appropriately. If you don’t do this step, then after the first failure, people are just always going to expect that there’s a bug in the report data.
• Prepare for failure – for each of those failure methods, decide how you’re going to react. For example, if AG replication breaks and reports are out of date, will you point reports at the primary until the problem is resolved, or will users just have to deal with unavailable reports while you troubleshoot or resync the replicas? If you don’t do this step, then you’re going to be winging it every time, and you’ll look unprepared while reports are wrong or down.
• Set realistic expectations for RPO and RTO – based on your process and preparation, make sure the business users understand how long their reports will be down when things break.
• Measure the overhead of replication – AGs and transactional replication can add performance slowdowns beyond what the reports used to cost. For example, if you were only running a few reports an hour, and only hitting a subset of the data, then suddenly replicating every individual delete/update/insert operation can have a huge overhead.
• Add monitoring – you need to start monitoring how far behind the reporting server is, and how performance is doing on both. Performance troubleshooting becomes a lot harder, too – for example, when you’re doing index tuning, you have to combine data across both the primary and the reporting servers in order to find the right mix of indexes across the board.

And at the end of all this, all you’ve got is a way to split out the read-only queries. Those might not even be your biggest problem – you might need to split up writes between servers, and that gets REALLY hard.

Scaling out writes with sharding:
splitting data between servers

Sharding is the technique of splitting your data between multiple shards. You have the same exact database structure on each server, but you split up customers into multiple shards – for example, maybe the US customers are on one shard, and the European customers are on another shard.

Sharding gets trickier when you have to keep data in sync between shards. For example, you might run a web site, and you want to keep all of the product and pricing data exactly the same across shards. You may also need to combine data back from all of the shards into one, like for reporting purposes.

Other techniques for splitting load across servers involve replication with multiple masters – multiple servers that can accept writes at any given time. If you’re interested in that topic, check out Many Masters, One Truth. (Spoiler: it’s a lot harder than it sounds.)

But the real bottom line with sharding, and the reason implementation is so hard, is that your application has to know which shard to query. If you’re connecting all of the apps to a single SQL Server and routing requests from there, you’re right back where you started: bottlenecked on a single SQL Server.

This whole post was a trick.

None of the scale methods are really easy. Whether you’re convincing the boss to buy more memory, taking an outage to upgrade SQL Server, or rewriting the app’s back end to accommodate sharding, you’re going to be putting in serious work.

That’s why I put so much emphasis on tuning indexes and queries. It’s hard to make your server 100x faster – but it’s really easy to make your database design and queries suddenly 100x more efficient. Fixing queries and tables and indexes is indeed hard work – but it’s totally worth it. The better you get at these techniques, the farther you can go in your career.