The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

SQL Server Management Studio was almost unchanging for so long, five years I'm told, that many of us gave up on expecting any startlingly new features from it. It did, of course, accommodate the enhancements in SQL Server, but one got the impression that the application was in 'do not resuscitate' mode. Now, suddenly, SSMS has a genuinely useful new tool, the 'import flat file' wizard. It may be lacking some obvious features, and has some quirks, but it is like seeing the first crocus of spring.

I'm expecting more spring flowers soon. A couple of years ago, there was a sudden change: a long list of connect items got fixed, and a new team was in put in place to revive the slumbering giant. They have had to refactor a sprawling application. Before they could get anywhere near current practices in continuous releases, they had to reduce all the interdependencies with other parts of the SQL Server behemoth. Management Studio is now a completely standalone product, not tied to any specific version or edition of SQL Server, and no longer requires licensing of any kind. There has been a new release roughly every six weeks. A lot has, at last, got fixed but there has been little time to spend on planning the way ahead.

There are, of course some other features pushing up through the permafrost such as the XEvent Profiler and the updated Waits Filtering in the Performance Dashboard, but these sorts of features are too rarified to appeal to the average member of the Visual Studio brigade. There isn't much else to make the pulses race: we were able to contain our excitement over previous enhancements, such as Multi-Factor Authentication (MFA), and enhancements to the SSIS scale-out management application.

I think that everyone has their own ideas of what to see in SSMS. There is a whole industry that is geared to making it easier to use SSMS. However, I suspect we are being too subtle. Here, with the new 'import flat file' wizard, we start instead at the level of, "Wow. I managed to create a database, now how on earth do I get data into it?" Surely, at their most basic level, databases should be as simple to create and alter as spreadsheets in file directories. We do ourselves no favors at all in making things unnecessarily complicated. No wonder 'document databases' such as Mongo can so easily steal our lunch. I shall be hectoring Microsoft for improvements to what I reckon is a good direction to develop SSMS. "Wow, I imported that flat file! But I have three hundred tables – am I expected to run that wizard three hundred times?"

Phil Factor.

Phil Factor 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.

T-SQL

Partition Level Locking: Explanations From Outer Space - It’s not that I don’t like partitioning. It’s just that most of my time talking about it is convincing people not to use it. They always wanna use it for the wrong reasons, and I can sort of understand why...(more)

Time Zones are a Drag … Seriously - The AT TIME ZONE implementation relies on a Windows mechanism to convert datetime values across time zones. This means SQL Server is not performing the time zone conversion, it is shelling out to the OS and having it perform the conversion. ...(more)

My Favorite SSMS Shortcut (After Copy/Paste) - The magic of ALT + multi-line edits in SSMS....(more)

SQL Server Security and Auditing

How to get started with Always Encrypted for Beginners Part 1 - Unlike Transparent Data Encryption (TDE) which only encrypts data files and backups at rest, Always Encrypted is configured at the column level and not database level. Also, DBAs can no longer see the unencrypted values of the data, as they could with cell-level encryption, because the column encryption key is stored outside of SQL Server....(more)

SQL Server News

SQL Updates Newsletter – November 2017 - Recent Releases and Announcements, Troubleshooting and Issue Alerts, Recent Blog Posts and Articles and more....(more)

Upgrade your SQL Server to scale before adding new hardware - Describing the SQLOS and memory enhancements in SQL Server 2016/2017 that allows the SQL Server engine to scale out of the box on high end servers....(more)

Security news and thoughts

Here's What I'm Telling US Congress about Data Breaches - Troy Hunt shares his written testimony on data breaches....(more)

R Language

How to generate a Secret Santa list with R - A roundup of various Secret Santa solutions and how they are implemented in R. ...(more)

PowerShell

2 Ways to Loop through collections in Pester - You probably have more than one instance that you want to test, so how do you loop through a collection of instances? There are a couple of ways....(more)

