Share via

Which is the right choose for a MSSQL based SASS solution with about 100 databases

Nikola Nikolov 26 Reputation points
2022-02-26T13:25:53.093+00:00

Hello,

I need assistance in choosing the right service from Azure in choosing the right database plan for an application using about 100 different databases for a SAAS solution. I am looking for a cheapest solution possible, having in mind that from one side the app is for a Work time check in /check out sollution and from the other side the expected price from potential customers is relativ low.

Azure SQL Database
0 comments
Accepted answer
  1. Alberto Morillo 33,426 Reputation points MVP
    2022-02-26T14:44:56.04+00:00

    I helped an Accounting application that is a SAAS solution to migrate their database from on-premises to Azure SQL Database. They track attendance for small companies same as the application you mentioned. Initially their databases were migrated to Azure SQL VM (IaaS), no issues in terms of performance, but the costs of licensing the OS and the SQL Server Enterprise Edition was huge. From the beginning Management of the organization was aiming Azure as a way of getting rid of licensing costs, and we are talking about dozens of thousands of dollars.

    We then merged all databases of the application on one database with different schemas and migrated later just one database to Azure SQL (DTU-model) with no licensing costs. Azure SQL Database provided key SQL Server Enterprise features like partitioned indexes as part of its offering, no additional costs. Azure SQL offers also artificial intelligence at not cost (Automatic tuning, forces the best query plan, creates indexes automatically), high availability, geo and zone redundancy at no cost or low cost. We also were able to scale up reserved database resources during business hours, and scale down reserved database resources during nights and weekends, while still offering 24x7 service to all customers. QA/Test environments were saving by using Serverless database servers that auto-paused themselves when they are not in used. In summary, Management saved more than 45K per year by moving databases to Azure SQL PaaS but moving to PaaS database offerings involved some architectural adjustments.

    Azure Elastic Pools can help you keep those 100 databases on a pool and save even more, but if cross database queries are needed you may need to consider using elastic queries or consolidate on one single database.

    Here you will find documentation to help you decide.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nikola Nikolov 26 Reputation points
    2022-02-26T18:42:38.287+00:00

    Thank you very much for the really fast reply. I was thinking the same but was worried about performance when all the tenants are in one database. The solution support OnModelCreating filter by tenantId. I will try to put them in a couple of shards and hope that will do the job well.

    However with all tenants in one place I will loose the option for separate db backup and restore if needed.

    But I suppose that a man could not request to pay as little as possible and from the other side to receive all the best as possible .