Post reply

Set up Availability Group without a cluster to create read-only database

  • March 27, 2024 at 10:20 pm

    #4403434

    I want to have a a secondary ready-only database available for reporting, which is up to date with production data from primary database.

    I know how to set up Availability group without cluster. My question is, do I need the listener ?

    I dont care about HA or DR....I just want to be able to report from the secondary database to take load of the primary one.

    Please dont suggest log shipping or anything else, I just want to know about the Listener.

    Thank you so much !

    Attachments:
    You must be logged in to view attached files.

  • March 28, 2024 at 3:09 am

    #4403544

    Talk to your SAN folks.  SAN snapshots are perfect for this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 28, 2024 at 9:46 am

    #4403694

    Did you read the documentation?

    https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-scale-availability-groups?view=sql-server-ver16

     

    More specifically this section

    https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-scale-availability-groups?view=sql-server-ver16#connect-to-read-only-secondary-replicas

     

    You can connect to read-only secondary replicas in either of two ways:

    Applications can connect directly to the SQL Server instance that hosts the secondary replica and query the databases. For more information, see Readable secondary replicas.

    Applications can also use read-only routing, which requires a listener. If you are deploying a read-scale scenario without a cluster manager, you can still create a listener that points to the IP address of the current primary replica and the same port as SQL Server listens on. You will need to recreate the listener to point to the new primary IP address after a failover. For more information, see Read-only routing.

  • March 28, 2024 at 6:28 pm

    #4403945

    As @Ant-Green stated - no, you do not need a listener for a read-only secondary.  Users can connect directly to that instance to run queries.

    You will need to setup a process to create the users in each database they will be accessing.  If the users are setup as windows logins - then you can add them to the databases without a login and grant permissions on the primary, which will then be replicated to the secondary.  On the secondary, create the login and it will tie to the users in the database(s).

    If using SQL logins - then you need to create a login first, grab the SID of that login and create the user with that specific SID.  If you create the login on the secondary - and the user in the database on the primary, that user will be replicated and will tie with the login on the secondary.  If you create the login on the primary then you have to also create the same login with the same SID on the secondary as well as adding the user to the database.

    If (when) you upgrade to SQL Server 2022 - their are other options available for managing logins and users.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • March 28, 2024 at 8:55 pm

    #4404033

    @jeffrey Williams...excellent answer, thank you !

    @Ant Green, thank you

    @jeff B...no, snapshot would not be the ticket, as we need dynamic data to be reported on, but thank you for your response.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply