The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I've been a car guy for most of my life, admiring them, owning quite a few (20+), and enjoying lots of time behind the wheel of a vehicle. I've been lucky enough to own my dream car for a few years and even had the chance to drive a few unusual cards. While I haven't driven a Tesla, I have ridden in one when the pedal was mashed to the floorboard (thanks, @GlennAlanBerry). That's quite an experience, and one I'd recommend if you don't have heart issues.

This week I ran across a note that Volvo's spin-off brand, Polestar, was unveiling an all electric car. This is their answer to a changing world where it seems every manufacturer wants to combat Tesla with an electric version of something. I might consider the Porsche Macan in an all electric version at some point, but the Polestar 2 is interesting for a reason besides the electric drive train. It's the first production car that is build around Google's Android system as the operating system for the car. There are entertainment systems using Android Auto, but this is a step higher.

What I find interesting, and perhaps disturbing, is this move to more electronic integration for our vehicles, and the potential for issues if the operating system fails. I already think that we've built a few too many integrations in mechanical cars at times, where we can't easily open doors, tow cars, or more if the electrical system fails. While I trust a plane or ship more because they are better maintained by mechanics, I worry about the durability and potential issues with updates for cars that are in daily use by users that might not take the best car of them. After all, people drive every day with small mechanical issues. I worry what this will mean for autos.

There's also the trust that we must have in both Google and Volvo to ensure that security is tight, patches are extremely well tested, and perhaps just as important, our privacy is respected. Google already gathers lots of data about us; do we want to give them more about how lifestyle? Will they sell data to insurance companies or others? I don't know what other data might be captured, but I do worry that there are issues I haven't considered, especially around security. Maybe even more disconcerting might be the charges for keys, updates, and more that will get added onto already expensive vehicles. What if systems become locked down to the point that only vendor mechanics can work on them?

I like the idea of better designed and more efficient vehicles, and think that all electric is the future. This despite my longing for a manual transmission in my next car. However, I'm not quite sure I'm ready to trust a phone operating system to run lots of parts of my car. Already I think there ought to be open-source, embedded systems for any mechanical control and security features that are completely separate from entertainment, without any access to the outside world. While I like DevOps and innovative software, what I worry about is the focus from many companies on features and costs, and not on security and quality.

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.

Webinars

The Importance of Monitoring your Azure SQL Database - Monitoring Azure SQL Database is still a necessary part of understanding how your system is behaving and ensures that you have the information needed to make necessary decisions about your databases in a timely and accurate manner. Wednesday March 6, 15.00-16.00 GMT / 9.00-10.00 CST...(more)

How DevOps keeps DBAs safe from being automated out of a job - Join our expert panel of Microsoft MVPs Kendra Little, William Durkin and Hamish Watson as they discuss how Database DevOps offers DBAs the opportunity to develop expertise in areas for which demand is increasing and how it can advance your career as a DBA. Thursday March 28, 16.00-17.00 GMT / 11.00-12.00 CDT...(more)

Don’t just think DevOps. Think Compliant Database DevOps! - 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? Join this webinar to discover how the answer lies in in going one step further than database DevOps and thinking about Compliant Database DevOps. Wednesday March 20, 15.00-16.00 GMT / 10.00-11.00 CDT. ...(more)

Virtualization and Containers

Deploying a SQL Server 2019 Availability Group into a Kubernetes Cluster - (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.) Over the last few months I have done a lot of work and ......(more)

Displaying the tags within the SQL Server docker repository - A while back Microsoft moved all their SQL images to their new registry mcr.microsoft.com which means that we can no longer see the SQL repository when running docker search To be honest, this isn’t really that much of an issue for me. I know the name ......(more)

Creating custom SQL Server Helm charts - In my previous post I went through how to deploy SQL Server to Kubernetes using Helm in which I used the SQL Server chart that is available in the Helm Hub. That’s great but what if we want to create our own charts? Let’s run through creating a simple ......(more)

Vendors/3rd Party Products

Share Oracle databases faster and safer - with Redgate's Deployment Suite for Oracle - Accelerate your Oracle development with clear and repeatable processes for deployments and sharing changes with source control. And with command line support you'll be able to automate the process setting you up for continuous integration. ...(more)

Transform your SQL Server permissions - SQL Census helps users understand who has access to their SQL Servers (including Active Directory groups) and simplify existing SQL Server permissions. A new feature reworks discrete user permissions into database roles, giving users the same effective rights in a more controllable structure. Download and use SQL Census (in preview) for free....(more)

Calling all Oracle Database users - We’re excited to announce that Redgate is doubling its efforts on Oracle and committed to aligning its DevOps solution across both SQL Server and Oracle. Please take a few minutes to complete this short survey to stay up-to-date on our Oracle developments....(more)

T-SQL

What are Inline Indexes? - Usually, the added features of the CREATE TABLE syntax in new releases of SQL Server are esoteric, and unless you are dealing with memory-optimized tables or other esoteric stuff, they aren’t of great interest. However, the Inline INDEX for both a table ......(more)

What Does the Undocumented UNCOMPRESS Function Do? - IntelliSense in SQL Server Management Studio (SSMS) can be quite helpful. It can save time typing by presenting possible object or function names. And, it can even show you the signatures ......(more)

A short guide to sp_executesql - Last week I talked about single use plans. One way to increase execution plan re-use is to parameterize queries by wrapping them in sp_executesql. I’ve started using this stored procedure enough that I wanted to write a short guide on converting a query ......(more)

SQL SERVER – Query Listing All the Indexes Key Column with Included Column - Today’s blog post is directly inspired by the conversation I had during my Comprehensive Database Performance Health Check. During the consulting engagement, we identified the customers had too many indexes on tables. The goal was to consolidate the ......(more)

Tools

How Do You Export A Database in Azure Data Studio - I’ve been writing a bunch about Azure Data Studio. I’ve also been recording videos on the topic. A comment I received recently asked how to export a database from Azure Data Studio. It made me want to explore the topic of exporting a database as it relates ......(more)

The Lighter Side

The marriage of SpaceX and NASA hasn’t been easy—but it’s been fruitful - On Saturday morning SpaceX will attempt to launch its Crew Dragon spacecraft for the first time, marking the latest ......(more)

Dem queries - Dem queries, dem queries, dem slow queries, Dem queries, dem queries, dem fast queries, Dem queries, dem queries, dem big queries, Now write dem crazy queries! The common table expression (CTE)’s connected to the SELECT clause, The SELECT clause is connected ......(more)

Huawei Launches New $2,600 Foldable 5G Phone to Rival Samsung - Huawei Technologies Co. announced a smartphone that unfolds into a small tablet computer, potentially rendering the need to carry two separate devices obsolete. It’s the second folding phone announced by a major manufacturer within a week. ...(more)

SQL Server Security

Fix Orphaned Users, Instance-Wide - The Overview Orphaned users are database principals that are no longer associated with a server principal, or login. This happens frequently when restoring a database from one server onto another server that doesn’t have the same pre-existing logins ......(more)

PowerShell and Group Managed Service Accounts (gMSA) - This is one article in a series that cover how to use PowerShell scripts to automatically install SQL Server. The full script set can be found here. Service Accounts are a requirement for installing and running a SQL Server. For many years Microsoft ......(more)

Introduction to SQL Server Security — Part 3 - The series so far: Introduction to SQL Server Security — Part 1 Introduction to SQL Server Security — Part 2 Introduction to SQL Server Security — Part 3 Microsoft introduced contained databases in SQL Server 2012. A contained database is one that ......(more)

SQL Server on Linux

PREEMPTIVE_OS_FLUSHFILEBUFFERS waits on Linux - Last week I blogged about Using the Wait Statistics Report in Azure Data Studio, using a Linux VM in Azure as an example host. As that was my first time running wait stats analysis on a Linux host, I found that the PREEMPTIVE_OS_FLUSHFILEBUFFERS wait ......(more)

Software Development

Git, GitHub, GitLab, BitBucket, What’s the difference? - I was watching Brent’s webcast session on GitHub recently. I’ve struggled to explain this to users in the past, and it’s always good to see what others do when they do similar sessions. You can watch Brent’s session, and I’ve got some content on git. ...(more)

Security news and thoughts

Data Leakage from Encrypted Databases - Matthew Green has a super-interesting blog post about information leakage from encrypted databases. It describes the recent work by Paul Grubbs, Marie-Sarah Lacharité, Brice Minaud, and Kenneth G. Paterson. Even the summary is too much to summarize, ......(more)

