patternsqlMinor
SQL Server Page Caching
Viewed 0 times
sqlcachingpageserver
Problem
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#200121, answer score: 9
Revisions (0)
No revisions yet.