Post reply

How to query AlwaysOn AG

  • January 22, 2013 at 5:39 am

    #269568

    Hi

    I have set up an AG however we have some external jobs which query the DB thats included in the AG. How do I go about querying a database thats in an AG?

    I have been unable to add in a linked server for the availability group listener name which I had expected to work, as for an application connection string that is what will be used.

    Thanks

  • January 22, 2013 at 5:47 am

    #1579579

    have you configured a listener for the group , thats the way you will connect to the Always on AG

    Jayanth Kurup[/url]

  • January 22, 2013 at 5:51 am

    #1579582

    Yeah as mentioned in the OP I have a listener for the group, although I can use that in a connection string, how do you query the database thats part of a AG.

    Do you use a 3 part convention like you do for linked servers? For example in my case my Listener is called TopGear. But what I run the following query it doesn't work:

    select * from topgear.extranet.ext_BrochureRequests

  • January 22, 2013 at 6:03 am

    #1579588

    the fully qualified version of specifying obejcts is

    servername . database name . schema name . object name

    by doesnt work do you mean it returns an error or that it returns no results.

    Jayanth Kurup[/url]

  • January 22, 2013 at 6:05 am

    #1579590

    yes apologies you are right, however its still doesn't work. By this I mean I get an error of:

    Msg 7202, Level 11, State 2, Line 1

    Could not find server 'topgear' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

    I have been unable to add the AG listener as a linked server though.

  • January 23, 2013 at 6:06 pm

    #1580325

    What error do you get when adding the AG as a linked server?

    The AG is a virtual SQL Server and should be created in the domain. Is it correctly created in the domain?

    Also can you connect to the listener using Mangagement Studio on a client?

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • January 23, 2013 at 9:13 pm

    #1580342

    You may want to read this: http://msdn.microsoft.com/en-us/library/76fb3eca-6b08-4610-8d79-64019dd56c44.

    You use the listener as part of the connection string from what I just read. Can't help beyond this as I don't have the resources to go beyond this as I can't setup an Always On test environment at this time.

  • January 24, 2013 at 2:18 am

    #1580423

    Hi all - thanks for the replies. I found that the problem was an authentication issues when adding the linked server, once I had resolved the security context and added the linked server I could query the AG as you would any normal linked server.

    Thanks

Viewing 8 posts - 1 through 8 (of 8 total)

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