August 31, 2018 at 12:46 am
Good Morning Experts,
Here is the requirement from client: They have a server A and they want exact replica of server A called B. There are many users wanting to write on server A but it is unable to handle. So, the client wants some writes handled by server B. Is this possible in SQL Server. Please let me know
September 1, 2018 at 3:38 pm
I think based of your description it would be hard for anyone to offer any advice, i can ready your explanation in a few ways and give different answers for example
I could guess server A is an application server and it is the app server that can't handle the load therefore i would say yes you could load balance your application over may application servers. On the other hand i could guess you are referring server A as SQL server and can not handle the load and you want to split over 2 SQL servers, i would say no, i just cannot see how this could ever work.
I am making a judgement based of my interpretation of your question, without knowledge of your problem or environment etc.
***The first step is always the hardest *******
September 1, 2018 at 3:51 pm
SGT_squeequal - Saturday, September 1, 2018 3:38 PMI think based of your description it would be hard for anyone to offer any advice, i can ready your explanation in a few ways and give different answers for exampleI could guess server A is an application server and it is the app server that can't handle the load therefore i would say yes you could load balance your application over may application servers. On the other hand i could guess you are referring server A as SQL server and can not handle the load and you want to split over 2 SQL servers, i would say no, i just cannot see how this could ever work.
I am making a judgement based of my interpretation of your question, without knowledge of your problem or environment etc.
SQL Server A is unable to handle writes. Is there any way where we can route some write requests to another sql Server
September 1, 2018 at 7:57 pm
coolchaitu - Saturday, September 1, 2018 3:51 PMSGT_squeequal - Saturday, September 1, 2018 3:38 PMI think based of your description it would be hard for anyone to offer any advice, i can ready your explanation in a few ways and give different answers for exampleI could guess server A is an application server and it is the app server that can't handle the load therefore i would say yes you could load balance your application over may application servers. On the other hand i could guess you are referring server A as SQL server and can not handle the load and you want to split over 2 SQL servers, i would say no, i just cannot see how this could ever work.
I am making a judgement based of my interpretation of your question, without knowledge of your problem or environment etc.
SQL Server A is unable to handle writes. Is there any way where we can route some write requests to another sql Server
Have you attempted to figure out WHY SQL Server A is unable to handle the writes? Probably not. I'd bet credits to Navy beans that it's both a code issue and an index issue. In case you can't interpret what I just said, 99.99% of the time, it's a code/index issue and throwing hardware at the problem isn't going to fix that (and I've actually seen it make things worse). Only fixing the code/indexes is going to fix it because <insert drum roll sound here>... that's where the performance actually is.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2018 at 11:22 pm
Jeff Moden - Saturday, September 1, 2018 7:57 PMcoolchaitu - Saturday, September 1, 2018 3:51 PMSGT_squeequal - Saturday, September 1, 2018 3:38 PMI think based of your description it would be hard for anyone to offer any advice, i can ready your explanation in a few ways and give different answers for exampleI could guess server A is an application server and it is the app server that can't handle the load therefore i would say yes you could load balance your application over may application servers. On the other hand i could guess you are referring server A as SQL server and can not handle the load and you want to split over 2 SQL servers, i would say no, i just cannot see how this could ever work.
I am making a judgement based of my interpretation of your question, without knowledge of your problem or environment etc.
SQL Server A is unable to handle writes. Is there any way where we can route some write requests to another sql Server
Have you attempted to figure out WHY SQL Server A is unable to handle the writes? Probably not. I'd bet credits to Navy beans that it's both a code issue and an index issue. In case you can't interpret what I just said, 99.99% of the time, it's a code/index issue and throwing hardware at the problem isn't going to fix that (and I've actually seen it make things worse). Only fixing the code/indexes is going to fix it because <insert drum roll sound here>... that's where the performance actually is.
Because it is a e-commerce website like Amazon. There are millions of writes
September 2, 2018 at 2:04 am
coolchaitu - Saturday, September 1, 2018 11:22 PMJeff Moden - Saturday, September 1, 2018 7:57 PMcoolchaitu - Saturday, September 1, 2018 3:51 PMSGT_squeequal - Saturday, September 1, 2018 3:38 PMI think based of your description it would be hard for anyone to offer any advice, i can ready your explanation in a few ways and give different answers for exampleI could guess server A is an application server and it is the app server that can't handle the load therefore i would say yes you could load balance your application over may application servers. On the other hand i could guess you are referring server A as SQL server and can not handle the load and you want to split over 2 SQL servers, i would say no, i just cannot see how this could ever work.
I am making a judgement based of my interpretation of your question, without knowledge of your problem or environment etc.
SQL Server A is unable to handle writes. Is there any way where we can route some write requests to another sql Server
Have you attempted to figure out WHY SQL Server A is unable to handle the writes? Probably not. I'd bet credits to Navy beans that it's both a code issue and an index issue. In case you can't interpret what I just said, 99.99% of the time, it's a code/index issue and throwing hardware at the problem isn't going to fix that (and I've actually seen it make things worse). Only fixing the code/indexes is going to fix it because <insert drum roll sound here>... that's where the performance actually is.
Because it is a e-commerce website like Amazon. There are millions of writes
So, is there any load balancing in sqS Server
September 2, 2018 at 2:33 am
coolchaitu - Saturday, September 1, 2018 11:22 PMBecause it is a e-commerce website like Amazon. There are millions of writes
If you're dealing with the volumes of data Amazon are, then you need infrastructure far more complex than a single SQL Server and an entire system designed specifically to cope with that kind of load. Think through the problem a little bit. If different servers are handling the writes, then each individual server is going to have a different concept of what data looks like - each one would read the copies they individually have. So you'd have to start designing a distributed data design, with concepts of "eventual consistency", to handle the distribution of load. It's enormously complex and extremely expensive - there is a reason Amazon has one of the biggest "cloud" infrastructures in the world - are you sure you are really that big?
On the other hand, if you're dealing with a much smaller scale there are much more practical aspects you can consider. What is the overhead on any given write? Can you reduce that? What is the overhead on any given read? Can that be reduced? Can the READS be offloaded to secondary servers to reduce the load on the "point of truth" where writes occur? Millions of writes sounds like a lot, but it actually isn't. Understanding where the real performance problems and limits in your system are is a good start to figuring out the best approach to improving your ability to scale.
September 2, 2018 at 3:48 am
andycadley - Sunday, September 2, 2018 2:33 AMcoolchaitu - Saturday, September 1, 2018 11:22 PMBecause it is a e-commerce website like Amazon. There are millions of writesIf you're dealing with the volumes of data Amazon are, then you need infrastructure far more complex than a single SQL Server and an entire system designed specifically to cope with that kind of load. Think through the problem a little bit. If different servers are handling the writes, then each individual server is going to have a different concept of what data looks like - each one would read the copies they individually have. So you'd have to start designing a distributed data design, with concepts of "eventual consistency", to handle the distribution of load. It's enormously complex and extremely expensive - there is a reason Amazon has one of the biggest "cloud" infrastructures in the world - are you sure you are really that big?
On the other hand, if you're dealing with a much smaller scale there are much more practical aspects you can consider. What is the overhead on any given write? Can you reduce that? What is the overhead on any given read? Can that be reduced? Can the READS be offloaded to secondary servers to reduce the load on the "point of truth" where writes occur? Millions of writes sounds like a lot, but it actually isn't. Understanding where the real performance problems and limits in your system are is a good start to figuring out the best approach to improving your ability to scale.
Brother, ours is a very small startup e-commerce website. We are expecting hundreds of inserts, updates and deletes
September 2, 2018 at 5:58 am
If your DB server is not capable of taking the through put then you need to up the spec of the DB server. alternatively you need to go back to the person who developed your ecommerce site. what have you done to test the throughput and are you sure SQL server is the bottle neck and not something else??
***The first step is always the hardest *******
September 2, 2018 at 6:39 am
coolchaitu - Sunday, September 2, 2018 3:48 AMandycadley - Sunday, September 2, 2018 2:33 AMcoolchaitu - Saturday, September 1, 2018 11:22 PMBecause it is a e-commerce website like Amazon. There are millions of writesIf you're dealing with the volumes of data Amazon are, then you need infrastructure far more complex than a single SQL Server and an entire system designed specifically to cope with that kind of load. Think through the problem a little bit. If different servers are handling the writes, then each individual server is going to have a different concept of what data looks like - each one would read the copies they individually have. So you'd have to start designing a distributed data design, with concepts of "eventual consistency", to handle the distribution of load. It's enormously complex and extremely expensive - there is a reason Amazon has one of the biggest "cloud" infrastructures in the world - are you sure you are really that big?
On the other hand, if you're dealing with a much smaller scale there are much more practical aspects you can consider. What is the overhead on any given write? Can you reduce that? What is the overhead on any given read? Can that be reduced? Can the READS be offloaded to secondary servers to reduce the load on the "point of truth" where writes occur? Millions of writes sounds like a lot, but it actually isn't. Understanding where the real performance problems and limits in your system are is a good start to figuring out the best approach to improving your ability to scale.
Brother, ours is a very small startup e-commerce website. We are expecting hundreds of inserts, updates and deletes
That's not exactly the scale of Amazon and it's certainly not the millions of writes you previously claimed.
To be honest and definitely not trying to be hurtful here, you're not going to get what you need for load balancing from an SQL Server forum. You need to get someone that knows how to do this and does it professionally involved either on site or via remote.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2018 at 6:46 am
Jeff Moden - Sunday, September 2, 2018 6:39 AMcoolchaitu - Sunday, September 2, 2018 3:48 AMandycadley - Sunday, September 2, 2018 2:33 AMcoolchaitu - Saturday, September 1, 2018 11:22 PMBecause it is a e-commerce website like Amazon. There are millions of writesIf you're dealing with the volumes of data Amazon are, then you need infrastructure far more complex than a single SQL Server and an entire system designed specifically to cope with that kind of load. Think through the problem a little bit. If different servers are handling the writes, then each individual server is going to have a different concept of what data looks like - each one would read the copies they individually have. So you'd have to start designing a distributed data design, with concepts of "eventual consistency", to handle the distribution of load. It's enormously complex and extremely expensive - there is a reason Amazon has one of the biggest "cloud" infrastructures in the world - are you sure you are really that big?
On the other hand, if you're dealing with a much smaller scale there are much more practical aspects you can consider. What is the overhead on any given write? Can you reduce that? What is the overhead on any given read? Can that be reduced? Can the READS be offloaded to secondary servers to reduce the load on the "point of truth" where writes occur? Millions of writes sounds like a lot, but it actually isn't. Understanding where the real performance problems and limits in your system are is a good start to figuring out the best approach to improving your ability to scale.
Brother, ours is a very small startup e-commerce website. We are expecting hundreds of inserts, updates and deletes
That's not exactly the scale of Amazon and it's certainly not the millions of writes you previously claimed.
To be honest and definitely not trying to be hurtful here, you're not going to get what you need for load balancing from an SQL Server forum. You need to get someone that knows how to do this and does it professionally involved either on site or via remote.
Thanks Jeff..curious to know SQL Server has load balancing feature
September 2, 2018 at 7:10 am
coolchaitu - Sunday, September 2, 2018 6:46 AMThanks Jeff..curious to know SQL Server has load balancing feature
There are multiple systems such as Mirroring, Clustering and Always On Availabilty Groups. Given the scale you're talking about though, the only real reason to worry about such things is for resilience rather than performance. And if your site is small enough that you can manage a suitable maintenance window without being too worried about downtime, even that may frankly be overkill. The key thing is to focus on what you actually need and not get bogged down with things that you could possibly do (or even things that you might need to consider later as your site becomes successful).
Almost every step-up in resilience comes with a cost in terms of licensing requirements and it's often better to worry about it when you have a better grasp on where you're going and what kinds of issues you're likely to face. Which is not to say ignore it till everything fails, but knowing where your system struggles will guide you in the right direction (which might well be adding web caching rather than SQL capacity for example).
September 2, 2018 at 7:16 am
coolchaitu - Sunday, September 2, 2018 6:46 AMJeff Moden - Sunday, September 2, 2018 6:39 AMcoolchaitu - Sunday, September 2, 2018 3:48 AMandycadley - Sunday, September 2, 2018 2:33 AMcoolchaitu - Saturday, September 1, 2018 11:22 PMBecause it is a e-commerce website like Amazon. There are millions of writesIf you're dealing with the volumes of data Amazon are, then you need infrastructure far more complex than a single SQL Server and an entire system designed specifically to cope with that kind of load. Think through the problem a little bit. If different servers are handling the writes, then each individual server is going to have a different concept of what data looks like - each one would read the copies they individually have. So you'd have to start designing a distributed data design, with concepts of "eventual consistency", to handle the distribution of load. It's enormously complex and extremely expensive - there is a reason Amazon has one of the biggest "cloud" infrastructures in the world - are you sure you are really that big?
On the other hand, if you're dealing with a much smaller scale there are much more practical aspects you can consider. What is the overhead on any given write? Can you reduce that? What is the overhead on any given read? Can that be reduced? Can the READS be offloaded to secondary servers to reduce the load on the "point of truth" where writes occur? Millions of writes sounds like a lot, but it actually isn't. Understanding where the real performance problems and limits in your system are is a good start to figuring out the best approach to improving your ability to scale.
Brother, ours is a very small startup e-commerce website. We are expecting hundreds of inserts, updates and deletes
That's not exactly the scale of Amazon and it's certainly not the millions of writes you previously claimed.
To be honest and definitely not trying to be hurtful here, you're not going to get what you need for load balancing from an SQL Server forum. You need to get someone that knows how to do this and does it professionally involved either on site or via remote.
Thanks Jeff..curious to know SQL Server has load balancing feature
As far as I know the load balancing available does not move the writes to multiple servers - writes always go to the primary replica - reads can go to secondary read only replicas.
There may be ways of doing it for writes - but from what I have read (no real experience on it) it will require significant design and code changes. See for example https://dba.stackexchange.com/questions/200189/is-it-possible-to-scale-out-with-microsoft-sql-server and also https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning for data partitioning.
As for volume of writes - I would not expect write (update) conflicts other than updating available stock - everything else should be atomic and never concurrent to other users (why would another user have to update the records that relate to my order?) so with correct design in advance and proper hardware you should be ok.
September 2, 2018 at 7:52 am
coolchaitu - Sunday, September 2, 2018 6:46 AMJeff Moden - Sunday, September 2, 2018 6:39 AMcoolchaitu - Sunday, September 2, 2018 3:48 AMandycadley - Sunday, September 2, 2018 2:33 AMcoolchaitu - Saturday, September 1, 2018 11:22 PMBecause it is a e-commerce website like Amazon. There are millions of writesIf you're dealing with the volumes of data Amazon are, then you need infrastructure far more complex than a single SQL Server and an entire system designed specifically to cope with that kind of load. Think through the problem a little bit. If different servers are handling the writes, then each individual server is going to have a different concept of what data looks like - each one would read the copies they individually have. So you'd have to start designing a distributed data design, with concepts of "eventual consistency", to handle the distribution of load. It's enormously complex and extremely expensive - there is a reason Amazon has one of the biggest "cloud" infrastructures in the world - are you sure you are really that big?
On the other hand, if you're dealing with a much smaller scale there are much more practical aspects you can consider. What is the overhead on any given write? Can you reduce that? What is the overhead on any given read? Can that be reduced? Can the READS be offloaded to secondary servers to reduce the load on the "point of truth" where writes occur? Millions of writes sounds like a lot, but it actually isn't. Understanding where the real performance problems and limits in your system are is a good start to figuring out the best approach to improving your ability to scale.
Brother, ours is a very small startup e-commerce website. We are expecting hundreds of inserts, updates and deletes
That's not exactly the scale of Amazon and it's certainly not the millions of writes you previously claimed.
To be honest and definitely not trying to be hurtful here, you're not going to get what you need for load balancing from an SQL Server forum. You need to get someone that knows how to do this and does it professionally involved either on site or via remote.
Thanks Jeff..curious to know SQL Server has load balancing feature
IMHO, by itself, it actually doesn't. Yes, you can spread out the database between multiple servers in multiple different manners but the front end web servers are going to need something to balance the traffic. That's probably even more important for a start up than anything else.
My recommendation would be to build a good SQL Server equipped with enough memory (critical) and enough CPUs (fairly critical) along with the right code and proper database design with the idea that someday in the future, you'll need to scale out. SSDs will certainly help but even those kinds of systems can be crippled pretty easily by just a couple of pieces of bad code and seriously lamed but a continuous din of background noise caused by other code (also known as "Death by a Thousand Cuts"). If you do believe that you're going to have the same needs as Amazon, then you're going to need to get some serious help. If you're a startup and you don't know how to do it or exactly what you need, you need less serious help but you still need help if you want the company to succeed. It may be in the form of consultants, FTEs, or a combination of the two but get some help.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2018 at 8:02 am
Jeff Moden - Sunday, September 2, 2018 7:52 AMcoolchaitu - Sunday, September 2, 2018 6:46 AMJeff Moden - Sunday, September 2, 2018 6:39 AMcoolchaitu - Sunday, September 2, 2018 3:48 AMandycadley - Sunday, September 2, 2018 2:33 AMcoolchaitu - Saturday, September 1, 2018 11:22 PMBecause it is a e-commerce website like Amazon. There are millions of writesIf you're dealing with the volumes of data Amazon are, then you need infrastructure far more complex than a single SQL Server and an entire system designed specifically to cope with that kind of load. Think through the problem a little bit. If different servers are handling the writes, then each individual server is going to have a different concept of what data looks like - each one would read the copies they individually have. So you'd have to start designing a distributed data design, with concepts of "eventual consistency", to handle the distribution of load. It's enormously complex and extremely expensive - there is a reason Amazon has one of the biggest "cloud" infrastructures in the world - are you sure you are really that big?
On the other hand, if you're dealing with a much smaller scale there are much more practical aspects you can consider. What is the overhead on any given write? Can you reduce that? What is the overhead on any given read? Can that be reduced? Can the READS be offloaded to secondary servers to reduce the load on the "point of truth" where writes occur? Millions of writes sounds like a lot, but it actually isn't. Understanding where the real performance problems and limits in your system are is a good start to figuring out the best approach to improving your ability to scale.
Brother, ours is a very small startup e-commerce website. We are expecting hundreds of inserts, updates and deletes
That's not exactly the scale of Amazon and it's certainly not the millions of writes you previously claimed.
To be honest and definitely not trying to be hurtful here, you're not going to get what you need for load balancing from an SQL Server forum. You need to get someone that knows how to do this and does it professionally involved either on site or via remote.
Thanks Jeff..curious to know SQL Server has load balancing feature
IMHO, by itself, it actually doesn't. Yes, you can spread out the database between multiple servers in multiple different manners but the front end web servers are going to need something to balance the traffic. That's probably even more important for a start up than anything else.
My recommendation would be to build a good SQL Server equipped with enough memory (critical) and enough CPUs (fairly critical) along with the right code and proper database design with the idea that someday in the future, you'll need to scale out. SSDs will certainly help but even those kinds of systems can be crippled pretty easily by just a couple of pieces of bad code and seriously lamed but a continuous din of background noise caused by other code (also known as "Death by a Thousand Cuts"). If you do believe that you're going to have the same needs as Amazon, then you're going to need to get some serious help. If you're a startup and you don't know how to do it or exactly what you need, you need less serious help but you still need help if you want the company to succeed. It may be in the form of consultants, FTEs, or a combination of the two but get some help.
Thanks Jeff. Happy long weekend
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply