enable Buffer pool extension in always on setup.

Question

hello,
We are planning to enable buffer pool extension in our 2 node always on setup. Primary and Secondary are synchronous replicas with manual failover. Can someone give me steps to enable BPE in always on setup and steps to disable as well in case i have to rollback. My confusion is do i have to do failover of AG to do this change like we do in applying service packs.

Answer 1

Hi,

Can someone give me steps to enable BPE in always on setup and steps to disable as well in case i have to rollback.

Enabling buffer pool extension is done simply by ALTER SERVER CONFIGURATION and it is done in the instance level and not related to the always on setup

ALTER SERVER CONFIGURATION  
    SET BUFFER POOL EXTENSION ON (FILENAME = 'F:\BP_CACHE\BufferPoolExtension.BPE',SIZE = 10 GB)  
GO  

Disabling is done in the same way

ALTER SERVER CONFIGURATION  
    SET BUFFER POOL EXTENSION OFF  
GO  

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-server-configuration-transact-sql?view=sql-server-ver15#BufferPoolExtension

do i have to do failover of AG to do this change like we do in applying service packs.

NOTE! Microsoft explicitly recommend to restart the instance

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/buffer-pool-extension?view=sql-server-ver15

After enabling buffer pool extension for the first time it is recommended to restart the SQL Server instance to get the maximum performance benefits.

In addition:

When disabled, the memory used to support the feature is not reclaimed until the instance of SQL Server is restarted. However, if the feature is re-enabled, the memory will be reused without restarting the instance.

Nice tutorial: https://www.sqlshack.com/buffer-pool-extension-bpe-works/

Answer 2

Hi @SQLServerBro,

BPE is a server configuration, BPE only makes sense if you extend the buffer pool to local disk, There is no point where it says It absolutely may or may not work with clustered instances, so remember to always test

Implementing Buffer Pool Extension

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.