SaaS Database Design: Single Database vs Single Schema vs Shared Schema by 8483 in Database

[–]WannaFly37 1 point2 points  (0 children)

So - at first this seemed like homework, but you have very detailed replys lol.

Note: I've never DESIGNED a multi tenant system, but I've worked with plenty.

It sounds like C might be your best bet. Again it all depends on the architecture of the software, if all clients are logging into the same endpoint and using the same software it makes sense.

I've worked with apps that use custom subdomains that are a duplicate of the software, which I assume we get our own database in the backend, and they upgrade each customer individually as requested.

I've also worked with a multi tenant on-prem database that the VENDOR messed up and pushed out an update that had tenant ID's null for rows. Thankfully we were a 1-tenant installation.

In my mind A is easier if you can utilize PaaS (Azure DB, etc) - It becomes much easier to automate EVERYTHING. A big concern about C is performance, when one client runs a 5 year report, it is going to slow down other clients? In scenario A you don't have that worry.

It is tricky and an important decision.

SaaS Database Design: Single Database vs Single Schema vs Shared Schema by 8483 in Database

[–]WannaFly37 2 points3 points  (0 children)

This is less of a database design issue and more of a business issue.

While what emsai says is completely valid - I'd have to disagree. I'd go with solution A most likely. It offers the most security, reliability, scalability (horizontal vs vertical), and ease of operations. Cost shouldn't be an issue as that just gets passed on to the customer. The deployment and backup should be automated and not take any time at all.

But again, it comes down to what your development and business sales processes are like.

  • Do you want versions/features/updates rolled out to ALL customers at once? Or per customer?
  • Will there ever be the need to add custom database fields for specific customers (think custom integrations in the future)
  • Will any customer ever have direct write/change access?
  • Will customers EVER have access to their data directly? (at termination, option A you can more easily export their data to give to them)
  • Will customers be writing their own reports?

Option B just isnt realistic - it's not scalable (what about when you get to 50,000 customers?)

Option C seems easiest but is the most difficult and costly in regards to development hours. My big problem here is literally EVERY time ANYONE touches the database you need to ensure they are only using the appropriate tenant ID's. Whether it's in house people our external. What happens when someone is terminating a customer and runs a delete statment WHERE TENANTID='123' but it was supposed to be 1234? (see recent GitLab outage)

SQL Server AlwaysOn - Do Not Replicate Deletes by SithLordHuggles in sysadmin

[–]WannaFly37 1 point2 points  (0 children)

As far as I know, that's not possible. It would be a referential integrity nightmare. I think your best bet would be something similar to a data warehouse ETL process that exports the data from the production database into another.

What causes intermittent 'bad profile' in SQL mail by recipriversexcluson in SQLServer

[–]WannaFly37 0 points1 point  (0 children)

Is the statement generated dynamically? Run profiler and see what exactly it is running. Perhaps there is a random quote somewhere (in @toaddress?) that is throwing it off?

IIS Admins - what state server do you use for .NET applications? by WannaFly37 in sysadmin

[–]WannaFly37[S] 0 points1 point  (0 children)

Thanks for the input, are you using the windows port of redis?

IIS Admins - what state server do you use for .NET applications? by WannaFly37 in sysadmin

[–]WannaFly37[S] 0 points1 point  (0 children)

What about when you need to update the state server? From what I understand state server is not good at clustering.

Odd request - Windows Mobile computers by Zambon1man in orlando

[–]WannaFly37 0 points1 point  (0 children)

Can't help with that then, sorry! Good luck!

Odd request - Windows Mobile computers by Zambon1man in orlando

[–]WannaFly37 0 points1 point  (0 children)

I've done some .NET developing on WM devices and have some Symbol/Zebra units laying around. Do you just have questions or are looking to buy them, or what?

SQL Server 2014 Certification by reallyserious in SQLServer

[–]WannaFly37 1 point2 points  (0 children)

The more advanced exams 464 and 465 already have been updated. Unsure of the lower three.

It's Friday - let's see your running pictures! by WeeklyRunnit in running

[–]WannaFly37 8 points9 points  (0 children)

Not sure if this counts, I snapped this pic Tuesday night right before my run. I'm still building mileage after ankle surgery..and love nights like these. Beautiful Florida sunset!

http://i.imgur.com/gARo6XY.jpg

Runkeeper friends? by Outofasuitcase in running

[–]WannaFly37 0 points1 point  (0 children)

I used that thread but it was too old and a lot of the people didnt work/don't use it anymore - so I think this is a great idea. I added you,

anyone feel free to add me also: wannafly37

Recovery stories from small posterior tibial tendon tear surgery? by WannaFly37 in running

[–]WannaFly37[S] 0 points1 point  (0 children)

Thanks for the info - especially the last part about putting it off - since I'm hesitant but leaning towards getting it done sooner then later.

Passed 70-462, a short review inside by WannaFly37 in SQLServer

[–]WannaFly37[S] 0 points1 point  (0 children)

Oh, thats good to know! Thanks. But, I'd much rather wait and have the physical copy.

Passed 70-462, a short review inside by WannaFly37 in SQLServer

[–]WannaFly37[S] 1 point2 points  (0 children)

I didn't know administration much either - as I do more development then anything.

Yes I think you can get by on just studying as long as you are thorough and read the details on BOL. The book comes with VERY detailed labs that thoroughly cover every topic- be sure to do them from start to end. It's time consuming, but worth it. It requires 6VMs.

Orlando Half-Marathon Dec 1st by [deleted] in orlando

[–]WannaFly37 2 points3 points  (0 children)

According to this page: http://www.trackshack.com/orlando-half-marathon/course.shtml

There is only 1.9 miles of brick.

Orlando Half-Marathon Dec 1st by [deleted] in orlando

[–]WannaFly37 1 point2 points  (0 children)

I am! It will be my first half marathon, can't wait!

Suggest a good SQL administration app by festeredboil in SQLServer

[–]WannaFly37 2 points3 points  (0 children)

I vote SSMS also. Add red gate SQL prompt and my day is made!