enable Buffer pool extension in always on setup.

Heisenberg 261 Reputation points
2022-02-03T19:44:20.953+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,662 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Ronen Ariely 15,096 Reputation points
    2022-02-03T22:17:48.507+00:00

    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/

    0 comments No comments

  2. YufeiShao-msft 7,056 Reputation points
    2022-02-04T03:13:41.86+00:00

    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.

    0 comments No comments