Product has been added to your cart.

Architecture

Office Hours: 7 Questions for You to Answer Edition

Last Updated March 2, 2022

Sometimes, I see questions and go, “You know, I’d rather let other people answer this.” And today, I’m gonna let you do it. My blog post this week about PollGab.com and about its data model brought out all kinds of interesting Office Hours questions. I’ve selected 7 of them that I don’t wanna answer –…

Meme Week: Queue in the Database

Last Updated February 19, 2022

When you have a hammer, everything looks like a nail. Unfortunately, a lot of people don’t even know how to use a hammer correctly. You really shouldn’t be doing queues in the database because: They’re a blocking nightmare, constantly locking rows in highly concurrent tables They’re a logging nightmare, constantly pouring data into the transaction…

Store Files in a File System, Not a Relational Database.

Last Updated July 1, 2021

Since the dawn of time, database vendors have called out to developers, “Store everything inside the database. You know you want to. Here, we’ll make it easy for you by adding data types like binary and features like filestream.” Ah, but this siren song leads you to your eventual doom. Users wanna store historical versions…

Brent Ozar

Free Webcast: Architecture Design for SQL Server High Availability & Disaster Recovery

Last Updated May 26, 2021

You’re building a new SQL Server, and you need to protect it. You want to learn when it’s right to use clustering, AlwaysOn Availability Groups, virtualization, or storage replication. You don’t have an unlimited budget, either, so you need to learn how SQL Server licensing impacts these choices. Or maybe you’re not doing it yet…

Where I See Databases Going in the Next 10 Years

Last Updated March 18, 2021

Architecture, SQL Server

This month, to mark the 20th anniversary of BrentOzar.com, I’m stepping back and looking at the big picture rather than blogging about database problems & solutions like I usually do. I’m going to generalize a little by decade here to keep things simple. In 1990-2000, databases were mostly tied to specific development languages. When you…

Want to Avoid Deployment Downtime? Replication Probably Isn’t the Answer.

Last Updated January 6, 2021

You’re tired of taking outages to deploy changes to your database schema. Wouldn’t it be cool to have two different SQL Servers so that you could: Point your customer traffic at ServerA Apply your deployment scripts to ServerB (which isn’t taking any customer traffic) Point your customer traffic over to the newly deployed ServerB Apply…

My new hardware

DBA Training Plan 20: Planning Your Next Server

Last Updated September 9, 2019

Architecture, DBA Training Plan

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…

I would like to scale out my Porsche collection.

DBA Training Plan 16: Is It Time to Scale Out?

Last Updated August 27, 2019

Architecture, DBA Training Plan

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…

Automatically Indexing in Azure SQL DB

Research Paper Week: Automatic Indexing in Azure SQL DB

Last Updated August 11, 2019

Architecture, Azure SQL DB, Indexing

Before I give you the link to the next research paper in this week’s series, I wanna give you a few questions to think about: If you had to build something to automatically add indexes to SQL Server databases, where would you start? What data would you use to make your decisions? How would you…

Brent Reading Book

4 Ways to Move Data Into a Reporting Server

Last Updated June 26, 2019

You’ve decided that you need to offload reports to a different SQL Server, and you’re willing to pay the costs. Here are the most common ways to move the data from production to a reporting server, arranged roughly from easiest-to-implement to hardest: 1. Log shipping. Log shipping is more of a technique than a feature:…

How to Implement In-Memory OLTP Quickly and Easily

Last Updated March 9, 2019

When you first hear about it, SQL Server’s In-Memory OLTP sounds like a new feature that’s hard to implement, requires a lot of schema changes, and takes planning and downtime. I’m here to tell you it’s simply not true, and you can do it in just 3 steps. Here’s how: Read Microsoft’s guidance on how…

SQL Server installation wizard

Should you run SSAS/SSIS/SSRS on the SQL Server?

Last Updated February 12, 2019

Architecture, Configuration Settings

When you’re building a new SQL Server, you’re going to see a few intriguing checkboxes during setup. It’s all free, right? Check everything! The services are all free, right? You can just check the boxes for Machine Learning Services, R, Python, Data Quality Services, PolyBase, Integration Services, Analysis Services, and Reporting Services – it’s not…

Database Design Choices To Consider When You’re Worried About Scale

Last Updated January 2, 2019

Tipping the Kilter I’m intentionally avoiding physical/hardware choices in this post, like how much hardware to buy, and scaling out vs. scaling up. It’s not that I don’t care about those things — I do, and they’re important — but most new apps these days are starting in the cloud, or an on-premise VM. In the…

Dad and I working on our DR plan

Thoughts On Microsoft’s Azure Outage Post-Mortem

Last Updated September 10, 2018

Last week, Azure suffered a day-long outage. One of the services involved was Visual Studio Team Services (aka Azure DevOps), and that team just published their outage postmortem. The postmortem is FANTASTIC: open, honest (at least it reads that way), and goes into enough technical detail to satisfy a wide variety of readers from managers…

Building SQL ConstantCare®: The Serverless Architecture

Last Updated March 27, 2018

Architecture, SQL ConstantCare

Our new SQL Constant Care® is an online service that analyzes your SQL Server’s diagnostic data, then gives you personalized advice on what you should do to make your database app faster and more reliable. I blogged about the vision last week, and today let’s talk about the things that drove our architecture decisions way back in…

How to Do a SQL Server Architecture Review

Last Updated October 12, 2017

Architecture, Processes and Practices

We’ve talked about how to do a free SQL Server health check, and then how to drill deeper with performance check. After these two, management usually steps back and asks, “Wait a minute – how’d we get into this mess? And long term, strategically, what should we do next?” And usually, we’re in a hot…

Does Separating Data and Log Files Make Your Server More Reliable?

Last Updated June 24, 2017

The old advice went something like this: “Put your data and log files on separate drives and your server will be more reliable. If you lose the data drive, you can still do a tail-of-the-log backup, and you won’t lose any data.” It’s advice. But is it actually good advice? Let’s think through it. If…

Brent Ozar reading

Building a Faux PaaS, Part 3: What the Ideal Engineering Team Looks Like

Last Updated June 8, 2017

Architecture, kCura Relativity, Microsoft Azure

Background: I’m working with kCura to build a Faux PaaS: something akin to Microsoft’s Azure SQL DB, but internally managed. You can catch up with what we’ve discussed so far in Part 1 and Part 2 of the series. In the last post, I talked about measuring backup and restore throughputs across different instance types, regions, storage configs, and backup locations.…

Building a Faux PaaS, Part 1: The SQL Server DevOps Scene in 2017

Last Updated June 8, 2017

Architecture, kCura Relativity

In the cloud, treat your servers like cattle, not like pets. Rounding up a herd of servers in Texas In the cloud, systems administration is very different than the on-premises stuff you’re used to. When you build VMs in the cloud with Infrastructure-as-a-Service (IaaS, meaning AWS EC2, GCE, or Azure VMs), you expect them to…

The Cost of Adding a Reporting Server

Last Updated February 13, 2017

Architecture, Licensing, Log Shipping, SQL Server

“We’d like to offload our reporting queries to a separate SQL Server.” The first costs are fairly 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…