Testing PowerShell Script Speed - It’s all well and good to automate aspects of your job but how can you test if they scale? I can now use Start-TestCaseMeasurement to check this out!...(more)

PowerPivot/PowerQuery/PowerBI

Decision Tree: Power BI- Part 2 - How to use a decision tree component in Power BI with the aim of Predictive analysis in the report....(more)

Line Chart Conditional Formatting - Have you ever wished you could change the line colour depending on the overall trend? For example, if your sales increase over time, the line is green; if there is a decline, then the line is red. While this functionality is not yet natively available in Power BI Desktop, it does not mean this cannot be done! ...(more)

Expression Dependency Tree: New Feature of Power BI Helper - Expression Tree will expand the tree for a Measure or calculated column, so you can see what other measures are used to create this expression, and where other measures, calculated columns, or even normal columns are located....(more)

Create mail-enabled security groups to use in Power BI via PowerShell - In this video, Adam looks at how you can create mail-enabled security groups via PowerShell and copy members from an Office 365 Unified group into that new group. ...(more)

Performance Tuning SQL Server

Wait Statistics and Query Store - The addition of wait statistics information in Query Store was the number one feature request after its initial release, and all that information together creates powerful troubleshooting capabilities....(more)

Query Store Settings - In SQL Server 2017 there are nine settings related to Query Store. Erin Stellato lists each setting, along with the default value and considerations around changing the setting....(more)

Database Scoped Configurations: the Legacy Cardinality Estimator - The Legacy_Cardinality_Estimation is part of a larger feature that controls feature versioning choices for the optimizer in SQL Server....(more)

Microsoft News : General Interest

Microsoft riles VMware with new VMware virtualization on Azure announcement - VMware is none too pleased about a new Microsoft offering enabling the full VMware stack to run on Microsoft's Azure....(more)

Hardware Testing

Recent TPC-E Results on SQL Server 2017 - Assuming you can split your workload across multiple database servers rather than just one, having two, two-socket servers instead of one, four-socket server will give you both more CPU capacity and better single-threaded CPU performance even when using the exact same model processor....(more)

HA/DR/Always On/Clustering

Distributed Availability Groups Setup and Monitoring - You have an AG on one set of servers that you want to replicate to set up as an AG on another set of servers, perhaps to set up a separate site for disaster recovery. Tracey Boggiano shows how to set up and monitor distributed AGs....(more)

DevOps and Continuous Delivery (CI/CD)

Automated Developer Machine Setup - Part I - Introduction and DISM - It seems no matter where you work as a developer, the first day or two is all about setting up the company issued laptop with the necessary tools to do the job. Everyone recognizes how much pain in the ass that is, and how much time is wasted doing it. The most typical solution to the problem is "we should make a developer image."...(more)

Database Design, Theory and Development

SQL: Columns – how big is too big? - When designing databases, one question that comes up all the time is how large columns should be. The real challenge comes with strings. I've seen developer groups that just say "make them all varchar(max)". Let's just say that's not a great idea....(more)

Where are key columns stored in a nonclustered index in SQL Server? - Kendra Little demonstrates, and proves, that all key columns in the index are at all levels of the index....(more)

Data Science

DevOps for Data Science – Infrastructure as Code - On the path for implementing DevOps in Data Science, the first thing you can do in your projects is to implement Infrastructure as Code (IaC)....(more)

Data Privacy

Questions about Data Masking That You Were Too Shy to Ask - The General Data Protection Regulation (GDPR) will be in effect in May of 2018. Data masking is one technique that can help your organisation comply with this and other regulations. William Brewer answers those questions about data masking that you were too shy to ask....(more)

Computing in the Cloud (Azure, Google , AWS)

Serverless compute with Azure Functions – Getting Started - Serverless compute refers to an economical model where we pay for compute resources used as opposed to “servers”. Wait… isn’t that what the Cloud is about? Well, yes, on a macro-scale it is, but serverless brings it to a micro-scale....(more)

