[dba-SQLServer] Schemas for separation of clients?

[Gustav Brock]

Hi Arthur

The main reason is, that at Azure you don't pay by the instance but by the database. The cheapest is B Basic at about USD 5 per month which is not bad but, obviously, if that can serve perhaps 50 clients' data it's even better. An even better deal - given low usage - is the S0 Standard level at about USD 14 per month for 250 GB database Space.

As for the linking it should be quite easy; by default, a dbo.mytable is linked as dbo_mytable which you normally rename locally to mytable. So, when linking, you should link to client1.mytable, which will be given the name client1_mytable, which you rename to mytable. From that on, your application knows absolutely nothing about the tables of schema client2 etc.

I cannot see why it shouldn't work, but I'm not a dba, indeed not regarding Azure SQL, so I'm looking for possible pitfalls.

/gustav

________________________________________
Fra: dba-SQLServer <dba-sqlserver-bounces at databaseadvisors.com> på vegne af Arthur Fuller <fuller.artful at gmail.com>
Sendt: 29. marts 2015 15:13
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] Schemas for separation of clients?

Gustav,

I don't have a precise answer for you. It's common practice in SQL Server
to create "families" of tables; the AdventureWorks sample databases are
show this ability off. But I have never tried to link to such a database
from Access, so can't even guess how the table-names would appear in
Access,

It would seem that you are imposing a task on your potential users -- all
that renaming. I'm also short on knowledge about Azure, but despite that, I
must ask, Why not create one database for each user? Would that setup
violate some constraint imposed by Azure's billing method?

/Arthur

On Sun, Mar 29, 2015 at 6:40 AM, Gustav Brock <gustav at cactus.dk> wrote:

> Hi all
>
> Is it advisable and possible to create custom schemas in one database to
> to hold otherwise identical tables separated for each client?
>
> For example, two tables:
>
>     client1.invoice
>     client2.invoice
>
> Then assign rights for client 1 to schema client1 only, and vice-versa for
> client2.
>
> When linking the tables from Access, client1 would see only one set of
> tables, link these, and name them locally using the last part of the name
> only: invoice.
>
> Would that be totally safe, so one client would never be able to see data
> belonging to other schemas?
>
> The reason for asking is, that the current minimum size offered at Azure
> (B Basic) is 2 GB which for the application, I have in mind, could hold
> data for at least 25 clients. And the next step, S0 Standard, allows as
> much as 250 GB. Of course, the DTUs also Counts but usage pr. client is
> expected to be low.
>
> /gustav