Powershell script to test Geo-Replication Configuration

Recently we have upgraded and downgraded multiple databases in SQLAzure and as part of that we have deleted and recreated geo-replication on large number of databases.

Considering the number of databases and priority we end up performing manual changes. Created a quick script to test geo-replication configuration on all the databases.

This script reads the DB and Server information from a CSV file and validates geo-replication status.

# VijRed
# This is to test is geo-replication is configured or not for a given database (if exists) and if the geo-replication is readable or not!

    $dbList = import-csv ".\geoReplicationTestDBList.csv"
        $FirstDataSet = New-Object System.Data.DataSet 

     ForEach($row in $dbList)
        $myservername = $row.servername

        $SQLQuery = "
            select A.myDBname as Search_DBName, sd.name as Primary_DBName, partner_server,partner_database, role_desc, secondary_allow_connections_desc,replication_state_desc FROM 
            ( select '$mydbname' as myDBname ) A
            LEFT JOIN sys.databases sd on sd.name = A.myDBname
            LEFT JOIN sys.geo_replication_links gr on gr.database_id = sd.database_id

        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
        $SqlConnection.ConnectionString = "Server=tcp:$myservername,1433;Initial Catalog=master;Persist Security Info=False;User ID=AzureDBAdmin;Password=Test1234;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
        $SqlCmd.CommandText = $SQLQuery 
        $SqlCmd.Connection = $SqlConnection 

        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
        $SqlAdapter.SelectCommand = $SqlCmd 


    #    $FirstDataSet.Tables[0].Rows.Count 


    $FirstDataSet.Tables[0].Rows | format-table 

catch {
    throw $_.exception

This entry was posted in Azure, Powershell, SQL, Uncategorized and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s