Most common challenges and solution with Availability group listener and multisubnetfailover

Industry started using SQL availability groups on most of their environments, DBA is coming across multiple issues related to AGListener connectivity.

This blog explains 2 major challenges and solutions in detail:
https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/

I recommend option-1 to enable client to use multisubnetfailover option to experience seamless fail over. If this is not feasible for any legacy reasons use option-2 to make sure AGListername is configured with single IP in DNS and change the TTL to shorter duration (Example: 1 minutes; Assuming a maximum 1 minute downtime is accepted)

To Summarize different categories:
1. MultiSubnetFailover is missing (Ref: https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/)
2. Client is not capable of using multiSubnetFailover, register Single IP in DNS – Ref: https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/
3. TransparentNetworkIPResolution resulting timeout after 500 ms for DNSlookup.
Resolution is to set this property to false (This is only after .NET Framework 4.6 or later) Ref –
https://blogs.msdn.microsoft.com/dataaccesstechnologies/2016/05/07/connection-timeout-issue-with-net-framework-4-6-1-transparentnetworkipresolution/

How to find .NET Framework version: https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/how-to-determine-which-versions-are-installed

Additional resources:
https://technet.microsoft.com/en-us/library/dd197575(v=ws.10).aspx

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/sql-server-multi-subnet-clustering-sql-server

Advertisements
Posted in Learn_Powershell, SQL, SQL Server, Uncategorized | Tagged , , , , , | Leave a comment

Azure Scripting errors after upgrading Powershell to V4 – Select-AzureRmProfile / Save-AzureRmProfile

After upgrading Powershell to V4, few of my scripts started failing. Few updated commandlets are missing backward compatibility, Select-AzureRmProfile/ Save-AzureRmProfile are couple of the broken commandlets.

You can replace them with Import-AzureRmContext, and Save-AzureRmContext.

Code block I use for reference:

#
         $myprofile = "C:\tmp\" + [Environment]::UserName + "_Profile.json"
          $SubscriptionId = “asdfsadfsd”

#        Login-AzureRmAccount
        if(test-path $myprofile)
        {
            try
            {
                Import-AzureRmContext -Path $myprofile
                Select-AzureRmSubscription -SubscriptionId $SubscriptionId -ErrorAction Stop
            }
            catch
            {
                Login-AzureRmAccount
                Save-AzureRmContext -Path $myprofile -Force
            }
        }
        else
        {
            Login-AzureRmAccount
             Save-AzureRmContext -Path $myprofile -Force
        }
      Select-AzureRmSubscription -SubscriptionId $SubscriptionId -ErrorAction Stop
#

Ref:
Ref: Breaking changes: https://github.com/Azure/azure-powershell/blob/preview/documentation/release-notes/migration-guide.4.0.0.md

Additional Documentation: https://docs.microsoft.com/en-us/powershell/module/azurerm.profile/get-azurermcontext?view=azurermps-3.8.0

https://arcanecode.com/2017/04/19/what-happened-to-save-azurermprofile/#comment-128901

Still working on one more problem with Get-AzureRmSqlElasticPoolDatabase after Powershell upgrade to V4….

Error: Save-AzureRmContext : The term ‘Save-AzureRmContext’ is not recognized as the name of a cmdlet, function, script file,
or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and
try again.

Select-AzureRmProfile : The term ‘Select-AzureRmProfile’ is not recognized as the name of a cmdlet, function, script
file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct
and try again.

Posted in Azure, Powershell, SQL Server, Uncategorized | Tagged , , , , , | Leave a comment

How to provide access to SSRS Reports – Giving access only on specific activity

SSRS Access will be managed through Roles only. Giving access to a user or group is assigning the user/group to a specific role. This can be done through the SSRS Reports. Each roles will be assigned with specific tasks so user gets access to the tasks.

SSRS Report access can be managed at Site level or at Specific Folder level.

Site Level Access:
• Open the SSRS Report
• Site Settings -> Security
• New Role Assignment or Edit if the user is already has some access
• Select the roles to be mapped to
o There will be 2 Roles available at Site Level
 System Administrator
 System User
• Click on Apply

Giving access to a specific Folder;
• Open the SSRS Report
• Go to Home Page (Home)
• Scroll the mouse over to the folder which needs access, Click on Down Arrow
o SSRS_20170529
• Select Security
• New Role Assignment or Edit if the user is already has some access
• Select the roles to be mapped to
o There will be 5 Roles available at Folder Level
 Browser
 Content Manager
 My Reports
 Publisher
 Report Builder
• Click on Apply

User Defined Roles:
There could be a scenario where user/group needs access on specific tasks or group of specific tasks which are not pre-defined. This can be achieved by connecting to the Reporting Server using SSMS and creating new Roles (Both System Roles and Folder Level Roles can be created with specific tasks)
Ref: https://docs.microsoft.com/en-us/sql/reporting-services/security/role-definitions-create-delete-or-modify

Task: Task is the Specific operation that can be performed on SSRS

• Tasks and Permissions: https://technet.microsoft.com/en-us/library/ms159840(v=sql.105).aspx
• System Level Tasks: https://technet.microsoft.com/en-us/library/ms157353(v=sql.105).aspx
• Item Level Tasks: https://technet.microsoft.com/en-us/library/ms160344(v=sql.105).aspx

• PreDefined Roles: https://technet.microsoft.com/en-us/library/ms159840(v=sql.105).aspx

• More about granting access on a Native Mode Report Server: https://docs.microsoft.com/en-us/sql/reporting-services/security/granting-permissions-on-a-native-mode-report-server

Posted in Productivity, Uncategorized | Tagged , , , , | Leave a comment

Search SQL Error Logs using TSQL – xp_readerrorlog


--
exec xp_readerrorlog 0	-- 0=current SQLServer log
,1 -- 1=SQL Server log, 2=SQL Agent log
,N'availability group'					-- Search string
,null					-- second search string
,'2013-11-10 15:30:39.083'			-- start date
,'2013-11-11 15:30:39.083'	-- end date
,N'DESC'				-- order the logs by date
--

-- Option.B


DECLARE @enddate datetime
DECLARE @startdate datetime

SET @enddate = getdate() 
SET @startdate = dateadd(hh,-24,getdate() )


exec xp_readerrorlog 0  -- 0=current SQLServer log
,1 -- 1=SQL Server log, 2=SQL Agent log
,N''                  -- Search string
,null                   -- second search string
,@startdate         -- start date
,@enddate  -- end date
,N'DESC'   


Posted in Productivity, Script, SQL, SQL Server, Uncategorized | Tagged , , | Leave a comment

Powershell commands – my quick reference


# Check if a folder exists, create if not
$bybakupdir = "D:\parentFolder\NewFolder"
if(!(Test-Path -Path $bybakupdir )){
  New-Item -ItemType directory -Path $bybakupdir
}


Posted in Learn_Powershell, Powershell, Productivity, Uncategorized | Tagged | Leave a comment

Using Idera to investigate a problem during specific time

I had a situation to troubleshoot a performance problem on a server which occurred a few days back. Following is quick reference on how Idera can be used to drill down details.

1. Select History Icon and select the time which needs investigation

2. First option I look for Active alerts during the impacted time.

Idera_Perf_0

3. Check for SQL Queries for performance analysis
Sessions -> Details -> Analyze and Export to Excel.

Idera_Perf_1

4. Resource consumption trend
Resources -> CPU / Memory / Disk

5. Server Waits
Server waits is critical to identify bottleneck and UI provides east options to drill down Server Waits.

Idera_Perf_2

Posted in SQL, SQL Server, Uncategorized | Tagged , , | Leave a comment

Azure – All DBs that are not in Elastic pools

--

SELECT
       @@SERVERNAME as [ServerName],
       dso.elastic_pool_name,
       d.name as DatabaseName,
       dso.edition
FROM
       sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master'
AND elastic_pool_name is NULL 
ORDER BY
       d.name, dso.elastic_pool_name
--
Posted in Azure, SQL, SQL Query, Uncategorized | Tagged , , , | Leave a comment