May 20, 2015 at 9:58 am
Are you able to specify read-only routing in invoke-SQLcmd?
$dataTable = Invoke-Sqlcmd -Query $Query -Database $SourceDatabaseName -ServerInstance $SourceServerName ;
maybe add something like -READONLY = TRUE? or -APPlicationintent = 'readonly'
I can't seem to find anything on it.
May 21, 2015 at 8:31 am
I ended up writing a function in PowerShell to do this. Only caveat currently is if you have multiple Secondary Replicas it doesn't have logic to pick which one. The Function will return the servername of the SecondaryReplica if you pass in the Listener Name to the function -
Call it like this -
SecondaryReplica $SourceServerName $SourceDatabaseName
See Function Below -
function SecondaryReplica{
Param
(
$ServerName ,
$DatabaseName
)
#FIND SQL VERSION
$Progress ='FIND SQL VERSION'
$SQLVERSIONQUERY = "SELECT SERVERPROPERTY('ProductVersion') as [Version]"
$SQLVERSIONNUMBER = Invoke-Sqlcmd -Query $SQLVERSIONQUERY -Database $DatabaseName -QueryTimeout 1000 -ConnectionTimeout 10 -ServerInstance $ServerName
$SQLVERSION = @($SQLVERSIONNUMBER | Select-Object -ExpandProperty Version)
write-host $SQLVERSION
#SEE IF ALWAYSON = ON
$Progress = 'SEE IF ALWAYSON = ON'
$SQLALWAYSONQUERY = "SELECT SERVERPROPERTY ('IsHadrEnabled') as [ALWAYSONENABLED]"
$SQLALWAYSONENABLED = Invoke-Sqlcmd -Query $SQLALWAYSONQUERY -Database $DatabaseName -QueryTimeout 1000 -ConnectionTimeout 10 -ServerInstance $ServerName
$SQLALWAYSON = @($SQLALWAYSONENABLED | Select-Object -ExpandProperty ALWAYSONENABLED)
write-host $SQLALWAYSON
#SET SOURCE DATABASE
IF ($SQLALWAYSON -eq 1 -and $SQLVERSION -gt 11){
#FIND SECONDARY REPLICA
$Progress = '#SEE IF ALWAYSON = ON'
$FindSecondaryReplicaQuery = "BEGIN
SELECT MAX(RCS.replica_server_name) AS [SERVERNAME] -- SQL cluster node name
FROM
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
ON
RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
ON
ARS.replica_id = RCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL
ON
AGL.group_id = ARS.group_id
WHERE
ARS.role_desc = 'SECONDARY'
END
"
$ReplicaName = Invoke-Sqlcmd -Query $FindSecondaryReplicaQuery -Database $DatabaseName -QueryTimeout 1000 -ConnectionTimeout 10 -ServerInstance $ServerName
$ServerName = @($ReplicaName | Select-Object -ExpandProperty SERVERNAME)
write-host 'SOURCE DATABASE SET TO SECONDARY REPLICA' }
else{
write-host 'ALWAYS ON IS DISABLED'
}
return $ServerName
}
October 25, 2018 at 1:03 am
$conn = new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Application Name={2};Integrated Security=True;Connect Timeout={3};ApplicationIntent=ReadOnly" -f $ServerInstance,$Database,$ApplicationName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply