The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

From the time you rise in the morning until you close your eyes at night, your day is filled with thousands of small decisions, many that you don’t even think about. For each decision that you make, there is a probably a good choice and a poor choice available. Most of the time, the good choice, or doing the right thing, is more difficult than doing the wrong thing. Should you drop those dirty socks on the floor or make the effort to transfer them to the proper receptacle? Should you come to a complete stop at the stop sign or just roll through since no one else is in the intersection? Do you head to the gym after work or binge-watch that new Netflix series?

I realized that doing the right thing is not as easy as doing the wrong thing recently when walking at a university campus. There were several concrete walkways from the parking lot to the buildings and between buildings. But there were also a number of paths worn in the lawns, shortcuts, that people walking at the campus had created. The right thing to do was to stay on the walkways, but it was easier – and more convenient – to take the unofficial paths. I had hoped that, during some future project to update the grounds, that the engineers will take note of how people actually walk through the campus and adjust the routes.

Choosing to do the right thing can be even harder in some circumstances, such as taking responsibility for making a mistake. It’s easier to blame someone or something else or just pretend you are not responsible. Of course, at a company, the culture and how mistakes are handled affect this. If mistakes are viewed as ways to learn and improve, people are more likely to step up than if people are verbally attacked when things go wrong.

It’s also easy to follow along with the crowd. I’ve noticed on the airport parking shuttle, that when I pull out money to tip the driver, other passengers notice and do the same thing. Also, at a stop light in the US, I might pull forward so I can see if it’s safe to make a right turn. More than half the time, the car to my left will also move forward blocking my view. There is no advantage for the driver, they just pull forward because I do it!

You may be wondering why it’s hard to do the right thing. My theory is that human nature programs us to do what is easier to help us survive. By being a bit lazy, we conserve precious energy. Deflecting blame lets us avoid danger. Greed encourages us to store up resources for our survival. Following the crowd might make us feel like we belong to the tribe or village. Instead of automatically doing the easy thing, start paying attention to your behaviours and your motivations. Start doing the right things instead.

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

Don’t just think DevOps. Think Compliant Database DevOps! - DevOps and data privacy do not need to oppose each other. Rather, they can complement one another. So how can the promise of releasing changes to the database faster and easier be balanced with the need to keep data safe and remain compliant with legislation?...(more)

The 2019 State of Database DevOps results, live with Donovan Brown! - Donovan Brown, Principal DevOps Manager at Microsoft, joins Steve Jones, Microsoft Data Platform MVP to discuss the latest in all things Database DevOps. They offer a closer look at the key findings in the 2019 State of Database DevOps Report, and investigate the growing importance of the database in successful DevOps and IT performance....(more)

Why the database is at the heart of DevOps success - DORA’s recognition of the critical importance of the database to DevOps in their Accelerate State of DevOps Report should act as a timely wake-up call to those companies that still see DBAs and developers as operating in separate silos. Join Microsoft Data Platform MVP and SQL Server Central Editor Steve Jones to discover how you can build a common understanding and atmosphere of collaboration....(more)

T-SQL

Why You Shouldn't Use SELECT * In Production Systems (EVER!) - OK so the title is a bit of a bold statement but bear with me, I’ve been burned by this too many times and the above is a rule I now follow for the reasons outlined below… This doesn’t mean I never SELECT * because I totally do use it for ad-hoc development ......(more)

Tuning Dynamic SQL by Hand with Short Circuits - When we think about building dynamic SQL, we usually think about a stored procedure like this that takes input parameters, builds a string, and then executes that string. Here’s a simple example:CREATE OR ALTER PROC dbo.usp_SearchUsers @SearchDisplayName ......(more)

Does NOLOCK Really Applies No Lock? – Interview Question of the Week #209 - Question: Does NOLOCK Really Applies No Lock? Answer: The answer is NOLOCK do apply the lock and it is Sch-S lock. Sch-S stands for Schema Stability. Quite a few people say it is actually Schema Shared lock and it is not correct. Sch-S or Schema Stability ......(more)

