Power BI – Change data source dynamically when opening Power BI report

-> I created a Power BI report on my development server that connected to a development SQL Server instance. I then moved onto a different server which doesn’t have connectivity to development SQL Server instance, when I opened and refreshed the report. I got below error,

Refresh
AlwaysON_Latency
Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

-> Changing SQL Server data source dynamically when opening this Power BI report will help.

-> I performed below on the development server. Click on “Transform Data” in the report,

-> Created a new parameter for SQL Server Instance,

-> Click “OK”. Created another parameter for DatabaseName,

-> Both parameters are created,

-> Save the created parameter by clicking “Close & Apply”.

-> Click on “Transform Data” in the report again,

-> On the right side you will see “Query Settings”,

-> Click “settings” icon just next to Source,

-> You will see below dialog box,

-> Change the Server and database to use “Parameter” and select appropriate parameters as below,

-> Click “OK” and Save the changes by clicking “Close & Apply”.

-> Perform a Save as and save the file as a Power BI Template,

-> Now try opening the saved Power BI Template file and this time it will ask for SQL Server Instance and Database name,

-> From now on this file can be moved to other servers and can be connected to appropriate SQL Server instance and database.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Always On Latency Report

Environment

-> Requirement will be to create an Always On latency report that will contain multiple database servers that are part of Availability group.

-> Data from multiple database servers will be placed on a centralized server from where the report will be generated.

-> We will use below procedure to create the Always On latency reports.

-> On the centralized database server we should perform below actions.

-> Create a database called JBRepository on the centralized server that will store data for the report.

create database JBRepository 

-> Create a table called AlwaysON_Latency on database JBRepository that will be used to store data related to Always on latency,

USE [JBRepository]
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[AlwaysON_Latency](    
     [Primary_Replica] nvarchar(500) NULL,
     [Secondary_Replica] nvarchar(500) NULL,
     [database_name] nvarchar(500) NULL,
     [Date_Collection] [datetime] NOT NULL,
     [last_commit_time] [datetime] NULL,
     [DR_commit_time] [datetime] NULL,
     [lag_in_seconds] [int] NULL
 ) ON [PRIMARY]
 GO

-> Create a table called AlwaysON_Latency_SQLServer_Instance on database JBRepository that will be used to store SQL Server instances from where Always On latency details will be pulled,

USE [JBRepository]
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[AlwaysON_Latency_SQLServer_Instance](
     [AlwaysON_Latency_ID] [int] IDENTITY(1,1) primary key,
     [SQLServerInstance] nvarchar(500) NULL,   
 ) ON [PRIMARY]
 GO

-> Create stored procedure spINSERT_AlwaysON_Latency_NEW_SQL_Inst that will be used to insert SQL Server instance details to table AlwaysON_Latency_SQLServer_Instance,

USE [JBRepository]
 GO
 CREATE OR ALTER PROCEDURE [dbo].[spINSERT_AlwaysON_Latency_NEW_SQL_Inst]
 @SQLServerInstance nvarchar(100),@Debug bit
 AS
 /*
 -- Object Name: dbo.spINSERT_AlwaysON_Latency_NEW_SQL_Inst
 -- Purpose: Insert a record into table [dbo].[AlwaysON_Latency_SQLServer_Instance]
 -- Database: [JBRepository]
 -- Dependent Objects: None
 -- if @Debug = 0 -> Insert data into table [dbo].[AlwaysON_Latency_SQLServer_Instance]
 -- if @Debug = 1 -> Select data from table [dbo].[AlwaysON_Latency_SQLServer_Instance] to validate the existing data
 -- @SQLServerInstance - Provide the output of @@servername from Alwayson Primary and Secondary replica
 -- Usage -- Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBSAG1',@debug=0
 */
 SET NOCOUNT ON
 if (@Debug = 0)
 begin
     IF EXISTS (select 1 from [dbo].[AlwaysON_Latency_SQLServer_Instance] where [SQLServerInstance] = @SQLServerInstance)
         BEGIN
             Print 'SQL Server Instance already exists in table [dbo].[AlwaysON_Latency_SQLServer_Instance]. Exiting.'
         END
         ELSE
         BEGIN
             INSERT INTO [dbo].AlwaysON_Latency_SQLServer_Instance VALUES (@SQLServerInstance)
         END
 end
 else if (@Debug = 1)
     begin
         select * from [dbo].[AlwaysON_Latency_SQLServer_Instance] where [SQLServerInstance] like '%'+@SQLServerInstance+'%'
     end
 GO

