Share via

How we can manage multiple DB for different customers using single SQL server hosted in Azure ..? Can anyone suggest a better solution for this ..?

favas vj 1 Reputation point
2020-07-08T20:22:55.16+00:00

Our aim is to reduce the SQL server licensing and hardware cost also for the high availability of the servers.

SQL Server on Azure Virtual Machines

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mike Ubezzi 2,776 Reputation points
    2020-07-09T04:55:43.02+00:00

    Hi @favasvj-8984 - Please spend some time consuming the information available with regard to Multi-tenant database patterns: Multi-tenant SaaS database tenancy patterns

    You are looking for the following pattern: Multi-tenant app with sharded multi-tenant databases but without knowing the specifics of your solution, please read through the sharding patterns based upon how the customer data is stored. There is a solution designed to allow you to try out various options. Please see: The Wingtip Tickets SaaS application

    Regards,
    Mike

    0 comments
  2. Ronen Ariely 15,096 Reputation points
    2020-07-09T05:20:31.347+00:00

    Good day ,

    using single SQL server hosted in Azure

    We must start with clarify this point. When you speak about "SQL server hosted in Azure", do you mean (1) Azure logical SQL Server or do you mean (2) SQL Server which is install on Azure Virtual machine?

    In the first case (using Azure Logical SQL Server), you should know that you do not pay for the logical server. It is not a real server but a logical rapper application, which is used to manage different type of database services (on the same logical server you can have different product like Azure SQL Database which is base on SQL Server, or Azure Data warehouse which is base on Parallel Data Warehouse). In this case the databases are totally separated from the user aspect and in fact they are physically might not even be in the same machine.

    Therefore, if the number of the client is low, then you can simple create separate logical server for each one.

    If the number of logical server is high then you can simply manage LOGINs and USERs according to your need and provide to each client a separate LOGIN and a USER in the database. This is why we have LOGIN and USERs

    In addition in order to manage multiple Azure SQL databases, you can use Azure Jobs which allow you to execute queries on multiple databases and manage them as one even if the databases are not physically on the same physical server behind the scenes. For more information you can check this page.

    In the second case (using SQL Server which is install on Virtual Machine), you manage it just like on-premises using LOGIN and USERS

    In both cases, you should check the option of using Contained Databases

    0 comments