Post reply

Schema dilemma

  • May 11, 2008 at 7:50 pm

    #813581

    Kenneth/GSquared, yes I'm trying to get rid of the schema parameter thing on our next meeting and you're right there will be so many things that are not yet apparent along the way.

    Antonio, yup thats why I'm all for separate logins for every schema but hopefully we can use one DB per tenant.

    Jeff, I'm checking the context switching if the "higher ups" will consider.

    Adam, thanks for your idea and suggestions, it will be a big help for me.

    Carl, Thanks for specific answers, at least I can defend one database per tenant, which is really much easier, during our meeting tomorrow. 😉

    Thanks guys, by the way, I just want to make a consensus for a multi-tenant application, are you all for the one database per tenant?

  • May 11, 2008 at 8:02 pm

    #813586

    The real key is that the other folks are right... there should be a separate DB for each company. I've found that when the "higher ups" get involved, and they're not real live DBA's, the data is at risk when they make a design decision.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 12, 2008 at 1:09 am

    #813628

    To make this as easy as possible for development, managing, maintenance, changes etc, and to insure the highest chance of success for this 'project' - Yes. Go for separate db's for each tenant.

    /Kenneth

  • May 12, 2008 at 6:58 am

    #813755

    Where I work we have a similar situation - and the solution was to use dynamic sql - setting up stored procedures that used synonyms and have the dynamic sql define the synonym for the database used.

    stored procedure

    @DatabaseID INT

    AS

    IF EXISTS mySyn DROP SYNONYM mySyn

    DECLARE @strSQL VARCHAR(600)

    SET @strSQL = 'CREATE SYNONYM mySyn FOR XX' + CAST(@DatabaseID AS VARCHAR) + '.dbo.ActualTableName'

    SELECT Column1, Column1 FROM mySyn

    you get the idea

    the database name is XXnn where nn is the databaseid

    I am not sure what happens, though, if two databases try to run the same stored procedure at the same time...

  • May 12, 2008 at 8:43 am

    #813849

    One more disadvantage of a single database with multiple schemas - The customer calls and indicates that they have messed up the data and want there information restored from backup - now what do you do ? There is no such command as "backup schema" or "restore schema".

    SQL = Scarcely Qualifies as a Language

  • May 12, 2008 at 11:58 am

    #813987

    Carl Federl (5/12/2008)

    One more disadvantage of a single database with multiple schemas - The customer calls and indicates that they have messed up the data and want there information restored from backup - now what do you do ? There is no such command as "backup schema" or "restore schema".

    Actually, you could do put each set of tables into its own filegroup and perform a filegroup backup and piecemeal restore, this would leave all other companies online. I definitely agree that this should be done using a database per company methodology.


    My blog: http://jahaines.blogspot.com

  • May 19, 2008 at 9:58 pm

    #817560

    Thanks to all of you, now we are using one database per tenant.

    Anyway, I use sqlpubwiz to generate the sql file that will generate (when called) the database objects including the data when we are making the new database for a tenant.

    Heres how we did it: making the db for a tenant

    1. create the database

    2. generate the database objects

    3. make the login/user

    4. give the proper rights to the login/user (datareader, datawriter)

    and thats it... thanks..

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply