[dba-SQLServer] Schemas for separation of clients?

[fhtapia at gmail.com]

Are you referring to the dataset just for a schema?

one method would be to do it via using MSSMS and scripting out both the
schema and data:
http://stackoverflow.com/questions/2254878/is-there-a-way-to-export-an-entire-sql-server-database-schema-and-data-to-te

On Tue, Mar 31, 2015 at 10:23 AM Gustav Brock <gustav at cactus.dk> wrote:

> Thanks Francisco, I'll mark that.
>
> Next question is if you can manage backup and restore of individual
> schemas - if perhaps not easily then, at least, reliably?
>
> /gustav
>
> -----Oprindelig meddelelse-----
> Fra: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] På
> vegne af fhtapia at gmail.com
> Sendt: 31. marts 2015 15:42
> Til: Discussion concerning MS SQL Server
> Emne: Re: [dba-SQLServer] Schemas for separation of clients?
>
> Hello Mark,
>   If the intent is to provide a segregated security space for multiple
> users, then Schema's offers a huge advantage.  They are simply containers
> to an entire database set per grouping.
>
> It is much simpler to set security based on the schema, than to focus on
> the individual db Table.  not to mention, the peril of accidentally
> returning data back to a customer that is not their own because the
> query/sproc had access to any data in the table.
>
> more solid points:
>
> http://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-
> schemas-in-sql-server/
>
> --
> Francisco
>
> On Sun, Mar 29, 2015 at 10:59 AM Mark Breen <marklbreen at gmail.com> wrote:
>
> > Hello Gustav
> >
> > I would not take this approach for a few reasons.
> >
> > Before I start however, I see no technical reason why you cannot do
> > it.  In fact you could even keep one schema and just set permission s
> > at dB table level.  I only used multiple schemas one time and found it a
> pita.
> >
> > 1 reason is such complexity for small financial benefit.  You are
> > going against the normal use and it will be a struggle sooner or later.
> >
> > 2 security will sooner or later leak across the tables.  You goal is
> > segregation but you are putting everything in one dB and hoping nobody
> > makes a mistake in a year or two.
> >
> > 3 financially, the gain is not sufficient to justify the hoops you
> > have to jump through.  Unless the customer is paying pennies.  If the
> > budget is so low to force this approach then reconsider and use a
> > cloud VM and install SQL express and have separate dB's for everyone.
> >
> > With aws and azure so cheap I would avoid the unconventional dB
> > storage if possible.
> >
> > Hth
> >
> > Mark
> >  On 29 Mar 2015 22:42, "Gustav Brock" <gustav at cactus.dk> wrote:
> >
> > > 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
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>