Sizing hardware and planning for growth, resources, and operational scale.
41 associated posts41 primary posts
Let's skip the shenanigans and get right down to it - you're responsible for SQL Server and someone in your company wants to move things into Amazon Web Services (AWS). You've got SQL Server setup covered thanks to our helpful SQL Server setup checklist and you're confident you've got hardware under control, but things are different up in the clouds.
When you're the database administrator working with a software product that stores every client's data in a different database, the sheer number of databases can be intimidating. As you grow from dozens to hundreds to thousands of databases, you can't treat all of them equally.
Start by making a graph of the database sizes - here's an example from one of my clients with 58 databases on a server (certainly not a big number, but just easy to digest in a blog post):
Before we pick CPUs or memory, let's start by looking at SQL Server 2012's licensing costs:These are MSRP prices and do not include the extra costs for Software Assurance (maintenance) or any discounts for being a school, non-profit, or those photos you have of Bill Gates.Physical, Standard Edition - you'll notice that I went with…
Wanna know where you fit in relation to someone else? Get the answers to these questions.
1. How many SQL Server instances do you have? The pro folks use the word "instances" rather than servers because clusters may have multiple SQL Server instances per node.
When you get a new laptop, you don't just pick Small, Medium, or Large. You look at all kinds of specs - screen resolution and size, memory size, storage, weight, and of course, price. Eventually you pick the one that's perfect for you, and then you shake your fist at the skies because you can't…
Servers are like milk cartons: both need an expiration date printed clearly on the outside.When a project needs a new database server, I ask the business users, "How long does this server need to last?"Inevitably somebody gives the flip answer of, "Forever." I laugh along with the group and then wait with a calm smile…
There are many ways to scale out your database; many of these techniques require advanced management and expensive add-ons or editions. Database sharding is a flexible way of scaling out a database. In this presentation, Jeremiah Peschka explains how to scale out using database sharding, covers basic techniques, and shares some of the pitfalls. This talk is for senior DBAs, database architects, and software architects who are interested in scaling out their database.
Let's bust the buzzword bubble. Big Data is a sexy problem to have, so everybody's claiming it. I'm sick of people using this phrase to gloss over their real challenges. You have a Small Server problem. If you haven't bought a new server in the last two years, you're not allowed to complain about the…
South Korean musician Psy's hilarious music video for Oppa Gangnam Style has been tearing up the charts:
https://www.youtube.com/watch?v=9bZkp7q19f0
Do you have a SQL Server that’s not getting enough action?
Maybe you have a new piece of production or lab hardware that you’d like to benchmark, to see how it compares with other gear in your environment.
Let's oversimplify the bejeezus out of this complex problem. Suspend your disbelief for a second and work with me:
We have a database server hosting just one 100GB table. Sure, in reality, we've got lots of databases and lots of tables, but we're going to keep this simple. We've got a simple sales table that stores a row for each sale we've ever had. We don't have any indexes: this is just 100GB of raw data in our clustered index.
We all hope that we’re going to succeed beyond our wildest expectations. Startups long for multi-billion dollar IPOs or scaling to hundreds, or even thousands, of servers. Every hosting provider is touting how their new cloud offering will help us scale up to unheard of heights. I’ve built things up and torn them down a few times over my career
Everyone poops and everyone grows. We all reach that growth stage in our own time, but sooner or later we all encounter it. Once you reach a growth stage you will experience some of these common emotions.
Denial
It's 8:37AM and your developers just barged into your cubicle yelling, "The database can't handle the load!" What's your first reaction? If you're like me, you probably threw a stapler at them. If you're a bit more level-headed, you most likely said something like, "That's unpossible!"
Scaling up is hard, scaling out is even harder. We all know that we can scale reads by adding some kind of replication or read-only copies of databases or using a massive caching layer. What happens when we have to scale writes? Borrowing Brent's grocery store metaphor, a database that is effectively scaled for reads…
When you're building an application for lots of clients, there are two common ways to design the database(s):
Option A: Put all clients in the same database
Option 2: Build one database per client
When you need SQL Server to handle more load, there's two big-picture ways to do it:
Scale up - use a more powerful SQL Server
Scale out - use more SQL Servers
StackOverflow went through a few infrastructure upgrades this week. Jeff Atwood blogged about the changes, and Kyle Brandt goes into more detail. Here's a short recap of some of the SQL Server changes: Changed physical server models (from IBM/Lenovo to Dells) Went from two quad-core Xeons to two six-core Xeons Enabled hyperthreading (so it looked…
Here's how to tell when you should learn NUMA. Say you've got an IBM 3950, a high-end rack-mount server with 4 sockets, 4 cores each (16 cores total) and 64 gigs of memory. If you've got several of these servers, you can connect them together via a daisy chain. IBM makes a special interconnect cable…
I've got a few servers in my home lab and I've always taken steps to make sure they're quiet. I like running my lab out of my office closet, but I don't want to hear any of them running. I've blogged about how to build a silent PC, but today I'm tackling another noisy beast - an off-the-rack server.
Our contacts at Microsoft offered us the services of a Microsoft Technology Center to do a two-week lab exercise on our data warehouse. We'd kept piling one application after another on our data warehouse SQL Server until the poor thing was plain out of maintenance windows and couldn't keep up, and we needed info from the pros to keep scaling it up.
