how to restrict Iplistener in sql server high availibilty group

Avahita Heidarnezhad 1 Reputation point
2022-01-17T06:02:17.737+00:00

I have a question about high availability group.
Your help will be appreciated.
How can I prevent clients to connect to IPlistener for a while rather than removing it completely?
By disabling TCP/IP in SQL configuration Manager we can limit clients to connect to SQL server instances but when we have high availability group and IPlistener, this way doesn’t work.
I know I can disable logins on server to prevent connecting them to our databases but I hope to find the easier method to reach my goals

best regards

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,610 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Seeya Xi-MSFT 16,436 Reputation points
    2022-01-17T07:51:05.427+00:00

    Hi @Avahita Heidarnezhad ,

    Welcome to Microsoft Q&A!

    How can I prevent clients to connect to IPlistener for a while rather than removing it completely?

    Obviously, the listener does not have a disable attribute. You can limit connections by removing and adding.
    Using T-SQL makes the process easy.
    --remove
    ALTER AVAILABILITY GROUP TestAG1 REMOVE LISTENER 'TestAGListener'
    --add
    ALTER AVAILABILITY GROUP TestAG1
    ADD LISTENER 'TestAGListener' ( WITH IP ( ('192.168.2.110','255.255.255.0') ) , PORT = 1433 );

    The above is an example of mine, you can refer to it.
    Here is the relevant documents from Microsoft: Create Listener and Remove Listener with using T-SQL.

    Best regards,
    Seeya


    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