4

If I am not mistaken, SQL Server caches pages in order not to hit the disk each and every time. How does this caching work in the case of shared storage and multiple active SQL servers in a cluster? Since there is more than one active SQL server, how does the server know if the page is still valid? Do we need to disable this caching behavior for shared storage case? If so, how?

9

Normally, any single database can only ever1 be opened by a single SQL Server instance. There is no way for multiple instances, even on shared storage, to simultaneously provide write access to the same pages of a database.

SQL Server does indeed cache pages, however each SQL Server instance allocates its own private memory, and no other process, including other SQL Server instances, can access that memory. This is by-design, and works that way as a principle tenet enabling consistency.

In the case of shared storage, typically used for SQL Server Clustering, you have two (or more) computers connected to the shared storage, however only one of those servers can actually open the databases stored on that shared storage at any given time. If one of the those servers stops, the clustering service will provide access to the other server, allowing the instance on that machine to serve the database.

A single Windows Failover Cluster Server can have multiple SQL Server Clusters installed, however each has it's own shared storage, and are essentially for all intents-and-purposes independent of each other. They may occupy the same server computer, and they may not. Still, they'll never be able to read from the same database file.


1 - read-only reporting workloads can be supported across multiple instances of SQL Server via Scalable Shared Databases. The cost-benefit-ratio of such a setup is typically far below that required to make it feasible.

  • I never see anybody using this, but I feel obliged to mention Scalable Shared Databases since it is technically possible, just painful: technet.microsoft.com/en-us/library/ms345392(v=sql.105).aspx – Brent Ozar Mar 13 '18 at 15:51
  • funny, I remember reading something about that a very long time ago now. It seems there's always a reason to "never say never" :-) – Max Vernon Mar 13 '18 at 15:54
  • "however only one of those servers can actually open the databases stored on that shared storage at any given time." But the following links led me to believe it is possible. What am I missing? blogs.msdn.microsoft.com/momalek/2012/04/10/… , logicalread.com/sql-server-clustering-alternative-options-w02/… , searchsqlserver.techtarget.com/tip/… – John L. Mar 13 '18 at 17:01
  • "Active/Active" refers to a two-computer cluster with two SQL Server instances, one instance per server. Each instance serves different databases. – Max Vernon Mar 13 '18 at 17:05
  • So if I understand you correctly, there is InstanceA on ServerA which hosts DatabaseA and InstanceB on ServerB which host DatabaseB. And the point of making them a cluster is that when ServerA fails, then InstanceB will actively host DatabaseA. Is this right? If it is, then isn't it misleading to call it an Active/Active cluster? I guess that's why they switched to using the Multi-Instance term instead of Active/Active right? – John L. Mar 13 '18 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.