We have big data, and we want to save money. We want to move the older data down to some kind of archives that will cost us less.

I ask exactly one followup question:

Are you willing to modify the app that queries the data, or do you want the users to still be able to query the old data in their existing screens and reports?

If you’re willing to modify the app – like having an “archive reports” section for when users wanna query the older data, and use a different connection string pointing to an archival server, then great! It’s easy. Just put together a cheap 4-core SQL Server Standard Edition VM with crappy storage, and archive the data over to that. I have all kinds of tips to make that easier and cheaper.

But you don’t wanna modify the app.

You want to still be able to use the same screens and same reports. When you wanna run an all-history trending report, or find Acme’s order from 2002, you wanna use the exact same app and same reports.

You just want old data to magically be cheaper.

Table partitioning doesn’t do that. In theory, table partitioning lets you put new data on expensive/fast storage, and old data on cheap/slow storage. In practice:

• None of your storage connected to production servers is really that cheap.
• The data’s still inside the same database, so maintenance jobs like backups, high availability, corruption checking, and statistics updates are still time-intensive, and development servers are still huge.
• As data ages, and you wanna move it to cheap/slow storage, that’s a row-by-row logged & locked operation. The bigger your data is, the uglier of a process this is.
• The index designs have to be the same across all partitions. (You can do some fancy stuff with filtered indexes, but it’s a giant pain in the keister.)
• Table partitioning comes with a lot of design & implementation considerations, basically requiring rewriting the whole table and all its indexes.
• Every single one of your queries have to have WHERE clause filters on the partitioning column (which is inevitably a date range in these scenarios). If not, query performance gets worse because SQL Server can’t eliminate the old partitions.

Partitioned views kinda do it – but not really. In theory, they suffer from most of the above disadvantages too, with a couple of interesting exceptions:

• The data can be in different databases, so old stuff can be in an Archive database. You can seal that one as read-only when you’re not transferring data over from the live database. That makes backups, high availability, corruption checking, and statistics updates faster.
• Indexes can easily be wildly different for archives – like columnstore, or tons of indexes without sacrificing the live data’s write performance.

But the rest of partitioning’s disadvantages still apply, and you’ll wanna review those, because they usually end up as showstoppers.

Linked servers kinda do it – but not really either. In theory, you can put the old data on a cheap Standard Edition VM with less frequent backups, less high availability, and maybe even no disaster recovery. You replace the old production table’s name with a view that does a union-all across the current live data, plus a reference to the linked server’s archival data. In practice:

• If any query doesn’t have the partitioning column (like date) as part of its where clause, performance can be spectacularly bad, because SQL Server doesn’t always filter data over on the archival server. Sometimes it brings all of the archive data across the network wire over to the production server, and does the filtering there.
• Linked server data isn’t cached. When multiple users run multiple queries, even simultaneously, their query data isn’t shared or cached. It’s fetched from the archive server over the network into your production server’s memory, taking up space every time, replacing stuff that your production server used to cache.
• As data ages, and you wanna move it to cheap/slow storage, this is really slow since we’re moving across servers now, and writing to a really slow destination.

I’ve seen a lot of people try to use the linked server approach. They test a bunch of queries, work to make sure they don’t touch the linked server, and then go live – only to get a horrible surprise about the queries they didn’t test. It only takes a handful of frequently-called queries accidentally fetching data from the linked archive server to bring your production box to its knees.

Try the linked server approach first.

If you still wanna try archival after reading the above limitations, then try the linked server approach. It’s the one that will produce the most business value if you can get it to work. Here’s how to approach it conceptually:

