Connect to Azure Data Lake from Power BI

This video describes how to connect to Azure Data Lake from Power BI.

Download link to Azure Open Datasets: 

https://docs.microsoft.com/en-us/azure/open-datasets/dataset-catalog

T-SQL to find Server and SQL restart date time

This script comes handy when you would like to find out the Server and SQL services restart times.

DECLARE @SQLServiceRestartDateTime AS VARCHAR(50),
		@ServerRestartDateTime AS VARCHAR(50)

SELECT @SQLServiceRestartDateTime = create_date FROM sys.databases WHERE database_id=2
SELECT @ServerRestartDateTime = CAST(DATEADD(SECOND, (ms_ticks/1000) * -1, GETDATE()) AS datetime) FROM sys.dm_os_sys_info

SELECT @SQLServiceRestartDateTime AS [SQL Service Start DateTime], @ServerRestartDateTime AS [Server Start DateTime]

T-SQL to find Backup or Restore Progress

Here is a script that comes handy while performing a huge database Backup or Restore. This script provides the details on the progress of the Backup or Restore operation including the estimated finish time.

SELECT 
   session_id AS SPID, command AS [Command], a.text AS Query, start_time AS [Start Time], 
   percent_complete AS [Percent Complete],
   dateadd(second,estimated_completion_time/1000, getdate()) AS [Estimated Completion Time]
FROM sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command like 'BACKUP%' OR r.command like 'RESTORE%') 

T-SQL to find Navigation path in CMS

In large enterprises there will be huge number of SQL servers which will be registered within a Central Management Server (CMS) and times it may become difficult to find out where the server is registered atleast for new team members in the Huge pile of servers and folder structure. This query give you the path where the specified servers is registered within the CMS.

DECLARE @ServerName nvarchar(50)
SET @ServerName='SQL01';

WITH CMSCTE
AS
(
--Anchor
SELECT server_group_id, name, description, parent_id, 1 AS [Level], CAST((name) AS VARCHAR(MAX)) AS CMSPath
FROM msdb.dbo.sysmanagement_shared_server_groups AS A
WHERE parent_id IS NULL
UNION ALL
--Recursive Member
SELECT B.server_group_id, B.name, B.description, B.parent_id, C.[level] + 1 AS [Level], CAST((C.CMSPath + '->' + B.Name) AS VARCHAR(MAX)) AS CMSPath
FROM msdb.dbo.sysmanagement_shared_server_groups AS B
JOIN CMSCTE AS C 
ON B.parent_id = C.server_group_id  
)

SELECT TOP 1 CMSPath AS 'Path in CMS' , B.name as 'Server Name', B.description AS 'Server Description', A.name AS 'Group Name', A.description AS 'Group Description'
FROM CMSCTE AS A
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers AS B
ON A.server_group_id=B.server_group_id
WHERE B.name = @ServerName
ORDER BY [Level] DESC

T-SQL to find Remote Server Name of the linked server

At times it happens that you might have named a linked server with a friendly name that the actual server name. As the time flies and people change in the team, it will become difficult to identify how or to where this is linked.  You can find the information using the sp_linkedservers stored procedure but what if the linked server configured uses a entirely different DNS name than the actual server? The below query helps to get the Remote Server Name with few other details when run against a linked server.  

DECLARE @linkedServerName varchar(100), @sql varchar(max)
SET @linkedServerName='Your Linked Server Name'
SET @sql = CONCAT('SELECT * FROM OPENQUERY([',@linkedServerName , '], 
''SELECT ''''',@linkedServerName,''''' AS LinkedServerName, 
@@SERVERNAME AS RemoteServerName, SUSER_SNAME() AS ConnectedWith, DB_NAME() AS DefaultDB'')')
EXEC (@sql)