SQL Spot – Part 2

Hi there. Back again with more splendid community articles. Once again I tried gathering some noticeable information for you on common SQL Server topics regarding business, BI, Data Science, Azure, maintenance, tuning, learning and more…. As always, all rights go out to the community for putting in all the work.

Business, releases and news

Business

• Remember to have your data recovery plan ready, as it was proven with the latest AWS outage that cloud services are not bulletproof.
• The impact of machine learning and automation on the job market: Will data scientists program themselves out of a job?
• Why Microsoft chose to introduce R in the SQL Server platform?
• What’s new for SQL Server offering in Azure Virtual Machines via Azure Marketplace?
• How safe is your data? Get some insight on brainstorming and measuring on how safe it really is.
• Is your business managing data correctly, make sure you are not violating one of these 5 deadly data management sins?
• If you are wondering about Azure SQL Data Warehouse: here are Microsoft Enterprise Services tips to using and implementing it effectively. Microsoft offers a full month trial to all customers and clients who are interested.
• Looking to provide a data drive culture within your business: see why Power BI is your main tool of choice.

Releases

• SQL Server vNext CTP 1.3 is generally available.
• There is a new SSDT Release candidate with support for SQL vNext.
• Microsoft announces the availability of an in-browser query tool that provides you an efficient way to execute queries on your Azure SQL databases and SQL data warehouses without leaving the Azure Portal.
• Ready yourself for SQL Server in docker containers: here is some insight on concept of docker and SQL Server containers and how to spin a container from a docker file.
• Announcing the data migration assistant: DMA replaces all previous versions of SQL Server Upgrade Advisor and should be used for upgrades for most SQL Server versions.
• Make note of the nearing release for R tools for Visual Studio.

Trivial

• It doesn’t hurt to be proud once in a while. Microsoft has picked up our Power BI version of the Gartner Magic Quadrant in their featured data stories gallery.
• An AI robotic story: The feature AI boss from the movies might be closer then we think !

Power BI

General

• Were you also frustrated when Visio 2013 kicked the data diagrams tool sets?  Here are some thoughts on integrating Power BI and Office Visio.
• Ever wondered about what the main difference is between M and DAX languages?
• Do we still need a date dimension ? Many client tools seem to pre-chew them for us nowadays.

Power BI

• Connect Power BI to semantic models provided in Azure Analysis Services.
• Announcing general availability of Power BI Real-Time Streaming Datasets.
• Announcing the availability of the Power BI report web part for SharePoint Online, allowing you to integrate Power BI reports in Sharepoint.
• A nice tutorial on using Arcgis Maps in Power BI to bring you some new cool map visualization capabilities.
• Get introduced to Power BI custom visuals based on R.
• Dynamically change shapes in Power BI with the help of a little R.
• Check out: Power BI Service February feature summary and March updates
• Check out: Power BI Desktop February feature summary and March feature summary.

Excel BI

• Power Query might be the future for Excel as the newer  get and transform will replace the older methods of loading data.
• Combine CSV files from a folder using Power Query

SSIS

• See this good example of deploying SSIS packages using Powershell.

SSAS

Tabular 2016

• The tabular object model (TOM) serves as an API to create and manage partitions. TOM was released with SQL Server 2016 and is discussed here. The Automated Partition Management for Analysis Services Tabular Models whitepaper is available here. It describes how to use the AsPartitionProcessing TOM code sample with minimal code changes.

SSAS Azure

• Create your first data model and host it in Azure Analysis Services, so your users can connect to it through Excel, Power BI and more.
• Where does Azure Analysis fit exactly in the nowadays BI Landscape ?

SSAS vNext

• CTP 1.3 introduces encoding hints, which is an advanced feature used to optimize processing (data refresh) of large in-memory tabular models.

DAX

• Use the divide function instead of the mathematical divide to overcome some common errors.
• Ever needed to provide dynamic other groups when showing data in categories?
• Check out the IN operator, which simplifies logical conditions checking whether a certain value is included in a list of values or expressions.
• This article describes how to create a virtual relationship in DAX using the TREATAS function, which is more efficient than approaches based on INTERSECT or FILTER.
• Introducing the SUMMARIZECOLUMNS function which might replace the SUMMARIZE function due to some performance issues.

SSRS

• Grasp the difference between operational reporting and analytics.
• Convert natural earth data into SQL Server and use it in maps within your SSRS reports.
• Get an idea on how you can scale out SSRS on a standard edition.

Data science

Machine Learning

• To use the data, it needs to be cleansed, transformed and processed to a format that we can use to build predictive models. This process is called the data science process and it can be leveraged using machine Llearning techniques.

R

• Use R packages in SQL and leverage the results using Power BI
• Use global maps in R
• dplyr is a powerful R package to manipulate, clean and summarize unstructured data.

Stream Analytics

• Azure stream analytics now supports geospatial functions, low latency dashboarding and more.

Cognitive Services

• Here’s a nice example on data text analyses using some U.S. presidential election data.
• Get introduced to Azure Cognitive Services and use text analytics APIs.

Maintenance

Backup and Restore

• Lessons to be learned from the big Gitlab outage: take care of your backups.
• Introducing enhanced automated backup for SQL Server 2016 in Azure virtual machines.
• Learn about write ahead logging: the concept that can help you make sense of recovery models and backup strategies in SQL Server.
• Get a grasp on how the get information out of your backup file using RESTORE HEADERSONLY and how you can use that information to build an automated restore code script solution.

Storage

• If you need to run SQL Server in an Azure virtual machine, your choice of Azure storage will have a great effect on its performance. Look here for some introduction on making your decisions.

Health Practices

• Get introduced to SQL Server trace flags. Once you have done that get some pro advice on important flags out there.
• Some SQL Server default settings are not what they should be. Read on some main settings your better change post installing SQL Server.
• Get some insight in your SQL Server with the help of Brent Ozar’s sp_blitz solution.

Index

• Kimberley Tripp releases a new version of her sp_helpindex solution, with newer functionality (included columns and filters) as well as being better at describing what’s really in your indexes.

Troubleshooting

• Also tired of SQL Server agent jobs not delivering enough info on failure? Here’s an approach for better SQL agent jobs feedback based on the full history of the job.
• Understanding deadlocks and different types of deadlocks in SQL Server and troubleshooting for beginners.
• Use the SQL Server activity monitor introduction.

Security

• Microsoft announces general availability in April regarding Azure SQL database threat detection.
• Get your knowledge on SQL Server encryption up to speed on column encryption, TDE and SQL Server 2016 always encryption.
• Get started with Azure SQL database auditing features like Table auditing and Blob auditing to help you identify business concerns or security violations.

Tuning

Internals

• Be aware that SQL 2016 holds a new auto update statistics methodology which might impact your performance.
• An interesting aspect on using R to define the cost threshold for parallelism for a database in SQL 2016.
• Get introduced with Max Worker threads and when or not to mess with it. As a follow up read about what the effects can be if you play with it.
• How busy is your SQL Server? Understand batch requests per second.
• Read up on some new query plan improvements in 2016 to help you out with your query tuning.
• Get a basic understanding on how joins work internally within SQL Server.
• Insights on parallel execution within SQL Server 2016.
• What about the optimize for AD HOC workloads setting: the how and why behind the setting.

Index Tuning

• Update your concepts around index types.
• Get some starter scripts on missing indexes, unused indexes, statistics details and identifying SQL wait statistics.
• Here’s an overview of columnstore indexes in SQL Server.
• What is columnstore segment elimination within indexes?
• Get some insight on memory grants and data size on how index tuning and data types might make a difference if you are struggling with memory.

In-memory optimization

• Improve query performance on memory optimized tables with temporal using new index creation enhancement in SP1.
• Improve performance exchanging temp tables with in memory optimized tables: part 1, part 2, part 3.
• The usage of temporal tables in SQL Server 2016 drastically increases the performance on historical logging of records.

Practices

• Use DBCC CLONEDATABASE instead of full backups for easier testing and tuning your queries.
• Breaking text into fragments can bring some relief to otherwise expensive leading wildcard searches in you T-SQL Scripts.
• Get some insight on building high performance stored procedures.
• Don’t go blindly adding crappy missing index requests.

Learning

Samples

• Change in-database samples with SQL 2016: bye bye Adventureworks, welcome WideWorldImporters datasets.

Courses

• Have 28 blog posts help you out on learning SQL Server wait types from the beginning.
• Microsoft offers Visual Studio subscribers free access to pluralsight’s technology learning platform.

Literature

• Get your free guide to text mining with R.
• Get a free Power BI E-book.

Vids

• One of the more interesting Channel 9 series to follow: data exposed.
• Some more learning vids from MSSQLTIGER January 2017 PASS Virtual Conferences, showing you how SQL Server 2016 changes the game using the power of In Memory OLTP.

General

• What makes a good online presentation?