The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

When I first began working with SQL Server twenty years ago, I had one book that I kept next to my computer. It was about four inches thick and contained just about everything I needed to know about SQL Server. Back then, I thought a 500 MB database was large, but I was working with relatively small organizations at the time, so my experience was limited. My primary job back then was as a developer, and SQL Server was really just the place where data was stored as far as I was concerned.

After becoming a DBA in 2002, the hot skill at the time was failover clustering. It was not easy to set up, but once in place and working, it was a thing of beauty. Today, the more flexible Always On Availability Groups are often used instead for high availability (and disaster recovery), but many folks still use failover clustering and it’s easier to configure than it was back then. Security, maintenance, patching, scripting, and testing restores were also important day-to-day skills for DBAs, and still are today.

In 2005, the data management views and objects (DMVs) were added which gave DBAs a way to “look under the hood” and better manage SQL Server. It seems to me at least, that was when interest in internals by many folks began. More DMVs are added with each version of SQL Server, and in 2008, Extended Events were introduced. Many of us are still being dragged “kicking and screaming” to Extended Events even though the feature is now ten years old.

Over time, SQL Server became more than just an RDMS platform as business intelligence components were added. Ten years ago, SSRS, SSIS, and SSAS were sought-after skills as more organizations built data warehouses for analytics and reporting. Over time, even more components have been added to SQL Server such as Master Data Services and Polybase. Today, you can run R and Python in SQL Server, and many database professionals are learning about machine learning and data science.

Even from a hardware perspective, things have changed a lot. At one time, virtualizing SQL Server was just not done – now it’s unusual to see a SQL Server on physical hardware. Direct attached storage was replaced by SANs which, in turn, have become faster as SAN storage has moved to SSDs. Processors are faster and contain multiple cores. (This seemed great until Microsoft changed the licensing model of SQL Server from per processor to per core in 2012.) Virtualization and SAN storage, while making SQL Server easier for the organization, adds complexity for the DBA. Just when we thought we had things figured out, now we can run SQL Server in Linux and Docker containers.

Column Store indexes, In-Memory OLTP, JSON support… The list of new things to learn is endless. Instead of a four-inch thick book next to my computer, I need an entire wall of books. Oh wait, that is one more thing that has changed. Now I read books on my phone.

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

How to achieve true DevOps by including the database - In this webinar Andrew Pierce will walk you through Redgate’s Database DevOps solution, showing reliable, scalable and repeatable processes for automating your database development and deployment....(more)

How to overcome data compliance challenges in Financial Services - Redgate are hosting this webinar to discuss some of the biggest challenges that the financial services sector faces regarding data compliance. And they’ll provide you with strategies for how you can begin to address them....(more)

Vendors/3rd Party Products

Getting Started with Database Development Using SQL Provision - In this article, Steve Jones has shown how a developer, who is a sysadmin on their own SQL Server instance, can migrate their existing development databases to clones, using SQL Provision. Using a simple PowerShell function, we have seen a repeatable process that is quick and consistent....(more)

Help Redgate do a better job (and win a $250 Amazon voucher) - Could you spare a few minutes to help Redgate improve its products and services? They’re running a survey of their users and the wider SQL Server community and they’d appreciate your insights. As a thank you for taking part, you’ll also be entered in a prize draw for a $250 Amazon Voucher. ...(more)

T-SQL

Variables, Literals, Procs and Stats - This post by US PFE Susan Van Eyck goes through considerations when developing stored procedures using local variables and literals, and their effect on the query plan / performance. I ran across a very interesting blog post by Kendra Little this week ......(more)

The Lighter Side

Star Trek's Vision of the Future - St. Louis, MO, has a wonderful museum called The St. Louis Science Center. If you are ever in the area, it’s worth a visit. I’m lucky enough to live less than an hour’s drive away and visit once a month or so. My favourite event at the Science Center is called First Friday, an evening based on a different theme each month. This month, the theme was Star Trek. Many people showed up in Star Trek costumes, several vendors were selling their Star Trek themed wares, two episodes of Deep Space Nine were featured, and the latest Star Trek movie was shown. In addition to these activities, there were also a couple of discussions about Star Trek philosophy. I attended one of the discussions, “Star Trek’s Vision of Humanity with James Croft.” ...(more)

Tech News

HPE Helps EPFL Blue Brain Project Unlock the Secrets of the Brain - According to a new press release, “Hewlett Packard Enterprise today announced that the Ecole Polytechnique Fédérale de Lausanne’s (EPFL) Blue Brain Project, a Swiss brain research initiative, selected HPE to build a next-generation supercomputer for ......(more)

SQL Server News

Released: Public Preview for Version Agnostic SQL Server MP (2012-2017) - Adding Support for SQL Server 2012, 2014, 2016 Monitoring in the Version Agnostic SQL Server Management Pack We moved to version agnostic management packs with SQL Server 2017+ MP. That is, 2017+ MP is built to monitor SQL Server versions 2017 and up. ...(more)

SQL Server 2008 and 2008 R2 are reaching end of support. What’s next? - SQL Server 2008 and 2008 R2 have had a tremendous run. But all good things come to an end, right? On July 9, 2019, Microsoft will end Extended Support, which means no more updates or support of any kind, potentially leaving you vulnerable to security and compliance issues....(more)

R Language

How to prepare data for a gantt diagram - There’s the new cool world of project management - agile, scrumbling, cool. There’s the old sluggish way of project management using stuff like gantt diagrams. Let’s stick to the old world and come up with a gantt diagram. The gant diagram itself is no big deal. Just some horizontal lines referring to dates. Somewhat more interesting is to populate a raw data frame in a way that allows for convenient plotting....(more)

Product Reviews and Articles

SQL Change Automation with PowerShell Scripts: getting up-and-running - In this article, I’ll demonstrate how to use SQL Change Automation, a PowerShell script, to take the contents of a source control directory for a database, check that it is possible to build from it, document it and then create a NuGet package and place the code in it, along with the documentation. Finally, I’ll show how to synchronize the database schema of an existing database so that it matches the schema of the source control version that we just built and validated....(more)

PowerPivot/PowerQuery/PowerBI

Power BI Tutorial: 4 Ways To Get Data - In this Power BI tutorial, Adam looks at 4 ways to get data in Power BI. This focuses on Power BI Desktop and the main storage modes - Import, DirectQuery and Live Connections to Analysis Services. If you are new to Power BI, be sure to check out this ......(more)

Combining Data From Multiple Worksheets In The Same Excel Workbook Using Power BI - It’s very common that you need to combine data from multiple worksheets in the same Excel workbook when you’re using Power BI or Power Query/Get&Transform in Excel. Indeed a lot of people have blogged about how to solve this problem, but none of the ......(more)

Performance Tuning SQL Server

T-SQL Tuesday: Code You Would Hate To Live Without - T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Bert Wagner (b|t) who has asked us to “write about code you’ve written that you would hate to live without.”...(more)

Index on Key vs Included - Indexing can be quite confusing at times if you not 100% sure on what to do. Do you just index on key or do you add included columns? Well this depends on what you are doing and what type of performance improvement you are looking for. Lets start with some history, SQL Server 2005 added the ability to include non-key columns in a non-clustered index. In SQL Server 2000 and earlier, for a non-clustered index, all columns defined for an index were key columns, which meant they were part of every level of the index, from the root down to the leaf level. When a column is defined as an included column, it is part of the leaf level only....(more)

What Is The Preferred Join Operator in SQL Server? - I schedule many of my blog posts at least 2-3 weeks out, so I have time to adjust them, change, them, insert new things into the schedule, what have you. So, as you read this, I’ll have written it at least two weeks ago. As I write this, I’m recovering ......(more)

Predicate Pushdown and why should I care? - If you follow this blog, you know that in the last couple years, we have been adding more information to showplan, to make it a one-stop-shop for all your query performance troubleshooting insights. You can see more about those showplan enhancements ......(more)

Microsoft News : General Interest

Five Outlook 365 Email Features You Might Not Be Using - These features can boost user productivity by streamlining common routines in email management. ...(more)

Microsoft News : Security

Could Multifactor Authentication Have Prevented the Timehop Hack? - Timehop's cloud account had not been protected by multifactor authentication at the time of the breach on July 4. ...(more)

DevOps and Continuous Delivery (CI/CD)

Making the database key to DevOps with comprehensive, customizable monitoring - The speed of business today demands that the development and deployment of applications is fast-moving, with frequent yet error-free releases. That’s why the adoption of DevOps is trickling down from Amazon, Facebook, Google and the other usual suspects to every company that relies on technology to drive its communications or sales with users....(more)

Data Science

Run R and Python Remotely in SQL Server from Jupyter Notebooks or any IDE - Did you know that you can execute R and Python code remotely in SQL Server from any IDE? This eliminates the need to move data around. Instead of transferring large and sensitive data over the network or losing accuracy with sample csv files, you can ......(more)

