Export SSRS Report Inventory to CSV files

Today when I am preparing a documentation i.e., pulling DB Instance reports which includes Databases Info, , Configurations, security, linked servers, settings ..e.t.c., to CSV files for SQL server Instance Migration which is scheduled for tonight ,which has reporting services installed and there is a request from customer to pull all SSRS reports to CSV files with Fields as SSRSServerName, ReportName, Connection details, Datasource details, DataSourcepath details

To achieve this we have methods to do but I wanted to do this using PowerShell script which is simple where we need to provide parameters of Reporting Services Server Name and provide file path where CSV file has to be saved.

I have just gone through and searched my old script repository which I already has this PowerShell script saved , We need to thank the Author for this script .

Please find the PowerShell script below :

CLS
$SSRS2012Server = “DESKTOP-02JIB76” # Provide your SSRS Server
$ReportPathMatch = “*” # you can also specify certain reports, * for all
$file = “C:\temp\SSRS_Inventory.csv” # Provide the path to create CSV file

#URL to the web service
$SSRSurl = “http://{0}/ReportServer/ReportService2010.asmx” -f $SSRS2012Server
# Get a handle on the service
$SSRS = New-WebServiceProxy -uri $SSRSurl -UseDefaultCredential;
# Get a collection of the reports on the server
$itemlist = $SSRS.ListChildren(“/”, $true) | Where-Object {$_.TypeName -eq “Report” -and $_.Path -like $ReportPathMatch -and $_.Path -notlike “/Users Folders*”} ;
Write-Host “Reports to review” $itemlist.Count
#Creating a report tracker
$Reports =@()
#Creates a report tracking object
function Add-Reports
{
param([string]$SSRSServer, [string]$reportPath, [string]$connString, [string]$dataSourceType , [string]$dataSourcePath)
$report = New-Object System.Object
$report | Add-Member -type NoteProperty -name “SSRSServer” -value $SSRSServer
$report | Add-Member -type NoteProperty -name “Report” -value $reportPath
$report | Add-Member -type NoteProperty -name “Connection” -value $connString
$report | Add-Member -type NoteProperty -name “DataSourceType” -value $dataSourceType
$report | Add-Member -type NoteProperty -name “DataSourcePath” -value $dataSourcePath
return $report
}

$SSRSServer = $SSRS2012Server

#Loop Through Each Report
Foreach($item in $itemlist)
{
Write-Host “Checking ” $item.Path
try
{
$dsList = $null #clearing it out explicitly
#Any given report may have multiple data sources, grab the collection
$dsList = $ssrs.GetItemDataSources($item.Path)
#Loop through each DS in the report
for($i=0;$i -le $dsList.Length-1;$i++)
{
$ds = $dsList[$i];
#If the DS has a reference it would appear to be a shared datasource
if($ds.Item.Reference -ne $null)
{
$dataSource = $ssrs.GetDataSourceContents($ds.Item.Reference);
$Reports += Add-Reports -SSRSServer $SSRSServer -reportPath $item.Path -connString $dataSource.ConnectString -dataSourceType “Shared” -dataSourcePath $ds.Item.Reference
}
else #embedded datasource
{
$dataSource = $ds.Item
$Reports += Add-Reports -SSRSServer $SSRSServer -reportPath $item.Path -connString $dataSource.ConnectString -dataSourceType “Embedded” -dataSourcePath “”
}
} #for each datasource
}
catch{
Write-Host “Error on ” $item.Path $Error[0]
$Error.Clear();
}
} # Foreach Report
$Reports | Export-Csv $file -Force

Output from above script will get like below :

And the above all 31 Reports will be exported to CSV file where File path has given like below :

AlwaysON Lease Timeout or Expired Error

Recently performed side-by-side migration from SQL Server 2008R2 Standalone databases(30 DB’s) to SQL Server 2016 by building new Windows Serever 2016 operating systems and also configured Alwayson( 2 replicas) for SQL 2016 servers with multiple Availability Groups.

Application team has modified their connection string to connect with ‘ ListnerName ‘ and tested the application connectivity. App team confirmed everything is working as expected and all good.

After 1 week got a call from application team(middle of the night) that they are having connectivity Issues and trouble connecting to databases.

When I logged in and checked the database server that the Primary Availability Groups alwayson Dashboard looks HEATHY and GREEN . All Databases are ONLINE and SYNCHRONIZED.

Checked if any connections are hitting to Databases using sys.sysprocesses and seen Application coonection are coming to database server.

So, informed the same to application team and they replied saying they have restarted their application services and then database connectivity is working fine.

I opened SQL Server Error Log and seen below error messages :

Message

SQL Server hosting availability group ‘XXX’ did not receive a process event signal from the Windows Server Failover Cluster within the lease timeout period.

Message

Error: 19421, Severity: 16, State: 1.

Message

The lease between availability group ‘XXX’ and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the instance of SQL Server and the Windows Server Failover Cluster. To determine whether the availability group is failing over correctly, check the corresponding availability group resource in the Windows Server Failover Cluster.

Message

Error: 19407, Severity: 16, State: 1.

Message

The state of the local availability replica in availability group ‘XXX’ has changed from ‘PRIMARY_NORMAL’ to ‘RESOLVING_NORMAL’. The state changed because the lease between the local availability replica and Windows Server Failover Clustering (WSFC) has expired. For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

Message

Always On Availability Groups connection with secondary database terminated for primary database ‘XXX’ on the availability replica ‘XXXXX’ with Replica ID: {95d2878a-3fe0-48bf-aa2a-a41805fda786}. This is an informational message only. No user action is required.

Message

Remote harden of transaction ‘user_transaction’ (ID 0x000000002af6b5b4 0001:99ea0a50) started at Apr 17 2021 1:46AM in database ‘XXX’ at LSN (7544210:90297:16) failed.

Message

The availability group database “XXX” is changing roles from “PRIMARY” to “RESOLVING” because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.

Message

State information for database ‘XXX’ – Hardened Lsn: ‘(7544210:90297:1)’ Commit LSN: ‘(7544210:89962:3)’ Commit Time: ‘Apr 17 2021 1:45AM’

Message

Stopped listening on virtual network name ‘ListnerNAme’. No user action is required.

Message

Process ID 250 was killed by an ABORT_AFTER_WAIT = BLOCKERS DDL statement on database_id = 16, object_id = 0.

Message

Always On: Explicitly transitioning the state of the Windows Server Failover Clustering (WSFC) resource that corresponds to availability group ‘XXX’ to Failed. The resource state is not consistent with the availability group state in the instance of SQL Server. The WSFC resource state indicates that the local availability replica is the primary replica, but the local replica is not in the primary role. This is an informational message only. No user action is required.

Message

Always On: The local replica of availability group ‘XXXX’ is preparing to transition to the resolving role in response to a request from the Windows Server Failover Clustering (WSFC) cluster. This is an informational message only. No user action is required.

Message

Unable to access availability database ‘DBNAME’ because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.

Message

Always On: The local replica of availability group ‘XXXX’ is preparing to transition to the primary role in response to a request from the Windows Server Failover Clustering (WSFC) cluster. This is an informational message only. No user action is required.

Message

The state of the local availability replica in availability group ‘XXXX’ has changed from ‘RESOLVING_NORMAL’ to ‘PRIMARY_PENDING’. The state changed because the availability group is coming online. For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

Message

Started listening on virtual network name ‘ListnerName’. No user action is required.

Message

The lease worker of availability group ‘XXXXX’ is now sleeping the excess lease time (164797 ms) supplied during online. This is an informational message only. No user action is required.

Message

The state of the local availability replica in availability group ‘XXXX’ has changed from ‘PRIMARY_PENDING’ to ‘PRIMARY_NORMAL’. The state changed because the local replica has completed processing Online command from Windows Server Failover Clustering (WSFC). For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

Message

A connection for availability group ‘XXXX’ from availability replica ‘InstanceName’ with id [3DB08082-C6D9-47E6-B53A-CCC8B15C21D2] to ‘AU2106CP0644\WDCP01’ with id [95D2878A-3FE0-48BF-AA2A-A41805FDA786] has been successfully established. This is an informational message only. No user action is required.

From SQL Server Error log , I could see the issue occurred because of Lease Time Out which was happened on Primary replica as it controls the mechanism of leae timeout .
This could occur due to high resource utilization on Database server( CPU 100% Usage), at this time SQL Server may go to hung or not responding state for sometime. This could have been resulted to Lease timout or expired.

To find out CPU Usage follow below steps to set it in Perfmon

After creating Data collection and clicked Start , leave it for running until you get lease timeout error or the issue re-Occurs. Once the issue happened again now stop the data collector, open the log in Performance Monitor and review the values of Processor / % Processor Time counter to see if sustained CPU utilization is detected during the time of the lease timeout.

In SQL Server Agent jobs I can see Optimization job was triggered at that time , when the job started running after few minutes the lease timeout error was occurred, also the optimization job was not completed successfully.

BY this I can say When Optimization job was triggered as per the schedule the CPU usage went high at 100% ,also there are Blockings happened on the Database server we can see SPID killed by SQL Server in errrolog message due to /this the Lease Timeout or Lease Expired error occurred .

By default the Lease Timeout Value is ‘ 20000 ‘ if the issue re surfacing again and again we can Increase the Lease Timout Value from Windows Server Failover Cluster( WSFC ).

Open ‘ cluadim ‘ –> click on Roles in Left Pane–>Now On Right Pane click Availability Group–>Down you will seeing Resource TAB,click on it —>Right click on ListnerName –> click Properties–>Again on Properties TAB—> change the Lease Timeout value here —> then click OK .

But the changing Lease Timeout Value will not resolve your issue , it is only temporary Issue. Need to find out why blockings had occurred , also try changing the Optimization job schedule and will see.

Alwayson Logins Sync SQL Agent Job

In our environment we have lot of SQL Server Instances configured with Alwayson Availability Groups HADR
When we create a login on Primary Replica of an Availability Group it will not be automatically moved or get sync with all secondary replicas
This will cause an issue(Failed login error messages) when a Failover occurs as in current Primary replica the login does not exist .

To avoid such issues I have googled for script and found one PowerShell script which suits ours requirement ( I have automated this long back ago , didn’t remember the site or link to provide here – thanks to the original Author who created this script ) we automated by creating a copy logins job on all replicas, here I am using a PowerShell script to perform our task
Creating an SQL Agent job which executes PowerShell script on all replicas and this job is smart enough to identify which node it should execute.
On Primary replica When a new login creation or password update or changing\providing permissions occurs, this job script will replicate to all the secondary replicas.

Pre-requisites:

Need to have stored procedure ‘Sp_hexadecimal’ Script on all replicas.
This script will not do anything until there are any modifications done on Primary replica.
This script does not copy existing logins until you make any changes to them e.g. password change, grant/revoke privileges etc.

Schedule this job on all replica based on your environment and need.

Before implementing in Production environment please do testing

Created new  SQL Agent job Copy\Sync-Logins and added a Job Step Sync as shown below :

In Job Step given Step Name as ‘ Sync Logins’ and Type as ‘ Operating System (CmdExec) ‘ and command as ‘powershell -file “C:\temp\Sync-Logins.ps1”

click OK–>Schedule job–>OK–>OK, then the job gets created.

After creating Job Script out and execute it on all secondary replicas.

Whenever there is a modification done on Primary replicas those will get replicated to all secondary when the scheduled job runs.

Please find the Full Script in below link:

https://drive.google.com/file/d/1BhbtBrqBwk_D1jdhHAw9A_idTMA83PFt/view?usp=sharing

