SQL Server built-In monitoring components

First, It should have been determined on where/what to monitor for the current situation. right ? Yes!

And, select an appropriate tools to proceed with. It can be either from Windows Itself Or SQL Server specific

Windows Monitoring Tools:

• System Monitor
• Performance Monitor
• Task Manager
• TypePerf

SQL Server Tools:

• Activity Monitor
• Database Console Commands (DBCC)
• Distributed Replay Utility
• Dynamic Management Views and Functions
• Extended Events
• Functions
• SQL Server Management Studio Graphical Showplan
• SQL Server Profiler
• SQL Trace
• System Stored procedures
• Trace flags

Note: 

• SQL Trace and SQL Server Profiler are deprecated. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
• Use Extended Events instead

Source: https://docs.microsoft.com/en-us/

The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory

When I was thinking to access/connect SQL Server using PowerShell in one of the following ways

1. .NET (System.Data.SqlClient)

2. SSMO (SQL Server Management Objects)

3. SQL Server PowerShell

I was actually trying with third method. Yes! unfortunately, It failed. Let me tell you, what I tried and how I resolved.

Open PowerShell ISE and try the below line of command

Connecting SQL from local machine

Import-Module SqlServer 

I got the Err...

Import-Module : The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.

I thought, we don't have the Module in my environment and check the available Modules named "SQLServer" finally

Get-Module -ListAvailable -Name *SQLServer*

I could not find any such a Modules

I tried to Install the Module in my environment, 

https://www.powershellgallery.com/stats/packages

The PowerShell Gallery is the repository from Microsoft

Click and open the specific Module "SQLServer"

I Installed the Module

Install-Module -Name SqlServer -RequiredVersion 21.1.18245 -AllowClobber

Actually, The Package has 23.82 MB

Let the process download the required Package and Install

Once the Installation complete

Let me try to check the Module installed

Get-Module -ListAvailable -Name *SQLServer*

Yes! I can see the Module Installed

Let me try to Import the Module

Import-Module SqlServer

Let us get the List of Databases from my local SQL Instance

Get-SqlDatabase -ServerInstance SQLBUDDY\SQL2019

See, I got the all available Databases in my SQL Instance "SQLBUDDY\SQL2019"

Note: I am using Logged-In same credential to connect with my local SQL Server Instance

File "*.ps1" Cannot be loaded because running scripts is disabled on this system

With this title, I hope you already know what we are going to explore about...

You may get this Err, When you try to execute a PowerShell script file

Usually, This feature may have been Disabled due to security vulnerability

Try enable it and see. That's it!

Let's start here...

Let me try to execute a script file in PowerShell ISE (Integrated Scripting Environment)

Open PowerShell ISE

I have a simple script (Script1.ps1) to connect with my local SQL named Instance

I have opened the script in PowerShell ISE and Trying to execute it. But, I got the below Err

Which means, This feature has been Disabled in this environment, Let us enable it

To check with all available policies and It's status

See, All of them are Undefined

Let us enable one of them

Kindly explore all the available Policies and Scopes HERE, Before enabling it.

See, What we have enabled

Let us execute the script file again and see

Yes! Now It's working...

Post your question If you still have any Err 

Resources...

What is PowerShell?