IoT Data Analytics: The Rise of As-a-Service Data Scientists - How the marketplace is adapting to accommodate the growing need for IoT data analytics. ...(more)

Data Privacy and GDPR

Spoofing Data Convincingly for Database Development. An Introduction - Many times I’ve been told, by developers who are using live data to develop a database, that it is impossible to anonymise or pseudonymize their data to comply with privacy legislation. One recurring explanation is that one can’t duplicate the distribution of data by faking it. In a sense, this is true, because if you change data you always upset some part of it, such as the number of people called ‘Nigel’, living in Montana. However, this is likely to be a minor inconvenience compared with the risks of exposing personal, financial or business data. ...(more)

How to be Accountable for Handling Personal Data - The GDPR is in full effect but meeting the requirements may still be confusing for many companies. In this article, William Brewer discusses what is needed to ensure compliance, including when a Data Protection Impact Assessment is required. He also explains the Data Protection Officer role....(more)

Data Mining/Data Analysis

Data as Storyteller: Three Ways to Turn Your Analytics into Action - Here’s a scenario to consider: A Data Analyst is told to prepare a report. She has reams of figures ahead of her, and she’s a pro, so she sifts and sums, weaving through the numbers and finding precisely ......(more)

Conferences, Classes, and Events

Data Platform Summit 2018. Bangalore. India. - DPS 2018 is an annual 3-day learning event (with 2-days of pre-conference training) on Microsoft Data Platform & Open Source. DPS is the only learning event in Asia where Microsoft Azure, SQL, Analytics & AI Product Team Members from Redmond & global experts from 15+ nations fly down to Bangalore and deliver in-depth sessions....(more)

Career Growth

How to get your dream job – Webinar recording - I recently presented for the PASS Professional Development virtual group with my session “How to get your dream job: resume & job hunting tips.” When I’m speaking at SQLSaturdays and User Groups, I frequently meet people looking to advance their career, ......(more)

Backup and Recovery

SQL SERVER – Backup to URL – Script to Generate the Create Credential and Backup Command using Access Keys - Backup to URL is one of the common methods used in SQL Server perform backup to Azure Blob Storage. In this blog, I am going to share a script to generate the create credential and backup command using access keys. If you don’t know already, Backup to ......(more)

Azure SQL Database

SQL Agent for Azure SQL Database? Not quite - The following post shows my preferred way to automate / schedule some code against my Azure SQL Database. No it is not PowerShell or Azure Runbooks but it is definitely my favourite way. I have been using Azure Logic apps recently to build some workflows ......(more)

Azure CosmosDB

Introduction to Azure Cosmos DB Emulator for Creating Applications - Azure Cosmos DB is Microsoft’s NoSQL database platform running in the cloud. In this article, Suhas Pande explains many of the core concepts in Cosmos DB. Additionally, he goes over how to set up a local Cosmos DB emulator to create collections and documents. Using a local emulator is free and allows development with Cosmos DB without being connected to Azure. ...(more)

AI/Machine Learning/Cognitive Services

Artificial Intelligence Has its Foundation in Good Data - Creating value and competitive advantage are two top use cases for Artificial Intelligence (AI). Now, it’s time for businesses to really get their hands on how to put it to work to those ends. Most companies are ready and willing to take it on. In the ......(more)

Administration of SQL Server

Registered Server, a Hidden Gem of SQL Server Management Studio. Part 1 - Managing numerous database servers in an organization is difficult. Either we must maintain a list or maintain an Excel file that has a list of database servers. To retrieve basic information about SQL Instance or databases, DBA must query on individual database servers. If a developer wants a DBA to execute an SQL Script on all database servers, DBA must review the list of servers and execute them one by one, which is time-consuming and error-prone. It might happen that DBA missed any server where it was required to execute. Imagine the cases when DBA must execute the script on specific database servers. It becomes very painful....(more)

MSDTC Supported Configurations - The MSDTC Configuration Conundrum MSDTC configuration is not as straight forward as you might think. It’s a different choice if you are using a local MSDTC, clustered MSDTC, on-premises, Azure, Failover Cluster, or Availability Group. Every one of those variables leads you down a different path with different choices. The goal of this article is to clarify those choices....(more)

.NET Related Articles

Working with the HttpClient Class - The HttpClient class is used to send and receive requests to an HTTP endpoint. In this article, Camilo Reyes describes how to work with the class and how to avoid common pitfalls and issue....(more)