Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,039,818 hits
  • Select GETDATE()

    September 2014
    M T W T F S S

Archive for September, 2014

Tips and Tricks : Creating Linked Server to an Availability Group Listener with ReadOnly routing

Posted by blakhani on September 30, 2014

In my recent interaction with customer, I was asked if linked server can be created to an availability group listener? My immediate response was “Yes”. Next question was immediately thrown at me that if they can redirect this listener to secondary replica? This time my little delayed response was – Yes. Provided we use proper connection string. Here is the linked server creation script

 EXEC sp_addlinkedserver @server = N'MyLinkedServer'
    ,@provider = N'SQLNCLI'
    ,@srvproduct = N'SqlServer'
    ,@datasrc = N'MyListener'
    ,@provstr = N'ApplicationIntent=ReadOnly'

…and it was not working as they expected.

Whenever we want to use routing of read-only connection via listener and connections are not going to secondary replica then below checklist should help.

Here is the checklist which I have created.

  • Verify that we are connecting to Listener Name.
  • Verify that the Read-Only routing list (READ_ONLY_ROUTING_LIST) is defined.
  • Verify that the Routing URL (READ_ONLY_ROUTING_URL) of each instance has a proper FQDN and port combination.
  • Verify that ApplicationIntent is specified in the connection string.
  • Verify that the Sync_State is SYNCHRONIZED or SYNCHORNIZING for the secondary replica
  • Verify that the secondary replicas are set to allow connections
  • Verity that the initial catalog is provided in connection string.

If complete checklist is followed, it should resolve most of the routing problems. Most common mistake which I have seen is to miss creating routing URL and routing list. This may be due to the fact that there is no User Interface available and it has to be done by T-SQL.

Coming back to original question – I asked them to use proper connection string while creating listener. They followed the checklist and they have missed last item the initial catalog. Here is my version of the script.

USE [master]
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer'
    ,@srvproduct = N'SQL'
    ,@provider = N'SQLNCLI11'
    ,@datasrc = N'MyListener'
    ,@provstr = N'Integrated Security=SSPI;Initial Catalog=Production;Data Source=MyListener;ApplicationIntent=ReadOnly'
    ,@catalog = N'Production'

How would you test whether the connection is working fine or not? That’s simple.

select * from openquery(MyLinkedServer,'select @@servername')


If Server name is returned as secondary replica then routing is working perfectly fine. If you are getting primary replica then routing has a problem. The best way to troubleshoot further would be to use SQLCMD and follow the checklist provided earlier in this blog.

sqlcmd -S MyListener -E -d Production -K ReadOnly

Hope this helps!

P.S. Checklist is explained in detail in my book SQL Server 2012 AlwaysOnPaperback, Kindle

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Advertisement

    Posted in Tips and Tricks | Tagged: , , , , , | 13 Comments »

    Tips and Tricks : SQL Agent Job history data missing?

    Posted by blakhani on September 18, 2014

    I have many good friend who call me only when they have SQL Server related problem to solve. I really love you guys (in a manly kind of way!) for remembering me, at least during problem time.

    One fine day my DBA friend called me and said that they have a data missing problem. There is a job which is suppose to move the data from one table to another. Application team reported that job didn’t run on one specific time as per their data calculation. As per their logic they pull hourly data from Table1 to Table2. They have observed that 1 hour data is missing. When they looked into job history they don’t see any evidence of job history. Oldest record for this job is 2 hours back.

    My immediate question was – do you have many jobs running on the server? As expected, answer was Yes! They had may such data movement jobs and few of them run every 5 minutes. I asked him to run below command

    select count(*) from msdb.dbo.sysjobhistory

    and answer was 1000. And I said – that is THE problem! The SQL Agent Job system limits the total number of job history on per job basis as well as overall basis. This information is stored in the MSDB database. I asked him to open SQL Server Agent Properties and go to History page. Over there Modify the ‘Maximum job history log size (rows)’ and ‘Maximum job history rows per job’ to suit, or change how historical job data is deleted based on its age.

    I also told that this won’t give you back the history, but it’ll help with your future queries! He was happy with the answer.

    Later I did more research and found that this can also be done using below T-SQL command

    USE [msdb]
    EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=1000, 

    If there is a need to purge the data manually using UI then we can use “Remove Agent History” checkbox and define the duration. The equivalent of that below

    EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date='2014-09-14T09:02:37'

    We need to provide the oldest date in T-SQL

    Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL Server Agent, Tips and Tricks | Tagged: , , , , | 9 Comments »