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

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

Azure Database pricing tier selection

Azuer provides multiple Servie level options to choose for your database(s)s depending on number of databases/ Size/ CPU/ Number of Sessions/ Number of Connections to the database.

As the number of database you want to host increases you have more challenging task to find optimal solution.

Following are the pointers which an be used for analysis:

* Azure SQL Database benchmark overview: https://azure.microsoft.com/en-us/documentation/articles/sql-database-benchmark-overview/
* SQL Database Service Tier and its limits: https://azure.microsoft.com/en-us/documentation/articles/sql-database-resource-limits/
* Elastic Pools and Limits: https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-pool/
* SQL Database Pricing: https://azure.microsoft.com/en-us/pricing/details/sql-database/
* Azure Database capacity is always measured with DTU, it is good to understand the DTU and eDTU (eDTU is the term used for Elastic pool DTU): https://azure.microsoft.com/en-us/documentation/articles/sql-database-what-is-a-dtu/
* Interested to follow all new articles: https://azure.microsoft.com/en-us/documentation/articles/?product=sql-database

* Azure health status: https://azure.microsoft.com/en-us/status/ This link can be used to check the status of Azure environment in each region.

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

Check if the Server is pending reboot

I have used this Powershell script to validate if the server is pending a reboot or not. This script can be used to validate server pending reboot status on any other server within the network!


Posted in Powershell, Windows Server | Tagged , , | Leave a comment

Install SQL Server Management Studio (SSMS)

Now SSMS (SQL Server Management Studio) can be installed as a stand alone, need not download full version only for SSMS.

SSMS can be downloaded and installed from:


Posted in Uncategorized | Tagged , , | Leave a comment

QUERY STORE for Azure database performance analysis

Query store can be enabled on Azure SQL Database to analyze any performance issues with SQL Server.

Example: Below image indicates one of the SQL statement was executed about 57,000 times along with Query plan to understand what is consuming maximum CPU. This query alone using at least 60% of the CUP during the given time.


Query store on a database can be enabled using below SQL Query;


I frequently use Regressed and Top Resource Consuming queries to analyze high DTU consumption analysis.

NOTE: Azure Portal can also be used to identify index recommendations to improve performance but Query store can be used for detailed analysis.

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

Process Monitor – Simple and efficient tool to investigate processes

While working with Microsoft on a SQL bug I have used a powerful tool (Process monitor) to troubleshoot the problem.

What is interesting with this tool:
* Simple, installation not required
* Filter is great: Filter can be applied on specific process or user or session etc
* UI is simple and efficient to clear old messages and no performance issues
* Exclude specific processes from UI to focus on what we need with just 2 clicks
* One click filter options to include/exclude specific processes (Network activity/ File System Activity/ Registry activity etc)

This tool can be downloaded from http://www.sysinternals.com -> Process Utilities -> Process Monitor




Posted in Productivity, Uncategorized, Windows Server | Tagged , , , | Leave a comment

Powershell script to search specific error on all log files in given servers

$searchpattern =  "Error I want to search in log file"


foreach ($server in $servers)
    $mypath = "\\" + $server + "\M$\SQLDBA\MyToolPath\LogFolder"

    $myfiles=Get-ChildItem $mypath | where {($_.CreationTime -ge $(Get-Date).AddHours(-120))}

    foreach ($file in $myfiles)
         IF ( Get-Content $file.FullName | Select-String -Pattern  $searchpattern )
            Write-Host $file.FullName


I have used this code to find specific activity failure during a critical process which is running across 25 different servers.
this script also searches the log files which are created in last 5 days.

Posted in Powershell, Productivity | Tagged , , , , , | Leave a comment