locked
Connect only to the secondary Database for Queries RRS feed

  • Question

  • Hello , <o:p></o:p>

    <o:p> </o:p>

    i have 2 SQL 2016 VMS with availability group deployed on azure with a load balancer in front <o:p></o:p>

    <o:p> </o:p>

    my connection to the DBs goes really smooth for the live database , am trying to redirect all my connection from the application for queries that are just read Only to the secondary database to get best performance <o:p></o:p>

    <o:p> </o:p>

    i was reading through azure resources and on azure sql as service there is something called ApplicationIntent fo r read only databases , is there anyway i can implement this on IaaS and how can i redirect this through the Azure load balancer ?<o:p></o:p>

    Friday, December 21, 2018 10:27 AM

All replies

  • Hi Alex,

    You can specify "ApplicationIntent" as part of the connection string you're using to connect to your database.

    For example:

    Data source=myDBServerInstance;integrated security=sspi;initial catalog=myDBName;applicationintent=readonly
    I'm not sure how the azure load balancer factors into this, though.


    Eitan Blumin; SQL Server Consultant - Madeira Data Solutions

    Friday, December 21, 2018 1:35 PM
  • Hi Alex,

     

    From your description, my understanding is that you want to redirect all my connection from the application for queries to the secondary replica. If anything is misunderstood, please tell me.

     

    You can try to configure the Read-only routing. Read-only routing refers to the ability of SQL Server to route incoming connections to an availability group listener to a secondary replica that is configured to allow read-only workloads.

     

    Then you can specify the application intent to read only in connection strings to use read-only routing like Application Intent=ReadOnly.

     

    For more detailed information, please refer to using a Listener to Connect to a Read-Only Secondary Replica (Read-Only Routing).

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 24, 2018 8:45 AM
  • Many Thanks Emily , 

    do you know how tro handle and configure this on the azure load balancer ?

    Thursday, December 27, 2018 8:47 AM
  • Hi Alex,

     

    Sorry, I am not familiar with Azure Load Balancer. I would suggest you post your question on the Azure forum for more professional help.

     

    Here is the link for Azure forum.

    https://social.technet.microsoft.com/Forums/en-US/home?forum=websitesvirtualmachinesonwinserver

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 27, 2018 9:55 AM
  • many thanks , already posted in there and waiting for some answeres , will keep you posted !

    Thursday, December 27, 2018 10:46 AM
  • You are welcome.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 28, 2018 1:52 AM