I am development a app multi tenant. I chose the approach Shared Database/ Separeted Schema.
My idea is has a schema default (dbo) and when deploy this schema, do update schema of outhers tenants (tanantA, tenantB, tenantC).
In other words, make sincronization schemas.
How can sincronized this schemas of tenants with schema default?
I am using SQL Server 2008.
Thank´s
There is no magic command to synchronize the schemas as far as I know. You would need to use a tool - either built in house or bought (Check out Red Gate's SQL Compare and SQL Examiner - you need to tweak them to compare different schemas).
Just synchronizing can often be tricky business though. If you added a column, do you need to also fill that column with data? If you split a column into two new columns there has to be conversion code for something like that.
My suggestion would be to very carefully track any scripts that you run against the dbo schema and make sure that they also get run against the other schemas when appropriate. You can then use a tool like SQL Compare as an occasional sanity check to look for any unexpected differences.
First thing you will need is a table or other mechanism to store the version information of the schema. If nothing else so that you can bind your application and schema together. There is nothing more painful than a version of the application against the wrong schema - failing, corrupting data etc.
The application should reject or shutdown if its not the right version - you might get some blowback when its not right, but protects you from the really bad day when the database corrumpts the valuable data
You'll need a way to track changes such as SVN or something else - from SQL you can export the intial schema. From here you will need a mechanism to track changes using a nice tool like SQL comapre and then track the schema changes and match to an update in version number in the target database.
we keep each delta in a separate folder beneath the upgrade utility we built. This utility signs onto the server, reads the version info and then applies the transform scripts from the next version in the database until it can find no more upgrade scripts in its sub folder. This gives us the ability upgrade a database no matter how old it is to the current version. If there are data transforms unique the tenant, thse are going to get tricky.
of course you should always make a backup of the databse that writes to an external file preferrrable with an human identifiable version number so you can find it and restore it when the script(s) go bad. And eventually it will so just plan on figuring out how to recover and restore.
--
I saw there is some sort of schema upgrader tool in the new VS 2010 but I havent used it. that might also be useful to you.
How do you handle a web application where there is a generic application user logging in to the SQL Server?
What about an instance where you have a corporate HR dept that can see everyone and then regional HR that should only see the employees in that location?