ISV and SaaS Database Design

sp_AllNightLog: Creating Jobs Like I’m The President

Last Updated August 11, 2017

Backup and Recovery, Clients and Case Studies, ISV and SaaS Database Design, kCura Relativity, Log Shipping, SQL Server

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) And,…

sp_AllNightLog: Poll Vaulting

Last Updated July 12, 2017

Erik Darling

Backup and Recovery, Clients and Case Studies, ISV and SaaS Database Design, kCura Relativity, Log Shipping, SQL Server

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. These are both part of what sp_AllNightLog has to do. The other components, which use workers and a…

sp_AllNightLog: ¿Por que los queues?

Last Updated July 7, 2017

Erik Darling

Backup and Recovery, Clients and Case Studies, ISV and SaaS Database Design, kCura Relativity, Ola Hallengren's Database Maintenance Scripts

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. It…

Databases Need a Kosher Certification

Last Updated February 9, 2017

Brent Ozar

ISV and SaaS Database Design, kCura Relativity, SQL Server

As I picked up a nutritious breakfast from my favorite bakery (love ya, Magnolia), I noticed a sign in the window saying they were certified Kosher. As I usually do, I thought about how that kind of thing applied to databases. This database is totally free of heaps. The Star-K symbol on food products or restaurants means…

How Many Databases Can I Put on One SQL Server?

Last Updated February 9, 2017

Brent Ozar

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…

Who’s Allowed to Add Indexes to ISV Apps?

Last Updated February 13, 2017

Brent Ozar

Indexing, ISV and SaaS Database Design, SQL Server

Performance tuning of independent software vendor (ISV) databases is a little tricky. To understand who does what, let’s think through all of the work required with building and hosting a database application: Who adds indexes and tunes queries? Typically the two parties involved – the vendor and the customer – start at opposite ends of…

Tiering kCura Relativity Databases (Or Any SaaS Product)

Last Updated February 9, 2017

Brent Ozar

ISV and SaaS Database Design, kCura Relativity, SQL Server

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 –…

How to Design Multi-Client Databases

Last Updated August 6, 2020

Brent Ozar

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…