Architecture

Architecture, SQL Server

If you’re a full time production database administrator, you need a staging environment. This is where you test infrastructure tasks like failing over mirroring, doing a disaster recovery role swap, cluster quorum changes, log shipping configuration, and patching. You need to test this stuff repeatedly so that when you’re under pressure in the production environment, you…

Why RPO and RTO Are Actually Performance Metrics Too

Last Updated February 23, 2021

Architecture, Backup and Recovery, CHECKDB and Corruption, SQL Server

Most companies come to us saying, “The SQL Server isn’t fast enough. Help us make it go faster.” They’re kinda surprised when one of the first things we fill out together is a variation of our High Availability and Disaster Recovery Planning Worksheet: Download the full PDF in our First Responder Kit They say things like, “Wait,…

Why Zero Data Loss in SQL Server is Harder Than You Think

Last Updated April 9, 2017

Kendra Little

Architecture, High Availability

But Kendra, it can’t be that hard… after all, we have synchronous modes in Database Mirroring and Availability Groups, right? Synchronous Commit doesn’t mean “zero data loss” When we think about limiting data loss, the first thing we think of is a technology that lets us reduce points of failure. If every transaction must be…

How Many Databases Can I Put on One SQL Server?

Last Updated February 9, 2017

Architecture, Backup and Recovery, ISV and SaaS Database Design, SQL Server

I’ve seen servers with thousands of databases on a single SQL Server, and it works. Sure, opening the databases list in SQL Server Management Studio is painful, and a lot of third party monitoring tools fall over, but it’s not so bad once you know how to work around these issues. But there’s two issues…

Solving SQL Server Scalability Problems

Last Updated October 4, 2013

Architecture, SQL Server

You’ve been nursing your SQL Server through day after day of increasing load. Stored procedures are taking longer and longer to run. There are timeouts on the client side. Both customers and management are getting upset. As you’re bemoaning the terrible code that the developers wrote, it hits you: you don’t have a code problem,…

Many Masters, One Truth

Last Updated February 9, 2017

Architecture, Replication

As businesses grow, many teams start considering scale out database solutions but they’re cautious – they want to know how to keep data up to date in multiple places. In theory, you can use log shipping, mirroring, or AlwaysOn Availability groups. While this answers the literal question, what people really mean is usually very different.…

Saving Session State (video)

Last Updated February 13, 2017

Session state frequently ends up on a busy SQL Server. What seemed like a good idea in development turns into a problem in production. While there are valid business reasons for persisting session state to permanent storage; there are equally valid reasons to avoid using SQL Server as the permanent storage. We’ll investigate why session…

Databases Five Years from Today

Last Updated April 24, 2018

Five years from now, in March 2018, what will be different about databases? I’m a Microsoft SQL Server guy, so keep that in mind when reading my thoughts. Looking Back at 2008-2013 The big story here for me was virtualization. In mid-2008, Microsoft added support for SQL Server running under Hyper-V, and later they added…

The Trouble with Keys

Last Updated September 25, 2012

Scaling up is hard: big hardware gets expensive fast. Scaling out is equally difficult; interesting design problems creep in to scale out solutions. One of the more troublesome issues architects face when scaling out is the issue of distributing identity. It’s often advantageous for object identity to be unique and portable across all database instances…

Why I’m at Amazon Architect Training This Week

Last Updated February 9, 2017

Amazon Web Services (AWS), Architecture, Cloud Computing

People bring me in when they’re having data problems. They can’t store data fast enough, they can’t make it reliable enough, they can’t hire people to manage it, etc. When I’m in the conference room, it’s because there’s a fire in the disco datacenter. You know how it is as a DBA, though – DBA…

Hash Partitioning, SQL Server, and Scaling Writes

Last Updated September 2, 2019

Architecture, Partitioning

At first glance, SQL Server’s partitioning seems like it should be an easy way to solve problems inserting data into busy tables. If the writes are spread across many partitions it only makes sense that we can avoid write hot spots in SQL Server, right? The truth is much more complicated than it appears. Asking…

Understanding Database Choice

Last Updated February 13, 2017

Everyone needs a distributed database right? Wrong. It’s easy to get so excited about trying out a new technology that you close your eyes to the problem you’re trying to solve. I came across a question on Stack Overflow where the poster was asking what kind of distributed database they needed. Rather than jump right…

Availability, Data Locality, and Peer-to-Peer Replication

Last Updated February 9, 2017

Architecture, Replication

I want to make something clear: high availability is not load balancing. The two options don’t have to be mutually exclusive, but they aren’t the same thing. Several months ago, I wrote about resolving write conflicts. Some of the approaches I mentioned for resolving write conflicts (such as taking a “last write wins” approach) involved…

Beep beep, back that thing up. Wait, on second thought...

The 9 Letters That Get DBAs Fired

Last Updated September 2, 2021

Architecture, Backup and Recovery

I’m proud to say that I’ve only lost one job in my life. I was a grocery store bagger when the store came under pressure to increase their percentage of minority workers. They laid off all of the Caucasian baggers, but I’m not bitter. I actually loved bagging groceries, and if I hadn’t been laid…

How to Design Multi-Client Databases

Last Updated August 6, 2020

Architecture, ISV and SaaS Database Design

When you’re building an application for lots of clients, there’s two common ways to design the database(s): Option A: Put all clients in the same database Option 2: Build one database per client I’ve got clients on both sides of this religious war, and last week I had the misfortune of taking two of them…

Which Database Is Right for Me?

Last Updated February 13, 2017

When you start developing a new application how do you pick the database back end? Most people pick what they know/what’s already installed on their system: the tried and true relational database. Let’s face it: nobody is getting fired for using a relational database. They’re safe, well understood, and there’s probably one running in the…

I can tell when someone's been surfing dirty web sites.

NoSQL Basics for Database Administrators

Last Updated June 7, 2020