Posts

Showing posts from August, 2021

READONLY ROUTING in Always On.

Image
This query will tell you how to handle READONLY ROUTING in AlwaysOn. ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N 'TCP://NODE1.HADRDOMAIN.COM:1433' )); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N 'TCP://NODE2.HADRDOMAIN.COM:1433' )); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ( 'NODE2' , 'NODE1' ))); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE2' WITH...

Convert ROWS to COLUMNS in Powershell; SQL Server Version and Edition Information from multiple instance.

  The below query will extract Editions,Version and Service Pack information of all the SQL Server Instances. In the server list array provide SQL Server instance information, if you provide windows server name it might throw error or will skip that paritcular server information. cls Import-Module sqlps -DisableNameChecking #[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.Smo')|Out-Null $Counter =0 $serverlist = @ ( 'SQLServerInstance1' , 'SQLServerInstance2' , 'SQLServerInstance3' ) #ForEach-Loop start $serverlist | ForEach -Object{ #$x = 1 #$perc=[math]::round($x/$serverlist.count) $indsserver = $_ $Server = New-Object Microsoft.SQLServer.Management.Smo.Server $indsserver #$Server.Information.Properties|Select-Object Name, Value $netName = ( $Server .Information.Properties| ?{ $_ .Name -like 'Netname' }).VAlue $product = ( $server .Information.Properties | ?{ $_ .Name -like 'Product' }).VAlue...