Application Intent with Invoke-SQLcmd

  • gwellbrock

    Ten Centuries

    Points: 1119

    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.

  • gwellbrock

    Ten Centuries

    Points: 1119

    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

    }

  • dale53

    Valued Member

    Points: 59

    $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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply