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

  • Question

  • We are planning a cloud based sales application for our customers and confused about the database design. Our each customers will have multiple items to sell  so the items count will be in thousands for each customer. In this case, do we need to have separate azure database for each customer or can we maintain in a single database for all the customers ? The items tracking log will become very huge if we maintain all the customers in a single database and that leads to the slower performance also if it crashes that effect all our customers.

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

    Can someone suggest a better design approach?

    Creating multiple instances of SQL server solve the problem..?

    Thanks in Advance


    • Edited by favas vj Wednesday, July 8, 2020 9:54 PM
    Wednesday, July 8, 2020 9:50 PM

Answers

  • How much customers do you have? You can maitain all customers in the single db by creating a separated schema.

    If you use SQL Azure ( not VM) do not worry about the backups, Azure DB takes care of it

    >>Creating multiple instances of SQL server solve the problem..?

    No,  you will need to have much RAM CPU to support all instances, pretty costly...


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by favas vj Tuesday, July 14, 2020 6:36 PM
    Thursday, July 9, 2020 7:18 AM

  • My question In brief,  If we create separate DB for each customer how we can manage each DB using single Sql server license..? 

    There is no concept of license actually in Azure VM. You go to azure marketplace select image of SQL Server you want to deploy and then deploy it. The amount of time you use SQL Server you will get bill for it. IF you already have sql server license you can use that in Azure VM with SQL Server configuration so that your cost comes down. 

    Is it possible by creating multiple instances of SQL server for each DB..?or Do we need to buy separate  SQL licenses for managing each DB..?

    For Azure VM since you get image from market place the answer is NO. only single instance is allowed either default or named. So you have to create multiple database on single instance for each customer. Their will be NO extra charge for adding multiple databases on single instance you would be charged for single instance on Azure VM


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    • Edited by Shanky_621MVP Thursday, July 9, 2020 12:10 PM
    • Marked as answer by favas vj Tuesday, July 14, 2020 6:36 PM
    Thursday, July 9, 2020 12:10 PM
  • For Azure VM since you get image from market place the answer is NO. only single instance is allowed either default or named. So you have to create multiple database on single instance for each customer. Their will be NO extra charge for adding multiple databases on single instance you would be charged for single instance on Azure VM

    Hm, I have not tried it, but is there really something to stop me from installing a second instance?

    Then again, creating a separate instance for a each customer would not be a good design.

    Overall, I am a little puzzled over the idea of using an Azure VM if this is a new product you are setting up. Wouldn't PaaS be a better option? That is, either Azure SQL Database or Managed Instance.

    As for the design, there is always a trade-off when designing multi-tenant applications. Having all customers in the same database and the same set of tables, reduces management, but you need to be very careful so that you don't leak data between customers. Performance can be a problem if the customers have different access patterns, as one query plan that fits one customer, may not fit another.

    With individual databases for the customers, you avoid these problems, but now you need to learn to automate deployment of updates. If there is common data that all databases need, you need to deal with that.

    There is also a middle-ground solution: a single-database, but one schema per customer.

    For a cloud solution, I think my prime hypothesis would be Azure SQL Database with one database per customer. I would also look into what Elastic Pools can give me. With one database per customer, different customers can hav different sizes - and different price tags.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by favas vj Tuesday, July 14, 2020 6:39 PM
    Thursday, July 9, 2020 9:38 PM
  • Hm, I have not tried it, but is there really something to stop me from installing a second instance?

    No but if you "really" want to do that you would have to copy SQL Server installation file and then install but that is NOT supported last time I confirmed with MS guys 6 months back. He said it is not the correct way as MS would not be able to track it. I found it little incredible but did not questioned him.

    Overall, I am a little puzzled over the idea of using an Azure VM if this is a new product you are setting up. Wouldn't PaaS be a better option? That is, either Azure SQL Database or Managed Instance.

    I agree here a managed instance would be better one but since OP never mentioned that and I assumed that may be incompatibility is forcing him to Azure VM i did not raised the point.

    There is also a middle-ground solution: a single-database, but one schema per customer.

    This can be a solution but again cumbersome.

    • Edited by Shanky_621MVP Friday, July 10, 2020 8:08 AM
    • Marked as answer by favas vj Tuesday, July 14, 2020 6:41 PM
    Friday, July 10, 2020 8:07 AM
  • Hi favas vj,

    >> Is it possible by creating multiple instances of SQL server for each DB..?or Do we need to buy separate  SQL licenses for managing each DB..?

    No. Suggest you create multiple DB in one SQL server instance. Add DB to instance do not need additional license cost.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by favas vj Tuesday, July 14, 2020 6:41 PM
    Friday, July 10, 2020 9:27 AM

All replies

  • Hi favas vj,

    Did your environment is SQL server on Azure VM?

    >> In this case, do we need to have separate azure database for each customer or can we maintain in a single database for all the customers ?

    Suggest you separate database for each customer when each customer has thousands items counts.

    If you want to restrict customer access to only one database, please check the suggestion from below links.
    Restrict SQL Server Login access to only one database
    How to hide SQL Server user databases in SQL Server Management Studio

    If I misunderstood your issue, please let me know.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 9, 2020 7:00 AM
  • How much customers do you have? You can maitain all customers in the single db by creating a separated schema.

    If you use SQL Azure ( not VM) do not worry about the backups, Azure DB takes care of it

    >>Creating multiple instances of SQL server solve the problem..?

    No,  you will need to have much RAM CPU to support all instances, pretty costly...


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by favas vj Tuesday, July 14, 2020 6:36 PM
    Thursday, July 9, 2020 7:18 AM
  • Hi Cathy,

    Thanks for your prompt response.

    Did your environment is SQL server on Azure VM?

    Actually we haven't hosted our DB we are planning to host the environment in SQL server on Azure VM and we don't have any customers at the moment but we are expecting this much after go live.

    My question In brief,  If we create separate DB for each customer how we can manage each DB using single Sql server license..? 

    Is it possible by creating multiple instances of SQL server for each DB..?or Do we need to buy separate  SQL licenses for managing each DB..?

    (If we go with separate DB for each customer we need to avoid the additional sql licensing cost )

    Please suggest me your solution..

    Regards,

    Favas vj


    Thursday, July 9, 2020 9:32 AM

  • My question In brief,  If we create separate DB for each customer how we can manage each DB using single Sql server license..? 

    There is no concept of license actually in Azure VM. You go to azure marketplace select image of SQL Server you want to deploy and then deploy it. The amount of time you use SQL Server you will get bill for it. IF you already have sql server license you can use that in Azure VM with SQL Server configuration so that your cost comes down. 

    Is it possible by creating multiple instances of SQL server for each DB..?or Do we need to buy separate  SQL licenses for managing each DB..?

    For Azure VM since you get image from market place the answer is NO. only single instance is allowed either default or named. So you have to create multiple database on single instance for each customer. Their will be NO extra charge for adding multiple databases on single instance you would be charged for single instance on Azure VM


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    • Edited by Shanky_621MVP Thursday, July 9, 2020 12:10 PM
    • Marked as answer by favas vj Tuesday, July 14, 2020 6:36 PM
    Thursday, July 9, 2020 12:10 PM
  • For Azure VM since you get image from market place the answer is NO. only single instance is allowed either default or named. So you have to create multiple database on single instance for each customer. Their will be NO extra charge for adding multiple databases on single instance you would be charged for single instance on Azure VM

    Hm, I have not tried it, but is there really something to stop me from installing a second instance?

    Then again, creating a separate instance for a each customer would not be a good design.

    Overall, I am a little puzzled over the idea of using an Azure VM if this is a new product you are setting up. Wouldn't PaaS be a better option? That is, either Azure SQL Database or Managed Instance.

    As for the design, there is always a trade-off when designing multi-tenant applications. Having all customers in the same database and the same set of tables, reduces management, but you need to be very careful so that you don't leak data between customers. Performance can be a problem if the customers have different access patterns, as one query plan that fits one customer, may not fit another.

    With individual databases for the customers, you avoid these problems, but now you need to learn to automate deployment of updates. If there is common data that all databases need, you need to deal with that.

    There is also a middle-ground solution: a single-database, but one schema per customer.

    For a cloud solution, I think my prime hypothesis would be Azure SQL Database with one database per customer. I would also look into what Elastic Pools can give me. With one database per customer, different customers can hav different sizes - and different price tags.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by favas vj Tuesday, July 14, 2020 6:39 PM
    Thursday, July 9, 2020 9:38 PM
  • Hm, I have not tried it, but is there really something to stop me from installing a second instance?

    No but if you "really" want to do that you would have to copy SQL Server installation file and then install but that is NOT supported last time I confirmed with MS guys 6 months back. He said it is not the correct way as MS would not be able to track it. I found it little incredible but did not questioned him.

    Overall, I am a little puzzled over the idea of using an Azure VM if this is a new product you are setting up. Wouldn't PaaS be a better option? That is, either Azure SQL Database or Managed Instance.

    I agree here a managed instance would be better one but since OP never mentioned that and I assumed that may be incompatibility is forcing him to Azure VM i did not raised the point.

    There is also a middle-ground solution: a single-database, but one schema per customer.

    This can be a solution but again cumbersome.

    • Edited by Shanky_621MVP Friday, July 10, 2020 8:08 AM
    • Marked as answer by favas vj Tuesday, July 14, 2020 6:41 PM
    Friday, July 10, 2020 8:07 AM
  • Hi favas vj,

    >> Is it possible by creating multiple instances of SQL server for each DB..?or Do we need to buy separate  SQL licenses for managing each DB..?

    No. Suggest you create multiple DB in one SQL server instance. Add DB to instance do not need additional license cost.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by favas vj Tuesday, July 14, 2020 6:41 PM
    Friday, July 10, 2020 9:27 AM
  • Thanks Shashank for your valuable feedback.
    Tuesday, July 14, 2020 6:35 PM
  • Thanks Erland for your valuable feedback and it helped me a lot.
    Tuesday, July 14, 2020 6:39 PM
  • Thanks shanky for your valuable comment.
    Tuesday, July 14, 2020 6:41 PM