Stairway to ScriptDOM Level 3 - Finding Pattern s in the Abstract Syntax Tree (2022-06-08)

Daily Coping Tip

Send a friend a photo from a time you enjoyed together.

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

One or Many Databases

These days I see more and more customers adopting a sharding or federation approach to their database, often putting each client in a separate database. This is one of two common ways to design a multi-client database system, and both of these have their pros and cons. If you read the link above, you will see some of the advantages of using one over the other, which might help you decide how to architect your own system.

There are lots of disadvantages to both as well. I find that customers who put all clients in one database often have issues with noisy neighbors, where one customer might cause problems for other customers with the load they place on the system. They also struggle with scaling up when they find the workload is too large for a single server. There is also the need to manage security better as each query needs to ensure it checks for authorized access to the appropriate set of data, something which is even more complex when you have clients or users that need access to multiple sets of data.

On the other hand, in the age of DevOps, I find customers who struggle with deploying code to hundreds, or sometimes thousands, of databases. Yes, I have customers who literally have the same schema across low thousands of databases and then wonder why a deployment takes hours. Many of these clients also struggle with schema drift, which can complicate their ability to even successfully deploy across their estate.

There are certainly other challenges with these two architectures as well. I've worked with both paradigms, and I find that no matter which you choose, there are going to be situations that make you wish you had chosen the other design. Ultimately, I think learning more about both architectures and experimenting a bit with each is the best way to learn what might work best in your situation.

I also have a client that has a hybrid of these two. Their schema is built as all clients in one database, but they have separated out certain clients into another database. Actually, I think they have 3 databases, each with a mix of clients. Their DBAs have written lots of routines to ensure they can "move" a client from one database to another. This is an interesting strategy, and it's good in that their deployments are fairly simple and quick, they can manage drift, and they can move noisy neighbors to a new database (or new instance). On the other hand, they still have worries about security and ensuring users only see the data they have the authorization to view.

Is there an approach you prefer? Stick all clients in one database or shard your systems out into multiple databases. I'm curious today if one architecture has worked better for you and why. Let us know in the comments.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums