SQL Server Buddy: 2021

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:

SQL Server Tools:

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?

Generate sequence number using ROW_NUMBER() without sorting the actual column

As we know about Ranking functions to return a ranking/sequence value for each row in a partition based on a specific/set of column(s). Right ?

Let me define a table and values

If Object_Id('dbo.SequenceData') Is Null

Create Table [dbo].SequenceData
(
Col1 Varchar(5)
)
Go

TRUNCATE TABLE [dbo].SequenceData

Insert [dbo].SequenceData
Select 'A' Union All
Select 'D' Union All
Select 'A' Union All
Select 'C' Union All
Select 'B'
Go

SELECT * FROM [dbo].SequenceData
Go

let me generate a sequence number for the value

SELECT ROW_NUMBER() OVER(),* FROM [dbo].SequenceData

Msg 4112, Level 15, State 1, Line 31

The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

But, It's not working, since we need ORDER BY clause specified as per the syntax. right ?

So. let me include ORDER BY clause and see...

SELECT ROW_NUMBER() OVER(ORDER BY Col1),* FROM [dbo].SequenceData

No..No..No, My actual column "Col1" value should not be Sorted. What should we do ?

Let us try something like this

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),* FROM [dbo].SequenceData

We have specified a virtual column in ORDER BY clause which will return always NULL. So, The actual data will not be sorted.

We get a sequence for the column value without sorting it - "AS IS"

DAC or Normally logged-in ?

How do I know whether I have logged-In as DAC or Normally logged-In ?

SELECT 'You have connected as "DAC"' [Who you are]
FROM sys.dm_exec_sessions s join sys.endpoints e
ON (s.endpoint_id = e.endpoint_id)
WHERE e.name ='Dedicated Admin Connection'
AND s.session_id = @@spid
GO

1. Connect with Instance using SQLCMD by Trusted Connection

2. Paste the above script and execute it

Connect normally (Trusted Connection) using SQLCMD :-

Connect as DAC using SQLCMD :-