how to go about separete database per tenant approach RRS feed

  • Question

  • how to go about multi-database option.

    we are going for one database per tenant option. The thing is we want to create a new database with some pre-defined tables, stored procedures and some initial data. The problem is how to create the new database, we have three options.

    1. Restore a database from template backup file
     (fast, usually nder 10 seconds)
    2. Copy from template database to new database
     (very slow)
    3. create a database through scripts
     (can be slow at later stages)

    we have ruled out the scripts for now, but is there any other way you guys can suggest to get around this thing. any best practise i might be missing? something which targets specially saas\paas domain?

    Tuesday, March 1, 2011 7:55 AM

All replies

  • What don't you like about (1)?
    Monday, March 7, 2011 10:32 PM
  • What part of the script approach was slow? 

    Was this a developer or a DBA who did the scripting?  ( There are options you can turn off. )

    I'm not a fan of the one database per customer approach.  These are all on the one server?  How many databases are you going to have?

    Why not one database for everyone?

    Tuesday, March 8, 2011 8:59 AM
  • I agree you need to evaluate if you want separate databases, what does it really give you? Data isolation, really, does it? How is data getting into your database(s)? Does your database support schemas, i.e. a way to isolate within a database? Although having said all I've worked on systems where the customer demands separate databases and sometimes logic and reason fails in the face of perceived security risks.

    Tuesday, March 8, 2011 10:39 AM