Collation change script - Normally when I blog, I spend a lot of time making sure that get as close to perfection as I can. This post is an exception. The problem I am talking about is too far out of my main specialization area that I want to spend more time on it than I already ......(more)

Using a Variable-length Datatype Without Explicit Length: The Whys and Wherefores (BP007/8) - In SQL, if you declare a string in any of the four formats, CHAR, NCHAR, VARCHAR or NVARCHAR, without specifying its length, the string is given the length of one character. If you coerce a string, using CAST or CONVERT, and make the same mistake, it ......(more)

Calculate Percentiles to Learn About Data Set Skew in SQL - B-Tree indexes are perfect when your data is uniformly distributed. They are not really useful, when you have skewed data. I’ll explain later why this is the case, but let’s first learn how to detect “skew” What is skew? Skew is a term from statistics ......(more)

When Data Isn’t There - I Got No Rows Over in the Votes table in the Stack Overflow database, a couple of the more popular vote types are 1 and 2. A vote type of 1 means that an answer was accepted as being the solution by a user, and a vote type of 2 means someone upvoted ......(more)

Tools

Microsoft Update Catalog - In today’s world of database administrations, there are a plethora of tools and resources that can be available to utilize to help solve problems, such as performance tuning, configuration, and many others.  Some are third party tools, some are free, ......(more)

Tech News

Gartner Survey Finds Government CIOs to Invest More in Data Analytics and Cybersecurity in 2019 - According to a recent press release, “Data analytics and cybersecurity pushed cloud out of the top spot for increased technology investment by government chief information officers (CIOs) in 2019, according to a survey from research and advisory firm ......(more)

Security news and thoughts

Weekly Update 123 - So it's been a bit of a crazy week. I got onto the plane in Australia on Thursday evening just as Europe was waking up to the news of the 773M email address credential stuffing list I loaded into HIBP......(more)

PowerShell

The PowerShell-Docs repositories have been moved - The PowerShell-Docs repositories have been moved from the PowerShell organization to the MicrosoftDocs organization in GitHub. The tools we use to build the documentation are designed to work in the MicrosoftDocs org. Moving the repository lets us build ......(more)

PowerPivot/PowerQuery/PowerBI

Power BI Roadmap Announcements In The April 2019 Release Notes - The latest version of the “release notes” document that details the roadmap for Dynamics 365 and the whole Power Platform – which includes Power BI – has just been released. You can view it online here: https://docs.microsoft.com/en-us/business-applications-release-notes/april19/ There ......(more)

The BI Journey: The Analyst - AdventureWorks, the famous bicycle and accessories seller, has hired a new intern who joined the North American regional sales department. Ruthie, who is studying for a degree in IT, landed the internship to work for the Sales Manager Stephen as an analyst ......(more)

Analyzing Slack Traffic with PowerBI - The other day in the SQL Community Slack channels, we started chatting about general usage of Slack – which channels saw the most activity, what topics were discussed, and so on. One thing led to another and I wondered if I could put that into a report ......(more)

Power BI Slicers, Dataflows, Personal Bookmarks and more… (January 21, 2019) - Another week, another roundup! Some good updates in this weeks video. Grab a coffee (or tea) and pull up to get the latest about #PowerBI Are Power BI Slicers Still Relevant with the New Filter Pane? (@dataveld) Power BI Data Privacy Levels And ......(more)

Import the First Sheet in a Workbook with Power Query - My series about common patterns for loading data using Power Query received quite a lot of interest over the last few weeks.  You can review those articles in the links below Combine all files in a folder with Power Query. Load the Latest Version of ......(more)

Performance Tuning SQL Server

Memory Grants part 5: Query hints - Proceed with caution, if you’re thinking of using query hints. This post will specifically cover min_grant_percent and max_grant_percent. My opinion on query hints is that you’re often better off tuning the query, than using a query hint. Especially ......(more)

The Execution plan comparison feature (in SSMS) - Did you know that you can compare two execution plans in SQL Server Management? It’s really cool. I use it a lot, as my first stop to compare performance. Let’s take two execution plans from my series on parameter sniffing. Demo comparing execution plans CREATE ......(more)

