September 2014
M	T	W	T	F	S	S
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

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]
GO
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'
GO

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 AlwaysOn – Paperback, Kindle

Cheers,

Balmukund Lakhani

Twitter @blakhani

Posted in Tips and Tricks | Tagged: AlwaysOn, Availability Group, linked server, listener, readonly, routing list | 13 Comments »

Help : Who dropped login from server?

Posted by blakhani on September 23, 2014

Long time ago I blogged about Who dropped objects from database and it was very useful for many readers. Few days back someone asked me via Facebook – “Is it possible to find who dropped login from server?” Initially I check the “Schema Change History” report but it was not showing expected output. I went ahead and looked into default trace and found that there are some entries when I delete a login. Since the data was available in default trace, I have written below script to read those files and show them in readable format.

DECLARE @enable INT

SELECT TOP 1 @enable = convert(INT, value_in_use)
FROM sys.configurations
WHERE NAME = 'default trace enabled'

IF @enable = 1 --default trace is enabled
BEGIN
    DECLARE @d1 DATETIME;
    DECLARE @diff INT;
    DECLARE @curr_tracefilename VARCHAR(500);
    DECLARE @base_tracefilename VARCHAR(500);
    DECLARE @indx INT;

    SELECT @curr_tracefilename = path
    FROM sys.traces
    WHERE is_default = 1;

    SET @curr_tracefilename = reverse(@curr_tracefilename)

    SELECT @indx = PATINDEX('%\%', @curr_tracefilename)

    SET @curr_tracefilename = reverse(@curr_tracefilename)
    SET @base_tracefilename = LEFT(@curr_tracefilename, len(@curr_tracefilename) - @indx) + '\log.trc';

    SELECT TargetLoginName 'Login which was dropped'
        ,StartTime 'When'
        ,LoginName 'who dropped'
        ,ApplicationName 'from where'
        ,HostName 'machine name'
    FROM::fn_trace_gettable(@base_tracefilename, DEFAULT)
    WHERE EventClass IN (
            104                    -- SQL Login would have "Audit Addlogin Event"
            ,105                -- Windows Login would have "Audit Login GDR Event"
            )
        AND EventSubclass = 2 --drop
END
ELSE
Select 'Default trace is not enabled'

Hope this would help some DBA to find culprits and punish them.

Cheers,
Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in Script, Security | Tagged: audit addlogin, audit login GDR, default trace, who dropped login | 7 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]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=1000, 
        @jobhistory_max_rows_per_job=100
GO

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'
GO

We need to provide the oldest date in T-SQL

Hope this helps!

Cheers,

Balmukund Lakhani

Twitter

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in SQL Server Agent, Tips and Tricks | Tagged: Delete SQL Job History, Job History Missing, Missing Job History, sql job history disappears, SQL Job History Query | 9 Comments »

« Previous Entries

Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Blog Stats

Blogroll

Select GETDATE()

Subscribe

Follow Blog via Email

Date < getdate()

Select location from world

Meta

Archive for September, 2014

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

Tips and Tricks : SQL Agent Job history data missing?