The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

This week's editorial is a guest post from Phil Factor.

I once managed a software house that did applications for several local businesses. It wasn't easy, because these businesses ran on a very tight budget, and they didn't want the frills. We worked on fixed-price contracts, so if we got it wrong we made a loss. It is a great way of developing a sense of urgency, and making sure that your programming work was sufficient but never fancy.

Our database guy was a quiet, methodical chap who went about his work slowly and carefully. He took every deployment seriously, even if he was making a change to the custom music player in the server room, cobbled together from an ancient Unix box.

As a manager, I gradually began to feel resentful that all his database deployments were taken so seriously, and took so long, even when it didn't matter, and that our development time was getting squeezed in consequence. Eventually, I challenged him about it. To my surprise, he came up with a very good reason.

The reason that every database deployment is taken with the same caution and seriousness, he explained, was that they generally went well; to the point that he could easily become rusty on the many roll-back procedures, belt-and-braces precautions and tests. When anything failed, he tested his skill in bringing things to a happy conclusion. We were managing applications where failure would cause businesses and their staff real catastrophe. A deployment gone wrong could, unless quickly remedied, cause significant loss of trading or reputation to these guys. Because our database guy treated every deployment with the same attention to detail, he occasionally had failed deployments to deal with that weren't important to the outside world, but allowed him to fine-tune his deployment scripts, procedures, and techniques.

I bought this explanation. We never ever had to apologise to a customer. It reminded me of the craftsman restoring a cathedral who maintained his meticulous work, including lively and beautiful carvings, in parts of the building that could never be seen close-up. When challenged to explain why he took such care over those parts of his work that nobody would ever see, he merely replied enigmatically "Well, God will see it." Of course: what they were driving at was that by honing your skills where mistakes don't matter, it allows you a better chance of getting it right when it does matter.

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.

Webinars

Extending DevOps practices to SQL Server databases with Redgate, Git & Jenkins - Join Microsoft Data Platform MVP, Grant Fritchey, for our next Database DevOps demo webinar. He’ll be plugging Redgate’s tools into Git and Jenkins to show you how Redgate's Database DevOps solution helps you build, test and deploy your database changes alongside your app code. Sign-up here...(more)

Vendors/3rd Party Products

New permissions features brings access control to SQL Clone - SQL Clone permissions has been high on the wish-list for users and the new feature addresses it in an intuitive way that enables you to allocate and change permissions quickly and easily....(more)

Using multiple custom SQL formatting styles in SQL Prompt - Usually, when coding SQL, you will want to go along with the coding style of the team you are working with. This article shows how to use SQL Prompt formatting styles to create and maintain multiple code styles, each for a dedicated purpose, and to switch between them and apply a new style to existing code, with ease....(more)

The Lighter Side

Password Guru Bill Burr's New Advice: Never Mind - The man responsible for setting the guidelines for complex passwords says he regrets writing the advice, and acknowledged that it "drives people bananas."...(more)

Painting with Data - The accidental aRt tumblr continues to provide a steady stream of images that wouldn't look out of place in a modern art gallery, but which in fact are data visualizations gone wrong....(more)

SQL Server News

SSMS 17.2 is now available - The newest version of SQL Server Management Studio is now available for download....(more)

New in SSMS: Performance Dashboard built-in - With the new SSMS 17.2, Performance Dashboard will be embedded as a built-in Standard Report. This means that it is available for any SQL Server instance starting with SQL Server 2008, without any extra downloads or running any extra scripts....(more)

New in SSMS: Searching in Showplan - SSMS 17.2, introduces the ability to search table name, index name, column name and more in graphical showplan....(more)

Security news and thoughts

Scientists Hack a Computer Using DNA - Malware can be encoded into a gene and used to take over the computer operating a gene sequencer....(more)

Using PowerShell to check if your password has been in a breach - This PowerShell script checks haveibeenpwned.com/ to see if your personal data has been exposed in a security breach....(more)

Reporting Services

Adding JavaScript to Your PowerShell HTML Reports - If you want to add some interaction to your reports, this tutorial shows you how....(more)

R Language

Tutorial: Publish an R function as a SQL Server stored procedure with the sqlrutils package - In SQL Server 2016 and later, you can publish an R function to the database as a stored procedure. This makes it possible to run your R function on the SQL Server itself....(more)