Query I/O over the Last Five Minutes - When faced with a SQL Server that is performing poorly, a great starting place for troubleshooting is looking at wait stats. Once you’re gathering wait stats, if you see lots of IO-related waits, you may... ...(more)

All Day, Training Day at SQLBits - It’s a somewhat late addition, but I have an all-day Training Day at SQLBits. It takes place on Thursday, February 28th. You can read all about it on the SQLBits web site. I want to take a moment here to expand on the information that we’re going to ......(more)

Microsoft News

Microsoft Acquires Citus Data, Re-affirming Its Commitment to Open Source - In a recent article on the Microsoft blog, Rohan Kumar announced, “I am thrilled to announce that we have acquired Citus Data, a leader in the PostgreSQL community. Citus is an innovative open source extension to PostgreSQL that transforms PostgreSQL ......(more)

Microsoft Scores Government Successes with Services & Security - Microsoft achieves two big successes this week with the U.S. government as they are awarded a five year services contract and receive certification for their mobile Outlook app on iOS and Android to be used by government workers. We also have more about ......(more)

HA/DR/Always On/Clustering

Finding Cluster Log Errors - Sometimes you know that a problem occurred, but the tools are not giving you the right information.  If you ever look at the Cluster Failover Manager for a Windows Cluster, sometimes that can happen.  The user interface won’t show you any errors, but ......(more)

ETL/SSIS/Azure Data Factory

Data Virtualization and ETL: Friends or Enemies? - Traditionally, companies have relied on the use of Extract, Transform, Load (ETL) solutions to gather data from disparate sources and populate a data warehouse. However, increasingly complex IT infrastructures ......(more)

DevOps and Continuous Delivery (CI/CD)

Implementing DevOps Doesn’t Get Rid of Database Administrators - I hear from a lot of database administrators who are worried about being automated out of a job. These kinds of worries are not new. Over the course of my career, I’ve seen CTOs outsource large groups of IT jobs to different regions around the world ......(more)

State vs Migration for Database Source Control – decide based on one question - One controversial topic in database development is how to properly store and deploy database changes. This is generally described as choosing between two options, which are approximately as easy ......(more)

Database Design, Theory and Development

Data and Meaning Part 4: Query and Result Correctness - As we have seen in Parts 1, 2, and 3, the RDM is a formal theory adapted and applied to database management: database relations (1) preserve the formal properties of mathematical relations, but also (2) have interpretations -- carry a real world meaning ......(more)

Data Privacy, Complianace, and GDPR

Data Privacy Day 2019 Reminds Businesses and Consumers About the Value of Personal Data - According to a recent press release, “Last year, worrisome headlines jolted consumers into reality about protecting personal data. A recent survey indicates that 90 percent of those polled were “very concerned” about their privacy. Our always-connected ......(more)

What the Google 50 Million Euro GDPR Fine Means for Big Data Analytics - A new press release reports, “Anonos BigPrivacy (www.anonos.com), announced today the following perspective on what the Google 50 Million Euro GDPR fine means for data insight driven companies. The 50 Million Euro fine against Google demonstrates that ......(more)

Computing in the Cloud (Azure, Google , AWS)

Customizing Alert notification email in Azure monitoring - Premier Developer Consultant Adel Ghabboun explorers how to setup alert notification email using Azure monitoring. A new feature was added recently which gives you the ability to customize your monitoring alerts email notification. And this feature ......(more)

Comparing Azure’s and AWS’ Cloud Blockchain Services - Cloud blockchain services are designed to help developers build, deploy or run applications that interact with a blockchain. ...(more)

Video: Azure Data Factory Data Flows Introduction - In January 2019, I was honored to be asked to contribute to the PASS Insights BI Edition Newsletter. I said yes, of course! :) I chose to create an Azure Data Factory Data Flows introduction video. This is a sneak preview of the upcoming Data Flows feature, ......(more)

Career Growth