• Set up a development environment with two servers: “production” and “archive”
• Do the development work to replace the production tables with views that union-all across your current production data, plus the archival data on the linked server
• Archive data over to the archival server, taking it out of production – you wanna accurately simulate the data distribution that you’re going to see in the real-life environment
• Capture a trace of activity in real-life production for a business day
• Set up a trace of activity on the development “archive” server
• Replay the production trace on the development “production” server
• Stop the trace of activity on the development “archive” server, and review its contents. Those are the queries that are going to hit your development “archive” server in real life. If you’ve done your work correctly, few queries will be hitting that server – because if they are, then you’re frequently querying archive data, and what was the point of all this?

It’s a lot of work – but it sure is better than yolo’ing your way through this and discovering how bad it’s going to be after you’ve already archived in production, and you have to move it all back.

How to Tune Indexes Faster – May 12 & 18 – Brent Ozar will show you how to use the latest features of sp_BlitzIndex to rapidly improve performance on an existing database. He’ll show you how to figure out quickly if you’ve got too many indexes or not enough, and then demonstrate how to get advice from AI on how to consolidate them in seconds.

How to Tune Queries Faster – May 14 & 20 – Brent Ozar will show you how to use the latest features of sp_BlitzCache to rapidly improve performance on an existing server. He’ll show you how to find your server’s top bottleneck, find the queries causing that bottleneck, and then demonstrate how to get advice from AI on how to tune them in seconds.

How to Gather Diagnostics Faster – June 2 – Whether you’re a consultant, a full time DBA at a big company, or just a lone sysadmin in a hurry, sometimes you need to gather a lot of diagnostic data, quickly, while SQL Server or Azure SQL DB is having a performance emergency. You wanna grab all this without running a bunch of time-consuming commands, and you want to be able to review the data later, at your leisure, to understand what was happening on the server. In this webcast, Brent Ozar will show you two ways to do it: by setting up Agent jobs to run the First Responder Scripts, and by using the Consultant Toolkit.

How to Get Daily Database Advice – June 4 – You’ve got a lot on your plate, and SQL Server is just one of the things trying to get your attention. You’re looking for a way to have someone else keep an eye on your servers to alert you about really important tasks that can’t wait. You’ve tried third party monitoring apps, but they’re constantly crying wolf, spamming your email in-box with warnings about things that don’t really matter. In this webcast, we’ll show you how to install the totally free SQL ConstantCare app, the kinds of things it warns you about, and explain our monitoring philosophy. If you’d like to get performance advice with the paid version, we’ll show you how that works, too.

All of the meetings start at the same time: 1PM Eastern, 10AM Pacific. If you can’t make the classes, we’ll send out recording links after the classes finish. The classes will be lecture-only – you don’t have to follow along on your machine, and you don’t have to install or configure anything.

Register here on the blog. See you in class!

If your company is hiring, leave a comment. The rules:

• Your comment must include the job title, and either a link to the full job description, or the text of it.
• An email address to send resumes, or a link to the application process – if I were you, I’d put an email address because you may want to know that applicants are readers here, because they might be more qualified than the applicants you regularly get.
• Please state the location and include REMOTE and/or VISA when that sort of candidate is welcome. When remote work is not an option, include ONSITE.
• Please only post if you personally are part of the hiring company—no recruiting firms or job boards. Only one post per company. If it isn’t a household name, please explain what your company does.
• It has to be a data-related job.

If your comment isn’t relevant or smells fishy, I’ll delete it. If you have questions about why your comment got deleted, or how to maximize the effectiveness of your comment, contact me.

Each month, I publish a new post in the Who’s Hiring category here so y’all can get the latest opportunities.

One of the most powerful ways to do it – especially if you’re stuck with code you can’t change, like code generated by Entity Framework or other ORMs – is to tune indexes. It sounds odd, but by changing the shape of indexes, you can reshape execution plans – either encouraging SQL Server to take the paths you want, or removing paths that result in inefficient plans.

On May 21, I’m doing a free webcast with Idera on How Index Tuning Reduces the Blast Radius. In there, I’ll also be demoing sp_BlitzIndex’s new AI features, which can help you tune indexes across more tables, faster, using whatever LLM you like, hosted or local.

See you there!