The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

What do you do when something goes wrong with an installation or you see some error message that you have never seen before? Most likely, you’ll ‘Google it’ to find the answer. Often, you’ll find a blog post, article, or forum thread describing the same or similar problem along with a possible solution. It’s surprising that most of the time, someone, somewhere, has already encountered the issue and figured it out. If you don’t find the answer, you can always post your question on a forum or even on Twitter and, usually, someone will provide an answer in a short time. I’ve noticed that the same names show up with the answers, blog posts, and articles again and again for a given subject area. These people are the experts that we rely on. I often wonder how they know so much and how they became experts.

My first thought is that they must have ‘insider information,’ such as some friends at Microsoft. For some experts this is true. I recently ran into a technical issue on Azure while editing an article. It turned out that a recent update had introduced a bug to the functionality I was testing. The author I was working with was able to find out from the Azure team when the bug was fixed so I could continue editing the article. Microsoft does depend on people in the community for feedback and to help drive the direction of their products. Many of these people are answering questions and writing blogs and articles about solving the issues.

Some folks do have insider information through the MVP (Most Valuable Professional) award program. Of course, this type of information can’t be shared with the public until it’s announced by Microsoft, but Microsoft does have a great relationship with the MVPs. I’ve even taken advantage of this relationship as an MVP myself. When writing my 2016 Reporting Services book a couple of years ago, I was able to get several answers from the team about the new features. This helped me out quite a bit since there was not much documentation available at that point.

I’ve also wondered if the experts have a sort of sixth sense when it comes to operating systems, programming, databases, or whatever the topic may be. From the outside, they make everything look so easy. What I realized is that they have spent so much time working with the technology, they have run into many common problems. They may also purposely ‘break’ things to test the limits. One thing I’m sure of, you learn a lot when things go wrong.

In addition to having some friends on the inside, lots of experience, and a willingness to experiment with the technology, these people share what they know with the rest of us. That makes our jobs easier, and for that, I thank them.

Do you have some ideas about how experts got that way?

Kathi Kellenberger 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.

Webinars

Free webinar: Change your SQL Server troubleshooting from reactive to proactive with a monitoring tool - Redgate’s James King and Ben Emmett will demonstrate how to troubleshoot your SQL Server estate, and discuss how it shouldn’t be about reacting to problems when they occur, but rather having the tools to make proactive decisions to plan ahead, identify potential disruptions, fix common problems and make improvements. ...(more)

Vendors/3rd Party Products

How to Test SQL Server Functions and Procedures using SQL Prompt - When writing functions or procedures, a common chore is to devise and implement the tests that ensure that the routine always works as expected....(more)

My Favorite SQL Prompt Features - SQL Prompt from Redgate Software is one of the tools that I cannot live without. Well, that’s probably an exaggeration but I use it daily in my job. Whether I’m writing stored procedures or crunching TSQL codes to troubleshoot data issues in SQL Server, ......(more)

Baselining a ReadyRoll project from an existing database - When creating a new ReadyRoll project, the schema of your production environment can also be imported as a baseline. Further work can be built on top of the baseline in a sandbox development environment and, when you’re happy with the changes, the project can be deployed to production....(more)

T-SQL

Locking in Microsoft SQL Server (Part 21) – Intro Into Transaction Management and Error Handling - As strange as it sounds, I have never considered transaction management to be the topic that requires explanation. However, it seems that some aspects of it are confusing and may benefit from the separate, long overdue, blog post. Transaction Types There ......(more)

Computed columns with scalar functions - Scalar functions can be a real headache when you’re performance tuning. For one, they don’t parallelize. In fact, if you use a scalar function in a computed column, it will prevent any query that uses that table from going parallel – even if you don’t ......(more)

Database Fundamentals #16: Removing Data With T-SQL - Deleting data from a table using T-SQL works quite a lot like the UPDATE statement. How it Works In the same way you supply the statement, DELETE, and then the table name. You’re not going to specify columns in any way because deleting data is all about ......(more)

Count_Big() function - Learned new function for count while going through the channel 9 video for salva. where for a very big table count it gave an error :   Count_Big() – BOL: Returns the number of items in a group. COUNT_BIG works … Continue reading ? ...(more)

