May 21, 2015 at 1:02 pm
I have 5 TB single database, we have reached to a point where we cannot scale up anymore, only option is scale out. Would like to know what experts say and how others have worked on it. One simple solution would be to have per client database but then in my case we are talking about 2000 + databases. Thanks in advance.
May 22, 2015 at 1:07 am
Immensely, incredibly, horribly difficult. There's no easy way to scale SQL out, just lots and lots and lots of work.
Consider, if you can split clients among different DBs, put groups of clients per DB rather than 1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2015 at 8:07 pm
curious_sqldba (5/21/2015)
I have 5 TB single database, we have reached to a point where we cannot scale up anymore, only option is scale out. Would like to know what experts say and how others have worked on it. One simple solution would be to have per client database but then in my case we are talking about 2000 + databases. Thanks in advance.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2015 at 8:07 pm
curious_sqldba (5/21/2015)
I have 5 TB single database, we have reached to a point where we cannot scale up anymore, only option is scale out. Would like to know what experts say and how others have worked on it. One simple solution would be to have per client database but then in my case we are talking about 2000 + databases. Thanks in advance.
Let's first establish why you think that you can't scale up anymore.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2015 at 2:29 am
I agree with Gail here, if the system is not designed for it, implementing out-scaling will be difficult at the best. Jeff taps on a very valuable point, why is up-scaling not an option or in fact, is it needed. How much of the data is "active", "inactive" or "residual"? Can part of the functionality be offloaded to another system (Data Marts, Historics etc.)?
😎
May 25, 2015 at 6:44 am
I didn't necessarily buy all of it but I did see an MS product manager give a fairly decent presentation on how Azure could be used in a massive multi-tenant style scenario such as you described.
Basically the presentation went along the lines of saying that managing a massive scale out operation on prem becomes incredibly difficult at scale because you have to worry about all the HA/DR, provisioning, infrastructure and so on. His point was basically that you could use azure powershell or other API's to spin up and destroy instances on demand and let MS worry about some of the more tedious aspects.
As I say - I wasn't entirely convinced by it all - not least of all because I'm a control freak and I like my critical apps running where I can get to them. That said, he did have some interesting points on infrastructure, availability and scripting that were specific to multi-tenant scenarios so I just thought I'd throw it out there.
He was fairly coy on how much all of this would cost mind 😉
S
May 25, 2015 at 12:11 pm
simon.harvey 41170 (5/25/2015)
I didn't necessarily buy all of it but I did see an MS product manager give a fairly decent presentation on how Azure could be used in a massive multi-tenant style scenario such as you described.Basically the presentation went along the lines of saying that managing a massive scale out operation on prem becomes incredibly difficult at scale because you have to worry about all the HA/DR, provisioning, infrastructure and so on. His point was basically that you could use azure powershell or other API's to spin up and destroy instances on demand and let MS worry about some of the more tedious aspects.
As I say - I wasn't entirely convinced by it all - not least of all because I'm a control freak and I like my critical apps running where I can get to them. That said, he did have some interesting points on infrastructure, availability and scripting that were specific to multi-tenant scenarios so I just thought I'd throw it out there.
He was fairly coy on how much all of this would cost mind 😉
S
There could be a whole lot of hidden costs as in rewriting stuff that no longer works.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2015 at 2:47 pm
There could be a whole lot of hidden costs as in rewriting stuff that no longer works.
Too true - I got the impression that even the SAAS costs alone would be enough to make your eyes water. As you say, then there would be potentially very large app modifications required as well.
If you're the sort of company that has a 5TB database though, perhaps all options are on the table.
S
May 25, 2015 at 3:03 pm
simon.harvey 41170 (5/25/2015)
There could be a whole lot of hidden costs as in rewriting stuff that no longer works.
Too true - I got the impression that even the SAAS costs alone would be enough to make your eyes water. As you say, then there would be potentially very large app modifications required as well.
If you're the sort of company that has a 5TB database though, perhaps all options are on the table.
S
Quick thought, 5Tb can easily be month's or a week's worth of data, regardless of the company's size, it however makes our jobs "slightly" more challenging.
The SAAS side of the coin (Salesforce??) often looks expensive but thinking of a simple thing like a Big Mac, £2.29 / $3.57 (note: there must be more people realizing the shooting irony of those numbers), you want to make your own then it's easy to do and doesn't cost but a fraction of the price but when maintaining the availability the cost piles up.
😎
May 25, 2015 at 6:18 pm
simon.harvey 41170 (5/25/2015)
There could be a whole lot of hidden costs as in rewriting stuff that no longer works.
Too true - I got the impression that even the SAAS costs alone would be enough to make your eyes water. As you say, then there would be potentially very large app modifications required as well.
If you're the sort of company that has a 5TB database though, perhaps all options are on the table.
S
Heh... or is a company in serious need of learning how to archive data. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2015 at 4:13 am
Immensely, incredibly, horribly difficult. There's no easy way to scale SQL out, just lots and lots and lots of work.
From your posts, seems like scale-up is costly, and scale-out is unsustainable.
Have you tried using PartitionDB[/url]?
I have had the same problem, using SQL Express and wishing to scale-out with my SaaS application. When I had one client it was pretty easy, but when my business grew, I understood that I cannot keep on working with snapshots to store my metadata structure and how do I upgrade all my clients at once? I looked out for sharding and other technologies out there, while we had a big debate about whether or not to stay in SQL Server environment.
last we found PartitionDB, which was perfect for us, and now we are on our testing phase. It's a great solution for scale-out SQL Server instance, and they support wide range of features, while gives the look&feel of one database. I strongly recommend you test it out.
October 11, 2015 at 6:31 am
jonjonben (10/11/2015)
Immensely, incredibly, horribly difficult. There's no easy way to scale SQL out, just lots and lots and lots of work.
From your posts, seems like scale-up is costly, and scale-out is unsustainable.
Have you tried using PartitionDB[/url]?
I have had the same problem, using SQL Express and wishing to scale-out with my SaaS application. When I had one client it was pretty easy, but when my business grew, I understood that I cannot keep on working with snapshots to store my metadata structure and how do I upgrade all my clients at once? I looked out for sharding and other technologies out there, while we had a big debate about whether or not to stay in SQL Server environment.
last we found PartitionDB, which was perfect for us, and now we are on our testing phase. It's a great solution for scale-out SQL Server instance, and they support wide range of features, while gives the look&feel of one database. I strongly recommend you test it out.
That may help but SQL Express was never meant to support what you're using it for. Instead of buying 3rd software, why not just migrate to SQL Server Standard to scale up?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2015 at 7:03 am
GilaMonster (5/22/2015)
Immensely, incredibly, horribly difficult. There's no easy way to scale SQL out, just lots and lots and lots of work.Consider, if you can split clients among different DBs, put groups of clients per DB rather than 1
That would be my suggestion without knowing more about how the data is being stored and used.
EDW -> Data Marts -> App
October 11, 2015 at 7:29 am
Hi Jeff,
I wasn't suggesting staying in SQL Express. I had a similar issue, with SQL 2012, and partitiondb seems to work for me (ah, and it's free).
A better RDBMS engine will surely boost your performance as you can use special indexing, partitioning, better hardware support.
Additional, SQL Express does not support some of the unique backup-restore & replication methods SQL offers today, as well as some of the management GUI tools.
In my case, the engine wasn't the issue, I just see an issue with SaaS programming on centralized database and looking for the best solution SQL has to offer me.
As more and more clients come and go, It is very hard to add, support & maintain those clients on a single database instance. Not to mention, that each client have different behavior (concurrency & data growth). Some of the clients, on the long run, may cause downtime or lock of the overall system and because data is not shared, isolation is the only solution; from all aspects - performance, security, scalability and high-availability. Now comes the tricky part of maintenance & upgrades.
How will you best architect such requirement?
October 11, 2015 at 8:16 am
jonjonben (10/11/2015)
How will you best architect such requirement?
"It Depends". I'd have to know a whole lot more than what's been offered on this post, so far. I believe that would be true in your case, as well. 😉 I can tell you that we currently have a system that supports several hundred "clients" that come and go, as you say. We didn't need the bit o' magic that you speak of, even for the large batch processing that we also do for each client.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply