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.
Vendors/3rd Party Products
Introducing the updated HTML comparison report in SQL Compare 13.1 - Whether you need to deploy changes, or simply view the differences between two SQL Server databases, SQL Compare helps you do the job quickly and accurately by exploring what’s changed in each database object, down to individual lines of SQL....(more)
A day in the life of a developer with SQL Prompt - There is a lot more to SQL Prompt than IntelliSense and code completion. Tony Davis takes a developer's eye view of its features, as they slot into a typical day of database development work, covering Prompt's snippets, Actions, custom code styling, safe database refactoring, as well as safety net features such as tab coloring and tab history....(more)
SQL Query Experiment - "WHERE" & "ON" - Sometimes the mistakes you make in the coding lead you to a better understanding of a particular concept.This happened recently with me. I accidentally replaced the 'WHERE' clause in a SQL query with "AND" and the results confused the heck out of me. Lets ......(more)
The Lighter Side
On Monday, SpaceX seeks to double its record for annual launches - Enlarge / The Falcon 9 rocket and Koreasat-5A have gone vertical on Pad 39A in Florida. (credit: SpaceX)
SpaceX has launched, on average, about 1.5 times per month during this year. From that perspective, the company's 16th launch of 2017 may not seem ......(more)
Exposing private state to enable unit testing - This article is part of the series of posts about unit testing anti-patterns. Exposing private state Last time, we talked about making private methods public in order to enable unit testing. It’s not the only way people expose implementation details ......(more)
A Pretty PowerBi Pester Results Template File - I have left the heat and humidity of Singapore where I have been presenting at the PowerShell Conference Asia and DevOpsDays Singapore to travel to Seattle for PASS Summit. During my Green is Good – Red is Bad session someone asked me if the PowerBi ......(more)
10 Reasons Why Mainframes Are Still at Work in Enterprise Data Centers - A survey and report by enterprise software and services company BMC asserts that mainframes are still delivering business value and will remain a fixture in corporate data centers for many years yet. ...(more)
USS McCain collision ultimately caused by UI confusion - Enlarge / Tugboats from Singapore assist the guided-missile destroyer USS John S. McCain (DDG 56) as it steers toward Changi Naval Base, Republic of Singapore, following a collision with the merchant vessel Alnic MC while underway east of the Straits ......(more)
FBI Increases Its Anti-Encryption Rhetoric - Earlier this month, Deputy Attorney General Rod Rosenstein gave a speech warning that a world with encryption is a world without law -- or something like that. The EFF's Kurt Opsahl takes it apart pretty thoroughly.
Last week, FBI Director Christopher ......(more)
SQL Server on Linux
SQL Server on Linux on Docker quick and easy - Often in conversations with healthcare customers the topic of architecture evolutions or roadmaps come up and in every conversation, is mention of a hybrid strategy – one where use of both on premise and cloud technologies need to be factored into the ......(more)
Exit Codes, systemd and SQL Server on Linux - In this blog post we’re going to cover systemd, process exit codes and highlight how systemd reacts in certain exit conditions from SQL Server on Linux. My friend and SQL Server guru Argenis Fernandez – @dbargenis asked about this behavior on Twitter ......(more)
SQL Server News
Three years in a row – Microsoft is a leader in the ODBMS Magic Quadrant - We’re happy to report that Gartner has positioned Microsoft in the Leaders Quadrant in the 2017 Magic Quadrant for Operational Database Management Systems again this year. This is the third year that Microsoft has been positioned farthest in completeness ......(more)
Security news and thoughts
Beware: The Equifax Hack Isn't Over -- More Risks Ahead - Long after the Equifax breach was announced, people were still struggling to freeze their credit reports as credit bureau websites failed, lured people to look-alike products or even redirected visitors to download malicious software.
But that's just ......(more)
IRS Fends Off 4 Million Hacking Attempts a Day - The IRS fends off 4 million hacking attempts a day, Commissioner John Koskinen said Tuesday, saying the tax agency does a solid job of keeping intruders out of its databases, though hackers have managed to spoof some taxpayers and steal their information ......(more)
Google Login Security for High-Risk Users - Google has a new login service for high-risk users. it's good, but unforgiving.
Logging in from a desktop will require a special USB key, while accessing your data from a mobile device will similarly require a Bluetooth dongle. All non-Google services ......(more)
Man finds USB stick with Heathrow security plans, Queen’s travel details - (credit: Heathrow Airports Limited,)
An unemployed London man discovered a USB flash storage device lying on the street as he was headed to the library to check the Internet for job listings. When he got to the library, he plugged it in and found it ......(more)
SQL Server R Services: Working with Multiple Data Sets - The series so far:
SQL Server R Services: The Basics
SQL Server R Services: Digging into the R Language
SQL Server R Services: Working with ggplot2 Statistical Graphics
SQL Server R Services: Working with Data Frames
SQL Server R Services: Generating ......(more)
New RStudio cheat sheet: Strings in R - The RStudio team has created another very useful cheat sheet for R: Working with Strings. This cheat sheet provides an example-laden menu of operations you can perform on strings (character verctors) in R using the stringr package. While base R provides ......(more)
SQL Server R Services: Working with Multiple Data Sets - Although it is easy to use SQL Server R Services to create R scripts that incorporate SQL Server data by passing in a T-SQL query as an argument when calling the sp_execute_external_script stored procedure, you are limited to that one query, unless you pass additional data directly between R and SQL Server via CSV files. It is simple to do, and opens up many additional opportunities for data analysis. Robert Sheldon explains how....(more)
Promises And Closures In R - Damian Rodziewicz looks at the new promises package in R:
Citing Joe Cheng, our aim is to:
Execute long-running code asynchronously on separate thread.
Be able to do something with the result (if success) or error (if failure), when the task completes, ......(more)
Power BI Custom Visuals Class (Module 75 – Enlighten Stack Shuffle) - In this module you will learn how to use the Enlighten Stack Shuffle Custom Visual. The Enlighten Stack Shuffle is helpful when you want to display a Top N set of values. For example if you want to display your top 5 selling employees this visual can ......(more)
Performance Tuning SQL Server
Preparing your data platform for peak period sales - In the last few years, Black Friday and Cyber Monday have become synonymous with both getting a good bargain and causing retail pandemonium. This blog post looks at how you can prepare your platform for the increase in traffic....(more)
Capture the most expensive queries across your SQL Server using Query Store - I’m a big fan of using queries based on the dynamic management view sys.dm_exec_query_stats to capture the most resource hungry queries across a SQL instance.
That approach has some drawbacks though. First, it is cleared out every time an instance restarts, ......(more)
What's new in Microsoft's Windows 10 IoT Fall Creators Update release - Microsoft's Windows 10 IoT Fall Creators Update release includes a bunch of new and enhanced features, plus a couple of new capabilities still in preview.
Controlling drillthrough using Detail Rows Expressions in DAX - The Detail Rows Expression in a Tabular model provides the user with control over the drillthrough results obtained by showing details of a measure. This article describes typical DAX expressions you can use in this property.
The Detail Rows Expression ......(more)
Seagate's New Skyhawk HDD Designed for AI Use Cases - SkyHawk AI hard drive, which comes in 10TB and 8TB capacities, has the processing power to manage always-on, data-intensive workloads.
Surface Pro with 450Mbps LTE launching December 1, starting at $1,149 - Microsoft already let slip most of the details of the Surface Pro with LTE back in September at its Ignite conference, but today at an event in London, Panos Panay, vice president of Microsoft Devices, ......(more)
Western Digital Now Shipping 14 TB HDDs: HGST Ultrastar Hs14 with 1000 Gb/in2 - Western Digital has started to ship its new HGST Ultrastar Hs14 hard drives, promoted as being suitable for cloud datacenters and for hyperscale developments. The capacity increase from its predecessor, the Ultrastar Ha10, from 10TB to 14 TB offers a ......(more)
Recent Updates to setting up SQL Server Availability Groups in Azure VM with AAD Domain Services - Reviewed by: Dimitri Furman, Kun Cheng
This blog is an extension to the one that was published in February 2017 . In these eight months there has been some notable improvements to AAD Domain Services (AAD DS) and Azure Virtual Network (VNET) capabilities. ...(more)
SQL SERVER – Adding New Database to AlwaysOn Replica is Slow - First, let me clarify, the words in the subject of this blog are the exact words used by my client and whatever you do and say but “customer is always right!“. Here is the complete story where we faced issue related to AlwaysOn Replica.
One fine day, ......(more)
How we did it: PASS 2017 Summit Session Similarity using SQL Graph and Python - I had previously shared a sneak preview of our upcoming session on Graph data processing in SQL Server. The talk is at the PASS Summit 2017. In that post, I had promised to share more details closer to the session. And here it is!
Inferring Graph Edges ......(more)
Sneak Preview: SQL Graph session at PASS Summit 2017 - At PASS Summit 2017, Shreya Verma and I will be co-presenting a session on using the newly added Graph data (nodes / edges) processing capabilities in your applications. One of the patterns we will discuss in that session is how to leverage in-database ......(more)
Handling Late-Arriving Members in SSIS - I was chatting with some other SQL Community members recently and the topic of handling late-arriving members for a warehouse load came up. I figured it was worth taking a couple of minutes to share the general concept I’ve been using to handle late-arriving ......(more)
What’s that SSIS Password - A recurring theme over the past several weeks (there are always recurring themes it seems) has been an issue that relates to SSIS. Clients will call for help with an SSIS package that has started failing and they are stuck trying to figure it out. They ......(more)
DevOps and Continuous Delivery (CI/CD)
DevOps Basics – Ignoring Files in Git - Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.
One of the things you’ll run into at times is the need to keep ......(more)
Five Mistakes to Avoid When Creating a KPI Dashboard - Click to learn more about author Alex Bekker. Is your goal to create a perfect KPI dashboard? Learn from the mistakes of others – here is the list of common pitfalls to avoid. The road to a perfect Key Performance Indicator (KPI) dashboard is full of ......(more)
DevOps for Data Science – Who needs it? - Data Scientists have often worked in a bit of a “silo” – meaning they were off to the side in an organization, maybe not even part of the Information Technology (IT) function. But that is changing. As data science projects are adopted into the mainstream, ......(more)
EU's GDPR Data Protection Regulations Bring Challenges, Opportunities - A McAfee report reveals how global organizations are preparing for the upcoming enforcement of the European Union's stringent General Data Protection Regulation (GDPR).
Conferences and Events
PASS Summit 2017 Day 2 - It’s day 2 of the PASS Summit. Yesterday was a whirlwind of a day, after my early-morning post-Halloween trip to Seattle. I spent some time with my Microsoft friends in the SQL Clinic and visited several vendor receptions, followed by a quick visit to ......(more)
Registration is now open for SQL in the City Streamed - Redgate's free virtual event is back again this year on December 13. Wherever you are in the world, you can tune in to see educational talks, technical demos, plus all the latest on what's new and upcoming with Redgate tools. Register your free space....(more)
Microsoft Connect(); is only a week away - Whether you are creating cloud-native applications, infusing your apps with AI, or just getting started, Connect(); 2017 will inspire you to build the apps of the future. Join Microsoft’s Cloud Chief, Scott Guthrie, and top developer minds for the latest in open source technologies, Azure, Data, AI, DevOps, Visual Studio, Xamarin, and cross-platform applications. ...(more)
#PASSsummit Day 2 Keynote: Dr. Rimma Nehme on Azure Cosmos DB - Dr. Rimma Nehme
Summit day 2 keynotes have become special.
Over the last few years, Microsoft has dedicated the day 2 keynote to a technical dive into an advanced, future-looking topic. Past examples have included future-looking guidance on Hekaton, ......(more)
#PASSsummit Day 1 Keynote Live Blog - Good morning, folks, and welcome to our annual live blog of the PASS Summit Day 1 keynote.
Open the free live video stream at PASSsummit.com in one browser tab, and then refresh this page every couple/few minutes. I’ll be adding my thoughts at the end ......(more)
SQL PASS Summit 2017: Day 1 Keynote Thoughts and Memories - Whee! It’s the first day of the main SQL PASS Summit conference and I made it to the keynote early for the first time ever. The hype says we’re going to see some great presenters this morning.
Memories: my first Summit, that time I was too shy to say ......(more)
PASS Summit 2017: Day 1 - Hey friends! After a one-year hiatus I am BACK at the PASS Summit and ready to blog the day 1 keynote I will update this post throughout the morning so refresh every so often to see the changes. You can also follow along on Twitter – check out the ......(more)
PASS Summit 2017: Day 2 - Day 2 is starting here at PASS Summit in Seattle – watch this space for updates the next hour and a half!
Grant Fritchey is kicking off day 2! I’m a big fan of Grant. He talks about the community and how people should take advantage of what’s available ......(more)
Announcing my new Mastering class series – and registration opens Wednesday. - We’ve talked about why traditional training sucks, how our all-new series is different, and what our early-access students said.
Now it’s time to unveil the new lineup:
Always On Availability Groups: The Senior DBA’s Field Guide – Taught by MVP Edwin ......(more)
Announcing a new PowerShell for DBAs online course with Brent Ozar Unlimited - I'm extremely excited to announce I'll be conducting a live, two day, online class centered around PowerShell for the fine folks at Brent Ozar Unlimited! Here's the details.
Computing in the Cloud (Azure, Google , AWS)
SQL Server 2017 and Azure Data Services – The ultimate hybrid data platform - This post was authored by Rohan Kumar, General Manager Database Systems Engineering.
Today at PASS Summit 2017, we are showcasing new advances across SQL Server 2017 and our Azure data services. And we’re demonstrating how these products – both on-premises ......(more)
Microsoft says 40 percent of all VMs in Azure now are running Linux - A year ago, Microsoft officials said nearly a third of all the VMs on Azure were running Linux. Now it's 40 percent.
Azure SQL Database Managed Instance - Azure SQL Database Managed Instance is a new flavor of Azure SQL Database that is a game changer. It offers near-complete SQL Server compatibility and network isolation to easily lift and shift databases to Azure (you can literally backup an on-premise ......(more)
If You’re Stuck at Home for the PASS Summit - Conferences are a great place to network, to see new technologies or to see existing technologies being used in new ways, or just to get away the day-to-day work. You may love your job, but having a break to refresh and re-focus is certainly good, too. ...(more)
Azure SQL Database
Deciding Whether to Use Azure SQL Data Warehouse - From time to time I publish on the BlueGranite team blog. My newest post is a decision tree about whether or not Azure SQL Data Warehouse is a good fit.In Azure you have several technology choices for where to implement a data warehouse. Since Azure ......(more)
Configuring Azure Data Sync - In this video David demonstrates how to configure Azure Data Sync 2.0 to synchronise an Azure SQL database with an "On premises" SQL server. As the name suggests the Azure Data Sync tool allows you to synchronise data between Azure SQL databases and ......(more)
Analysis Services / BI on the MS Stack
Can’t Connect to Analysis Services – Invalid Connection Information - I have a new installation of SQL Server 2017, along with an instance of Analysis Services Tabular 2017. I tried to connect to the SSAS instance for the first time, and I was greeted with the following error:
“Invalid connection information to establish ......(more)
AI/Machine Learning/Cognitive Services
Sentiment analysis with Python in SQL Server Machine Learning Services - One very popular machine learning scenario is text analysis. In this blog post, we will show you two different ways in which you can implement sentiment analysis in SQL Server using Python and Machine Learning Services. This means analyzing text to determine ......(more)
Could Not Find Function rxSqlUpateLibPaths - I ran into an interesting SQL Server Machine Learning Services issue the other day. We decided to upgrade our SQL Server 2016 machine running R Services to SQL Server 2017. This was an in-place upgrade rather than rebuild and reinstall.
Administration of SQL Server
In-Memory OLTP Indexes – Part 1: Recommendations. - In-Memory OLTP Indexes
In-Memory OLTP technology, available in Azure SQL Database and SQL Server, can significantly help you in improving the performance of applications that require high throughput and low latency online transaction processing, high ......(more)
SQL SERVER- New DMF in SQL Server 2017 – sys.dm_os_file_exists. A Replacement of xp_fileexist - Recently I published a blog about new DMV in SQL Server 2017 called dm_os_enumerate_fixed_drives. While doing that I found another new DMF (Dynamic Management Function) sys.dm_os_file_exists.
Another new DMF which I saw in SQL Server 2017 was sys.dm_os_file_exists. ...(more)
Using tokens in SQL Agent Jobs and renaming output files - In this post, I want to tell you about some useful feature that can be used within SQL Agent jobs in SQL Server This post I want to tell you about an error that just happened to me recently. It’s very silly, but still can make you waste some time trying ......(more)
Python For The DBA: Copying SQL Logins - David Fowler has an example of how DBAs can use Python to do something interesting:
There are plenty of times when you might want to copy your SQL logins (including the SID) from one server to another. Perhaps you’re running an AG and need to make sure ......(more)
DB Mail error “File attachment or query results size exceeds allowable value of 1000000 bytes” - Issue : One email report is working fine from long time but suddenly it failed today with below error.
Msg 22051, Level 16, State 1, Line 0
File attachment or query results size exceeds allowable value of 1000000 bytes.
Solution : Email report ......(more)
SQL SERVER – SQL Express Installation Error – Wait on the Database Engine Recovery Handle Failed - Sometimes I get emails from software vendors who use SQL Server as a part of their product. One of such vendor contacted me for an issue where their software install was failing because SQL Express was not getting installed. It was failing with an error. ...(more)
SQL SERVER – Msg 0, Level 11 – A Severe Error Occurred on the Current Command. The results, if Any, Should be Discarded - It’s always fun to fix something when there is no meaningful error message. As you can see in the blog title, message id = 0 and it doesn’t tell me how to fix the issue. Here is my checklist whenever someone reports below severe error:
It’s always fun ......(more)
SQL Server Diagnostic Information Queries for November 2017 - This month, there are more minor updates to the all of the versions of the queries, primarily in the comments and documentation. I often make additional minor updates to the queries periodically during the month, so if you are in doubt, downloading the ......(more)
Quick Model Database Tidbit - Are you using your Model Database to its full potential?
I am finding more and more that Database Admins are not using the Model database to its fullest potential and some not at all.
What is that Model Database for?
The model database is basically ......(more)
.NET Related Articles
Hacking Visual Studio - Visual Studio, like any Integrated Development Environment, can host extensions for more specialist languages or development tasks. This sort of work is reasonably straightforward most of the time but occasionally you need functionality that isn't available in the APIs. Michal takes two examples, printing code in an editing window, and gaining access to the Visual Studio Notifications, and explains how to hack Visual Studio to get to the functionality....(more)
Developing a Google Chrome Extension using Angular 4 - It may seem a tricky task to create a Chrome extension with TypeScript and Angular 4 but it turns out to be relatively simple. The advantage is that you gain the modularity of Angular and the object-oriented programming experience with strong typing of Typescript. Jacub explains how it is done....(more)
The Secrets of ASP.NET Core User Secrets - Although configuration data kept in a user profile folder is reasonably secure for development work, all bets are off once it gets saved with the project. Passwords in source code are always wrong. 'User Secrets' can help but are only intended for development work. There are plenty of ways of ensuring that such things as connection strings with passwords or API keys are properly encrypted or protected. Dino Esposito explains....(more)