none
Multi tenancy database pick RRS feed

  • Question

  • Migrating one of my products which as a back end use SQL Server database. 

    Current state is like this. 

    For each new company I'm creating 5 new databases ( don't ask why ) one of those 5 databases is called current year (db_2019). Which means each new year that number is rising by one etc....

    If someone else decide to be on same server that number is going +5 db and +1 each year. 

    Now i have a problem because one of my next client is going to use at least 20 company's ( accounting Booker house ) That means 20 x 5   ( + 20 ( each year )) which is kinda to much. 

    I was reading a lot about tenancy users and advantages disadvantages. What would you suggest me in this specific case scenario. 

    One of the most important things is that there is a lot of data inside of those databases ( which is the main user why i spliced it by the years ) Millions of table rows inside the db objects.

    Thanks in advance

    Sunday, March 31, 2019 8:44 AM

Answers

  • Why not keep all data in single database itself and have a date column in the tables to indicate time period for the data

    Even if data is huge, you can better manage it by implementing table partitioning based on date column to organize data into multiple files (ndf) based on time period. You can also apply indexes on recent partitions alone as recent data will get more regularly queried compared to historic ones.

    And for multiple users you can even organize data into multiple schemas if you need to implement different security level for the objects based on users/groups


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, March 31, 2019 9:09 AM

All replies

  • Are  the structures of those 5 database per company is the same?

    Take a look  into SCHEMA

    https://www.brentozar.com/archive/2010/05/why-use-schemas/

    https://dba.stackexchange.com/questions/4075/what-are-some-best-practices-for-using-schemas-in-sql-server


    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

    Sunday, March 31, 2019 8:55 AM
    Answerer
  • Why not keep all data in single database itself and have a date column in the tables to indicate time period for the data

    Even if data is huge, you can better manage it by implementing table partitioning based on date column to organize data into multiple files (ndf) based on time period. You can also apply indexes on recent partitions alone as recent data will get more regularly queried compared to historic ones.

    And for multiple users you can even organize data into multiple schemas if you need to implement different security level for the objects based on users/groups


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, March 31, 2019 9:09 AM
  • All my database structures are exact the same. 

    May be good approach by using the schema's. But probably i wont be able of using express version anymore because all data will be in single .mdf database 

    Thanks for your solution

    Sunday, March 31, 2019 6:33 PM
  • All my database structures are exact the same. 

    May be good approach by using the schema's. But probably i wont be able of using express version anymore because all data will be in single .mdf database 

    Thanks for your solution

    What is the relationship between using schema and using express version? As far as I know, SQL Server version has no impact on using schema.

    Maybe I misunderstand your meaning, but I know all the DB objects including schema, tables could be stored in single .mdf database.

    Best Regards,

    Will


    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.

    Monday, April 1, 2019 2:51 AM
    Moderator
  • Its about storage in database .mdf file.

    AS far as i know Express version may go up to 10 GB .  Since I'm packing all databases in one i need to think about that.

    Thanks

    Thursday, April 18, 2019 11:09 AM