You've been reading my blog, watching my videos, and maybe even taking some of my training classes. You've heard me say things like "my clients" from time to time, and you're wondering... why do companies actually hire me? What problem are they trying to solve?
Well, when clients meet with me for the first time, I have a series of questions I put up on the screen for them to talk through:
Software vendors have a wide variety of clients: Some clients don't have any IT staff at all, and just outsource everything. The client wants to hand an installation manual to their contractor and say, "Build whatever the software vendor wants." Some clients have a full time sysadmin or two, and those sysadmins don't really know…
Stack Overflow publishes a data dump with all user-contributed content, and it's a fun set of data to use for demos. I took the 2024-April data dump, and imported it into a Microsoft SQL Server database.
It's an 31GB torrent (magnet) that expands to a ~202GB database. I used Microsoft SQL Server 2016, so you can attach this to anything 2016 or newer. If that's too big, no worries - for smaller versions and past versions, check out my How to Download the Stack Overflow Database page.
Stack Overflow, the place where most of your production code comes from, shares a version of their data in XML format from time to time, and then I import it into SQL Server format.
Stack Overflow's database makes for great blog post examples because it's real-world data: real data distributions, lots of different data types, easy to understand tables, simple joins. Some of the tables include:
Earlier this week, I wrote about how we designed the database for PollGab.com. Because we went with AWS DynamoDB for the database storage and AWS Lambda for the processing work, costs are really, really low:
That is not a typo: that's $1.85 USD.
In the last post, I talked about how Richie and I sketched out the goals of PollGab.com, the site where you can post questions for other folks to answer during their live streams.
I challenged you to decide:
Years ago when we did online presentations or live Office Hours webcasts, attendees would type their questions into the "Questions" section of whatever webcast platform we were using - WebEx, GoToWebinar, Zoom.
This sucked because:
Stack Overflow, the place where most of your production code comes from, publicly exports their data every couple/few months. @TarynPivots (their DBA) tweets about it, and then I pull some levers and import the XML data dump into SQL Server format.
Stack Overflow's database makes for great blog post examples because it's real-world data: real data distributions, lots of different data types, easy to understand tables, simple joins. Some of the tables include:
Stack Overflow, the place where most of your production code comes from, publicly exports their data every couple/few months. @TarynPivots (their DBA) tweets about it, and then I pull some levers and import the XML data dump into SQL Server format.
Stack Overflow's database makes for great blog post examples because it's real-world data: real data distributions, lots of different data types, easy to understand tables, simple joins. Some of the tables include:
Taryn and the kind folks at Stack Overflow have updated their public XML data dump for June, so I've imported that into an updated sample database for your blogging and presenting satisfaction.
You can download the 40GB torrent (magnet) and it expands to a ~350GB SQL Server 2008 database. Because it’s so large, we only distribute it with BitTorrent – if you’re new to that, here are more detailed instructions.
You present on SQL Server topics at user groups and conferences, and you've been wondering how to get started with the Stack Overflow public database. Here's a quick list of things to know.
For stable demos, use StackOverflow2010. This smaller 10GB database has data from the first years of Stack Overflow's history. It doesn't change, so you don't have to worry about updating your screenshots and metrics every time Stack releases a new data dump.
@Taryn and the kind folks at Stack Overflow publish their data export periodically with your questions, answers, comments, user info, and more. It's available as an XML data dump, which I then take and import into SQL Server for teaching performance tuning. You can download the 38GB torrent (magnet), which gives you a series of 7Zip files…
My public SQL Server database copy of the Stack Overflow data dump only includes clustered indexes by default. I want to keep your database size as small as possible for quick downloading.
But like any database - when you create it, should you add your own indexes to make queries go faster? The answer lies in knowing your workloads, but we don't usually know our database workloads until they start.
Look, we need these things
The setup for sp_AllNightLog creates jobs for four separate activities
1 job to poll for new databases to back up (primary)
1 job to poll for new databases to restore (secondary)
10 jobs to poll for backups to take (primary)
10 jobs to poll for restores to... whatever (secondary)
Carry on
It turns out that the only thing harder than checking for new databases restored to a SQL Server, is checking a folder for a backup of a database that doesn't exist on another SQL Server.
Building stuff
I sometimes really hate coming up with ideas, and much prefer someone to just say "I want this" so I can go out into the world and figure out how to do that. Occasionally though, I realize that I'm not going to get all that much direction. That's what happened with sp_BlitzQueryStore.
Nick Craver and the kind folks at Stack Overflow publish their data export periodically with your questions, answers, comments, user info, and more. It's available as an XML data dump, which I then take and import into SQL Server for teaching performance tuning.
You can download the 16GB torrent (magnet), which gives you a series of 7Zip files that you can extract to produce a 118GB SQL Server 2008 database. You can then attach it to any 2008-2017 SQL Server.
In our Faux PaaS project, we need a backup plan - or rather, a restore plan.
On each SQL Server instance, clients can create as many databases as they want, anytime they want, with no human intervention. We need those databases covered by disaster recovery as quickly as practical.
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. It's a lot of work to answer questions like "How should we configure our new SQL Server VMs?"
In the cloud, treat your servers like cattle, not like pets.
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 die. It's just a matter of time. If you're lucky, it'll be years from now, but if you're unlucky, it'll be tomorrow.