On the Security of Password Managers - There's new research on the security of password managers, specifically 1Password, Dashlane, KeePass, and Lastpass. This work specifically looks at password leakage on the host computer. That is, does the password manager accidentally leave plaintext ......(more)

FBI: End-to-End Encryption Is an Infectious Problem - The FBI's Amy Hess said that end-to-end encryption "is a problem that infects law enforcement and the intelligence community more and more so every day." ...(more)

Plain wrong: Millions of utility customers’ passwords stored in plain text - In September of 2018, an anonymous independent security researcher (who we'll call X) noticed that their power company's website was offering to email—not reset!—lost account passwords to forgetful users. Startled, X fed the online form the utility account ......(more)

Supermicro hardware weaknesses let researchers backdoor an IBM cloud server - More than five years have passed since researchers warned of the serious security risks that a widely used administrative tool poses to servers used for some of the most sensitive and mission-critical computing. ...(more)

R Language

An architecture for real-time scoring with R - Let's say you've developed a predictive model in R, and you want to embed predictions (scores) from that model into another application (like a mobile or Web app, or some automated service). If you expect a heavy load of requests, R running on a single ......(more)

Robust Regressions: Dealing with Outliers in R - Robust Regressions in RCategoriesRegression ModelsTagsMachine LearningOutlierVideo TutorialsIt is often the case that a dataset contains significant outliers – or observations that are significantly out of range from the majority of other observations ......(more)

Number 6174 or Kaprekar constant in R - Not always is the answer 42 as explained in Hitchhiker’s guide. Sometimes it is also 6174. Kaprekar number is one of those gems, that makes Mathematics fun. Indian recreational mathematician D.R.Kaprekar, found number 6174 – also known as Kaprekar constant ......(more)

Let's get LEGO'd! - If you follow my blog, you may have noticed that I like to create some pretty weird and nerd-fabulous graphs. In this blog I’ll introduce you to a new and exciting form of neat R graphs that I recently stumbled upon. Often times, my blogs are tutorials. ...(more)

Python in SQL Server

#ConditionalCode in Python with Dominika - Here it is! The second article about Python! This time I have described how tou use simple conditional code in Python! You can find it here. ...(more)

Product Upgrades and Releases

SQL Server 2019 community technology preview 2.3 is now available - At the SQL bits keynote today, we announced the release of SQL Server 2019 community technology preview 2.3, the fourth in a monthly cadency of preview releases. Previewed in September 2018, SQL Server 2019 is the first release of SQL Server to closely ......(more)

SQL Server Management Studio 18.0 Public Preview 7 Released - After a brief gap since releasing SQL Server Public Preview 6, and having survived through the February 2019 Snowcalypse in Seattle, Im happy to share that everyone in the SQL Tools team has made it out alive. I am also very excited to announce the Public ......(more)

Drivers 5.6 for PHP for SQL Server Released - We are excited to announce the production ready release of the Microsoft Drivers 5.6 for PHP for SQL Server. The drivers enable access to SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure SQL DW from PHP 7.1-7.3 applications on Linux, ......(more)

What’s new for SQL Server 2019 Analysis Services CTP 2.3 - We find great pleasure in announcing the public CTP 2.3 of SQL Server 2019 Analysis Services. New features detailed here are planned to ship later in Power BI Premium and Azure Analysis Services. Calculation groups Here is a question for seasoned BI ......(more)

Microsoft Unveils Azure Sentinel Cloud Security Program - Azure Sentinel, available for customer preview Thursday, is what’s called a Security Information and Event management tool. Microsoft said it’s the first of its type based in the cloud. The product uses artificial intelligence to comb through data to ......(more)

ODBC Driver 17.3 for SQL Server Released - We are pleased to announce an update to the Microsoft ODBC Driver 17 for SQL Server! Version 17.3 is generally available and brings a few added features and several fixed issues. Added Support for XA distributed transactions Support for streaming input ......(more)

OLE DB Driver 18.2 for SQL Server Released - We are pleased to announce the release of version 18.2 of the Microsoft OLE DB Driver for SQL Server! This general availability release brings a couple of new features: Features added Support for UTF-8 server encoding For more information, see the blog ......(more)

Proactively monitor your SQL Server estate with SQL Monitor 9 - Increasingly, organizations need to monitor bigger databases across higher numbers of SQL servers, installed across diverse networks and platforms, including cloud platforms such as Azure. Often, these large estates are still managed by a relatively ......(more)

Product Reviews and Articles

Book: Azure Machine Learning Studio: An Unleashed Guide - About the book; Quick Intro from Author Azure Machine learning has been introduced in 2014. By seeing a demo in the SQL PASS Summit, I get interested in this product. From that time, I start to work with and demonstrating in different conferences. After ......(more)

Course Reviews: Fundamentals of Bayesian Data Analysis in R - This is a review of Rasmus Baath’s Fundamentals of Bayesian Data Analysis in R DataCamp course. I really enjoyed this course. Rasmus takes us through an intuitive understanding of Bayesian data analysis without introducing Bayes’s Theorem until the 4th ......(more)

Deploying and Reverting Clones for Database Development and Testing - SQL Clone is a very handy device for database developers. The main thing stopping me from using it more widely, initially, was culture shock. It is a new and strange experience to be able to have several local copies of the database I’m developing, without ......(more)

PowerShell

Running a .PS1 or .CMD file from Visual Studio Project Explorer - Today’s blog is one of those topics that I just needed to get written down somewhere, and I expect that a few readers may find this interesting and want to do the same thing. In many of the Visual Studio projects we have where I work, there are Powershell ......(more)

Undercover TV – Sean McCown Joins Us For a Session on Beginning Powershell SMO - In this episode, Adrian looks at some of the new features of the Undercover Inspector1.3 and special guest, Sean McCown give us a fantastic intro into the Powershell SMO (0:23:20). All Sean’s scripts can be found at on Git Hub at https://github.com/SQLUndercover/UndercoverToolbox/tree/master/Undercover%20TV%20Scripts/BeginningSMO ......(more)

PowerPivot/PowerQuery/PowerBI

Sharing and Collaboration in Power BI - Adam looks at the different sharing and collaboration items within Power BI. From the share button to apps and embedding options. Power BI offers many options. The post Sharing and Collaboration in Power BI appeared first on Guy in a Cube. ...(more)

Query by Example to Extract YYYY-MM from a Date Column - I was answering a question for a student in one of my online training courses this week. The question was, “Do you know of a way in power query to efficiently extract YYYY-MM from a Date column?” This can be done ‘manually’ with multiple steps including: ......(more)

Remove Duplicate Doesn’t Work in Power Query for Power BI? Here is the Solution! - One of the most common transformations in Power Query is the Remove Duplicates. This transformation is used in many scenarios, one of the examples, is to create a dimension table with unique IDs in it, so can be used as the source of a one to many relationships ......(more)

Handling Different Granularities In Power BI Using Slicer Groups - Reading Prathy Kamasani’s excellent blog post on Sync Slicers the other week I had a revelation: in the post Prathy shows how it is possible to sync selections between two slicers that are based on different columns from different tables in your dataset, ......(more)

Create a Power Query custom data connector in 10 minutes - Getting set up When I heard about custom data connectors for Power Query, I had assumed there would be a lot of work involved. While there is definitely quite a bit of work in implementing advanced features like query folding, creating your very first ......(more)

Build Your Own Power BI Audit Log; Usage Metrics Across the Entire Tenant - Power BI dashboard and reports come with a usage metric, which you can see how users used this content. There is also another usage metrics across the entire tenant, which you can see it if you have access to Power BI Administrator account, under Admin ......(more)

Overview of Power BI Workspaces - Adam gives you an overview of Power BI Workspaces. This includes the My Workspace and App Workspaces within Power BI. This focuses on the new workspace experience. Organize work in the new workspaces (preview) in Power BI Create the new workspaces (preview) ......(more)

Modify the Power BI Concatenated list of values quick measure - Patrick walks you through how to use a Power BI quick measure and then easily modify it. In this example, he uses the concatenated list of values quick measure in Power BI Desktop. The post Modify the Power BI Concatenated list of values quick measure ......(more)

Polybase/HDInsight

The PolyBase Papers - This week’s PolyBase Revealed post is all about a couple of academic papers that Microsoft Research published a few years back. In their first paper, entitled Split Query Processing in Polybase (note the pre-marketing capitalization), David DeWitt, et ......(more)

Performance Tuning SQL Server

It’s always parameter sniffing: Line Endings - Combining a few themes of recent posts today. I’ll mix in some sp_executesql, it’s always parameter sniffing, and the plan cache. Consider this situation We converted an ad-hoc query into a prepared query using sp_executesql. Then, a user called in ......(more)

The Metrics That Matter For Performance Tuning - If you’re stepping up to a SQL Server you’ve never seen before, you’re probably only armed with what people tell you the problem is. Sometimes they’re right, more often they’re wrong. Even more often they’re just too vague, ......(more)

Scalar UDF Inlining in SQL Server 2019 - How does Scalar UDF Inlining affect the performance of scalar functions? SQL Server 2019 introduces a new feature called “Scalar UDF Inlining”. In a nutshell, this feature will take a scalar function and it will inline it into the query plan (similar ......(more)

Split - Introduction The Split operator is typically used to optimize update processing, usually in combination with Collapse and Sort operators. This operator splits rows that represent an update into two rows each, representing a delete and an insert. Input ......(more)

Does sp_executesql WITH RECOMPILE Actually Recompile Anything? - No, No It Doesn’t But it’s fun to prove this stuff out. Let’s take this index, and these queries.CREATE INDEX ix_fraud ON dbo.Votes ( CreationDate ); SELECT * FROM dbo.Votes AS v WHERE v.CreationDate >= '20101230'; SELECT * FROM dbo.Votes AS v WHERE ......(more)

Implicit vs Explicit Spools - Spools Of A Feather There are plenty of spools in query plans, and they’re all pretty well labeled. Index Table Rowcount Window They can be either eager or lazy. An eager spool will take all the rows at once and cache them, and a lazy spool will only ......(more)

NoSQL

Graph Database vs. Document Database: Different Levels of Abstraction - “Remember that every science is based upon an abstraction. An abstraction is taking a point of view or looking at things under a certain aspect or from a particular angle. All sciences are differentiated by their abstraction.” (Fulton Sheen) Graph and ......(more)

Microsoft News

The real reason that Microsoft is already testing a 2020 Windows release? Azure - Enlarge / Windows is now perpetually under construction. (credit: David Holt / Flickr) The release earlier this month of a preview Windows 10 build that isn't due until 2020 was a little strange. At the time, Microsoft said vaguely that it was because ......(more)

Where do I file bugs and feature requests for Microsoft Data Platform products? - It’s not just you: it’s hard for all of us to figure out where to go when we find a bug or want to add a feature. Here’s a quick rundown: Azure Data Studio – open an issue in the Github repo. While you open an issue, Github helps by searching the existing ......(more)

Microsoft IIS Web Server's CPU Gobbling Bug - Microsoft's fix for a bug in its IIS web server will require configuration by admins once the patch is applied. ...(more)

MDX/DAX

Calculation Groups in DAX – first impressions - Microsoft announced at SqlBits a very important feature for DAX: Calculation Groups. These are my first impression about the new feature and the impact it will have on Analysis Services and Power BI. The Calculation Groups provide the ability to create ......(more)

Hardware News

The Samsung 983 ZET (Z-NAND) SSD Review: How Fast Can Flash Memory Get? - Samsung's 983 ZET is a high-end enterprise SSD and the first retail drive to feature Samsung's low-latency SLC Z-NAND flash memory. Designed for highly performance-bound workloads that favor IOPS and minimal latency above all else, the 983 ZET is designed ......(more)

18 TB HDDs: Toshiba Collaborates with Showa Denko for MAMR HDDs - Showa Denko K.K. (SDK) announced on Thursday that it had completed the development of its microwave assisted magnetic recording (MAMR) platters for next-gen hard drives. The company is set to ship platters to Toshiba, which plans to start sampling of ......(more)

ChargeTech 124,800 mAh Battery Pack: Charge Your iPhone 47 Times - One of the fundamental elements of a backpack when going on a journey is to take a battery pack in case your smartphone doesn't make it through the day. These devices typically hold from 5000 to 20000 mAh and can charge a device a few times. For example, ......(more)

Intel Reveals Name of Next Generation Xeon D: Hewitt Lake - The Xeon D processor line for Intel has been a combination networking/microserver processor that increased significantly in core count, power, and capability in the previous generation. We now have two splits – the 500 family for networking, and 100 ......(more)

USB 3.2 at 20 Gb/s Coming to High-End Desktops This Year - The USB 3.0 Promoters Group announced its USB 3.2 specification update that increases theoretical performance of a USB 3.2 interface over a Type-C cable to 20 Gbps back in mid-2017 and then ASMedia demonstrated its USB 3.2 controller in mid-2018. According ......(more)

Micron and Western Digital Unveil 1 TB microSD Cards with A2 - The evolution of 3D NAND memory had enabled storage device makers to offer rather unprecedented capacities. To that end, this week at Mobile World Congress, Micron and Western Digital their first 1TB microSDXC cards, marking the point where the pint-sized ......(more)

HA/DR/Always On/Clustering

7 more ways to Query Always on Availability groups - Post Updated: Replaced Query 3 with transactions/sec query. When we first published 7 ways to Query Always On Availability Groups using SQL we had no idea it would so popular! So here is a quick post with 7 more ways to query Always on availability groups ......(more)

ETL/SSIS/Azure Data Factory

Biml in Azure aka CallBimlScriptContent - CallBimlScriptContent was introduced with the migration from Mist to BimlStudio. Why is this cool? You do not have to use files sitting on your computer as the source for your Biml. As long as you can reconstitute the Biml contents into a string, you ......(more)

SQL Server Bulk Insert – Part 2 - In the previous part of this article, we discussed how to import CSV files to SQL Server with the help of BULK INSERT statement. We discussed the main methodology of bulk insert process and also the details of BATCHSIZE and MAXERRORS options in scenarios. ...(more)

Example BCP export and import commands - The Microsoft Bulk Copy Utility, BCP.exe, can be used to copy data from a table in one SQL Server instance to the same table in another SQL Server instance. Since the BCP Utility is designed to cover a vast array of possible requirements, the command-line ......(more)

Copy Data From On-Premise SQL Server To Azure Database Using Azure Data Factory - Let’s take a break from our SQL Server 2017 Reporting Services Basics Series and jump to Azure Data Factory (v2). The latest installment on our SSRS Series is about adding a simple parameter to the report, which you can find here. Azure Data Factory ......(more)

Updated ADF Pipeline Activity for SSIS packages - CaseIn April 2018 Microsoft added the SSIS pipeline activity to ADF. A couple of days ago the released a new version. What are the changes?ADF - Execute SSIS Package activitySolutionThe big difference is that you can now select your packages and environment ......(more)

DevOps and Continuous Delivery (CI/CD)

How PASS introduced compliant database DevOps - In 2018, PASS was faced with the challenge of complying with the General Data Protection Regulation (GDPR), which affects any organization that collects, processes and stores the data of European citizens. Personally identifiable information has be protected, ......(more)

Approaching Zero: Making Life Simpler - This is part three in a series on near-zero downtime deployments. In the last post, I walked through several assumptions, particularly around source control, deployment processes, and testing. In this post, we will look at a few things you can do to ......(more)

Data Privacy, Compliance, and GDPR

I’ve added data classifications. Now what? - About a year ago I wrote about a new feature in SSMS that allows you to add a data classification to your columns. Today we’ll discuss what happens behind the scene and how to look at the classifications later. I have been working with in health care ......(more)

Conferences, Classes, and Events

SQL in the City Summits – UK, US & Down Under - Redgate are inviting senior data professionals to attend one of the upcoming SQL in the City Summit events taking place in April, May and June. If you’re interested in learning how your business can benefit from implementing Compliant Database DevOps this event is for you. Find out who’s presenting and register for a Summit near you today. ...(more)

SQL Saturday 813 – Boston #SSIS Precon 29 Mar 2019! - I’m honored to deliver Intelligent Data Integration with SSIS – a full-day pre-conference session – at SQL Saturday 813 in Boston 29 Mar 2019! What is Intelligent Data Integration?SSIS (SQL Server Integration Services) packages developed using tried ......(more)

Computing in the Cloud (Azure, Google , AWS)

Expanding the Azure Data Box Family - In a previous blog I introduced Azure Data Box. Today I’d like to talk about how Microsoft is expanding the Azure Data Box family by introducing you to the Azure Data Box Gateway and the Azure Data Box Edge devices. Until now the Data Box Family has ......(more)

Automate Azure VM Storage - The Problem One of the best things about Azure, and the cloud in general, is we can automate most anything, and we are going to look at how to automate Azure VM Storage. This allows us to come up with some outside-of-the-box solutions. I had a customer ......(more)

Checking Progress of the Creation of your Azure Resources - When I’ve created resources in Azure it’s usually taken from a few minutes and up to quarter of an hour – though sometimes longer. When you’re new to this stuff, you can be uncertain and wonder, “Is it really creating it?”, “Did I hit the right buttons?”. ...(more)

Adding Azure VM Boot Diagnostics - A couple of weeks ago, I blogged about boot diagnostics and how they can help you troubleshoot issues when virtual machines won’t start. But what if you selected to disabled boot diagnostics when the virtual machine was created? Can you go back and ......(more)

Keeping Up with Azure Changes - Since I started focusing primarily on Microsoft BI/DW/analytics around 2005, I've always been happy being a generalist within that space. Fast forward to around 2017 when I started focusing predominantly on Azure. Being a generalist (still within BI/DW/analytics) ......(more)

Big Data

How to Make Data More Manageable—and Trustworthy - eWEEK DATA POINTS: Whether dealing with a slew of errors or getting overwhelmed by an overload of information coming from a staggering number of sources, tech and business teams are struggling with data. The end result: data that never reaches its promise ......(more)

Unstructured Data: Everything Your Company Should Know About It - Click to learn more about author Chirag Shivalker. Unstructured data, in its simplest form means “Data in any form which does not easily fit into a data model or belong to a dataset of database tables.” Unstructured data prevails in formats including ......(more)

Big Data Integration 101: The What, Why, and How - Big Data Integration is an important and essential step in any Big Data project. There are, however, several issues to take into consideration. Generally speaking, Big Data Integration combines data originating from a variety of different sources and ......(more)

Backup and Recovery

Backup Solutions – what do you use? - In the consulting world we come across many different backup and restore options with the different clients that we work with. Personally I am a big fan of the SQL Server backups using a combination of Full, Differential, and Log backups so that we can ......(more)

Azure SQL Database

Restoring a Database from Azure - In a previous post, I described how you can use Azure storage to store SQL Server database backup files. This flexible option offers the ability to get your critical backups off site and in certain regions, they could be configured to be automatically ......(more)

There’s a bottleneck in Azure SQL DB storage throughput. - As you pay more for Business Critical Azure SQL DB servers, they’re supposed to get more storage throughput. The documentation on this is kinda hard to read, but boiling it down, for every core you add to a Gen5 server, you’re supposed to get 2,500 IOPs. ...(more)

Azure SQL Database and Trace Flags - You cannot enable trace flags (globally or by session) within Azure SQL Database but did you know that some global trace flags are enabled by default? What is on? SELECT @@VERSION DBCC TRACESTATUS Well, a lot of these are not … Continue reading ? ...(more)

Azure SQL Data Warehouse and Data Lake

Resources for Learning About Azure Data Lake Storage Gen2 - A couple of people have asked me recently about how to 'bone up' on the new data lake service in Azure. The way I see it, there are two aspects: A, the technology itself and B, data lake principles and architectural best practices. Below are some links ......(more)

Analysis Services / BI on the MS Stack

SSAS Tabular 2019, Calculation Groups And Migration From SSAS Multidimensional - With the release of CTP 2.3 of SQL Server 2019 today there was big news for Analysis Services Tabular developers: Calculation Groups. You can read all about them in detail in this blog post: https://blogs.msdn.microsoft.com/analysisservices/2019/03/01/whats-new-for-sql-server-2019-analysis-services-ctp-2-3/ In ......(more)

AI/Machine Learning/Cognitive Services

Azure ML Part 10, Create Web service from Models - In previous posts, I have explained how to create a machine learning scenario using Azure ML components. One of the main advantages of using Azure ML is the ability to create a web service from it. That means the created model can be used in other services ......(more)

Administration of SQL Server

SQL SERVER – SQL Server Configuration Manager Missing from Start Menu - Earlier I wrote a blog on the same topic and today I found another way to do the same thing. In this blog, we would discuss the steps you can take to open SQL Server Configuration Manager when its missing from Start Menu in the Windows operating system. Before ......(more)

Physical_Device_Name Not Updated On Restore - The Problem You restored a database on top of an existing database to refresh your development environment, but the Physical_Device_Name in msdb..backupmediafamily does not get updated. That seems odd, but let's take a look at the documentation on ......(more)

Running SQL Database Maintenance Tasks Using SQLCMD - This article is about developing an advanced understanding of the Sqlcmd utility which lets you run T-SQL commands directly from the command prompt without needing SSMS (SQL Server Management Studio). The article also highlights the importance of using ......(more)

SQL SERVER – How to Drop All the Hypothetical Indexes of Database? - Earlier I wrote a blog post SQL SERVER – Say No To Database Engine Tuning Advisor and I got an email from my existing client where I frequently engage for Comprehensive Database Performance Health Check. They wanted me to help them remove all the negative ......(more)

Use XEvent Profiler to capture queries in SQL Server - In the course of monitoring performance or troubleshooting a problem such as system slowness, it may be necessary to find or capture queries that have high duration, high CPU, or generate significant I/O during execution. You can use the DMVs or Query ......(more)

Transferring Jobs and Schedules between Instances using T-SQL - Introduction Quite often, there is a need to transfer Agent jobs to a different instance of MS SQL Server. Restoring a msdb database will not always be the best decision – there are cases in which you will need to transfer only the Agent jobs specifically, ......(more)

Identifying Page Information in SQL Server 2019 - SQL Server 2019 has some new and very interesting functions to identify information about pages. Many operations, such as analyzing current activities, locks or deadlocks, may result in some metadata pointing/blaming one page in the database. However, ......(more)

SQL SERVER – SQL Service Not Getting Started Automatically After Server Reboot While Using gMSA Account - This was the first experiment with gMSA account in my lab and I faced an interesting issue. In my lab environment, I have a complete domain server and member servers. Once I configured gMSA for SQL Server service and restarted the machine, SQL Service ......(more)

Proposed SQL Server defaults: disable priority boost - A few months ago I suggested that the following settings should be the default for most SQL Server instances: Set cost threshold for parallelism to 50 Disable lightweight pooling if it is enabled Disable priority boost if it is enabled Set optimize for ......(more)

SQL SERVER – FIX: 3637 – A Parallel Operation Cannot be Started From a DAC Connection - This was indeed one of an interesting error which I have never seen earlier and it is about DAC Connection. Here is the complete error message which my client reported. Msg 3637, Level 16, State 3, Line 1 A parallel operation cannot be started from a ......(more)

The Curious Case of… copying FILESTREAM files between tables - (The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some ......(more)

What should you do about memory dumps? - A SQL Server monitoring tool is warning you that you’re getting memory dumps, and you’re wondering what that means. This isn’t normal: you’re encountering a situation where SQL Server is so freaked out that it’s unable to proceed normally with a query. ...(more)

Proposed SQL Server defaults: disable lightweight pooling - A few months ago I suggested that the following settings should be the default for most SQL Server instances. Set cost threshold for parallelism to 50 Disable lightweight pooling if it is enabled Disable priority boost if it is enabled Set optimize for ......(more)

How to resolve ‘Login failed for user’ and sa password lost (Error 18456) - This blog post explains how to get into SQL Server if you have lost your sa password or you have no way of getting into your SQL Server instance. This blog post is dedicated to the people who came along to my training course in Wellington 22nd February ......(more)

Database context and compatibility level - To add onto yesterday’s post about which cardinality estimator (CE) your query will use, there’s an additional complexity. This specifically applies to cross database queries. The database context in which you run the query will determine which CE is ......(more)

SQL Server 2016 SP2 CU4 Access Violation on certain Autostats Updates - I spent a lot of time tracking this error down.When I started investigating, the error was known only as an access violation, preventing some operations related to data cleansing or fact table versioning.It occurred deep within a series of stored procedures. ......(more)

Missing Stats DMV? - Useful statistics are critical for great performance in SQL Server. So much so, that by default, SQL Server creates statistics for columns it deems worthy without any help or interjection from anyone.1 However, SQL Server isn’t yet quite smart enough ......(more)

Optimal Log File Growth and Virtual Log Files - Intro The SQL Server Transaction Log is a critical component of each database, and should be managed pro-actively. Optimal log file growth is simple to setup, but knowing the right numbers to use for initial size, file growth rate, and maximum size are ......(more)