Availability Group Database Not Synchronizing / Recovery Pending State

Availability Group Database Not Synchronizing / Recovery Pending State:


This may occur when SQL Server is unable to access the database log files of your Availability Group Database/Databases.This issue can be occurred on Primary Replica and also on Secondary Replica .

Issue happened on Primary Replica:

On Primary Replicas you can check SQL Server error log for database log file is being accessible or inaccessible
Also the error number 9001 is being recorded if the log file is inaccessible

To Resolve the issue on Primary Replicas we can do the following :

  1. Make sure Database files are Accessible.
  2. Try to SUSPEND and RESUME the Availability Group database ,sometimes it may not work and the issue still persists.
  3. Use the below ALTER command to set the database ONLINE ALTER database DBNAME set ONLINE
  4. Wait for few minutes and check the DB status it comes to GREEN and HEALTHY state.

If the Same Issue happens on Secondary Replica:

Check for error message in SQL Server error log for database log file is being accessible or inaccessible, Also the error number 9001 is being recorded if the log file is inaccessible

To Resolve the issue on secondary Replicas do the following :

  1. Make sure Database files are Accessible.
  2. Try to SUSPEND and RESUME the Availability Group database ,sometimes it may not work and the issue still persists.
  3. Here we cannot use Alter command to set the Availability Group database to online as the secondary replica will be on restoring state, the db status showing on sys.databases reports as ONLINE.
  4. We need to RESTART the secondary replica SQL Instance as it will initiate the recovery process of all databases, it also helps to recover the problematic Availability Group database Successfully and
    the DB gets synchronization with Primary Replica.

SQL connectivity issues troubleshooting Steps

As a DBA you may have seen\trying to fix connectivity issues of SQL. So, I thought to put some points here on resolving the connectivity issues to SQL Server, based on my experience I have given below basic steps to follow Below are the points I use to check when you failed to connect SQL Server :

  1. SQL Server configuration Issue
  2. Firewall Issue
  3. Network Issue
  4. Logon Issue
  5. Configuration Issue in Application

SQL Server configuration Issue :
–> Check SQL Server is running on the Machine and is listening on protocols.
–> You can check and enable protocols in SQL Server Configuration Manager.
–> SQL Server to connect remotely Named Pipes and TCP protocols are reQuired, make sure these are enabled
–> In SQL Server Errorlog we can check if server is listening on protocols or not.
–> Also make sure SQL Server Browser is running on the Machine is SQL Instance is a Named Instance.

Firewall Issue:
–> SQL connectivity reQuest can be blocked by Firewall between Client and Server.
–> To fix this we can DISABLE Firewall but it is Temperory Fix as in real time we can disable it Permanentely.
–> The Permanent fix for this is adding SQL Server and SQL Browser in Firewall Exception.
–> While adding the exception for SQL Server DB Engine provide the Port Number in which SQL is listening.
–> While adding exception for SQL Browser provide the UDP port 1434.

Network Issue :
–> The first thing to check for SQL connectivity is Ping response from your SOurce Server to Target Server.
–> You can also do Telnet ‘SQL ServerName’,PortNumber and UDL testing from client machine to SQL Server box.
–> check nslooup, if DNS entry is wrong that may cause the SQL connectivity issue.

logon Issue to SQL Server:
–> This is different from above all cases..This needs to troubleshoot based on the error message which we get from Application team.
–> As it is Login Connectivity issue to SQL, we have to study case by case.
–> If the Login reQuest is coming to SQL box and getting failed then the errors can be recorded in SQL ErrorLogs and event viewer of SQL box.
–> We can go through the logs and fix the issue.

Configuration Issue in Application :
–> If the configuration part in application is wrong then that also can lead to SQL connectivity Issue.
–> Check the connection String Parameters Provided in Application Config File.
–> Also check which Login Account Application is using to connect SQL
–> If it is SQL account check the SQL instance properties which Authentication Mode is Enabled.
–> And check what level of Permissions that Account has.