August 6, 2014 at 3:25 am
Hello,
is it possible to create a cluster configuration, where 2 (or more) SQL Server instances running on different servers will simultaneously serve the same database attached to the same storage?
Something like this:
Instance A Instance B Instance C
(active) (active) (active)
\ | /
\ | /
\ | /
SAN for Database and Quorum
The point would be to improve reliability and performance at the same time. All nodes would share load. If a node fails, the other nodes still work and take over the load.
I know a failover cluster can be done, where 1 instance is active and others are passive (active/passive) which improves reliability, but in this configuration just 1 instance is serving at the same time.
I have searched for a while, but haven't found anything suggesting a "yes we can".
(As far as I understand, the active/active configuration is meant to run different databases on 2 (or more) instances such that the databases on a failing node are taken over by any other instances in the cluster.)
Any info / advise is much appreciated 🙂
Thx and Regards,
Protix.
August 6, 2014 at 3:27 am
No, it is not.
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
August 6, 2014 at 3:49 am
Thx,
that was to the point 😉
August 6, 2014 at 10:35 pm
Whilst you cannot use the same database, you may be able to distribute some of your load onto other servers using read only replicas - part of Availability Groups.
i.e. if you can isolate queries that only read data, you can use a connection to an availability group for that purpose with queries that change data using a different connection.
Have a look at http://msdn.microsoft.com/en-au/library/hh710054.aspx for more information.
August 17, 2014 at 3:15 pm
Interesting idea, sounds very promising. I will look into that definitely.
Kind regards and thx for replying.
August 17, 2014 at 4:24 pm
protix (8/6/2014)
Hello,is it possible to create a cluster configuration, where 2 (or more) SQL Server instances running on different servers will simultaneously serve the same database attached to the same storage?
Gail is right, no way you can have more than one instance of SQL Server accessing a physical instance of a database. If you consider "more than one instance of SQL Server running on different servers with an instance of an original/master/copy database", then there are few options. In fact, this is a fairly straight forward load balancing issue.
😎
August 18, 2014 at 6:29 am
If you feel you need the level of performance that such a configuration can give you, you need to look at DB2.
DB2 has had a shared disk architecture for mainframe instances for about 18 years, and for Windows/*nix instances for about 3 years. It works well, both for your performance and the IBM bank balance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 18, 2014 at 2:28 pm
@Erikur Eiriksson
Do you mean a kind of solution where 2 (or more) instances work on different physical storages, which are synchronized to hold the same data.
Like this?
Instance A Instance B Instance C
(active) (active) (active)
| | |
| | |
| | |
Storage X ------ Storage Y ------ Storage Z
I'm not sure, where it is correct to draw the horizontal mirroring lines, between the storages or between the instances...
Btw. is this called mirroring?
Kind regards and thx.
August 18, 2014 at 2:41 pm
EdVassie (8/18/2014)
If you feel you need the level of performance that such a configuration can give you, you need to look at DB2.DB2 has had a shared disk architecture for mainframe instances for about 18 years, and for Windows/*nix instances for about 3 years. It works well, both for your performance and the IBM bank balance.
Well, I'm interested in the performance mainly. But if it works I don't mind the IBM bank balance either 😉
Btw, if you had the choice, what would you prefer: Windows or *nix?
Regards and thx for replying.
August 18, 2014 at 6:52 pm
protix (8/18/2014)
Well, I'm interested in the performance mainly.
If that's true, then look into the code. SQL Server is VERY well equipped to handle some very high loads. Bad code can even bring DB2 to its knees.
To give you some incentive, I just repaired a bit of code for a client of mine. The performance improvement was an overall 2,000X decrease in CPU useage and more than a 2,700X decrease in both duration and memory I/O.
Until you actually know WHAT the performance problem is, save your money because even if such a configuration as what you originally mentioned were possible, it would most likely (and I do mean MOST!) wouldn't have helped much at all. Even if it did, unless you buy a moster MPP appliance, it's not likely that you're going to get the kind of performance improvement that I got by reworking the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2014 at 2:27 am
Jeff is right about the capabilities of SQL Server, there are very few workloads that it can't handle.
If you are looking at building a new system for a new and large application, then sizing what servers you need can be very difficult. Choosing the wrong technology for implementation can be a costly mistake, either you pay way too much or simply cannot get the work done on what you have purchased. A public forum such as this can give advice on specific issues, but is not the place to tell you how to design a big system.
If you think you may need more than $100k of CPU capacity, then you really should be getting a good consultancy organisation involved. If you are UK based, then Coeo are about the best for SQL Server, and if your wet finger in the air says you need DB2 then Triton are the people you should talk to.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 19, 2014 at 3:47 am
protix (8/18/2014)
Well, I'm interested in the performance mainly.
How many transactions/sec are you talking here? What hardware? What are your current bottlenecks?
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
August 20, 2014 at 1:53 am
Jeff Moden (8/18/2014)
If that's true, then look into the code. SQL Server is VERY well equipped to handle some very high loads. Bad code can even bring DB2 to its knees.
Yeah, completely agree. This is what I would like to do and what is most promising. Code and DB design is what I think I'm reasonably well at. Problem is I haven't acces to the code yet, neither application nor DB :ermm:
Big project, lots of companies involved, hiding stuff from each other...
August 20, 2014 at 2:06 am
EdVassie (8/19/2014)
Jeff is right about the capabilities of SQL Server, there are very few workloads that it can't handle....
A public forum such as this can give advice on specific issues, but is not the place to tell you how to design a big system.
Thx for the advice. System is already designed and hardware already is > $100k. Actually I'm not the guy in the team to design the hardware or the server infrastructure. Just personal interest.
August 20, 2014 at 2:33 am
GilaMonster (8/19/2014)
How many transactions/sec are you talking here? What hardware? What are your current bottlenecks?
About 500/sec long term average and 1500/sec peak, the bigger part reading.
Hardware is on on the upper limit of x86, 40 cores, 1 TB RAM. There is still a reserve of doubling cores and RAM but thats all.
Actually it looks like the load is handled quite well. Trying to find out if there are bottlenecks at all and look into future because load is expected to grow.
The application programmers claim that read latencies are to high sometimes. But I don't quite believe that. We see < 10 ms for requests not served from the cache, and I think thats quite ok (theoretical limit should be 4 ms).
I expect the bigger reserves to be in the application itself, but I can't say because I haven't seen any code.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply