Schema dilemma

Question

Schema dilemma

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

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

drahcir.nivaled SSC Enthusiast Points: 173 More actions · Answer 1

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?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

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)

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

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

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 4

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...

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

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

drahcir.nivaled SSC Enthusiast Points: 173 More actions · Answer 7

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..