The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Many of us are data professionals that deal with the acquisition, transforming, storing, querying, and ultimately, display of data. We often spend a lot of effort on that last one, trying to find ways to help our clients easily understand and use their data. In most of my career, the use of text reports was the primary way that people would visualize the data under my stewardship. I remember many ours spent producing tables of data that would print on (first) greenbar paper and (later) standard A4 paper.

As technology has advanced, and become much more powerful, various graphical and other visual representations of data, or based on data, are becoming more prevalent. SQL Server added Reporting Services with SQL Server 2000 and since then our tools have grown with the various "Power" tools, notably Power BI, but also with the ability to integrate our reports into various applications with all sorts of tools.

This week I saw a few items that talked about how we use data to achieve some purpose, and then create some visualization to influence others. I could see this technology being used in lots of ways, some good, and some bad, depending on your perspective. While there are no shortage of business applications, there are others that can help change the way the world works.

There's a new technology to create screens out of store windows, or at least for now, display cases. How long before that becomes personalized in some way, maybe similar to what Minority Report showed us as personal advertising. I could see that the need for real time data integration with customers, or perhaps with the data they share through some Bluetooth broadcast, an RFID customer card, or something else. This is one that gets into the scary category for me because marketing people often can't help but ask for too much data and overuse it.

I am fascinated by maps, and in some sense, I miss the older physical maps that helped me navigate across the country. These days they're not convenient, as up to date, or useful as the digital copies that are much less satisfying. However, the best maps of the future may be for cars, self-driving or not. Whether you like the idea of autonomous cars, having more detailed maps, updated by cars themselves as they drive is interesting. This also presents an interesting data challenge. A multi-mode replication, everyone is a subscriber and publisher, perhaps even voting by multiple nodes to determine what data that is shared to other nodes. I could see interesting visualizations that might point out data useful to drivers, such as potential safety issues (safe speed limits, potholes, condition changes).

Speaking of real time data, we see more and more cases where we use data to respond in crisis. The mining and analysis of data after Hurricane Sandy was used to help responders, and is being used to plan for future issues. I expect that some of us will work in other situations where we need to quickly surface data and help our organization make use of the information, perhaps by quickly altering the gathering or analysis of the data. There are challenges here, and certainly opportunities for those that have the skills to build and alter visualizations quickly.

One of the most popular topics in the technology world of data today is machine learning. It seems that so many vendors and industries are trying to find ways to make sense of their data and turning to various AI type technologies to help them. One interesting one I saw this week was the use of machine learning to help guide students and suggest classes for them. One of the things machine learning algorithms need is lots and lots of data, and I expect more of us to learn how to build, train, and deploy models in various environments. This might be one of the more exciting, but also frustrating since we don't really program the model. We decide what data to feed it and then evaluate its performance, doing our best to produce a better outcome than other efforts. Not how most data professionals want to work, but this will be a way that more and more of us will actually surface information from our troves of data.

We have more and more ways to showcase our data outside of text displays, with more options coming all the time. I see more and more custom visuals for Power BI, new mapping tools, better graphical analysis, and more. I hope that some of you will consider other options the next time a business person asks you to help them analyze some bits.

Steve Jones from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Vendors/3rd Party Products

Extending DevOps practices to SQL Server databases - In this free webinar, Steve Jones and Arneh Eskandari show how to use migration scripts as part of an automated database deployment. For the demo, they’ll be plugging Redgate’s Database DevOps solution into Git, TeamCity and Octopus Deploy....(more)

T-SQL

When Is It Appropriate To Store JSON in SQL Server? - Every once in a while I hear of some technologist say that relational databases are dead; instead, a non-table based NoSQL storage format is the way of the future. SQL Server 2016 introduced JSON functionality, making it possible for some “non-SQL” data storage to make its way into the traditionally tabled-based SQL Server....(more)

COUNT without GROUP BY - I found a novel way of doing a record count today, without using a GROUP BY. Normally I would have joined another results set containing a GROUP BY, but it appears you can use the OVER window function without a PARTITION BY / ORDER BY statement.The syntax ......(more)

SQL SERVER – FIX : Error Msg 8672 – The MERGE Statement Attempted to UPDATE or DELETE the Same Row More Than Once - While I have been blogging about SQL Server for a long time, I still believe that there is still a lot of error which are not discovered by me. It is always fun to see new error and find a fix for the issue. Let us see how to fix error related to MERGE ......(more)

Data Type Mismatches Don’t Always Cause a “Bad” Implicit Conversion and Index Scan - One of the reasons that I enjoy getting questions is that I can try to help others, and it helps me be lazy: I don’t have to think of blog topics myself. Here’s a great recent question that I got about query tuning and index use: Assuming that the documented ......(more)

How to Compute Date and Time Difference in Parts - Sometimes you need to use T-SQL to compute the time passed between two events whose date and time values you get as inputs. For some purposes, it’s sufficient to compute the difference as a duration in terms of some part. What’s significantly trickier ......(more)

Testing Software

Using tSQLt to Find Min/Max Times - I love tSQLt. It’s a good way to write tests that can determine if your code is actually working. Since I’m a fan of unit testing, I think using tests to verify your logic is great. What’s excellent with tSQLt is that I can verify a number of cases at ......(more)

Tech News : General Interest

How does IBM Watson search TED Talks? - Learn how you can open up non-text content to search with deep learning.Continue reading How does IBM Watson search TED Talks?. ...(more)

How does Facebook recognize my face and the faces of friends and family? - Learn how Facebook and other trailblazers use AI technologies to recognize human features.Continue reading How does Facebook recognize my face and the faces of friends and family?. ...(more)

How does Google's driverless car work? - Learn how deep learning has accelerated the realization of driverless vehicles and what that means for the future.Continue reading How does Google's driverless car work?. ...(more)

Nvidia and Bosch team up to build an AI supercomputer for your self-driving car - Enlarge / A cutaway image of the Bosch/Nvidia car supercomputer. (credit: Nvidia) It seems that barely a day goes by without news of a tech company teaming up with the auto industry to advance the art of self-driving vehicles. On Tuesday, it was Nvidia ......(more)

These recently declassified nuclear test videos are utterly mesmerizing, terrifying - Enlarge (credit: US Department of Defense) From 1945 until the practice was ended in 1963 with the Limited Nuclear Test Ban Treaty, the US conducted 210 above-ground nuclear weapons tests. The majority of those took place at the Nevada National Security ......(more)

SQL Server Security and Auditing

T-SQL Tuesday #88 – The security audit WTF - In this post I want to share the stored procedure I wrote to audit the different logins and server permissions within an instance. This post is part of the T-SQL Tuesday, which is a monthly blog party on the second Tuesday of each month. Everyone ......(more)

SQL Server Security

Transparent Data Encryption and Replication: SQL Server’s Rear Window - You think your SQL Server data is encrypted? If you use replication, you might be in for a nasty surprise. ...(more)

SQL Server on Linux

SQL Server on Linux: Running jobs with SQL Server Agent - In keeping with our goal to enable SQL Server features across all platforms supported by SQL Server, Microsoft is excited to announce the preview of SQL Server Agent on Linux in SQL Server vNext Community Technology Preview (CTP) 1.4. SQL Server Agent ......(more)

SQL Server News

Released: Public Preview for Microsoft Azure SQL Database Management Pack (6.7.25.0) - We are working on significantly updating the Management Pack for Azure SQL Database. Your feedback on this public preview will improve the quality of the final release. Please install the bits from the download location below and send us your feedback. ...(more)

Released: System Center Management Pack for SQL Server and Dashboards (6.7.20.0) - We are happy to announce that updates to SQL Server Management Packs have been released! Downloads available: Microsoft System Center Management Pack for SQL Server 2016 Microsoft System Center Management Pack for SQL Server 2014 Microsoft System Center ......(more)

SQL Server next version CTP 1.4 now available - Microsoft is excited to announce a new preview for the next version of SQL Server (SQL Server v.Next). Community Technology Preview (CTP) 1.4 is available on both Windows and Linux. In this preview, we added the ability to schedule jobs using SQL Server ......(more)

Software Development

An Introduction to Containers and Docker - By Chris GibsonThis is the debut blog post of Chris Gibson. Chris is in expert in Microsoft Windows and is turning his attention more and more to the Cloud. You can read more about Chris on the About Us Page ...(more)

Security news and thoughts

Digital Security Exchange: Security for High-Risk Communities - I am part of this very interesting project: For many users, blog posts on how to install Signal, massive guides to protecting your digital privacy, and broad statements like "use Tor" -- all offered in good faith and with the best of intentions -- can ......(more)

IoT Teddy Bear Leaked Personal Audio Recordings - CloudPets are an Internet-connected stuffed animals that allow children and parents to send each other voice messages. Last week, we learned that Spiral Toys had such poor security that it exposed 800,000 customer credentials, and two million audio recordings. As ......(more)

Using Intel's SGX to Attack Itself - Researchers have demonstrated using Intel's Software Guard Extensions to hide malware and steal cryptographic keys from inside SGX's protected enclave: Malware Guard Extension: Using SGX to Conceal Cache Attacks Abstract:In modern computer systems, user ......(more)

New Gmail Phishing Scam - The article is right; this is frighteningly good. ...(more)

Security and Reliability Top Hosting Concerns for Web Businesses - There is no doubt that the adoption of web and cloud technologies continues to accelerate at a phenomenal rate. A recent survey by Liquid Web, LLC, a $100 million managed hosting provider focused on web-dependent professionals, found nearly 80 percent ......(more)

How did Yahoo get breached? Employee got spear phished, FBI suggests - Enlarge / Dmitry Dokuchaev, Igor Sushchin, Alexsey Belan, and Karim Baratov—the four indicted by the US in the Yahoo hacking case. SAN FRANCISCO—The indictment unsealed Wednesday by US authorities against two agents of the Russian Federal Security ......(more)

Reporting Services

Query designer support for DAX now available in Report Builder and SQL Server Data Tools - Today, we’re pleased to announce a long requested feature from the SQL Server Reporting Services community. With the latest releases of Report Builder and SQL Server Data Tools – Release Candidate, you now have the ability to create native DAX queries ......(more)

R Language

Neural Networks: How they work, and how to train them in R - With the current focus on deep learning, neural networks are all the rage again. (Neural networks have been described for more than 60 years, but it wasn't until the the power of modern computing systems became available that they have been successfully ......(more)

Product Upgrades and Releases

T-SQL Programming for Developers Course - Are you a software developer? Did you ever get to develop a software that works with a Microsoft SQL Server database? Perhaps you started out all nice and good, seeing how well and easy SQL Server works, but then with time, as your software grew and ......(more)

Product Reviews and Articles

Creating database documentation in ePub format using SQL Doc and PowerShell - With SQL Doc and a bit of PowerShell, you can automatically generate database documentation in a range of formats, including help files, Markdown files, Word documents, or an intranet site of HTML files. Sometimes, however, SQL Doc can’t give you the ......(more)

Automatically build-and-fill multiple development databases using PowerShell and SQL Data Generator - When you are working with a database, you always need data. This is why you need to stock the database with data after you build it. Sometimes, you just want a large number of made-up customer details, sales figures or the like. You also will need columnar ......(more)

Time travelling with SQL Compare - In SQL Server 2016, Microsoft introduced support for system-versioned temporal tables. Temporal tables are a database feature that provides information about data stored at any point in time, rather than only the data that is correct at that specific ......(more)

3 Results Grid features SQL Prompt brings to SSMS - You may not have heard about it yet, but three helpful features were recently added to SQL Prompt that are activated when you right-click on the Results Grid in SQL Server Management Studio (SSMS). 1. Copy as IN clause This feature is available when ......(more)

How to use custom filters in SQL Compare - I used to work at JD Edwards, and in one of our ERP products we had a cumbersome database schema. There were tables like this: These were the tables that held various information in the system. Trying to find data was an exercise in frustration, even ......(more)

Book Review: Testing R Code - When it comes to getting things right in data science, most of the focus goes to the data and the statistical methodology used. But when a misplaced parenthesis can throw off your results entirely, ensuring correctness in your programming is just as ......(more)

PowerShell

PowerShell: Managing Service(s) Using WMI, CIM and Get-Service Cmdlet - A common task of any administrator is to manage and maintain the application availability by checking the various services status, accomplish configuring the services and restarting the service on a local/remote system. There are several components ......(more)

Adding a PowerShell Job Step to an existing SQL Agent Job Step with PowerShell - In my last post I showed how to add a T-SQL Job step to an existing SQL Agent Job. The process is exactly the same for a PowerShell job step. As before I gathered the required jobs using Get-SQLAgentJob command from the sqlserver module which you can ......(more)

Check Those Settings - Recently, I was tasked with “enhancing” a third party application. This third party application (TPA) outputs a bunch of files to a file share in a way that makes sense to the application, but makes no sense to a human. The Task Make a copy of these ......(more)

Defending Invoke-SqlCmd - Twitter. It can be annoying, it can be frivolous, but you’d be surprised at how many times it gives me a blog topic. This time around, I was having a back and forth with some folks about Invoke-SqlCmd. I had recommended someone try using it, which was ......(more)

PowerPivot/PowerQuery/PowerBI

Red, Green, Yellow Icons in Power BI - This week during Avi’s Power BI “Let’s talk Power BI” and the internal Power BI office hours I heard the question “How do i get excel like stop lights in my Power BI Report” If black white “images” will work you might check out my earlier post: https://blogs.msdn.microsoft.com/charles_sterling/2017/02/10/status-icons-in-power-bi-tables/ If ......(more)

Power BI Custom Visuals Class (Module 42 – Stars) - In this module you will learn how to use the Stars Power BI Custom Visual. The Stars visual is a great way to visualize ratings data. Module 42 – Stars Downloads Power BI Custom Visual – Stars Dataset – Movie Ratings.xlsx Completed Example – Module ......(more)

Polybase/HDInsight

Polybase Use Cases - James Serra talks about Polybase use cases: For federated queries: “N” requires all data from the source to be copied into SQL Server 2016 and then filtered. For “Y”, the query is pushed down into the data source and only the results are returned back, ......(more)

Policy Based Management

Why Cost Threshold For Parallelism Shouldn’t Be Set To 5 - CURRENT_YEAR() Here it is, 2017. A full 20 years since SQL Server 7. Back then, there was a developer named Nick. Nick was on the development team, and apparently the query-costing-magic development was done on his machine. All of it. The magical metric ......(more)

Performance Tuning SQL Server

Performance Myths : Clustered vs. Non-Clustered Indexes - I was recently scolded for suggesting that, in some cases, a non-clustered index will perform better for a particular query than the clustered index. This person stated that the clustered index is always best because it is always covering by definition, ......(more)

SQL WTF for T-SQL Tuesday #88 - The topic for this month’s T-SQL Tuesday is: “Be inspired by the IT horror stories from http://thedailywtf.com, and tell your own daily WTF story. The truly original way developers generated SQL in project X. Or what the grumpy "DBA" imposed on people ......(more)

The Performance Penalty of Bookmark Lookups in SQL Server - (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.) I have previously very often talked on my weblog about ......(more)

SQLskills SQL101: Indexing Basics - SQLskills introduced our new SQL101 recently and well… indexing is something that everyone needs to get right. But, it’s not a simple task. And, as I start to sit down to write a SQL101 post on indexing, I suspect I’m going to struggle keeping it simple? ......(more)

SQLskills SQL101: Temporary table misuse - As Kimberly blogged about recently, SQLskills is embarking on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there ......(more)

No seriously, don’t use SQL_VARIANT for that - I’d been meaning to write this for a while I half-stumbled on the weirdness around SQL_VARIANT a while back while writing another post about implicit conversion. What I didn’t get into at the time is that it can give you incorrect results. When I see ......(more)

Indexed Views And Data Modifications - Love/Hate When indexed views help, they make aggregation queries super fast. There are some kind of horrible limitations and requirements. When they hurt, your modifications slow to a crawl because SQL has to not only keep your horrible indexes synchronized, ......(more)

Why Indexes Reduce Locks for Update and Delete Queries - I have a cat. Well, she’s Jeremiah’s cat, really, and you’ve even seen her: she’s in the logo of this website! She supervises my work most days at home from a cat bed in my office. I’d love to hug her all the time. But kitty has claws, and she makes ......(more)

Microsoft News

Why Microsoft Says ARM Chips Can Replace Half of Its Data Center Muscle - There are technological advantages, but it’s mostly about creating more competition for Intel. Read More ...(more)

MDX/DAX

DAX Studio 2.6.0 Release - Originally posted on: http://mosslover.com/darrengosbell/archive/2016/12/21/dax-studio-2.6.0-release.aspxWe seem to somehow have gotten into the habit of doing pre-Christmas releases every year, so why break with tradition now :) The latest release of ......(more)

SSMS DAX Query Editor - We are excited to announce the SQL Server Management Studio DAX Query Editor! Have you ever authored a DAX query in SSMS using the MDX editor? With the new DAX Query Editor, you no longer need to do. Download RC3 for vNext from the SSMS release candidate ......(more)

Context Transition and Expanded Tables - This article describes how table expansion and filter context propagation are important DAX concepts to understand and fix small glitches in DAX expressions. I keep on telling my students how important is – with DAX – to spend time learning the theory ......(more)

Hardware News

The Chuwi LapBook 14.1 Review: Redefining Affordable - In this industry, it is all too easy to focus only on the high end of the PC market. Manufacturers want to show off their best side, and often provide samples of high-end, high-expense devices more than their other offerings. While these devices are ......(more)

Seagate Announces Enterprise Capacity 12 TB HDD: 2nd-Gen Helium-Filled Hard Drives - Seagate has introduced its second-generation of helium-filled HDDs. These drives will be aimed at capacity-demanding enterprise and cloud applications, and the new drives store up to 12 TB of data. The new drive uses eight platters, which is more than ......(more)

MWC 2017: Panasonic Demonstrates Store Window as a Transparent Screen - At Mobile World Congress this year, Panasonic demonstrated a glass that can be turned into a display in an instant. The solution relies on a thin film between the sheets of glass that can quickly change its properties when electricity is supplied, allowing ......(more)

HA/DR/Always On/Clustering

SQL SERVER – Availability Group Missing or Deleted Automatically? - As a part of my passion, I try to help people in fixing simple issues and explain the behavior by looking at logs. Here is an email from my friend about Availability Group Missing or Deleted Automatically. Pinal, We need your services again to help ......(more)

Availability group replica murdered in cold blood by WSFC - This problem was first flagged by a daily operational validation test I wrote using everything in my recent webinar to health check all of my AG components. My test wrapped some of the logic of the Test-Sql* functions built into the SqlServer module: Test-SqlAvailabilityGroup Test-SqlAvailabilityReplica Test-SqlDatabaseReplicaState These ......(more)

ETL/SSIS/ELT

SSIS: Capturing PerfMon Counters During Package Execution - While troubleshooting SSIS package performance, having the ability to view the performance monitor counters can be handy for determining where bottlenecks may be occurring. Usually, however, this is a very reactive request. The package is slow, so perfmon ......(more)

Run a Job on SQL Azure (Using SSIS Package) - Requirements: A valid azure account, Local Instance of SQL Server (>= 2012), SQL Server Data Tools, Excel 2007 or aboveScenario: Dump Excel File Contents to a Table in SQL AzureSteps: Develop SSIS Package-Drag Data Flow Task on the control flow window ......(more)

T-SQL Tuesday #88 – The daily (database-related) WTF - It’s the time of the month again: the T-SQL Tuesday! This month’s edition is hosted by Kennie Nybo Pontoppidan (blog|twitter) and the topic is “daily database related WTF”. In other words, we get to tell one of our horror stories we encountered in our ......(more)

DocumentDB/Key-Value NoSQL Databases

Querying Documents With Different Structures in Azure DocumentDB - This is a quick post to share how we can use the coalesce operator in Azure DocumentDB (which is a schema-free, NoSQL database) to handle situations when the data structure varies from file to file. Varying data structure is a common issue in big data ......(more)

DevOps and Continuous Delivery (CI/CD)

Instagram's 5 principles for implementing and scaling continuous deployment - How to approach continuous deployment at your own organization.Continue reading Instagram's 5 principles for implementing and scaling continuous deployment. ...(more)

Data Science

Updates to the Data Science Virtual Machine for Linux - The Data Science Virtual Machine (DSVM) is a virtual machine image on the Azure Marketplace assembled for data scientists. The goal of the DSVM is provide a broad array of popular data-oriented tools in a single environment, and make data scientists ......(more)

The Best Academic Programs in the USA for Data Science - When the 2011 McKinsey report estimated that 2018 will witness a severe shortage of about “140,000 to 190,000 Data Analytics experts” in the US industry, the universities began to wake up to the need for new and improved US academic programs in Data ......(more)

Nearly Nine Out of Ten Consumers Concerned About Data Security and Privacy - by Angela Guess A recent press release states, “Results of an international study from Verint® Systems Inc. have shown that concerns around data privacy and security remain at the top of the consumer agenda. Of more than 24,000 consumers surveyed across ......(more)

Defining a Data Risk Strategy for an Organization - Click to learn more about author Tejasvi Addagada. Why Defining a Data Risk Strategy is Crucial? Risk and Value are two sides of the same coin. Value realization along with benefits management is vital to appreciate the strategic goals of an organization. ...(more)

Data Privacy

We’ve lost control of our personal data (including 33M NetProspex records) - Sponsored by: Checkmarx – Application Security Testing Developers Actually UseEarlier this week, I read a really interesting piece on 3 things that need to be done to save the web. The first observation was that "we’ve lost control of our personal data" ......(more)

How EU's Data Privacy Law Will Impact You - The EU's new General Data Protection Regulation or GDPR offers data protection for consumers and harsh penalties for violators. Here's what you need to know. ...(more)

Data Mining/Data Analysis

Data Preparation and Data Analysis Don’t Have to be Painful - Consider the time and effort involved in the ‘old world’ of Business Intelligence data preparation! How on earth did we ever get anything done? Armies of data analysts and IT staff slaving over data, compiling, analyzing, graphing, charting, taking requests ......(more)

When Is “More Data!” The Wrong Solution? - Click to learn more about author Karl Zimmerman. Data-driven decision-making is vitally important to modern enterprises. Without the analysis of data, we can’t hope to make intelligent decisions about how to achieve our business goals. The data we have ......(more)

Conferences and Events

SQLSaturday #584 - Colorado Springs 2017 - Join this free, one day SQL Server conference....(more)

SQLSaturday #597 - Phoenix 2017 - Join this free, one day SQL Server conference....(more)

The Recording for the SSIS Catalog Compare Version 2 Launch Event is Available - The recording for the SSIS Catalog Compare Version 2 Launch Event is now available for viewing (registration required). Enjoy! :{> Learn More: SSIS Catalog Compare...(read more) ...(more)

Introducing Microsoft Data Amp - This post was authored by Mitra Azizirad, Corporate Vice President, Cloud Application Development & Data Marketing, Microsoft Today, I am excited to announce that on April 19, we will host a new online event, Microsoft Data Amp. Microsoft Data Amp is ......(more)

PASS Business Analytics Virtual Group March 21 – Excel to Power BI - The next PASS Business Analytics Virtual Group meeting will be next week on Tuesday, March 21. We welcome Avi Singh who is a Microsoft Data Platform MVP, trainer, and author and he will be presenting on taking the leap from Excel to Power BI. –> REGISTER ......(more)

The Livestream Product Launch - SQL in the City Streamed in 2016 was a success, with lots of feedback that people enjoyed the sessions and format. With the release of SQL Clone, we’ve decided to do it again. The SQL Clone Product Launch Live Stream is coming in a couple weeks, on ......(more)

Comparing Conferences: AWS re:Invent, Google Cloud Next, Microsoft Ignite, PASS Summit, and SQL Intersection - I have this weird job where I get to pick which conferences I go to, so, I, uh, go to a lot of them. Here’s how a few of them stack up. Amazon Web Services re:Invent Audience: About 30,000 AWS users. Database session quantity: not a lot. (I didn’t go ......(more)

Computing in the Cloud (Azure, Google , AWS)

New White Paper: How to Build a SQL Server Disaster Recovery Plan with Google Compute Engine - You’re a systems administrator or database administrator who wants to protect your production SQL Server. However, you don’t have a separate data center or another colo site. Learn about what we call Log Shipping 2.0 You’re looking for instructions on: How ......(more)

Azure Managed Disks Deep Dive, Lessons Learned and Benefits - Introduction Managed Disks (MD) have been recently released for production (February 2017), and represent an important step to make Azure VM management much easier. In short, Azure Managed Disks is an abstraction built on top of current Standard and ......(more)

Community Sites

Thinking about Chapters (aka Groups) - I think it was 2003 or 2004 when someone from PASS came to Orlando and helped start what became OPASS. They stressed the need to find speakers each month and the need to have multiple people involved so that the group wouldn’t die if someone moved on. ...(more)

Columnstore Indexes

Columnstore Index – List of blogs on columnstore index published by SQL Server Product Team - SQL product team has made significant implements in columnstore index functionality, supportability and performance during SQL Server 2016 based on the feedback from customers. SQL Server product team has created a large set of blogs across multiple ......(more)

Career Growth

Why Automate? - Because, as Jen Underwood ( jenunderwood.com | LinkedIn | @idigdata ) states in an upcoming podcast: The future of data science is automation . If automation is the future, how do we decide what to automate? We look for the long pole . What’s the long ......(more)

Big Data

Big Data Processing and McKayla Maroney Face - I've been taking a few steps down a path that's new for me. So keep that in mind if you've been down this path too. Because you've likely traveled farther than me and the lens you look through offers more clarity than mine. What's prompted this post ......(more)

Backup and Recovery

Backing up a VLDB to Azure Blob Storage - Reviewed by: Pat Schaefer, Rajesh Setlem, Xiaochen Wu, Murshed Zaman All SQL Server versions starting from SQL Server 2012 SP1 CU2 support Backup to URL, which allows storing SQL Server backups in Azure Blob Storage. In SQL Server 2016, several improvements ......(more)

Azure SQL Data Warehouse and Data Lake

Copying data from On Prem SQL to ADLS with ADF and Biml – Part 2 - I showed in my previous post how we generated the datasets for our Azure Data Factory pipelines. In this post, I’ll show the BimlScript for our pipelines. Pipelines define the activities, identify the input and output datasets for those activities, and ......(more)

Analysis Services / BI on the MS Stack

What makes a Data Source a Data Source? - It should be obvious, and it is — at least at the Tabular 1200 compatibility level: A data source definition in a Tabular model holds the connection information for Analysis Services to connect to a source of data such as a database, an OData feed, or ......(more)

AI/Machine Learning/Cognitive Services

Partner Center and the Bot Framework - The Microsoft Bot Framework is a comprehensive offering that is utilized to build and deploy high quality bots for users to enjoy. It provides just what is needed to build, connect, manage and publish intelligent bots that interact naturally wherever ......(more)

Part 4 – Live Streaming Weather Station with Cortana Analytics (Online Data Analytics with Azure ML) - In previous posts, I have showed the process of creating a live weather data streaming with Cortana. In Part 1 the process of setting up Raspberry PI3 and weather station sensor has been explained. Following, in Part 2, the process of setting up the ......(more)

Administration of SQL Server

How to Get Status of Running Backup and Restore in SQL Server? – Interview Question of the Week #113 - Question: How to Get Status of Running Backup and Restore in SQL Server? Answer: One of the reasons I started my blog long ago was to have a repository of scripts which can be very useful by DBA during daily troubleshooting. There are many times when ......(more)

SQL Server Diagnostic Information Queries for March 2017 - This month, there are minor updates for the SQL Server 2012 and newer version queries. Rather than having a separate blog post for each version, I have just put the links for all seven major versions in this single post. There are two separate links ......(more)

SQL Server PBM: On Schedule Failures - I tend to like the automation and convenience of using Policy Based Management (PBM) to check simple things like my backups have been running, there’s enough disk space on my servers, and the consistency of my configurations (min/max server memory, ad ......(more)

SQL Server Query Store - Query Store was, probably without doubt, the most anticipated and talked out new feature in SQL 2016. Certainly by the DBA community. In this post we’ll just take a brief look at Query Store, what it is, how you set it running, and what you can use it ......(more)

Why You Should Change the Cost Threshold for Parallelism - I’ve written several times about the Cost Threshold for Parallelism and it’s relationship to your execution plans, how to determine your execution plan cost, and even how to decide what value to set your Cost Threshold to. What I haven’t explicitly addressed ......(more)

A Collection of SQL Server Help Scripts - Like probably every SQL DBA, consultant, architect etc etc out there that has ever worked on or used SQL Server they will likely have their own personal collection of SQL Server Help Scripts. So not unsurprisingly I also have such a collection – and ......(more)

TempDB Multiple Datafiles - ProblemMost DBAs are aware that (prior to SQL 2016) one of the most common, post installation changes that needs to be made in SQL Server is the addition of extra TempDB datafiles. What seems to be less well known is that ALL these files all need to ......(more)

Insufficient Disk Space (T-SQL Tuesday #88) - This month’s T-SQL Tuesday – hosted by Kennie T Pontoppidan(@KennieNP) – is called “The daily (database-related) WTF“. He asked us to be inspired by the IT horror stories from http://thedailywtf.com, and tell our own daily WTF story. Years ago in a previous ......(more)

Global v Session Trace Flags - I wrote a short article on enabling and disabling trace flags. You can read it, but I didn’t really discuss the implications of session v global trace flags, which is something I’d like to do here. In the article, I set trace flag 3226 for my session. ...(more)

SQL Server: DBCC CHECKDB Command with Complete Explanation - SQL Server is used widely for the management of database. It is possible that the data gets corrupt due to different reasons. Therefore, to deal with this issue SQL Server has a command, which act as a manual solution for corruption of the database. This ......(more)

Schedulers To Rule Them All - Sometimes things just don’t make sense. One of our production servers has 4×8 (4 sockets, 8 core) processors with hyper threading enabled. This results in 32 physical cores or 64 logical cores. Therefore, this particular server should have 4 NUMA nodes ......(more)

Using a Tail Log Backup in a SQL Server Migration (Dear SQL DBA Episode 34) - When you migrate a database, it can be useful to prove that you moved all the data and didn’t miss any transactions. Learn how to use a tail log backup in a migration scenario. Watch this week’s 13 minute video. Subscribe to my YouTube channel, or check ......(more)

Summary of my Container Series - I’ve written a few articles on containers so I thought it’d be a good idea to have a page that listed them for easy reference. So here’s all the posts: – Running your first container Building a custom image Pushing images to the Docker Hub Using WinDocks ......(more)