PowerShell

Exploring SQL Server With Powershell And SMO Basics - Diving into the Sql Server Management Objects library can be a pretty interesting process. You get to work with database objects as in a new way, and begin manipulating and execute code in a much different approach than purely using T-SQL. Powershell offers a unique way to interact with prebuilt cmdlets....(more)

Performance Tuning SQL Server

Five Mistakes Performance Tuners Make - There’s no Top in the title, and that’s because a TOP without an ORDER BY is non-deterministic, and you’ll get yelled at on the internet for doing that....(more)

DevOps and Continuous Delivery (CI/CD)

DevOps and the DBA: The Ghost in the Machine - Kellyn Pot’Vin-Gorman gives a dose of DevOps 101 to database administrators....(more)

Database Design, Theory and Development

Why Parameter Sniffing Is Making Your Queries Receive Sub-Optimal Execution Plans - Parameter sniffing isn’t always a bad thing. Most of the time it’s good: it means SQL Server is caching and reusing query plans to make your queries run faster. Parameter sniffing only becomes a problem when the cached plan isn’t anywhere close to being the optimal plan for given input parameters....(more)

How Indexing Affects Deletion Queries - Indexing can affect the performance cost of row deletion as well as insertion....(more)

The Power of Python and SQL Server 2017 - Python is new to SQL Server 2017. It is intended primarily to allow the use of Python-based machine-learning within SQL Server, but it can be used for far more than this, with any Python libraries or Frameworks. To provide an example of what is possible, Hitendra shows how to use the feature securely to provide intelligent application caching, where SQL Server can automatically indicate when data changes to trigger a cache refresh....(more)

Data Privacy

Who was that masked man anyway? - How anonymous is anonymous data?...(more)

Computing in the Cloud (Azure, Google , AWS)

Beware the automated backup window when running native SQL Server backups in RDS - If you’ve ever fired up an RDS instance you know you can set an automated backup window for your instance. During this window Amazon will kick off an automated snapshot of your RDS instance each day. Set it and forget it. Kinda nice. Backups are good, right?...(more)

How to choose a cloud provider - Practical questions to help you make a decision.If you look up the phrase “boiling the ocean,” it’s defined as writing a post on choosing a cloud provider—there are so many different facets and use cases, and each variable complicates your choice....(more)

Career Growth

How to be a bad interviewer - You’re not obligated to accept a job once it’s offered, and if you’ve had to put up with a lot of rubbish during the application process then seriously consider if it’s just going to get worse once you’re in the role. Here are some antipatterns to look out for....(more)

The DBA Job I Turned Down - Brent Ozar shares a tale of a job he turned down, and the warning signs that sent him running....(more)

Learning designed for the data professional - PASS Summit offers five educationally-packed days lead by highly-rated, experienced speakers and sessions focused on the way you work with data. Speaker highlights include Bob Ward, and Sunil Agarwal from the Microsoft Tiger Team. Join them Oct 31 to Nov 3....(more)

Azure SQL Database

SQL Server to Azure SQL Database data movement options and considerations with changing schemas and triggered refreshes - This post looks at the problems caused when moving the data at irregular intervals from an on-premise SQL instance with a constantly changing schema to an Azure SQL database....(more)

Initializing Azure SQL Database with SQL Server Transactional Replication - For migrating databases from an on-premise SQL Server to Azure SQL Database, Transactional Replication has become more and more popular. This approach has some important advantages: you may maintain a live copy of your production data in Azure SQL Database, and reduce the downtime for the actual switch to the cloud to a minimum....(more)

Azure SQL: work with JSON files where they are - Dealing with CSV or JSON data today is more and more common. SQL Server 2017 and the latest editions of Azure SQL Server offer new ways to work with these data types....(more)

AI/Machine Learning/Cognitive Services

Podcast: Integrating data with AI - Tamr’s Eliot Knudsen on algorithms that work alongside human experts, integrating data from different silos....(more)

Administration of SQL Server

How to Drop All Your Indexes – Fast - If, for whatever reason, you need to drop all of your indexes in a hurry, Brent Ozar has you covered....(more)

Do Index Changes Remove Execution Plans from Cache? - When you modify the indexes on a table, SQL Server needs to reconsider how it executes queries that reference that table. But the way this appears when you’re looking at your execution plan cache is far from obvious....(more)