Getting Started with Azure - Getting a handle on Azure concepts, terminology, and features can be more than a little intimidating. Azure an extremely large and fast-changing platform. The best place to start depends on your role, and on the services for which you're responsible, each of which has its own set of gotchas....(more)

Columnstore Indexes

Splitting and Merging Non-Empty Partitions in Columnstore Indexes - The common, by the book approach recommends dropping columnstore index, splitting or merging partitions and recreating the index afterwards. Fortunately, you can minimize the overhead with simple workaround....(more)

Career Growth

The One Piece Of Career Advice I Wish I’d Gotten - A career built on solving business problems using technical tools and resources is far more likely to be evergreen than one built on just the nuts and bolts of technology....(more)

Book Review: Database Reliability Engineering by Campbell & Majors - A Database Reliability Engineer doesn't just know how to work with one database – they also know when certain features should be used, when they shouldn’t, and from a big picture perspective, how they should build automation to avoid weaknesses....(more)

Backup and Recovery

So You Want a Cheap Disaster Plan - There’s a lot that goes into a Disaster Recovery plan. One of them is offsite backups. There are some businesses that don’t have backups going offsite, let alone a Disaster Recovery plan....(more)

Analysis Services / BI on the MS Stack

How to handle BLANK in DAX measures - This article describes a counter intuitive behavior of BLANK in DAX measures affecting Power BI, Analysis Services, and Power Pivot. That behavior could cause mistakes in a report using alternate expressions of the same calculation. Indeed, these expressions are not equivalent when BLANK is involved....(more)

Build a Performance monitoring tool for SSAS using Power BI - How to build a performance monitoring tool for SQL Server Analysis Services (SSAS) using free out of the box windows tools and Power BI Desktop, showing some of the metrics that you can measure to evaluate a SSAS server’s performance and health....(more)

AI/Machine Learning/Cognitive Services

SQL Server Machine Learning Services – Part 1: Python Basics - Python is a very popular language used for many purposes including machine learning. SQL Server 2017 supports Python with its Machine Learning Services component. Robert Sheldon explains how to get started using Python in SQL Server in the first article of this series....(more)

SQL Server 2017 Machine Learning Services – Installation - With the release of SQL Server 2017, you now have the capability to incorporate both R and Python into SQL Server. This, the first post in the series, covers installation....(more)

Data Prep Essentials for Automated Machine Learning - Data preparation is critical for any analytics, business intelligence or machine learning effort. Although automated machine learning provides safeguards to prevent common mistakes and is robust enough to handle imperfect data, you’ll still want to properly prepare your data to get optimal results....(more)

Administration of SQL Server

Performance and Stability Fixes in SQL Server 2017 CU Builds - Glenn Berry scans the hotfix list for all of the Cumulative Updates for SQL Server 2017, looking for performance and general reliability-related fixes for the SQL Server Database Engine. There are quite a few!...(more)

Good Reasons to Rebuild or Reorganize Indexes - Erik Darling argues that there aren't as many good reasons as you might think....(more)

Upgrading an expired SQL Server 2016 Evaluation Edition - It's happened to almost everyone. Someone installs Evaluation Edition and now you need to upgrade it to a licensed copy using "Edition Upgrade" in the SQL Server installer. Microsoft would have you think this is easy even if the copy has expired but the truth is far more difficult....(more)

Checking SQL Configuration with Pester & Dbatools - Presenting a script that uses Pester & Dbatools to check all settings in sys.configurations against values stored in a .csv file. The script will then alter the settings in SQL Server if they differ. So be warned!!...(more)

Closing all the connections to a database - You want to run a database restore operation but there are people using the database. Well, then we just ALTER DATABASE...WITH ROLLBACK IMMEDIATE, right? Kenneth Fisher advises thinking about this a bit harder. Should we dump everyone out of the database? Are you sure?...(more)