[dba-SQLServer] Schemas for separation of clients?

[Gustav Brock]

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