The Complete Weekly Roundup of SQL Server News

In this issue:

Virtualization

Vendors/3rd Party Products

T-SQL

SQL Server Security

SQL Server News

SQL Server Internals

Security news and thoughts

Reporting Services

R Language

PowerShell

Performance Tuning SQL Server

HA/DR/Always On/Clustering

ETL/SSIS/ELT

DevOps and Continuous Delivery (CI/CD)

Data Privacy

Data Mining/Data Analysis

Data Access / ORMs

Computing in the Cloud

Career Growth

Analysis Services / BI on the MS Stack

Administration of SQL Server

Database Weekly - www.databaseweekly.com

The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

SQL Server News for 2017-03-06

SQL Clone NEW SQL Clone - version 1 now available!
Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Try it free.
Editorial - Creative IT Learning

I reckon that the art of a successful career in IT lies in the ability to learn new skills and technologies. All the people in the profession that I admire have carried on learning even when they were supposed to be in a deck chair on the beach in Corfu.

If I'm right, and it is not just sheer brainpower that counts, but also tenacity, interest and retention, then how does one go about continuous learning and, more importantly, how should learning materials be presented? What if you seem to have a memory like a bucket with a hole in it? Could it be that to learn facts, you need them presented in a particular way?

Some people like video courses, others prefer structured and graded materials such as Stairways, which try to introduce concepts sequentially. For others, the only effective approach is to throw themselves in the deep-end, and learn by doing, as the adrenaline rushes through them. There are many people who like to approach an area of knowledge as if from a helicopter, just seeing the tops of the trees before descending slowly into the jungle of technological detail. Others don't feel at ease until they are flaying away at the darkness with a machete.

It would be wonderful for education if the same learning methods were effective for all of us, but they're not. I'd like to be able to say that all you need to do is to be certain of what best works for you, and get the appropriate learning materials. However, I can't because they may not yet exist; however, the internet is a great place for nurturing new approaches to learning, and I like the lateral thinking that sometimes happens.

One of my favorite IT books was done in cartoon form, and there have been several in the form of a comic strip. I really enjoyed the Manga Guide to Databases and the Manga Guide to Regression Analysis.

If all this still seems a bit...err…bookish, then there are some good wallcharts and cheat sheets to be had, such as the long-running SQL in one page or the SQL to Hive cheat sheet. I also like the idea of cheat sheets on coffee-mugs such as the Mug of Perl or the DatasSQL mug.

Learning-by-doing suits many people best, and it can be a lot of fun as long as you're clear on your objectives. Why not try creating and running a PostgreSQL server on your network based on a Raspbery Pi? It is just a matter of following instructions. When you've succeeded, reward yourself with SQL T-shirts and beermats.

I wish I'd had programmable Lego Mindstorms, such as a programmable banner printer, when I was learning programming, but heck, I'm tempted even now. To think of all the time that I wasted trying to make disk drives hum tunes!

On SQLServerCentral, Steve leads the way in providing a range of learning materials, including articles, quizzes, code samples, podcasts, webinars, stairways and cartoons. It has been a heroic effort over the years. Is there more that can be done to build the community of database professionals and find new ways to help them learn? If so, how can you help to make it happen?

Phil Factor.

» Join the debate, and respond to today's editorial on the forums


The Weekly News

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.

Virtualization

A GUI for Docker Container Administration - I’ve been working with containers for a while now and one of the questions that always gets asked when I demo the technology to people is, is there a graphical user interface out there that can be used to manage containers?...(more)

An Introduction to Docker and Containers for SQL Server Developers and DBAs - A high-level look at containers and their use by SQL Server DBAs and developers. ...(more)

Vendors/3rd Party Products

Team-based database development with SQL Clone - With SQL Clone, developers can even create and delete their own database clones, whenever they need to, based on images the DBA provides. Suddenly, the database is just a lightweight resource they spin up, integrate into their development workflow, and then tear down again, as required. It’s a big leap from how development teams have traditionally been forced to work with ‘heavyweight’ databases....(more)

T-SQL

ERRORLOG records max out at 2049 characters - Stupid question… what's the schema of a table with sys.sp_readerrorlog output? Well you might be surprised if you're used to using nvarchar(max) or nvarchar(2048)....(more)

SSMS: Removing all tabs in your query window with spaces - It’s a pain in the neck when I receive a script file from someone and it’s full of tabs, but nowadays, SSMS can help yet very few people seem to realize it....(more)

Quickly Identify Database and File Sizing for Any SQL Server Instance - As a Database Administrator, you probably need to know the sizes of your databases, both at database level (aggregating the log file and all the data files that comprise the database) or the file level. Sometimes you need this information for all the databases and sometimes just a single database. Tim Ford provides a stored procedure to do all this for you....(more)

SQL Server Security

Identify SQL Accounts Without Password Policy Enforced - When ‘Enforce Password Policy’ for SQL Server logins is turned off, you can make your password anything you want! Hooray! No picky criteria and trying to remember yet another convoluted password! Not the best idea, especially if you need to have a more secured server....(more)

SQL Server Permissions – Database Roles - Ever taken a look at the User Mapping page and discovered a user is a member of every database role, just in case? Jason Brimhall has....(more)

SQL Server News

SQL Updates Newsletter – February 2017 - SQL-related news, announcements, issue alerts, whitepapers and more....(more)

SQL Server vNext Management Pack Demo - Demo'ing the CTP2 for SQL Server vNext Management Pack, which can be used to monitor SQL Server vNext versions both on Linux and Windows. In addition to the standard agent monitoring where you have a SCOM agent on the server you are monitoring, you can also have agentless monitoring and mixed mode monitoring....(more)

SQL Server Internals

Context in perspective 2: A pretty stack frame is like a melody - This is part 2 in a series exploring ways in which a chunk of running SQL Server code gains context, i.e. the mechanisms by which a bunch of instructions get to participate in something bigger and just possibly a bit unpredictable....(more)

Security news and thoughts

Researchers uncover PowerShell Trojan that uses DNS queries to get its orders - Delivered by "secure" Word doc, pure PowerShell malware fetches commands from DNS TXT records....(more)

Reporting Services

Data Types in SSRS Mobile Reports Category Charts - The Category Chart displays a value over a series. For example, you may want to display total sales by territory. The category, or series, is required to be one of the character data types (char, nvarchar, etc.)....(more)

Data Types in SSRS Mobile Reports Category Charts - Kathi Kellenberger demonstrates the Category Chart, which displays a value over a series. For example, you may want to display total sales by territory. The category, or series, is required to be one of the character data types (char, nvarchar, etc.)....(more)

R Language

Detrermining the Cost Threshold for Parallelism - Knowing cumulative estimated cost of your execution plans can help you determine what the Cost Threshold on your system should be. However, we don’t want to just take the average and use that. You need to understand the data you’re looking at. Let’s explore this just a little using R....(more)

Using an R Package within SQL Server with Real time analysis in Power BI - We all know how easy it is to run R, together with dependent packages, from SQL Server on order to do statistical analysis, and then turn the result into a real-time graph using Power BI; but can you actually do it? Saurabh Desai gives a step-by-step hand-holding guide to turn even the most timid into a BI guru. There is nothing difficult in using R in SQL Server 2016 once you know the steps....(more)

PowerShell

PowerShell – Hate The Error Text And Warning Text Colors? Change It! - How could such a nice tool have such a terrible choice of colors for errors and warnings? I am talking about the ISE and the unreadable contrast of colors....(more)

PowerShell: Getting More From Generic Error Messages - SQL Server has some really stupid, generic error messages. Case in point…"String or binary data would be truncated." Yes, but what column would be truncated? What value would be the offender here? I am okay with not having the exact answer but it would be nice to have more!...(more)

Performance Tuning SQL Server

A few new things in XML plan to help you troubleshoot query performance - In SQL 2016 SP1 release, execution plans show the top wait stats for the query. ...(more)

Benchmarking storage using Diskspd.exe - Diskspd.exe is great to generate read write activity in a volume, using different parameters to hammer it in a variety of ways, trying to replicate the multiple ways SQL Server performs IO operations....(more)

Tracking database usage with Extended Events returns invalid database_id? - The TLDR answer to this is that Extended Events isn’t returning an invalid database_id at the time that the event occurs, the database_id just isn’t valid when the data is being reviewed or consumed. Jonathan Kehayias explains how this can happen....(more)

USE THIS: sp_helpindex - Kimberley Tripp releases a new version of sp_helpindex, with newer functionality (included columns and filters) as well as being better at describing what’s REALLY in your indexes (at the leaf level AND up the b-tree)....(more)

HA/DR/Always On/Clustering

SQL Server Fullscan Statistics Being Overwritten with Sample Statistics - Once I update my statistics with fullscan, within 10-20 seconds some of the statistics on the same table are getting updated on the AG secondary with a sample percent of rows. In other words, my "best" statistics are being overwritten with "good" statistics....(more)

ETL/SSIS/ELT

Deploying Multiple SSIS Projects via PowerShell - When there are several SSIS projects with packages in a SQL Server Database or Data Warehouse development, automated deployments as part of Continuous Integration can get tricky. Nat Sundar describes how he created a Deployment script that is intended to provision a Data Warehouse for System Integrated testing (SIT)....(more)

DevOps and Continuous Delivery (CI/CD)

SSDT Deploy / Publish Performance - Publishing dacpac's is a little bit of a pain when you have multiple databases, it can easily start to take minutes to hours to deploy changes depending on how many databases and the size of those databases. Ed Elliott wanted to understand more about the publish process and what we can do to speed it up...(more)

Data Privacy

Data from connected CloudPets teddy bears leaked and ransomed, exposing kids' voice messages - CloudPets left their MongoDB database, containing over 2 million kids' voice messages, exposed publicly to the web without so much as a password to protect it. The data was leaked and ransomed. Troy Hunt describes the details....(more)

Data Mining/Data Analysis

SQL Text Analysis with Donald Trump’s Tweets - Being the data junkies that we are, we couldn’t resist downloading President Trump’s entire tweet history and loading it into Periscope Data. It’s an excellent opportunity to slice, analyze, and quantify presidential communications while also exploring methods for analyzing text in SQL at the same time....(more)

The Trouble with Data Warehouse Analytics - Warehouses are essentially databases biased for some data applications (and against others) and are rooted in poor database foundation knowledge and logical-physical confusion. So argues Fabian Pascal in his latest database debunking....(more)

The BabbyNames Sample Database – Now on GitHub - I love playing around with the free data that the Social Security Administration publishes on baby names each year. It’s fun to manipulate the dataset in a variety of ways, and you learn odd things along the way… like the fact that more than 7,000 babies in the US have been named ‘Kale’ since 1917....(more)

Data Access / ORMs

SQL Browser, what is it good for? Absolutely something! - The SQL Browser provides a valuable service when an application tries to connect to a SQL Server named instance; it listens on UDP 1434 and provides information about all SQL Server instances that are installed on the server....(more)

How to: Solve General SQL Server Connectivity Issues - Thomas La Rock suggests some web resources to bookmark for people struggling with SQL Server connection problems....(more)

Computing in the Cloud

AWS Outage that Broke the Internet Caused by Mistyped Command - An Amazon Web Services engineer was debugging an issue with the billing system for the company’s popular cloud storage service S3 and accidentally mistyped a command. What followed was a several hours’ long cloud outage that resulted in hundreds of millions of dollars in losses for AWS customers and others who rely on third-party services hosted by AWS....(more)

Exploring Azure Storage for SQL Server DBAs – Part 2 - Managed Disks have simplified way that Azure storage interacts with the users' virtual machines, thanks to the way that it eliminates the need to deal with the Storage Account. It is now easier to add new disks to a virtual machine, either in PowerShell or via the portal. The Storage Spaces feature in Windows Server can be used to aggregate disks together and obtain higher levels of performance. Joshua Feierman explains how to do it all....(more)

Serverless architecture with Azure - Azure provides an idea platform for hosting microservices because it offers a number of managed services that allow developers to create microservices that can run reliably and at scale. The problem is in understanding how these managed services can help and which is most suitable for the task. Christos Matskas provides a useful summary for anyone developing cloud-based applications....(more)

Career Growth

What Kinds of Raises do DBAs Get? - I took the data from the 2017 Data Professional Salary Survey, and filtered just for database administrators in the United States. Then, I pivoted it on the two experience questions. First, we’ll cover database experience, and then we’ll cover job experience....(more)

Analysis Services / BI on the MS Stack

Biml Database Inspection - Bill Fellows has been using Biml to reverse engineer a very large database but the import methods to build the Biml always seemed to fail somewhere along the way. Here he talks through the basics of how the modeling works within Biml....(more)

Dynamic Grouping in Power BI using DAX - The user wants a report with a column chart. The X axis will have Subcategory Name and the value will be the sum of Internet Sales. Along with this chart, the user will have a slicer where they can select the Subcategory Names. The column chart should “update” showing one column for each selected subcategory, and another column named “Others” with the summed amount of the rest of the unselected categories....(more)

The IN operator in DAX - This article describes the IN operator in DAX, which simplifies logical conditions checking whether a certain value is included in a list of values or expressions....(more)

M or DAX? That is the Question! - What is the main difference between M and DAX? Why can we do a calculated column in two different places? What are pros and cons of each? Which one should I use for creating a profit column? ...(more)

Administration of SQL Server

The Guide: SQL Server Installation Checklist (settings that increase SQL Server Performance) - How’d you like to see my secret stash of untapped SQL Server tuning items I personally use to when setting up new SQL Servers to make them go faster and be more reliable?...(more)

SQLskills SQL101: Dealing with SQL Server corruption - All through his professional career, Paul Randal has seen people make mistakes when they encounter corruption. Here, he offers some quick guidelines for how to approach SQL Server corruption....(more)

Better SQL Agent Job Alerts - It is nice that we have the option for the SQL Agent to tell us when a job fails or succeeds, but if you have ever looked at the message, there isn’t much detail in it....(more)

WAL: The concept that makes recovery models & backups make sense - When you’re a Junior DBA, it’s really hard to take in all the information out there. Learn about write ahead logging: the concept that can help you make sense of recovery models and backup strategies in SQL Server. This is a foundational concept that can help you understand how SQL Server works....(more)

SQLskills SQL101: Trace Flags - SQL Server trace flags are used to change the behavior of the engine in some way. If you're unsure why you might need one, and how you would know if you did need it, then read Erin Stellato's 101 guide....(more)

Let’s Corrupt a Database Together, Part 2: Nonclustered Indexes - Brent Ozar demonstrates how SQL Server reacts when it finds corruption in a data file, and why you should check your databases for CHECKSUM settings, and set up alerts so you can react fast....(more)


Administrative