Conditional Formatting – Excel

Simple tip on using Conditional formatting.
This sample is to format a cell if it exceeds certain threshold (which is a different column on the same row).

1. Identify first row, first column where the formula needs to be created.
2. Home -> Conditional Formatting -> New Rule
3. Use a formula to determine which cells to format
4. Enter the Formula (=IF(D4>$B4,1,0)): against D4 if Column B has reference value. -> Select the Format -> OK
5. Home -> Conditional Formatting -> Manage Rules -> change the Applied to values from single cell to multiple values (Example: =$D$4:$ZE$58)

 

 ConditionalFormatting_Sample

There are multiple options with Conditional formatting which are self explanatory and easy to use. got explore…

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

Frequently used Azure powershell commands

Few of my frequently used powershell commands on Azure

#

Login-AzureRmAccount

Get-AzureRmSubscription | sort SubscriptionName | Select SubscriptionName

Select-AzureRmSubscription -SubscriptionName ContosoSubscription

Get-AzureRmLog -StartTime 2017-07-26T01:00  | Where-Object {$_.Authorization.Scope -like "*databases/DBNAME"} | Sort-Object EventTimestamp

#

Ref:
https://docs.microsoft.com/en-us/azure/search/search-manage-powershell

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

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

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