The world of Automatic Tuning – SQL Server 2017 - Times are changing, 10 years ago I would never have thought that self-tuning databases would be available as a packaged product. I was testing out SQL Server 2017 Automatic Tuning recently and I ended up with the following situation. Below shows an image ......(more)

Software Development

Quality-Driven Software Development - While software is among the most widely used products in human history, it also has one of the highest failure rates due to poor quality. In this article, Cynthia Dzikiti explains how quality should be part of all aspects of the software lifecycle. ...(more)

PowerShell

PowerShell Core 6.1 Roadmap - The release of PowerShell Core 6.0 is only the beginning and we are already thinking about the next leg of our journey. Most of the effort of the team with PowerShell Core 6.0 was in these areas: Learning how to work in an Open Source project (and we ......(more)

PowerPivot/PowerQuery/PowerBI

Setting SQL Server Connection String Properties In Power BI and SSAS Tabular Modern Data Sources - It may not be immediately obvious, but you cannot set your own connection string properties when connecting to SQL Server using the built-in SQL Server connector from either Power BI or a modern data source in Azure SSAS/SSAS Tabular 2017: All you can ......(more)

Microsoft News

Microsoft cuts Azure Standard support from $300 to $100 per month - Microsoft is bringing its Azure Standard support more in line with what AWS is offering for certain eligible customers. ...(more)

Microsoft News : Security

Microsoft Introduces New Privacy Tools For Windows Insiders - Microsoft announced new privacy changes in Windows 10, ahead of the Data Protection Day and the GDPR going into effect later this year. ...(more)

Internet of Things

Working with Azure IoT data in Azure SQL Database - IoT use cases commonly share some patterns in how they ingest, process, and store data. First, these systems need to ingest bursts of data from device sensors of various locales. Next, these systems process and analyze streaming data to derive real-time ......(more)

HA/DR/Always On/Clustering

Database File movement in SQL AlwaysOn Availability group: A walk-through - In this blog, we are covering the scenario of moving the database files of AlwaysOn Availability Group (AG) enabled database to another location on all replicas with minimum downtime. To demonstrate database file movement, using Windows ......(more)

DevOps and Continuous Delivery (CI/CD)

Bringing DevOps to the database. Part 2: Continuous delivery - In part 1 of Bringing DevOps to the database, we saw how DevOps thinking is moving from the application to the database. By encouraging collaboration not competition between developers and Database Administrators (DBAs), choosing the right tools, and ......(more)

Bringing DevOps to the database. Part 2: Continuous delivery - Many application developers already use continuous integration to automatically test their code, and release management tools to automate application deployment. Database developers can join them....(more)

DBA Tools

Configuration Manager Missing - Windows 10 - SQL Server Configuration Manager is one of the fundamental tools for a DBA to do basic tasks like starting or stopping SQL Service, knowing how many instances and what components of SQL Server are been installed on the machine.Imagine this, suddenly ......(more)

More SSMS Tips & Tricks - Wayne Sheffield’s been busy since our last visit.  Here are six more SSMS tips and tricks. First, Wayne shows how to create keyboard shortcuts for common activities.  Then, he shows how to color-code SQL Server instances, which is very helpful when trying ......(more)

Database Design, Theory and Development

Degree of Duplication - Technology is constantly moving forward, but it is also helpful to understand how we arrived where we are today. Joe Celko reminisces about the history of database design and how it relates to the concept of ‘Degree of Duplication’ in this article....(more)

SQLskills SQL101: Why does my heap have a bunch of empty pages? - SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious ......(more)

Data Privacy

Questions about Data Governance that You Were Too Shy to Ask - A company’s data is one of its most valuable and important resources. Managing and protecting that data are big responsibilities, and a data governance processes must be put into place to avoid misuse and to meet regulations. In this article, William Brewer answers questions you may have about data governance but were too shy to ask....(more)

SQL SERVER – Authentication in SQL Server (Windows and Mixed Mode) – GDPR Series - On May 25, 2018, European privacy law is going to enforce new global standards for privacy rights, security, and compliance. This new regulation is known as GDPR – General Data Protection Regulations. Lots of users who are using SQL Server often wonder ......(more)

The Seven Samurai of SQL Server Data Protection - I want to help in the struggle against the adversaries. I’ve identified seven data security measures you can use. These ‘Seven Samurai’ will protect against all three possible attack vectors: data at rest, data in use, and data in motion. The post The ......(more)

Will GDPR cause a “Database Run”? - What is GDPR, everyone’s (current) favorite acronym? It stands for “General Data Protection Regulation” and is an act / law / set of laws that governs the collection, retention, and use of personal information for EU (European Union) and UK citizens. ...(more)

Conferences and Events

SQL in the City is coming Feb 28 - The next edition of the SQL in the City broadcast is coming Feb 28, and I’m planning travel now to head back to the UK for the event. We don’t have a schedule yet, but once we do, it will be on the Redgate Hub Livestream page. This edition will likely ......(more)

Computing in the Cloud (Azure, Google , AWS)

Find Pipelines Currently Running in Azure Data Factory with PowerShell - This is a quick post to share a few scripts to find what is currently executing in Azure Data Factory. These PowerShell scripts are applicable to ADF version 1 (not version 2 which uses different cmdlets).Prerequisite: In addition to having installed ......(more)

Career Growth

WIT: A Quick Thought - I recently had the opportunity to be a Dungeon Master (DM) at a gaming convention. It was my first time. I loved it. So what does that have to do with Women in Technology (WIT)? The young lady standing up also DMed her first convention. She’s my daughter ......(more)

More DBA Salary Research - Ginger Grant digs into the DBA salary survey a bit further: I know that I have heard that if you want to make money you need to get into management. Being a good manager is not the same skill set as being a good database professional, and there are many ......(more)

Big Data

SQL Server VLDB in Azure: DBA Tasks Made Simple - As any experienced DBA knows, supporting a very large database (VLDB) tends to be exponentially more complex than supporting smaller databases......(more)

Azure SQL Database

Using Azure SQL Database Failover with Power BI - In this video, Patrick shows you how you can easily use Azure SQL Database Failover Groups with Power BI to ensure you have access to your data. Connecting to an Azure SQL Database Failover Group from Power BI is easier than connecting to a secondary ......(more)

Azure SQL Data Warehouse and Data Lake

From unstructured data to dashboard with Azure Data Factory and Azure Data Lake - When I joined the Big Data team at Microsoft, sifting through all the technologies and products left me with more questions than answers – as it does for many customers taking their first steps on the cloud.  I’ve always learned best by doing, so I decided ......(more)

Analysis Services / BI on the MS Stack

Power BI User lookup with the gateway using Active Directory - In this video, Adam looks at how you can use Active Directory custom attributes for the user lookup with the Power BI Gateway. This can be used for both Analysis Services mapping and the Kerberos SSO with DirectQuery connections. Manage your data source ......(more)

AI/Machine Learning/Cognitive Services

Azure Notebooks Overview with Shahrokh Mortazavi Free hosted Jupyter Python/R/F# REPL for learning - Learn more about the FREE Jupyter Notebooks services which is hosted on Azure with a interview with  Shahrokh Mortazavi Azure Notebooks Lead http://notebooks.azure.com, So Azure Notebooks are free hosted Jupyter Python/R/F# REPL for learning to program ......(more)

Building A Model Using SQL Server ML Services - I have a post which shows how to build a simple R model to predict demand for an item: I am a huge fan of the Poisson distribution.  It is special in that its one parameter (lambda) represents both the mean and the variance of the distribution.  At the ......(more)

Administration of SQL Server

Introduction to System-versioned temporal tables – New database feature of SQL Server 2016 - System-versioned temporal tables is new database feature of SQL Server 2016. Another improved version of table level auditing with some new features after trigger, CT & CDC. System-versioned temporal tables stores data of table data modification history. ...(more)

Configuring Persistent Storage for SQL Server in Azure Container Services - I’ve been playing around with SQL Server running in Kubernetes in Azure Container Services (AKS) for a while now and I think that the technology is really cool. You can get a highly available instance of SQL Server up and running with a few lines of ......(more)

An Easier Way of Pivoting Data in SQL Server - There are several methods to pivot, or transpose, data in T-SQL, but most are difficult to understand and write. Phil Factor demonstrates how to take advantage of JSON support, introduced with SQL Server 2016, to create a function that makes pivoting data simple....(more)