Microsoft Azure Data Platform – July (2017) update

August 1, 2017 Leave a comment

Microsoft Azure updates curated from July-2017 on: Azure SQL DB/DW, HD Insight, Machine Learning (ML), Azure Data Lake (ADL), Power BI, and more.

1. Handling data encoding issues while loading data to Azure SQL Data Warehouse

2. Super Charge Power BI with Azure Analysis Services

3. Azure SQL Data Warehouse previews 3x compute scale with unlimited columnar storage

4. Public Preview of compatibility level 140 for Azure SQL databases

5. Resumable Online Index Rebuild is in public preview for Azure SQL DB

6. Database Scoped Global Temporary Tables in public preview for Azure SQL DB

7. Azure Data Lake Tools for Visual Studio Code (VSCode) July updates

8. Tableau and Azure SQL DB, a match made in the cloud

9. Artificial Intelligence tunes Azure SQL Databases

10. How Azure Security Center helps protect your servers with Web Application Firewall

11. Introducing the Azure Analysis Services web designer

12. Go serverless with R Scripts on Azure Function

13. HDInsight tools for IntelliJ & Eclipse June updates

14. Model comparison and merging for Azure Analysis Services

15. Enhance Azure SQL Data Warehouse performance with new monitoring functionality for Columnstore

16. Import Power BI Desktop files into Azure Analysis Services


SQL Server 2017 Release Candidate (RC1, full & final version) is available for download

July 21, 2017 1 comment

On 17th July 2017 Microsoft released the full & final Release Candidate 1 (RC1) version of SQL Server 2017.

As announced earlier with the first CTP release, the new SQL Server 2017 will run both on Windows & Linux. Not only Linux, but it will be supported on Docker, and macOS (via Docker) too.

–> Download SQL Server 2017 bits:

To download the SQL Server 2017 you can Register and Download the Full version or Free evaluation version (180 days).

Or, directly download the ISO (~1.7 GB): SQLServer2017RC1-x64-ENU.iso

–> Check version and SQL build:

select @@version

Microsoft SQL Server 2017 (RC1) – 14.0.800.90 (X64)
Jul 11 2017 07:03:16
Copyright (C) 2017 Microsoft Corporation. All rights reserved.
Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 14393: ) (Hypervisor)


–> New Features & Enhancements: This Release Candidate version is the final version of SQL Server 2017 and adds following features:

1. SQL Server on Linux supports Active Directory Authentication, which enables domain-joined clients on either Windows or Linux to authenticate to SQL Server using their domain credentials and the Kerberos protocol.

2. SQL Server on Linux can use TLS to encrypt data that is transmitted across a network between a client application and an instance of SQL Server.

3. Added more model management capabilities for R Services on Windows Server, including External Library Management. The new release also supports Native Scoring.

4. Additional DMVs, enabling dependency analysis and reporting (DISCOVER_CALC_DEPENDENCY, MDSCHEMA_MEASUREGROUP_DIMENSIONS).

5. Added support for SSIS scale out in HA environments, customers can now enable Always On for SSIS.

6. Features already rolled out in previous CTP versions:

– All new features added in SQL Server 2016 SP1, [link].

– New features added in SQL Server 2017 CTP 1.x, [link].

– New features added in SQL Server 2017 CTP 2.x, [link].

–> Videos on SQL Server 2017

Download & Install SQL Server 2017 & SSMS on Windows

Install SQL Server on Linux

–> References:

>> SQL Server 2017 official Page

>> Docs for SQL Server 2017

SQL DBA – Query to check Status of last running SQL Jobs

To check the status of current running jobs you can use the “Job Activity Monitor” feature in SSMS, but sometimes opening and refreshing the tool takes time.

Here is a simple query you can run in SSMS or from any custom tool to get the status of current running jobs:

–> Query #1: This query uses sysjobs view to get the list of all jobs and sysjobhistory view to get the latest status record.

	,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */  
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */  
          + (run_time - (run_time/100) * 100)  /* secs */
           ,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS RunTimeStamp
		WHEN j.enabled = 1 THEN 'Enabled'  
		ELSE 'Disabled'  
	END JobStatus
		WHEN jh.run_status = 0 THEN 'Failed'
		WHEN jh.run_status = 1 THEN 'Succeeded'
		WHEN jh.run_status = 2 THEN 'Retry'
		WHEN jh.run_status = 3 THEN 'Cancelled'
		ELSE 'Unknown'  
	END JobRunStatus
FROM msdb.dbo.sysjobs j
	FROM msdb.dbo.sysjobhistory jh
	WHERE jh.job_id = j.job_id AND jh.step_id = 0 
	order by jh.instance_id DESC) jh
where like 'ADF%'
ORDER BY, jh.run_date, jh.run_time 


–> Query #2; This query uses sysjobs view to get the list of all jobs and sysjobservers view to get the current status of job.

	,IIF(js.last_run_date > 0, 
		DATETIMEFROMPARTS(js.last_run_date/10000, js.last_run_date/100%100, js.last_run_date%100, 
		js.last_run_time/10000, js.last_run_time/100%100, js.last_run_time%100, 0), 
		NULL) AS RunTimeStamp
		WHEN j.enabled = 1 THEN 'Enabled'  
		ELSE 'Disabled'  
	END JobStatus
		WHEN js.last_run_outcome = 0 THEN 'Failed'
		WHEN js.last_run_outcome = 1 THEN 'Succeeded'
		WHEN js.last_run_outcome = 2 THEN 'Retry'
		WHEN js.last_run_outcome = 3 THEN 'Cancelled'
		ELSE 'Unknown'  
	END JobRunStatus
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobservers js on js.job_id = j.job_id
where like 'ADF%'
ORDER BY, js.last_run_date, js.last_run_time 


And if you noted in both the queries above, I’ve used different way to calculate the RunTimeStamp, first by parsing the rum_time column, second by using DateTimeFromParts() function.

Categories: DBA Stuff Tags: ,

SQL DBA – SQL Agent Job history not showing or vanishing away

This happened when we started working on a new SQL Server instance for our DEV environment. The history of SQL jobs was not getting retained after a day or few runs.

I checked on the SQL Agent Properties and found that there are some config values set which were very low:
jobhistory_max_rows = 10000
jobhistory_max_rows_per_job = 100

So I checked on MSDN and found that the max permissible values and set them to 999999 & 49999 respectively.

–> This can also be changed by below T-SQL Query by using the system SP sp_set_sqlagent_properties:

USE [msdb]

EXEC msdb.dbo.sp_set_sqlagent_properties 

Categories: DBA Stuff Tags: ,

SQL DBA – Stop multiple running SQL Jobs at once

Just like my previous post on Enable/Disable multiple SQL Jobs [link], here I will show how can we stop multiple jobs running by SQL Agent at once.

–> Although you can very well Stop a SQL Job in SSMS by simple right-clicking on it. But if you have multiple SQL Jobs running, to stop each of them will be a cumbersome and time taking task.

I will show how this can be done by a T-SQL query:

USE msdb;

-- Stop Multiple running SQL Jobs at once:

SELECT @dynSql += N' msdb.dbo.sp_stop_job @job_name = ''' + + N'''' 
			+ CHAR(10) + CHAR(13)
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobactivity AS ja 
ON ja.job_id = j.job_id
WHERE ja.start_execution_date IS NOT NULL
AND ja.stop_execution_date IS NULL

PRINT @dynSql;

Simply Copy-Paste the the above Dynamically generated SQL Script and Execute it, it will Stop all Running SQL Jobs at once.

Categories: DBA Stuff, SQL Tips Tags: ,