Looking for SQL Database advice for a cms

Question

Hi

I am looking for some advice on which sql database setup to use, I am building a multi tennent CMS, in the simplest form, it has 3 table structures 1 for file search, one for cms search and 1 for statistics.

I can't use no sql becuase the costs are so high mainly because the amount of content being returned will be fairly large.

while the storage isn't going to be big certainly not in database terms, the bigger of the issues i have is security, ensuring information isn't leaked between tennents, and wasted resources, some databases will hardly be used and some will be used alot.

the ideal version would be something like Cosmos Db but in SQL server, where i can allocate a container and resources are automatically split, but it scales almost infinitly. But sql setup is much less fluid. and while its not being used is fairly cheap.

So question 1 is does each table stucture need its own database, or should i just have one database per tennent?

but Sql Elastic pool seems really expensive, and doesn't offer the fluidity of cosmos db.

Any suggestions.

Answer 1

Hi @EnenDaveyBoy,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

As I understand, you are looking for advice on building a multi-tenant CMS with SQL databases, which can be challenging, especially when balancing cost, security, and scalability.

Here are some considerations and recommendations:

1.Database Per Tenant vs. Shared Database

• Database Per Tenant: This model provides strong isolation, as each tenant’s data is stored in a separate database. This can simplify security and compliance but may lead to higher costs and management complexity, especially if you have many tenants.
• Shared Database with Tenant Isolation: This model stores multiple tenants’ data in the same database but uses mechanisms like Row-Level Security (RLS) to ensure data isolation. This can be more cost-effective and easier to manage but requires careful design to ensure security and performance.

2.SQL Elastic Pools

• Elastic Pools: These allow you to share resources among multiple databases, which can help manage costs and provide performance elasticity1. However, as you mentioned, they might not offer the same fluidity as Cosmos DB.

3.Security and Resource Management

• Row-Level Security (RLS): This feature in SQL Server can help ensure that each tenant can only access their own data, even when using a shared database.
• Always Encrypted: This feature can help protect sensitive data by encrypting it both at rest and in transit.

Recommendations

• Start with a Shared Database Model: Use a shared database with RLS for tenant isolation. This approach can help manage costs and simplify management while ensuring data security.
• Monitor and Scale: Regularly monitor database performance and usage. If certain tenants require more resources, consider moving them to their own database or using elastic pools to manage resource allocation.
• Use Elastic Pools Judiciously: If you have a mix of high and low usage tenants, elastic pools can help balance the load and optimize costs.

For more information, please refer the documents:

https://learn.microsoft.com/en-us/azure/architecture/guide/multitenant/service/sql-database

https://learn.microsoft.com/en-us/azure/azure-sql/database/saas-tenancy-app-design-patterns?view=azuresql

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

It depends on the number of tenants you plan on supporting. if only a couple, then either works.

if 100's then managing isolated databases becomes an issue. say you have 200 tenants, and the next software release requires modifying 200 databases. what do you do if one fails, rollback the release? you also may have 100's of backups to maintain.

if multi-tenant database, then database management easier. but what if you out grow a single database instance? you would want to pick a database that supported partitioning.

also a CMS typically uses a whole text index, not a database index for search. a relational database is typically only for account information. You might look at blob or table storage with an index service.

you also might look at Postgresql.