-> Create stored procedure spDELETE_AlwaysON_Latency_NEW_SQL_Inst that will be used to remove unmwated SQL Server instance details from table AlwaysON_Latency_SQLServer_Instance,

USE [JBRepository]
 GO
 CREATE OR ALTER PROCEDURE [dbo].[spDELETE_AlwaysON_Latency_NEW_SQL_Inst]
 @SQLServerInstance nvarchar(100),@Debug bit
 AS
 /*
 -- Object Name: dbo.spDELETE_AlwaysON_Latency_NEW_SQL_Inst
 -- Purpose: Insert a record into table [dbo].[AlwaysON_Latency_SQLServer_Instance]
 -- Database: [JBRepository]
 -- Dependent Objects: None
 -- if @Debug = 0 -> Insert data into table [dbo].[AlwaysON_Latency_SQLServer_Instance]
 -- if @Debug = 1 -> Select data from table [dbo].[AlwaysON_Latency_SQLServer_Instance] to validate the existing data
 -- @SQLServerInstance - Provide the output of @@servername from Alwayson Primary and Secondary replica
 -- Usage -- EXEC [spDELETE_AlwaysON_Latency_NEW_SQL_Inst] 'JBSAG2',@debug=0
 */
 SET NOCOUNT ON
 if (@Debug = 0)
 begin
     IF EXISTS (select 1 from [dbo].[AlwaysON_Latency_SQLServer_Instance] where [SQLServerInstance] = @SQLServerInstance)
         BEGIN
             delete from [dbo].[AlwaysON_Latency_SQLServer_Instance] where [SQLServerInstance] = @SQLServerInstance
             delete from [dbo].[AlwaysON_Latency] where Primary_Replica = @SQLServerInstance
         END
         ELSE
         BEGIN
             Print 'Specified SQL Server Instance does not exist in table [dbo].[AlwaysON_Latency_SQLServer_Instance]. Exiting.'         
         END
 end
 else if (@Debug = 1)
     begin
         select * from [dbo].[AlwaysON_Latency_SQLServer_Instance] where [SQLServerInstance] like '%'+@SQLServerInstance+'%'
     end
 GO

-> Use below query to insert required SQL Server instances into table AlwaysON_Latency_SQLServer_Instance for which Alwayson Latency details are required. In my case I am inserting 6 SQL Server instances,

Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBPUB-PRIMARY',@debug=0
 Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBPUB-DR',@debug=0
 Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBSUB-PRIMARY',@debug=0
 Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBSUB-DR',@debug=0
 Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBDIST-PRIMARY',@debug=0
 Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBDIST-DR',@debug=0

-> Execute below query to verify inserted SQL Server instances,

use [JBRepository]
select * from AlwaysON_Latency_SQLServer_Instance

-> Save below query on the centralized server to a location. In my case I am placing it in location F:\Automation\AlwaysOn_Latency\.

WITH DR_CTE ( replica_server_name, database_name, last_commit_time)
 AS
 (
 select ar.replica_server_name, database_name, rs.last_commit_time from master.sys.dm_hadr_database_replica_states  rs inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id where replica_server_name != @@servername
 )
 select  ar.replica_server_name as 'Primary_Replica', DR_CTE.replica_server_name as 'Secondary_Replica',dcs.database_name, getdate() as Date_Collection ,rs.last_commit_time, DR_CTE.last_commit_time 'DR_commit_time', abs(datediff(ss,  DR_CTE.last_commit_time, rs.last_commit_time)) 'lag_in_seconds'
 from master.sys.dm_hadr_database_replica_states  rs 
 inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id
 inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id
 inner join DR_CTE on DR_CTE.database_name = dcs.database_name 
 where ar.replica_server_name = @@servername
 order by lag_in_seconds desc

