[dba-SQLServer] Schemas for separation of clients?

[Mark Breen]

Hello Gustav

I have played with RDS but mostly I use ec2.  For you I was suggesting ec2
and SQL express.  Then you can have multiple dB's.

Mark
On 30 Mar 2015 16:28, "Gustav Brock" <gustav at cactus.dk> wrote:

> Hi Mark
>
> Interesting. We've used AWS for years, but for storage only, so I was not
> up-to-date with their RDS offerings. It seems like it could be well suited
> for my purpose.
>
> I had to update our login options, and that seems for some reason to be a
> major task as they claim it can take up to 24 hours before settled - and
> until then no RDS service. I have to turn on some patience ...
>
> /gustav
>
> -----Oprindelig meddelelse-----
> Fra: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] På
> vegne af Mark Breen
> Sendt: 30. marts 2015 05:46
> Til: Discussion concerning MS SQL Server
> Emne: Re: [dba-SQLServer] Schemas for separation of clients?
>
> Hello Gustav
>
> I have been using aws for two years now and love it.  Amazon have some new
> micro machines (t2) that are really cheap and yet have some power.  Their
> costs are low enough yo consider for low budget projects yet offer all the
> quality of true best in class cloud.
>
> I usually do all my setting up on an enormous machine, then shutdown and
> switch to a micro instance and restart.  That way I get to deal with great
> performance when in rdp and once I am done I pay pennies per day.
>
> For me the absolute cost is secondary to the almost 100% likelihood my
> hardware will never fail.  This reliability is what u am really buying.
>
> As an aside, I have automated all my daily backups and transferred them
> all off machine to Amazon s3.
>
> Hth
> Mark
>  On 30 Mar 2015 02:42, "Gustav Brock" <gustav at cactus.dk> wrote:
>
> > Hi Mark
> >
> > Good points. The added precautions and potential issues may very well
> > not be more "expensive" than the little money saved.
> >
> > /gustav
> >
> > ________________________________________
> > Fra: dba-SQLServer <dba-sqlserver-bounces at databaseadvisors.com> på
> > vegne af Mark Breen <marklbreen at gmail.com>
> > Sendt: 29. marts 2015 19:58
> > Til: Discussion concerning MS SQL Server
> > Emne: Re: [dba-SQLServer] Schemas for separation of clients?
> >
> > 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
> > >
> > > ________________________________________
> > > 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
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>