SQL: A love story - I’m a college drop-out. When I was trying to figure out my life, a friend (my brother’s ex) referred me to a software company where she was working–I got the job because I was (a) breathing, (b) eager to do the job, and (c) cheap. The application we ......(more)

Data Scientist vs. Data Engineer - The Background of Data Science Roles It was thought that the year 2018 would create a huge demand-supply gap in the Data Science market as supply would fail to keep pace with the rising demand for expert Data Scientists. However, the recent buzz from ......(more)

Backup and Recovery

Modify device path for multiple Backup Devices - Backup Devices provide a nice way to permanently configure the backup location, enabling BACKUP DATABASE to look like: BACKUP DATABASE [xyz] TO [backup-device-name]; When you create a Backup Device, you specify the physical location where... ...(more)

What’s a differential backup? - Of the different basic types of backups (full, differential and log) I find the differential the most interesting, and frequently the least understood. Full backups are easy. The whole database including any log information needed to make the committed ......(more)

Azure SQL Managed Instance

Getting started with Azure SQL Managed Instance - Azure SQL Managed Instance is fully managed PaaS version of SQL Server hosted in Azure cloud and placed in you own VNet with private IP address. In this post, I will shortly explain how to configure and create Managed Instance including network environment, ......(more)

Azure SQL Database

How to auto-scale an Azure Database for MySQL/PostgreSQL instance with Azure run books and Python - One of the many great features of Azure SQL Database is the ability to scale up or down depending on the amount of workload an instance is processing, which means there is greater control of the instance that can translate into greater cost savings. But ......(more)

Lesson Learned #67: Azure SQL Database – SSH, VNET and Firewall - Hello, Today I worked in a service request when our customer tries to connect using SSH to the 1433 port from a Linux environment using a JumpBox in Azure to perform the connection. In this situation, we need to know that in Azure, depending on where ......(more)

Azure SQL Database and Transaction Log - Checking out the transaction log in Azure SQL Database. If you are curious like me, you will want to know about what your transaction log is doing in the cloud. The following queries have been tested and run okay within … Continue reading ? ...(more)

Azure SQL Data Warehouse and Data Lake

FAQs About Organizing a Data Lake - This post covers several things I've heard or been asked recently about organizing data in a data lake. Q: Partitioning by date is common. Where should the dates go in the folder hierarchy?Almost always, you will want the dates to be at the end of the ......(more)

AI/Machine Learning/Cognitive Services

Machine Learning Transformed: Data Quality and Operational Necessities - Machine Learning elicits mixed reactions. On the one hand, some consider Machine Learning a company’s new super power that has “swept enterprise technology, using mass amounts of data and algorithms to make predictions.” At the same time Machine Learning ......(more)

Administration of SQL Server

Steps to Recover Deleted Records from SQL Server Database Tables - In case you forgot to take backup of current database in SQL Server and deleted few records from SQL Server table. The post will help you in recovering deleted records from SQL Server 2017 & below versions. ...(more)

Scheduling things to Run in SQL Server - A key part of the SQL Server Agent is the ability to schedule jobs. While you can create one schedule for each agent job, frequently with applications like Reporting Services, users use Shared Schedules across multiple jobs. For instance, you can set ......(more)

How long since you ran DBCC CHECKDB? - If you’re not regularly looking for corrupt databases with DBCC CHECKDB, you’re putting your organization’s data at risk. I run DBCC CHECKDB once per day, or as is reasonably possible. Typically, DBCC CHECKDB is setup... ...(more)

When a SQL Server Data File Reaches Maximum Capacity - Did you know the maximum capacity of a SQL Server data file is 16 TB? I didn't either. And I recently learned the hard way. When I got the call, I got as much information as I could, and started sleuthing. There was some blocking, and the head of the ......(more)

IRL: Cannot Connect to SQL Server - IRL #2 – Connectivity Issues Problem: Application owner is trying connect to SQL Server to create a database, connection failed.  Error message suggests checking Instance Name and “Allow Remote Connections” option set to True. Background: This client ......(more)