How to create multiple instance in sql server dynamically in a SAAS environment?

Question

I want to create a new project on contract management system. In this I have to manage multiple organizations and want to create new instance in sql server dynamically for each organization in a saas environment. How is this posiible? I am using asp.net for development. Any help would be appreciated.

Answer 1

A true SaaS application has a single application and database. It has the ability to have multiple tenants use the application. All data in the database needs to know what tenant it belongs too.

For instance if you have a booking system: Your customers sign up to use the software and become a row in the customer table with an ID. When a booking is made it has a customerID column as a foreign key to the customer table. Then all reports, booking views etc are done for that tenant using their customer id. You as a service provider can then run reports on all customers/tenants for your own purposes. Multiple tenants can use the system and all their data be in the same set of tables. This then means when you need to cluster replicate backup etc it is a single db, and a single migration for schema updates.

See these wiki's on multitenancy and SaaS

This of course requires your DB schema (and your app) to support this - if you are unable to change the schema then there are a number of options to up a new DB based upon the technology you are using. If you are using code first EF, then there will be db creation and migration scripts you can use. Otherwise it may justhave to be a sql script you have to generate and then maintain and run this each time a new customer is required. Personally i would rather have a single DB with an appropriate schema.