-> Save below power shell script that will fetch Alwayson Latency data from all SQL Server instances and place the latency data in table AlwaysON_Latency in location F:\Automation\AlwaysOn_Latency\.

<#Script to pull Alwayson on Latency data from mutiple SQL Databases from multiple Server#>
  <# Ver 1.0 #>
  Import-Module SqlServer
  <#Variables - details of the connection, tsqlcommand and parameters #>
  $RepositorySQLInstance = "JBPUB-PRIMARY"
  $RepositoryDB = "JBRepository"
  $connectionString = "server=$RepositorySQLInstance;database=$RepositoryDB;trusted_connection=true;"; 
  $tsqlcommand = "select [SQLServerInstance] from [dbo].[AlwaysON_Latency_SQLServer_Instance]"; 
  <#SQL Connection - connection to SQL server #>
  $sqlConnection = new-object System.Data.SqlClient.SqlConnection; 
  $sqlConnection.ConnectionString = $connectionString; 
  <#SQL Command - set up the SQL call #>
  $sqlCommand = New-Object System.Data.SqlClient.SqlCommand; 
  $sqlCommand.Connection = $sqlConnection; 
  $sqlCommand.CommandText = $tsqlcommand; 
  <#SQL Adapter - get the results using the SQL Command #>
  $sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter 
  $sqlAdapter.SelectCommand = $sqlCommand 
  $dataSet = new-object System.Data.Dataset 
  $recordCount = $sqlAdapter.Fill($dataSet) 
  <#Close SQL Connection #>
  $sqlConnection.Close(); 
  <#Get single table from dataset #>
  $data = $dataSet.Tables[0] 
  <#Provide the name of the Local Instance to store the data#>
  $LocalInstance = $RepositorySQLInstance 
  <#Provide the name of the database to store the data#>
  $Repositorydb=$RepositoryDB
  <#Looping through each Instance#> 
  foreach ($SqlInstance in $data) 
  { 
  Invoke-Sqlcmd -inputfile "F:\Automation\AlwaysOn_Latency\Always_On_Latency.sql" -ServerInstance $SqlInstance.SQLServerInstance -Database master| Write-SqlTableData -ServerInstance $LocalInstance -DatabaseName $Repositorydb -SchemaName dbo -TableName AlwaysON_Latency -Force 
  Write-Host "Always on Latency details collected from" $SqlInstance.SQLServerInstance 
  }
  $purge_table_AlwaysON_Latency = "delete from [dbo].[AlwaysON_Latency] where Date_Collection < (getdate()-90)"
  Invoke-Sqlcmd -ServerInstance $RepositorySQLInstance -Database $RepositoryDB -Query $purge_table_AlwaysON_Latency

-> Remember to change below details before executing the Power shell query,

Line 5 – Change $RepositorySQLInstance to your centralized SQL server instance.

Line 6 – Change $RepositoryDB to appropriate database.

Line 32 – Change -inputfile to the correct location where Always_On_Latency.sql is placed.

-> Try executing the script manually,

-> Use below query to check the Always ON latency data,

use [JBRepository]
select * from AlwaysON_Latency

-> I have configured a SQL Server agent job to execute every 5 mins that will execute this powershell script. This in turn will collect Always on latency data for all applicable SQL server instance.

-> I use below Power BI file to check this as a report,

-> Below is what I see in my lab machine.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Azure SQL database – Reset administrator password

-> I was trying to login to a Azure SQL database using SQL Server management studio and got below error,

TITLE: Connect to Server
Cannot connect to jbreplsub.database.windows.net.
ADDITIONAL INFORMATION:
Login failed for user ‘jvivek2k1’. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
BUTTONS:
OK

-> I have created this for testing long time ago and don’t remember the password.

-> I decided to reset the administrator password for this Azure SQL Database using below instructions,

  1. Login to Azure portal.
  2. Navigate to the Azure SQL database and click on “Server Name url”,

3. Once in “SQL Server”, click on reset password,

-> I was able to login to Azure SQL database just fine.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.