The v.Next Bleeding Edge (Database Weekly 1/30/2017)

The Complete Weekly Roundup of SQL Server News In this issue: T-SQL - [Handling Graphs in SQL] - [SQL SERVER – Understanding JSON Use is Case-Sensitive] - [How to Count the Total Number of Rows Across Multiple Tables] - [SQL Server 2016: JSON integration] - [Compare column names for case sensitivity] - [DATEDIFF vs. DATEADD] - [SCOPE_IDENTITY() or @@IDENTITY] - [Unicode strings and implicit conversions] - [Muscle Memory And CTRL+F] - [Keyboard shortcut to close a query tab in SSMS] - [Debugging Complex SQL Queries] The Lighter Side - [SpaceX is about to launch one of its final expendable rockets] - [Time to start watching The Magicians, the season’s best dark fantasy] - [Technical troubles likely to delay commercial crew flights until 2019] - [The most underrated show on Data Platform – SQL Unplugged] SQL Server Security and Auditing - [The EXECUTE permission was denied on xp_msver] - [What is a Database Master Key?] - [My SQL logins don’t work. Again.] SQL Server on Linux - [SQL Server on Linux: Debugging ELF and PE Images (dbgbridge)] - [Transaction log shipping in SQL Server on Linux] SQL Server News - [Cumulative Update #4 for SQL Server 2016 RTM] - [Cumulative Update #1 for SQL Server 2016 SP1] - [Cumulative Update #7 for SQL Server 2012 SP3] - [Last Cumulative Update #16 for SQL Server 2012 SP2] - [Announcing the SQL Server v.Next Early Adoption Program] - [SQL Server next version CTP 1.2 now available] - [SQL Server Management Studio Version 16.5.2 Now Available for Download] Software Development - [Challenge: What does this code do?] Security news and thoughts - [Duress Codes for Fingerprint Access Control] - [How To Protect Your Privacy at Work] - [SEC on Yahoo's Case for Data Breaches] - [Ex-Goldman Sachs programmer found guilty, again, of source code theft] R Language - [SQL R Services optimization for concurrent execution of sp_execute_external_script] - [Free guide to text mining with R] - [Kung Fu R] Product Upgrades and Releases - [First Responder Kit Updates: So Many Updates, Brent Quit] Product Reviews - [How to build multiple database versions from the same source: post-deploy scripts] - [Useful #SQLPrompt Tips] PowerShell - [Error While running PowerShell code from SQL Agent job – “Path Does Not Exist”] - [Using #PowerShell to Restore to a New Location] PowerPivot/PowerQuery/PowerBI - [Power BI On-Premises; Hello World!] Performance Tuning SQL Server - [Performance Surprises and Assumptions : GROUP BY vs. DISTINCT] - [A new (to me, and possibly you) SSMS feature - Query Plan Comparing] - [Incremental Statistics – My New Favorite Thing] - [GroupBy Conference – 3 Common Mistakes to Kill SQL Server Performance] - [Data About Execution Plans] - [Analyzing Temporary Stored Procedures with sp_BlitzCache] - [How to dynamically determine the correct Process\Working Set performance counter instance] - [Data About Execution Plans] - [What’s that Garbage in my Execution Plan? (Dear SQL DBA Episode 26)] NoSQL - [SQL versus NoSQL Databases: A Review of Key Themes] Microsoft News - [Microsoft Announces Q2 FY 2017 Results] - [Reminder: Microsoft to no longer update original Windows 10 release after March 26] - [Microsoft to cut 700 employees in next layoff round: Report] - [New Zealand bank replaces SAS server with R Server] MDX/DAX - [Year-over-year comparison using the same number of days in #dax] - [The List.* M Functions And The equationCriteria Argument] Internet of Things - [The Emphasis is on Out-of-the-Box Internet of Things Data Management] Hardware News - [Hardware Selection for a Home Lab – Part 1] - [Two Recent Laptops Compared] - [Seagate wants to push huge 16TB HDD out the door in next 18 months] - [SanDisk Launches A1-Class microSDXC Card] - [HP Recalls Over 100,000 Laptop Batteries Due to Potential Fire and Burn Hazards] HA/DR/Always On/Clustering - [SQL Server Corruption – Read Before] - [When Always On Isn’t: Handling Outages in Your Application] Events to attend - [IoT in Action with Microsoft Virtual Bootcamp 28th-30th March 2017] - [Upcoming R Conferences] ETL/SSIS/ELT - [An Example of Data Integration Lifecycle Management with SSIS, Part 3] - [Run PowerShell scripts in SSIS] Data Warehousing - [Don’t Call it a Data Lake, its a Data River. Here’s Why.] Data Privacy - [Microsoft Wins Another Victory in Federal Email Case] Data Mining/Data Analysis - [The state of Jupyter] - [SCUBA: Diving into Data at Facebook] - [Data Science Platforms Help Insight Leaders Outperform Competition] Case Studies - [Learn Redis the hard way (in production)] Career Growth - [What Is Your Career Investment Portfolio?] - [5 Characteristics Every Leader Should Practice] - [Career Corner: Go Beyond Networking] - [The Two Best Things Paul Randal Taught Me #DevIntersection #SQLintersection] Backup and Recovery - [Code to show rolled back transactions after a crash] - [Automate SQL server backup file removal/deletion from Azure blob storage] - [Using Backup to URL in SQL Server 2012 instance] - [Backup/Restore–What’s My Status?] - [Restoring An Encrypted Backup Of [master]] Azure SQL Database - [Connect to your Azure SQL DB behind MsAccess web application] - [Interactive query experience for SQL databases] Azure SQL Data Warehouse and Data Lake - [Sampling Data Lake Data] - [Scheduling Statistics Maintenance in Azure SQL Data Warehouse] - [Microsoft Azure Data Lake Tools for Visual Studio Code] Azure Analysis Services - [Process Azure Analysis Services databases from Azure Automation] - [Azure Analysis Services cross tenant access] - [Azure Analysis Services processing/refresh sample AMO code] - [Azure Analysis Services – How to switch to a different user account in report tools] Analysis Services / BI on the MS Stack - [SSAS processing performance gain by using OLE DB provider for Oracle instead of using .NET provider for Oracle] - [SSAS Activity Monitor] - [SSAS Events Analyzer] AI/Machine Learning/Cognitive Services - [What’s a Database Encryption Key (DEK) in TDE] - [Infographic: The Growth of Artificial Intelligence in E-commerce] - [Maropost Introduces Industry’s Most Sophisticated Machine Learning Capabilities] - [How Machine Learning and AI are Impacting the Data Industry] Administration of SQL Server - [Migrating a Disk-Based Table to a Memory-Optimized Table in SQL Server] - [SQL SERVER – Using dm_db_stats_properties With InMemory OLTP Tables] - [SQL 2016 – Temporal Tables – How do you drop a Temporal Table?] - [CREATE DATABASE – I’ve not seen that before.] - [Using Extended Events Telemetry Session] - [Change Tracking Cleanup – Part 2] - [SQL Server Mysteries: The Case of TDE and Permanent Tempdb Encryption] [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-01-30 [SQL Toolbelt] Not enough hours in your day? The SQL Toolbelt lets you reduce the time spent on SQL Server development and administration. Cambridge University developer David Spaxman, for example, says: “I’m saving 10-12 hours a week using the SQL Toolbelt.” Learn how you can double your productivity, speed up deployments and protect your data. [Download a free trial.] [Database DevOps] State of Database DevOps Report Learn how teams are extending DevOps practices to SQL Server databases. [Get the report.] [SQL Compare] New Redgate SQL Compare 12 has landed! SQL Compare 12 has landed with a brand new user interface, support for SQL Server 2016, and a wealth of fixes and improvements. Check out this blog post from Redgate's Carly Meichen to hear more about what’s new, why the team have built it, and how. [Read now.] Editorial - The vNext Bleeding Edge Microsoft has had early access programs for years. I worked at a few companies that were a part of the TAP ([Technology Adoption Program]) program, and we would get access to code early in exchange for providing feedback and working closely with Microsoft engineers. This was interesting, as well as good and bad. We could move early to implement new features, but we also had the struggles of dealing with code that wasn't always working as expected. Microsoft has stared a new program, [the SQL Server Early Adoption Program (SQL EAP)] to allow companies interested in moving to SQL Server v.Next as soon as possible. I suspect lots of ISVs that might want to build products for SQL Server on Linux might get involved, but there will also be some large corporations that want to influence the platform and get certain features changed or prioritized. The benefits note that participants can come to the SQL CAT team lab to test things, work with product managers, and get separate support if they go into production. For companies that might feel the next version provides some new benefits, Microsoft gets all sorts of feedback, but working with a small group of product managers or engineers is a good way to get your voice heard above others. I know that MVPs get the chance to exert similar influence, and at times this can be valuable. However, one has to keep in mind that this is the chance to voice opinions and thoughts, not votes. Microsoft isn't necessarily going to implement something you want just because you convinced some engineers it's a good idea. Getting into the program does mean that your are bound by an NDA, and that you may have a separate EULA for production workloads. However, if your company likes being on the bleeding edge, and wants the chance to upgrade early, perhaps you should consider applying. I would say that participation in the TAP program was a beneficial experience for my employers. Perhaps the SQL EAP will be for you as well. Steve Jones from [SQLServerCentral.com] » [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. T-SQL [Handling Graphs in SQL] - Graph databases can answer several types of questions about data that are difficult with a relational database. They are particularly useful for social networking applications, fraud detection, optimizing LAN topologies, forensic work or any requirement ......[(more)] [SQL SERVER – Understanding JSON Use is Case-Sensitive] - The things I get to learn from my daughter is amazing. I think all of us need to constantly explore without any prejudice and set biases. Whenever I get a new toy for some occasion, I see there is at least a half hour of exploration to understand how ......[(more)] [How to Count the Total Number of Rows Across Multiple Tables] - I have been working on SQL Server Consulting assignment,  a SQL Server 2014 migration for a new client over the past few weeks. When I’m undertaking such tasks I’m often asked additional questions that make my  day a little interesting. I got a question ......[(more)] [SQL Server 2016: JSON integration] - This article helps to explain the application of JSON data and the use of new SQL 2016 constructs that enable an integration of JSON data into a relational schema. The idea behind this article is to list as many examples of JSON nested elements, ......[(more)] [Compare column names for case sensitivity] - I’m reviewing the code for the upcoming Minion CheckDB, and one of the things we’re checking for is case consistency in column names. For example, if Table1 has a column named Col1, and Table2 has COL1, well that’s no good. But, how do we easily find ......[(more)] [DATEDIFF vs. DATEADD] - Let’s talk about the DATEDIFF vs. DATEADD functions in SQL Server. Each one performs a different task. The former calculates the differences between two given date & time values based on some date part (day, minute, second, etc.). The latter calculates ......[(more)] [SCOPE_IDENTITY() or @@IDENTITY] - I've seen a lot of usage of both functions in the T-SQL codes. However, their usage sometimes matters. The expectation from @@IDENTITY can sometimes be not the desired.This post shows why you should use SCOPE_IDENTITY() instead of @@IDETNITY when using ......[(more)] [Unicode strings and implicit conversions] - Recently we had an interesting customer question about a seemingly strange behavior (and perhaps not widely known) on implicit conversions to Unicode. Imagine you declare a non-unicode string variable, and when concatenating strings that seem to fit ......[(more)] [Muscle Memory And CTRL+F] - I experienced a handful of "firsts" this month. I installed Windows 10, followed soon after by installing SQL Server 2016 and SSMS 2016. It didn't take long for me to hit the CTRL+F keyboard shortcut in SSMS, which of course opens the "Find" dialogue. ...[(more)] [Keyboard shortcut to close a query tab in SSMS] - Shortcuts & tips from an Impatient DBA This series of short posts will demonstrate some lesser-known features, keyboard shortcuts, and other tips that make my day as a DBA more productive. None of these are groundbreaking super-secret features–they are ......[(more)] [Debugging Complex SQL Queries] - We favor the offloading of application logic to SQL queries. Developers can save time, effort, and reduce mistakes by substituting SQL for programming code. This leads to lengthy and complex SQL that must be debugged. It is common to write a SQL query ......[(more)] The Lighter Side [SpaceX is about to launch one of its final expendable rockets] - Enlarge / The landed Falcon 9 rocket that launched the Iridium satellites on Jan. 14, 2017. (credit: SpaceX) After successfully returning to flight on January 14, SpaceX will make its next launch from Cape Canaveral no earlier than January 30. With ......[(more)] [Time to start watching The Magicians, the season’s best dark fantasy] - Syfy Warning: This piece contains minor spoilers for The Magicians S1 and early S2. The Magicians will sneak up on you. At first it might seem like a cheesy CW show about angst and magic at an exclusive East Coast college. But suddenly, it gets seriously ......[(more)] [Technical troubles likely to delay commercial crew flights until 2019] - Enlarge / SpaceX revealed its Dragon V2 spacecraft in May, 2014. It's still a ways from the launch pad. (credit: SpaceX) This week Boeing made a public splash by debuting a new, blue spacesuit for astronauts to wear aboard its Starliner spacecraft. What ......[(more)] [The most underrated show on Data Platform – SQL Unplugged] - I would like to write a blog post about by far the most underrated online show about Data Platform – SQL Unplugged. Leaded by Scott Klein (One of the best tech evangelist that Microsoft has), this show focuses on talking about the Microsoft Data Platform ......[(more)] SQL Server Security and Auditing [The EXECUTE permission was denied on xp_msver] - Recently after installing SQL Server and configuring SSIS for project deployment in a SQL Server 2012 environment, a customer was experiencing issues when attempting to execute a package deployed to the Integration Services catalog or through SQL Agent ......[(more)] [What is a Database Master Key?] - The encryption mechanisms in SQL Server are interesting, and they work well, but they are somewhat poorly named. I ran across a few people struggling to understand, so I decided to cover the concepts in a series of posts. This one looks at the Database ......[(more)] [My SQL logins don’t work. Again.] - My recent post on why SQL logins aren’t working has generated a couple of follow up posts. The first one was on how to create a connect item and then this one. It was pointed out to me that the errors in the log have quite a bit more information than ......[(more)] SQL Server on Linux [SQL Server on Linux: Debugging ELF and PE Images (dbgbridge)] - In my last post I highlighted the marriage of PE and ELF images within the same process space to build SQL Server on Linux. In this post I will expand upon the dbgbridge component, as mentioned by Slava in his latest channel 9 video. The dbgbridge ......[(more)] [Transaction log shipping in SQL Server on Linux] - SQL Server on Linux has been out for a bit now and I’ve played around a little (see here) but haven’t really used it in “anger” nor will I for the foreseeable future if I’m honest. Nevertheless it’s an area that I find very interesting as I know very ......[(more)] SQL Server News [Cumulative Update #4 for SQL Server 2016 RTM] - The 4th cumulative update release for SQL Server 2016 RTM is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......[(more)] [Cumulative Update #1 for SQL Server 2016 SP1] - The 1st cumulative update release for SQL Server 2016 SP1 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......[(more)] [Cumulative Update #7 for SQL Server 2012 SP3] - The 7th cumulative update release for SQL Server 2012 SP3 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......[(more)] [Last Cumulative Update #16 for SQL Server 2012 SP2] - The 16th cumulative update release for SQL Server 2012 SP2 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......[(more)] [Announcing the SQL Server v.Next Early Adoption Program] - This post was authored by Anna Shrestinian, Program Manager, SQL Server What is the SQL Server Early Adoption Program (SQL EAP)? The SQL Server Early Adoption Program (SQL EAP) is a Microsoft program started in January 2017 to help both customers and ......[(more)] [SQL Server next version CTP 1.2 now available] - As part of our rapid preview model, Microsoft is excited to announce that the next version of SQL Server (SQL Server v.Next) Community Technology Preview (CTP) 1.2 is now available on both Windows and Linux. In CTP 1.2 we implemented bug fixes and added ......[(more)] [SQL Server Management Studio Version 16.5.2 Now Available for Download] - The latest release from the SQL Server product group at Microsoft has just gone public and is available for download today. Read on to see what has changed. read more ...[(more)] Software Development [Challenge: What does this code do?] - This was a wild trip. Take a look at the following piece of code, what do you think it is doing? Hint, it isn’t printing “Oren” to the console. Now, can you figure out why?! ...[(more)] Security news and thoughts [Duress Codes for Fingerprint Access Control] - Mike Specter has an interesting idea on how to make biometric access-control systems more secure: add a duress code. For example, you might configure your iPhone so that either thumb or forefinger unlocks the device, but your left middle finger disables ......[(more)] [How To Protect Your Privacy at Work] - Work has gradually crept into our personal time, and personal life has crept into our work, spurred on by technology. In many jobs, there is an expectation that people work long hours and respond to emails outside of the traditional office hours. The ......[(more)] [SEC on Yahoo's Case for Data Breaches] - Getting hit with hacks that hacks that affected more than a billion users may have only been the beginning of Yahoo?EU?s troubles. The company is now being investigated by the Securites and Exchange Commission (SEC) over how it responded to the attacks. According ......[(more)] [Ex-Goldman Sachs programmer found guilty, again, of source code theft] - Enlarge (credit: Mario Tama / Getty Images News) A New York state appellate court has restored the criminal conviction of an ex-Goldman Sachs programmer who was convicted of stealing trade secrets—high-frequency trading source code—before leaving his ......[(more)] R Language [SQL R Services optimization for concurrent execution of sp_execute_external_script] - With SQL Server 2016, we have introduced in-database analytics by bringing R closer to the database. This allows the compute to happen closer to the data, and also leverage the power of SQL Server including resource governance. For production scenario, ......[(more)] [Free guide to text mining with R] - Jilia Silge and David Robinson are both dab hands at using R to analyze text, from tracking the happiness (or otherwise) of Jane Austen characters, to identifying whether Trump's tweets came from him or a staffer. If you too would like to be able to ......[(more)] [Kung Fu R] - A great way to hone your skills as a data scientist is to pick a topic you're passionate about, find some data related to it, and analyze the heck out of it. Jim Vallandingham is clearly passionate about old Kung Fu movies — particularly those from the Shaw ......[(more)] Product Upgrades and Releases [First Responder Kit Updates: So Many Updates, Brent Quit] - I got 128 GB of RAM just to develop sp_BlitzCache with! Also, thanks to @RichBenner @BrentOzar and @douglane for code, and @LitKnd and @HenrikStaunPoulsen for great ideas! Grab the newly updated First Responder Kit, and here’s what’s in it: sp_Blitz ......[(more)] Product Reviews [How to build multiple database versions from the same source: post-deploy scripts] - This is the second post in a three-part series that explains how to use SQL Compare to maintain a single source of truth in version control for a SQL Server database which can be deployed to multiple production instances, despite the fact that some production ......[(more)] [Useful #SQLPrompt Tips] - One of the tools that Redgate write is SQL Prompt. This might be my favorite product, and I’m constantly impressed by the improvements that the dev team releases. In fact, I’m also impressed by the rate at which they produce changes, with updates happening ......[(more)] PowerShell [Error While running PowerShell code from SQL Agent job – “Path Does Not Exist”] - Issue: When I am running code from PowerShell console its running fine but when I execute it through SQL agent job step, I am getting below error: – The job script encountered the following errors. These errors did not stop the script: A job step received ......[(more)] [Using #PowerShell to Restore to a New Location] - Now that I’ve gotten some of my thought pieces out of my brain, I wanted to get back to some more technical posts, starting with some simpler techniques for people trying to figure out how to use SQL Server and PowerShell together. I know that a lot ......[(more)] PowerPivot/PowerQuery/PowerBI [Power BI On-Premises; Hello World!] - It has been a very long time request from users around the world to make Power BI available for on-premises, not for using on-premises data sources (this is available from long time ago with gateways), but for publishing reports into on-premises server. ...[(more)] Performance Tuning SQL Server [Performance Surprises and Assumptions : GROUP BY vs. DISTINCT] - Last week, I presented my T-SQL : Bad Habits and Best Practices session during the GroupBy conference. A video replay and other materials are available here: T-SQL : Bad Habits and Best Practices One of the items I always mention in that session is ......[(more)] [A new (to me, and possibly you) SSMS feature - Query Plan Comparing] - Wow, Microsoft has really changed in culture recently. This new rapid release cycle for SSMS is seemingly paying dividends in a major way. In a recent build of SSMS (okay, perhaps not "recent", more like this October of 2015, according to this blog by ......[(more)] [Incremental Statistics – My New Favorite Thing] - So many new features get added with each release of SQL it is hard to use them all. Sometimes, I often don’t realize how helpful a new feature is until I see it in action. Incremental Stats was introduced in SQL 2014, but I recently had the opportunity ......[(more)] [GroupBy Conference – 3 Common Mistakes to Kill SQL Server Performance] - Earlier this year, I was fortunate to take part in the first edition of the Group By Conference organized by Brent Ozar. I was extremely fortunate that I was able to present an hour long presentation on the subject 3 Common Mistakes to Kill SQL Server ......[(more)] [Data About Execution Plans] - If you look at the Properties for the first operator of a graphical execution plan, you get all sorts of great information. I’ve talked about the data available there and how important it is in this older post. Checking out the properties of a plan you’re ......[(more)] [Analyzing Temporary Stored Procedures with sp_BlitzCache] - All the cool kids Know that when you’re trying to figure out why a stored procedure is slow sometimes, you should use a temporary stored procedure instead of pulling the code out and running it ad hoc. We frequently point people to Erland Sommarskog’s ......[(more)] [How to dynamically determine the correct Process\Working Set performance counter instance] - In an environment with multiple instances of SQL Server running on the same machine, you may run into an issue while trying to collect the Process performance counter. The Process performance counter object uses the following #1, #2 naming scheme whenever ......[(more)] [Data About Execution Plans] - If you look at the Properties for the first operator of a graphical execution plan, you get all sorts of great information. I’ve talked about the data available there and how important it is in this older post. Checking out the properties of a plan you’re ......[(more)] [What’s that Garbage in my Execution Plan? (Dear SQL DBA Episode 26)] - Today I was working on some code samples for a user question, and I hit a weird roadblock. There was a bunch of garbage in my execution plan that I couldn’t explain. And by ‘garbage’, I mean a nested loop to a whole branch of code that I hadn’t asked ......[(more)] NoSQL [SQL versus NoSQL Databases: A Review of Key Themes] - Non-relational databases (aka NoSQL), in many different variations, have become a popular database model for handling Big Data. They use two novel concepts, making them different from earlier, “classical” database models. The concepts having so much ......[(more)] Microsoft News [Microsoft Announces Q2 FY 2017 Results] - This afternoon, Microsoft had its earnings announcement for the second quarter of their 2017 fiscal year. The results for this quarter are the first to contain results from their acquisition of Linkedin, which Microsoft purchased for $26.2 billion. For ......[(more)] [Reminder: Microsoft to no longer update original Windows 10 release after March 26] - Microsoft has released the media for its Windows 10 Anniversary Update to business users. That means it's almost time for support to end for its original release. ...[(more)] [Microsoft to cut 700 employees in next layoff round: Report] - Microsoft looks ready to cut 700 employees as part of the group of 2,850 that the company said it would be laying off in fiscal 2017. ...[(more)] [New Zealand bank replaces SAS server with R Server] - Heartland Bank, a rapidly growing bank in New Zealand, has adopted a data-driven approach to analyzing risk, evaluating credit lines, and understanding cash flows. But they found their legacy SAS system to be labor-intensive and time consuming when it ......[(more)] MDX/DAX [Year-over-year comparison using the same number of days in #dax] - When you use the time intelligence functions in DAX, it is relatively easy to filter the same dates selection in the previous year by using the SAMEPERIODLASTYEAR or DATEADD functions. However, if you follow the best practices, it is likely that you ......[(more)] [The List.* M Functions And The equationCriteria Argument] - Last week a reader left a comment on my blog asking what the third argument of the List.Contains() M function (somewhat cryptically called equationCriteria) does. I didn’t know, so I did some research and found out that lots of the List.* functions have ......[(more)] Internet of Things [The Emphasis is on Out-of-the-Box Internet of Things Data Management] - Enterprises face growing Data Management challenges as the industrial Internet of Things (IoT) becomes an increasing part of their daily activities. The difficulties for companies in sectors like manufacturing and energy start with a slew of Internet ......[(more)] Hardware News [Hardware Selection for a Home Lab – Part 1] - I have long believed that if you want to be more successful as a developer, database professional, or in any technically-oriented I.T. career, it is a very good idea to have some sort of home lab environment to use for experimentation and learning. Depending ......[(more)] [Two Recent Laptops Compared] - I have two fairly recent-vintage 13” personal laptops that I use primarily for teaching and presentations that I thought would be interesting to compare from some common performance perspectives. The first one, which is slightly over a year old is a ......[(more)] [Seagate wants to push huge 16TB HDD out the door in next 18 months] - (credit: Seagate) Good news if you like big hard drives: Seagate announced on an earnings call yesterday (as reported by PC World) that it has both 14TB and 16TB versions of its helium-filled spinning hard drives in the pipeline for the next 18 months. ...[(more)] [SanDisk Launches A1-Class microSDXC Card] - At CES 2017, we had the chance to catch up with Western Digital to check out the updates from their end in the direct-attached storage space. After the acquisition of SanDisk, a consolidation of sorts led us to interacting with G-Technology (from the ......[(more)] [HP Recalls Over 100,000 Laptop Batteries Due to Potential Fire and Burn Hazards] - HP has expanded its voluntary recall of batteries due to fire and burn hazards. The batteries were used for various laptops sold under the HP and Compaq brands between March 2013 and October 2016. In total, the company has recalled over 140 thousand ......[(more)] HA/DR/Always On/Clustering [SQL Server Corruption – Read Before] - SQL Server Corruption is a rather complex topic. As a consultant, I’ve been called in many times to help a client suffering from SQL Server corruption in some form or another. As a forum post answerer I’ve helped some pretty sad situations and tried ......[(more)] [When Always On Isn’t: Handling Outages in Your Application] - Today’s brief Stack Overflow outage reminded me of something I’ve always wanted to blog about: Stack Overflow is in limp-home mode There’s a gray bar across the top that says, “This site is currently in read-only mode; we’ll return with full functionality ......[(more)] Events to attend [IoT in Action with Microsoft Virtual Bootcamp 28th-30th March 2017] - Microsoft, Hackster and Raspberry Pi have partnered to deliver the first ever Virtual IoT Bootcamp! Enjoy subsidized hardware, guest speakers like Eben Upton, founder of Raspberry Pi, and invaluable classes. You already know how IoT will impact billions ......[(more)] [Upcoming R Conferences] - Since a few new events have been announced recently, I thought I'd give a run-down on some major R conferences coming up in the next six months. February 18: satRdays, Cape Town (South Africa). This is the second in a series of one-day conferences inspired ......[(more)] ETL/SSIS/ELT [An Example of Data Integration Lifecycle Management with SSIS, Part 3] - In this post I will demonstrate how to use SSIS Catalog Environments, References, and Reference Mappings to override SSIS parameter values at execution time. There three sources of SSIS parameter values: Design-time defaults – these are the parameter ......[(more)] [Run PowerShell scripts in SSIS] - PowerShell is a powerful task automation tool from Microsoft. With Azure PowerShell cmdlets, you can write PowerShell scripts to automate various Azure-related tasks. It would be quite nice if we can run PowerShell scripts within SSIS. Although SSIS ......[(more)] Data Warehousing [Don’t Call it a Data Lake, its a Data River. Here’s Why.] - Click to learn more about video blogger Stefan Groschupf. Introducing the Big Data & Brews video blog series presented by Stefan Groschupf, Founder of Datameer. The series will touch on hot topics within the business of Big Data, Machine Learning, Analytics, ......[(more)] Data Privacy [Microsoft Wins Another Victory in Federal Email Case] - On Tuesday, a federal appeals court refused to reconsider a previous decision in which the U.S. Department of Justice (DOJ) was prevented from forcing Microsoft to hand over customer data stored in an overseas data center. The victory is a huge one for ......[(more)] Data Mining/Data Analysis [The state of Jupyter] - How Project Jupyter got here and where we are headed.In this post, we’ll look at Project Jupyter and answer three questions: Why does the project exist? That is, what are our motivations, goals, and vision? How did we get here? Where are things headed ......[(more)] [SCUBA: Diving into Data at Facebook] - How they query data on a billion users....[(more)] [Data Science Platforms Help Insight Leaders Outperform Competition] - by Angela Guess According to a recent press release, “DataScience, Inc. today announced the results of a commissioned study, ‘Data Science Platforms Help Companies Turn Data Into Business Value,’ conducted by Forrester Consulting on behalf of DataScience. ...[(more)] Case Studies [Learn Redis the hard way (in production)] - For our products, like the trivago hotel search, we are using Redis a lot. The use cases vary: Caching, temporary storage of data before moving those into another storage or a typical database for hotel meta data including persistence....[(more)] Career Growth [What Is Your Career Investment Portfolio?] - Every year, financial advisors will recommend their clients to take a review of their investment portfolio and make necessary changes according to one’s financial reality, like whether you are going to be married, to have a baby soon, to have a new house ......[(more)] [5 Characteristics Every Leader Should Practice] - The word leadership has many synonyms attributed to it. Something that can be learned early on in one’s career is that having a leader you can trust is very important. Trust is something that is earned; not given. As I reflect over the years I’ve noticed ......[(more)] [Career Corner: Go Beyond Networking] - Networking with others is good for your career. You should do it. Don’t stop there. Keep going. Networking builds contacts and associates you can reach out to with respect to your technical discipline. You need these to grow effectively in your career. However, ......[(more)] [The Two Best Things Paul Randal Taught Me #DevIntersection #SQLintersection] - Several years back, I had the pleasure of working with Paul Randal (blog – @PaulRandal) – first in the Microsoft Certified Master program, and then later at SQLskills with Paul & Kim. I had a phenomenal time – they’re truly wonderful people – and I want ......[(more)] Backup and Recovery [Code to show rolled back transactions after a crash] - In Monday’s Insider newsletter I discussed an email question I’d been sent about how to identify the transactions that had rolled back because of a crash, and I said I’d blog some code to do it. First of all you need to know the time of the crash. We can’t get ......[(more)] [Automate SQL server backup file removal/deletion from Azure blob storage] - In this post, we would like to explain one of the interesting issues that we encountered while working with azure backups and restore. Symptoms Cannot delete the .bak files from azure blob storage through maintenance plans or any other options from SQL ......[(more)] [Using Backup to URL in SQL Server 2012 instance] - In this post, we would like to explain on how we can overcome the limitation of using the feature “backup to URL” in Maintenance Plan wizard in SQL 2012  Symptoms We cannot take backups from maintenance plans using backup to URL to Azure Blob Storage ......[(more)] [Backup/Restore–What’s My Status?] - The day begins and you find yourself going through a list in your mind of things that need to be accomplished. You either make a mental note, jot it down on paper, or input notes into your mobile device. The day is getting off to a great start; you feel ......[(more)] [Restoring An Encrypted Backup Of [master]] - Restoring a backup of [master] from one instance to another is challenging. The source and target instances have to be the same version. And after [master] is restored, the instance won't start if it can't find the files for [model] or the path for [tempdb] ......[(more)] Azure SQL Database [Connect to your Azure SQL DB behind MsAccess web application] - When you create new Custom web application from Ms Access on your Office 365 SharePoint your application will be hosted on Microsoft Cloud. Sometimes you can have more value if you can gain direct access you your Azure SQL DB database. This ......[(more)] [Interactive query experience for SQL databases] - This is a new feature where you can issue TSQL commands via a TSQL editor straight from the Azure portal, why this is great is because sometimes you do not want to keep switching between your SSMS (SQL Server Management … Continue reading ? ...[(more)] Azure SQL Data Warehouse and Data Lake [Sampling Data Lake Data] - Alex Whittles shows how to use U-SQL to sample data to read in Power BI: The answer is sampling, we don’t bring in 100% of the data, but maybe 10%, or 1%, or even 0.01%, it depends how much you need to reduce your dataset. It is however critical to know ......[(more)] [Scheduling Statistics Maintenance in Azure SQL Data Warehouse] - The power and capabilities of Azure SQL Data Warehouse are pretty amazing. However, it’s still basically SQL Server under the covers. SQL Server still needs maintenance and one type of maintenance is keeping statistics up to date. Statistics maintenance ......[(more)] [Microsoft Azure Data Lake Tools for Visual Studio Code] - Welcome to the Microsoft Azure Data Lake Tools preview for Visual Studio Code, an extension for developing U-SQL projects against Microsoft Azure Data Lake! This extension provides you a cross-platform, light-weight, keyboard-focused authoring experience ......[(more)] Azure Analysis Services [Process Azure Analysis Services databases from Azure Automation] - In my last blog post I showed how to trigger Azure Data Factory (ADF) pipelines from Azure Automation. I also mentioned the option to process an Azure Analysis Services cube from Azure Automation. For example right after your ADF data processing finishes, ......[(more)] [Azure Analysis Services cross tenant access] - Problem: You want to allow user from Azure tenant A to login to an Azure Analysis Services in tenant B. This is called cross-tenant access. Solution: There is a workaround for granting access to users from a different tenant. Say the Azure Analysis ......[(more)] [Azure Analysis Services processing/refresh sample AMO code] - Visit this web page Data providers for connecting to Azure Analysis Services, URL is At the bottom of ......[(more)] [Azure Analysis Services – How to switch to a different user account in report tools] - All the Azure Analysis Services tools, SSMS, SSDT, and Power BI Desktop share the IE cookies cache that stores the Azure Analysis Services login credential. If you need to login using a different credential, and the tool does not provide an interface ......[(more)] Analysis Services / BI on the MS Stack [SSAS processing performance gain by using OLE DB provider for Oracle instead of using .NET provider for Oracle] - Problem: You are unable to change the size set parameter in the data source of an SSAS model that gets data from an Oracle database Cause: Oracle .NET provider may not support the property you want to set Solution: Switch to user the Oracle OLE DB ......[(more)] [SSAS Activity Monitor] - SSAS Activity Monitor is an open source tool to monitor the current activity (sessions, transactions, connections, and locks) on an Analysis Services instance. It works for both Tabular and Multidimensional mode. The source code is available in the SSAS ......[(more)] [SSAS Events Analyzer] - SSAS Events Analyzer is a simple monitoring system that can be easily applied to any SSAS server with a minimal effort. The goals are the following: Monitor a production server with a minimal impact, collecting information about query and process Analyze ......[(more)] AI/Machine Learning/Cognitive Services [What’s a Database Encryption Key (DEK) in TDE] - The encryption mechanisms in SQL Server are interesting, and they work well, but they are somewhat poorly named. I ran across a few people struggling to understand, so I decided to cover the concepts in a series of posts. This one looks at the Database ......[(more)] [Infographic: The Growth of Artificial Intelligence in E-commerce] - by Angela Guess RedStag Fulfillment recently put together an infographic regarding the growth of artificial intelligence in e-commerce. According to Jake Rheude of RedStag, “Whether Artificial Intelligence (AI) is something you’ve just come across or ......[(more)] [Maropost Introduces Industry’s Most Sophisticated Machine Learning Capabilities] - by Angela Guess According to a new press release, “Maropost, a Canadian software company that provides email and marketing solutions, announced today the successful culmination of their machine learning research. The proprietary machine learning technology ......[(more)] [How Machine Learning and AI are Impacting the Data Industry] - Click to learn more about video blogger Andrew Brust. The Big Data & Brews video blog series continues with host Andrew Brust, Senior Director of Market Strategy and Intelligence. The series touches on hot topics within the business of Big Data, Analytics, ......[(more)] Administration of SQL Server [Migrating a Disk-Based Table to a Memory-Optimized Table in SQL Server] - In-Memory OLTP, also known as Hekaton, can significantly improve the performance of OLTP (Online transaction processing) database applications. It improves throughput and reduces latency for transaction processing, and can help improve performance where ......[(more)] [SQL SERVER – Using dm_db_stats_properties With InMemory OLTP Tables] - The whole concept of InMemory OLTP has been around for a while and still there are areas that I try to go back to learn every single time. If you are new to InMemory OLTP, I would highly recommend searching this blog for more content. For a starter, ......[(more)] [SQL 2016 – Temporal Tables – How do you drop a Temporal Table?] - As we continue our journey with Temporal Tables, the next topic will be how do you drop them. While this may seem like an odd thing to write about, dropping a Temporal Table does have a couple extra steps.  Let’s begin by creating a temporal table:  CREATE ......[(more)] [CREATE DATABASE – I’ve not seen that before.] - G’day, It’s been a while I thought that I’d make a quick blog post about an incident that I encountered today that I had not come across before. I was working on my laptop when I tried doing a really basic task – creating a database. So I keyed in CREATE ......[(more)] [Using Extended Events Telemetry Session] - You are probably wondering what this is, quite honestly I was wondering too. Well it is an extended events session known as telemetry_xevents that is shipped with SQL Server 2016. This is not the same as the system_health session that … Continue reading ......[(more)] [Change Tracking Cleanup – Part 2] - In the first part of my Change Tracking Cleanup post, I talked about how automatic and manual cleanup happens in SQL Server. In this post, we will explore a bit more in depth on how the cleanup actually works with the help of some metadata from an actual ......[(more)] [SQL Server Mysteries: The Case of TDE and Permanent Tempdb Encryption] - I’m a huge Sherlock Holmes fan (I’ve read all the books, watch Elementary on CBS every week, and loved the most recent season Four of Sherlock) so when I recently got a question about some unexplained behavior for SQL Server, I thought of the idea of ......[(more)] --------------------------------------------------------------- Administrative To be removed from this list, please click [here] Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can [change your profile] or follow the instructions on the daily newsletter. --------------------------------------------------------------- This transmission is ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: [webmaster@sqlservercentral.com]