Internet access to your SQL Server? (Database Weekly 4/2/2018)

The Complete Weekly Roundup of SQL Server News In this issue: Virtualization and Containers - [Applied Use of Docker Containers and Database Clones](#31501) T-SQL - [New For SQL Server 2017: T-SQL Function CONCAT_WS](#31598) - [Dealing with date and time instead of datetime](#31595) - [SDU Tools: Julian Day Number to Date in T-SQL (and reverse)](#31571) - [Dates and Times in SQL Server: DATETIMEOFFSET](#31567) - [Opinion: Don’t write CREATE UNIQUE INDEX (ok, well not too often)](#31564) - [AT TIME ZONE: The easy way to deal with time zones and daylight savings time](#31554) - [Pivoting Data in SQL Server](#31543) - [T-SQL Options for Comparing “Distinctness”](#31533) - [New For SQL Server 2017: T-SQL Function TRIM](#31503) The Lighter Side - [SpaceX has a busy weekend ahead of it](#31591) - [This spacecraft will get closer to the Sun than any before it—without melting](#31590) SQL Server Security - [Internet access to your SQL Server?](#31542) - [Securing access to data for admin and dbo](#31537) SQL Server News - [The March release of SQL Operations Studio is now available](#31593) Software Development - [X++ the most extensible language on the planet!](#31589) - [Shortcut: Filters in Object Explorer within SQL Server Management Studio](#31586) - [Retrieve JSON Data from SQL Server using a Stored Procedure and C#](#31545) Security news and thoughts - [Atlanta in Recovery Mode After Ransomware Attack](#31577) - [Adding Backdoors at the Chip Level](#31534) - [Thousands of servers found leaking 750MB worth of passwords and keys](#31520) - [Expedia's Orbitz Hack May Have Compromised 880,000 Credit Cards](#31502) Reporting Services - [Upgrade of SSRS from SQL 2008 R2 to SQL 2012](#31574) Replication - [Avoiding Database Downtime via Replication – SharePlex](#31594) R Language - [Vectors and Functions in R](#31573) Python in SQL Server - [Processing Huge Dataset with Python](#31588) - [Multi-Class Text Classification with PySpark](#31546) PowerShell - [Wait! There are JOINS in PowerShell???](#31541) - [Scripting Tables with dbatools](#31508) PowerPivot/PowerQuery/PowerBI - [Power BI Export to Power Point: Things You Need to Know](#31565) - [Display Last N Months & Selected Month using Single Date Dimension in Power BI](#31556) - [Power BI Custom Visuals Class (Module 96 – Enlighten Waffle Chart)](#31551) - [Power BI Licensing Walk-through Guide](#31549) - [Power BI Licensing – Cost Forecast Guide](#31548) - [Highlighting Scatter Charts in Power BI using DAX](#31547) - [Are local credentials or passwords stored in the Power BI Desktop file?](#31544) - [Capturing Power BI queries using DAX Studio](#31511) - [Power BI Custom Visuals Class (Module 95 – VitaraCharts–MicroChart)](#31507) - [Use the Power BI Rebind API to move from cached to Azure AS](#31499) Performance Tuning SQL Server - [Hash Join Memory Grant Factors](#31592) - [Auto-Tuning: Automatic Plan Correction is Cleared on Restart](#31566) - [When a forced plan isn’t forced](#31562) - [Missing Index Impact and Join Type](#31559) - [Row Goals, Part 4: The Anti Join Anti Pattern](#31538) - [SQL SERVER – How to Fix High CPU Consumption on SQL Server 2017 and 2016](#31532) - [Which is Better for Performance – SELECT or SET? – Interview Question of the Week #166](#31531) - [Table Valued Parameters: Unexpected Parameter Sniffing](#31522) Microsoft News - [Microsoft Unveils Biggest Reorganization in Years as Myerson Out](#31580) Hardware News - [HP Introduces World’s Most Powerful Workstation for Machine Learning Development](#31579) - [Intel Previews Optane Enterprise M.2 SSD](#31518) - [Samsung Demos 64 GB RDIMM Based on 16 Gb Chips, Promises 256 GB LRDIMMs](#31517) - [ADATA Launches XPG SX8200 SSDs Featuring 3D TLC NAND, SM2262 Controller](#31516) - [Hands On With Samsung's New NF1 SSDs: 36 x 16 TB in 1U](#31515) HA/DR/Always On/Clustering - [‘Object Reference Not Set to an Instance of an Object’ When Failing Over an Availability Group Using SSMS](#31581) - [TempDB Filling Up On Secondary Replicas](#31540) - [SQL SERVER – AlwaysOn Automatic Seeding – Database Stuck in Restoring State](#31530) - [Database Master Keys and Availability Groups](#31529) - [Simplifying Availability Group Troubleshooting](#31528) Graph Databases - [SQL Server Graph Databases – Part 2: Querying Data in a Graph Database](#31536) ETL/SSIS/ELT - [Testing SSIS Projects with ssisUnit](#31597) - [Writing first tests with ssisUnit](#31596) - [Eliminating Annoying Space Characters Via Check Constraints](#31561) DevOps and Continuous Delivery (CI/CD) - [SQL Server DevOps Tools](#31568) Data Science - [Exploring Data is an Iterative Process](#31563) - [Data Science and the Art of Producing Entertainment at Netflix](#31558) - [You Are Not Data-Driven](#31509) Data Privacy and GDPR - [Cambridge Analytica’s London offices raided by British investigators](#31519) Conferences, Classes, and Events - [Redgate’s SQL Privacy Summit – London, 18 May](#31576) - [No Fall U.S. classes in 2018](#31527) Computing in the Cloud (Azure, Google , AWS) - [Moving to AWS EC2](#31585) - [Beginner’s Guide to Azure Automation](#31514) Career Growth - [Interview With Matt Gordon](#31583) - [Parsing the PASS Blog Posts about FY 19 and SQLSaturday](#31553) - [SQLSaturday Tools](#31550) Big Data - [Azure vs. AWS Analytics and Big Data Services Comparison](#31555) Backup and Recovery - [Sharing TDE Encrypted Backup outside the organisation.](#31557) - [Recover Data in SQL Server AWS RDS with the Help of BACPAC](#31535) Azure SQL Managed Instance - [Azure SQL Managed Instances Brain Dump on CPU, Memory, HA/DR, and Backups](#31523) Azure SQL Database - [Lesson Learned #33: How to make “cliconfg” to work with SQL Alias (on-premises) to Azure SQL Azure (PaaS)](#31513) - [Backup and Restore Options for Azure SQL DB](#31506) Azure SQL Data Warehouse and Data Lake - [Azure SQL Data Warehouse Costs vs AWS Redshift](#31587) - [Database Has Been Paused](#31552) Azure CosmosDB - [Azure Cosmos DB Costs vs Dynamo DB and Neptune](#31510) Analysis Services / BI on the MS Stack - [BI Survey 18](#31572) AI/Machine Learning/Cognitive Services - [Introduction to the Microsoft AI Platform](#31560) - [The Lifesaving Power of Data](#31500) Administration of SQL Server - [SQL Script: - How to find the last access date of a database](#31584) - [Toolbox - Just the Basics](#31582) - [a new command to find all of your sql instances](#31578) - [Audit SQL Server stop, start, restart](#31575) - [What’s the difference between Files and Filegroups?](#31570) - [Rotating TDE Certificates without re-encrypting data](#31569) - [DMV for Log Statistics in SQL Server](#31539) - [SQLskills SQL101: Is the recovery interval guaranteed?](#31526) - [SQL Server 2017 Cumulative Update 5](#31525) - [SSMS 17.6 is now available: Managed Instance and many bug fixes](#31524) - [So You Wanna Debug SQL Server Part 1](#31521) - [Installation of SQL Server 2017 failing with ‘VS Shell installation has failed with exit code 1638’](#31512) - [Query Store and Automated Cleanup](#31505) - [Partitioned Tables: Rolling/Rotating/Round-Robining Partitions](#31504) [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 2018-04-02 [SQL Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more]( [ReadyRoll]( Database migrations inside Visual Studio Feeling the pain of managing and deploying database changes manually? Redgate ReadyRoll creates SQL migration scripts you can use to version control, build and release, and automate deployments. [Try it free]( [Database DevOps]( Continuous Delivery for SQL Server Databases Spend less time managing deployment pain and more time adding value. [Find out how with database DevOps]( Editorial - A New New Microsoft Microsoft is changing again. [Microsoft is reorganizing]( and the head of Windows, Terry Myerson, is leaving. The reorganization isn't that surprising, as like most large companies, Microsoft usually does something each year. What is interesting is that they are moving away from their major divisions focusing on products. We've had divisions for Windows, Servers, Devices, etc. Now we move to Experiences and Devices as one major group. The other will be Cloud + AI Platform, which seems more product oriented, but still is rather amorphous. Gaming is still separate and there's still a large Research engineering group. Why are they doing this? There's some thoughts [at ZDNet]( and [Geekwire](. It seems that the cloud is becoming the most profitable part of Microsoft, and I'd expect that we will continue to see more push for cloud and subscription type software. Along with the advances in AI, Microsoft seems to be hoping that more of us will start to do work using their infrastructure. That makes sense for some, but not for others. For those of us using the data platform, I do think that we ought to be thinking cloud first unless we already have substantial infrastructure and automation capabilities to quickly stand up new instances. As development and other departments look to get work done, especially around data analysis, we need to meet their needs or many people will start to use the easy to provision and use cloud services, especially those for machine learning and AI. Companies are entranced by new technology, even if we aren't, and the media hype around ML and AI will put pressure on us to do better. We don't need to learn R or Python, but we should be able to run those scripts against data if our business demands the capability. The GDPR and other security initiatives might seem to slow cloud deployment, but in many cases the cloud isn't more or less secure than our own systems. Certainly we may have issues with privacy with the new US CLOUD Act, but for most of our businesses, that won't really matter. The conflicts there are more around illegal activities, and if you are in that space, US based companies might not be your choice for hosting data. For many of us, we do need to understand how to build and enforce more secure applications, and I'd argue that Azure makes this as easy any any other system. Our recent move at SQLServerCentral to AWS was essentially the same as if we'd have moved to a VM at Azure, Google, or even back to the Redgate office. The systems look the same, it's the cost, the firewall and networking, and things beyond the data platform that are just different. And that's the key, they're not necessarily better or worse, but different. As Microsoft changes, I think their code quality improves, though certainly their rapid pace means that people getting updates too quickly are on the bleeding edge. Their DevOps deployment, however, means that the bleeding edge usually doesn't last tool long if the issues are severe. 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. Virtualization and Containers [] [Applied Use of Docker Containers and Database Clones]( - Click to learn more about author Paul Stanton. This is Part 2 of the “Docker Containers and Database Cloning for DBAs, Data Governance, and IT Decision Makers” Series. Part 1 of the series is at: Docker Containers and Database Cloning for DBAs, Data ......[(more)]( T-SQL [] [New For SQL Server 2017: T-SQL Function CONCAT_WS]( - In the last post, I looked at a new T-SQL function for SQL Server 2017. Let's continue down that path and look at CONCAT_WS(), which is also new for SQL Server 2017. Here's the definition of the function from Microsoft Docs: "Concatenates a variable ......[(more)]( [] [Dealing with date and time instead of datetime]( - Data professionals don’t always get to use databases that have an optimal design. Sometimes the things that make you cry are things that we’ve done to ourselves, because they seemed like good ideas at the time. Sometimes they’re because of third party ......[(more)]( [] [SDU Tools: Julian Day Number to Date in T-SQL (and reverse)]( - Working with dates and times in database systems and programming languages has always been "interesting", at least interesting in the sense that the old Chinese curse meant when it said "may you live in interesting times". One of the curious variations ......[(more)]( [] [Dates and Times in SQL Server: DATETIMEOFFSET]( - This post continues our look at date and time data types in SQL Server. SQL Server 2008 introduced new data types to handle dates and times in a more intelligent way than the DATETIME and SMALLDATETIME types that we looked at previously. This week, we ......[(more)]( [] [Opinion: Don’t write CREATE UNIQUE INDEX (ok, well not too often)]( - The CREATE INDEX statement is used to do exactly what its name says, it creates an index. But when you say CREATE UNIQUE INDEX, you are doing more than that; you are enforcing a business rule that involves uniqueness. I have a simple rule on this. Wherever ......[(more)]( [] [AT TIME ZONE: The easy way to deal with time zones and daylight savings time]( - Many people think daylight savings time was created to help align the hours that the sun is up with our waking hours so farmers and school children didn’t have to be outside in the dark. Or something like that. Well let me fill you in on a little secret ......[(more)]( [] [Pivoting Data in SQL Server]( - Hi Blog, long time no see! How’s it going?! I know, I really should up my blog game…. I was recently approached by my firms Marketing Manager with a request for some information. She wanted to know “Which departments have our top clients never done ......[(more)]( [] [T-SQL Options for Comparing “Distinctness”]( - I had the privilege of listening to Itzik Ben Gan talk about “distinctness” in a talk he gave at PASS Summit. Distinctness is a relationship or comparison between two variables, just like equals (=). But unlike equality, distinctness treats NULLs in ......[(more)]( [] [New For SQL Server 2017: T-SQL Function TRIM]( - There's a new T-SQL function for SQL Server 2017 named TRIM(). If you're familiar with LTRIM() and/or RTRIM(), you can probably guess what it does. Here's the definition of the TRIM() function from Microsoft Docs: "Removes the space character char(32) ......[(more)]( The Lighter Side [] [SpaceX has a busy weekend ahead of it]( - The booster to be used as early as Monday for CRS-14 flew last August during the CRS-12 mission. (credit: SpaceX) SpaceX has launched five rockets so far during the first quarter of 2018, but now the company will amp up that pace by tryng to go for ......[(more)]( [] [This spacecraft will get closer to the Sun than any before it—without melting]( - Enlarge / Artist’s concept of the Parker Solar Probe spacecraft approaching the sun. Launching in 2018, Parker Solar Probe will provide new data on solar activity and make critical contributions to our ability to forecast major space-weather events that ......[(more)]( SQL Server Security [] [Internet access to your SQL Server?]( - Occasionally I see questions on dba.stackexchange.com where it appears the SQL Server is exposed directly to the Internet, allowing remote connections. This is very bad. In this post, I provide some reasons why. First, it... The post Internet access ......[(more)]( [] [Securing access to data for admin and dbo]( - In this blog, I want to explore what you can do to block the owner of a database from doing stuff in the database they “own”. Own is a strange term, because really there is just one user that is listed as owner, but there are there are three users who ......[(more)]( SQL Server News [] [The March release of SQL Operations Studio is now available]( - This post is authored by Alan Yu, Program Manager, SQL Server and Karl Burtram, Senior Software Engineer, SQL Server. We are excited to announce the March release of SQL Operations Studio is now available. Download SQL Operations Studio and review the ......[(more)]( Software Development [] [X++ the most extensible language on the planet!]( - Disclaimer: I have no idea if the title is true or not. There are no world cups for languages competing against each other for the trophy. What I do know is that X++ has taken some gigantic leaps forward lately in terms of extensibility – if someone ......[(more)]( [] [Shortcut: Filters in Object Explorer within SQL Server Management Studio]( - If you are working with databases with large numbers of objects, the contents of Object Explorer in SQL Server Management Studio can start to become a bit overwhelming. I have to admit that I don’t understand why it doesn’t offer an option to group by ......[(more)]( [] [Retrieve JSON Data from SQL Server using a Stored Procedure and C#]( - The previous post demonstrated how to load JSON into SQL Server using a stored procedure and C#. This post will examine the opposite – how to retrieve JSON data from SQL Server using a stored procedure and C#. Just like with the JSON loading technique, ......[(more)]( Security news and thoughts [] [Atlanta in Recovery Mode After Ransomware Attack]( - The city of Atlanta on Tuesday authorized its employees to use their government computers for the first time since ordering them offline last week in response to a debilitating ransomware virus that continues to cause complications across Georgia's capital. "Today, ......[(more)]( [] [Adding Backdoors at the Chip Level]( - Interesting research into undetectably adding backdoors into computer chips during manufacture: "Stealthy dopant-level hardware Trojans: extended version," also available here: Abstract: In recent years, hardware Trojans have drawn the attention of governments ......[(more)]( [] [Thousands of servers found leaking 750MB worth of passwords and keys]( - Enlarge (credit: Mike Pratt) Thousands of servers operated by businesses and other organizations are openly sharing credentials that may allow anyone on the Internet to log in and read or modify potentially sensitive data stored online. In a blog post ......[(more)]( [] [Expedia's Orbitz Hack May Have Compromised 880,000 Credit Cards]( - Expedia Inc.-owned Orbitz said hackers may have accessed 880,000 credit-card numbers used to book travel through the site and other companies serviced by Orbitz, including American Express Co. ...[(more)]( Reporting Services [] [Upgrade of SSRS from SQL 2008 R2 to SQL 2012]( - Yesterday, I encountered a weird scenario where in the SSRS component was failing to upgrade from SQL 2008 R2 to SQL 2012 where in the SQL database engine and all the other components succeeded. SSRS component upgrade was failing with the below error ......[(more)]( Replication [] [Avoiding Database Downtime via Replication – SharePlex]( - The scariest word for any DBA is – Database Downtime. Every DBA who is going off duty for the day or going away on vacation for a month is always worried about Database Downtime. I totally sympathize with everyone who is constantly worried about their ......[(more)]( R Language [] [Vectors and Functions in R]( - In my last post had answers to some of the common questions in R that a person who has just begun exploring the language, needs to know. As we advance and immerse further, this post will contain some essential components whose basic understanding is ......[(more)]( Python in SQL Server [] [Processing Huge Dataset with Python]( - This tutorial introduces the processing of a huge dataset in python. It allows you to work with a big quantity of data with your own laptop. With this method, you could use the aggregation functions on a dataset that you cannot import in a DataFrame. In ......[(more)]( [] [Multi-Class Text Classification with PySpark]( - Apache Spark is quickly gaining steam both in the headlines and real-world adoption, mainly because of its ability to process streaming data. With so much data being processed on a daily basis, it has become essential for us to be able to stream and ......[(more)]( PowerShell [] [Wait! There are JOINS in PowerShell???]( - And no I'm not talking about -join ...[(more)]( [] [Scripting Tables with dbatools]( - I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items. I’ve ......[(more)]( PowerPivot/PowerQuery/PowerBI [] [Power BI Export to Power Point: Things You Need to Know]( - Exporting a Power BI report to PowerPoint is a good way of integrating these two tools together. The analytical power of Power BI combined with the commentary and presentation features of PowerPoint enables you to present your reports differently. You ......[(more)]( [] [Display Last N Months & Selected Month using Single Date Dimension in Power BI]( - It is Friday, and I thought of writing a quick tip on how you can use the same date dimension for displaying the last N months (say, in a bar chart) and the data for selected month (say, in a card visual). Most of the techniques for doing the same use ......[(more)]( [] [Power BI Custom Visuals Class (Module 96 – Enlighten Waffle Chart)]( - In this module you will learn how to use the Enlighten Waffle Chart. The Enlighten Waffle Chart is a simple way to visualize a percentage of values on a 10 x 10 grid of dots. Module 96 – Enlighten Waffle Chart) Downloads Power BI Custom Visual – Enlighten ......[(more)]( [] [Power BI Licensing Walk-through Guide]( - Licensing in Power BI is not a complicated one, however, understanding that which features are included in which licensing plan is always a question from users. In this post, you will learn about all different licensing plans in Power BI, the scenarios ......[(more)]( [] [Power BI Licensing – Cost Forecast Guide]( - The other day, one of my enterprise customers asked me if there were any guides or documentation to forecast the cost for Power BI service licensing (Pro and Premium) for 3 years based on user growth. I did mention that we can use the official Power ......[(more)]( [] [Highlighting Scatter Charts in Power BI using DAX]( - Another Friday, another tip. Power BI has undergone a lot of innovative updates over the years, but my favorite one has been a combination of the cross-filtering & highlighting capabilities, multi-selecting across visuals and drilling down filtering ......[(more)]( [] [Are local credentials or passwords stored in the Power BI Desktop file?]( - When I presented on Power BI at Cleveland, I wrote up a blog post with all the questions I didn’t have an immediate answer to. I presented last week at Cincinatti and wanted to do the same thing. This time there were some more difficult questions so ......[(more)]( [] [Capturing Power BI queries using DAX Studio]( - This article describes how to use DAX Studio to capture DAX queries generated by Power BI. Analyzing DAX queries can be helpful to better understand performance issues, and to improve the writing of DAX queries. Preparing the Power BI report Every page ......[(more)]( [] [Power BI Custom Visuals Class (Module 95 – VitaraCharts–MicroChart)]( - In this module you will learn how to use the VitaraCharts – MicroChart. The VitaraCharts – MicroChart provides a intuitive way to look at multiple metrics in a compact grid layout. Module 95 – VitaraCharts – MicroChart Downloads Power BI Custom Visual ......[(more)]( [] [Use the Power BI Rebind API to move from cached to Azure AS]( - The Power BI Rebind API allows you to change the dataset of a report. Patrick shows how you can use the Power BI Rebind API to move from a cached dataset to a live connection on Azure Analysis Services. PowerShell examples The post Use the Power BI Rebind ......[(more)]( Performance Tuning SQL Server [] [Hash Join Memory Grant Factors]( - Buskets Much like Sorts, Hash Joins require some amount of memory to operate efficiently — without spilling, or spilling too much. And to a similar degree, the number of rows and columns passed to the Hashing operator matter where the memory grant is ......[(more)]( [] [Auto-Tuning: Automatic Plan Correction is Cleared on Restart]( - The most delicious plans are mauve, but teal is a close second The new Enterprise Automatic Tuning feature in SQL Server 2017 may sound intimidating at first — one question I get a lot lately is whether or not there’s a future for DBAs. Will Auto-Tune ......[(more)]( [] [When a forced plan isn’t forced]( - One of the uses for the Query Store, added in SQL 2016, is to force plans. Once forced, plans are supposed to remain unchanged, however there are cases where a forced plan will not be applied and a new plan will be generated. Statistics changes, which ......[(more)]( [] [Missing Index Impact and Join Type]( - Just Another Way No matter how you delve into missing index requests — whether it’s the plan level, DMV analysis, or (forgive me for saying it), DTA, the requests will generally be the same. They’ll prioritize equality predicates, the columns may or ......[(more)]( [] [Row Goals, Part 4: The Anti Join Anti Pattern]( - This post is part of a series about row goals. You can find the other parts here: Part 1: Setting and Identifying Row Goals Part 2: Semi Joins Part 3: Anti Joins Apply Anti Join with a Top operator You will often see an inner-side Top (1) operator in ......[(more)]( [] [SQL SERVER – How to Fix High CPU Consumption on SQL Server 2017 and 2016]( - In this blog post, we are going to discuss how to fix high CPU Consumption on SQL Server 2016 and SQL Server 2017. One of the large multinational corporations recently hired me for Comprehensive Database Performance Health Check. Usually, customer hires ......[(more)]( [] [Which is Better for Performance – SELECT or SET? – Interview Question of the Week #166]( - Question: Which is Better for Performance – SELECT or SET? Answer: Some questions never get old. This is one of such question. Remember that SELECT is designed to return data whereas SET is designed to assign values to local variables. SET @foo1 = 1; SET @foo2 = 2; SET @foo3 = 3; SELECT @foo1 = 1, @foo2 = 2, @foo3 = 3; While ......[(more)]( [] [Table Valued Parameters: Unexpected Parameter Sniffing]( - Like Table Variables, Kinda Jeremiah wrote about them a few years ago. I always get asked about them while poking fun at Table Variables, so I thought I’d provide some detail and a post to point people to. There are some interesting differences between ......[(more)]( Microsoft News [] [Microsoft Unveils Biggest Reorganization in Years as Myerson Out]( - Microsoft Corp. Chief Executive Officer Satya Nadella unveiled the company’s biggest reorganization in years, combining the divisions that focus on devices and software for businesses while moving the Windows operating system unit into the cloud operations. ...[(more)]( Hardware News [] [HP Introduces World’s Most Powerful Workstation for Machine Learning Development]( - by Angela Guess A recent press release reports, “HP today unveiled a set of industry-leading machine learning (ML) solutions, including the HP Z8, the world’s most powerful workstation for ML development. HP Z Workstations, with new NVIDIA technology, ......[(more)]( [] [Intel Previews Optane Enterprise M.2 SSD]( - At the Open Compute Project Summit this week, Intel previewed their upcoming enterprise Optane SSD in the M.2 form factor, currently planned to be named the Optane SSD DC P4801X. Intel's current Optane product family includes the flagship Optane SSD ......[(more)]( [] [Samsung Demos 64 GB RDIMM Based on 16 Gb Chips, Promises 256 GB LRDIMMs]( - Samsung is demonstrating its 64 GB DDR4 memory module based on 16 Gb chips this week at the OCP U.S. Summit. The 64 GB RDIMM that the company is showcasing is designed for mainstream servers, but ultimately the design will lend itself to build 128 GB ......[(more)]( [] [ADATA Launches XPG SX8200 SSDs Featuring 3D TLC NAND, SM2262 Controller]( - ADATA on Thursday formally launched its XPG SX8200-series SSDs, which it previously demonstrated at CES earlier this year. The new drives are based on one of the latest controllers from Silicon Motion and are the fastest consumer SSDs from ADATA to date. ...[(more)]( [] [Hands On With Samsung's New NF1 SSDs: 36 x 16 TB in 1U]( - Samsung this week demonstrated progress in its new NF1 form-factor (formerly known as NGSFF and sometimes called M.3), unveiled last August. At the OCP Summit this week, the company exhibited its NF1 SSDs in a dual-socket Supermicro server running 36 ......[(more)]( HA/DR/Always On/Clustering [] [‘Object Reference Not Set to an Instance of an Object’ When Failing Over an Availability Group Using SSMS]( - Here’s a quickie that I’ve just stumbled across. I’ve just tried to initiate a manual failover of an availability group and received the following error. After a spot of head scratching and thinking that there was something wrong with my AG setup, it ......[(more)]( [] [TempDB Filling Up On Secondary Replicas]( - Have you ever had an issue where TempDB was filling up on your secondary replicas? Do those secondaries happen to be read only replicas? When I have an issue with tempdb filling up the first thing that I usually do is try to figure out exactly what ......[(more)]( [] [SQL SERVER – AlwaysOn Automatic Seeding – Database Stuck in Restoring State]( - As a part of my consulting, I observed that there is a lot of interest in using AlwaysOn availability group. I have deployed many AlwaysOn solutions from start-to-finish. Recently I encountered an issue and in this blog, we would discuss reason of an ......[(more)]( [] [Database Master Keys and Availability Groups]( - Recently I received an email from a client experimenting with Availability Groups for the first time in a development environment that had run into an issue with adding one database out of fifteen to the Availability Group in the UI. The database in ......[(more)]( [] [Simplifying Availability Group Troubleshooting]( - The AlwaysOn_health event session in Extended Events is intended to make analyzing problems with Availability Groups possible after they have occurred. While this event session goes a long way towards making it possible to piece together the puzzle ......[(more)]( Graph Databases [] [SQL Server Graph Databases – Part 2: Querying Data in a Graph Database]( - The series so far: SQL Server Graph Databases – Part 1: Introduction SQL Server Graph Databases – Part 2: Querying Data in a Graph Database Microsoft incorporated the graph database in SQL Server 2017, providing a logical structure for storing and ......[(more)]( ETL/SSIS/ELT [] [Testing SSIS Projects with ssisUnit]( - During the upcoming SQLDay 2018 conference (10th edition of SQLDay!) I’ll be speaking about testing SSIS packages and projects. From my observations, I see that we don’t like testing (I’m talking about database and ETL people), but when we start doing ......[(more)]( [] [Writing first tests with ssisUnit]( - Previously I wrote about the importance of testing the SSIS packages and introduced you to ssisUnit. In this post, I will show you how to write simple tests for the variables and parameters using Test Suite Builder. As I wrote before: just start slow ......[(more)]( [] [Eliminating Annoying Space Characters Via Check Constraints]( - Along with designing databases, I do a lot of ETL from, well, let’s call them “third party systems that I did not design or implement.” That may seem arrogant, but hey, I write books about database design so I should at least seem like I am able to do ......[(more)]( DevOps and Continuous Delivery (CI/CD) [] [SQL Server DevOps Tools]( - This article is part of the SQL Server Tools series, aimed at giving you an idea of the available tools and techniques to build applications using SQL Server tools. The first article of the series covers the rudimentary DevOps concepts and discusses ......[(more)]( Data Science [] [Exploring Data is an Iterative Process]( - As much as I would like to barrel ahead and do super cool exciting analytics with the hockey data that I’ve been exploring in the last several posts, I’m simply not ready yet. The exploration process is not complete and I discovered (so far) one problem ......[(more)]( [] [Data Science and the Art of Producing Entertainment at Netflix]( - by Ritwik Kumar, Vinith Misra, Jen Walraven, Lavanya Sharan, Bahareh Azarnoush, Boris Chen, Nirmal GovindNetflix has released hundreds of Originals and plans to spend $8 billion over the next year on content. Creators of these stories pour their hearts ......[(more)]( [] [You Are Not Data-Driven]( - Many people (and companies) say they are “data-driven”. Unfortunately, what that means is “hey, I think this is true, go get the data that supports my idea. No, not that data, get the other data.” This is the common definition I see on a regular basis. ...[(more)]( Data Privacy and GDPR [] [Cambridge Analytica’s London offices raided by British investigators]( - Enlarge / The offices of Cambridge Analytica in central London. Facebook expressed outrage over the misuse of its data as Cambridge Analytica, the British firm at the center of a major scandal rocking the social media giant, suspended its chief executive. ...[(more)]( Conferences, Classes, and Events [] [Redgate’s SQL Privacy Summit – London, 18 May]( - Are you responsible for data privacy and protection of SQL Server databases in your organization? Then you should join us for Redgate’s brand new conference, the SQL Privacy Summit, in London on Friday 18 May at the 5 star Grange Tower Bridge Hotel. The ......[(more)]( [] [No Fall U.S. classes in 2018]( - We’ve been finalizing our schedule for the remainder of 2018 and wanted to let everyone know that we will not have any Immersion Events in the U.S. this Fall. While we typically have several weeks of in-person classes in Chicago in October or November, ......[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Moving to AWS EC2]( - SQLServerCentral has been stuck in the past for some time, but we’ve finally decided to move. I’ve been asking for this, and it’s usually a resource issue. However, earlier this year we started to debate and examine the costs, and decided to pursue a ......[(more)]( [] [Beginner’s Guide to Azure Automation]( - Azure Automation For Azure IAAS enthusiasts, Microsoft has provided a platform to automate all the azure services using powershell. The language is tweaked and used as “powershell workflow”. Why to Use Reducing Manual Effort and help in consistent testing Managing ......[(more)]( Career Growth [] [Interview With Matt Gordon]( - I’ve been wanting to share a little bit about Matt’s story for a while now and this past week I reached out to him to see if he’d be up for an interview for the blog. As is usually the case he was happy to oblige. I’ve known Matt for, O gosh, a couple ......[(more)]( [] [Parsing the PASS Blog Posts about FY 19 and SQLSaturday]( - In February PASS President Grant Fritchey posted PASS Priorities FY 19 followed by An Open Letter to SQLSaturday Organizers, and then held an online town hall for SQLSaturday organizers in March to address some of the questions generated by the two posts. ...[(more)]( [] [SQLSaturday Tools]( - Much has been said about SQL Saturday tools and how PASS is pulling back from development of those tools past a few active items, and letting SQLSaturday go “off to college as we play catch with our other kids” To get back to its “by the community, ......[(more)]( Big Data [] [Azure vs. AWS Analytics and Big Data Services Comparison]( - Last week I wrote a post that helped visualize the different data services offered by Microsoft Azure and Amazon AWS. This week I’m writing about the Azure vs. AWS Analytics and big data services comparison. This comparison took a bit longer because ......[(more)]( Backup and Recovery [] [Sharing TDE Encrypted Backup outside the organisation.]( - In order to share the TDE Encrypted Database backup with somebody outside the organisation, the below steps can be followed. ++ Create new temporary database in order to prepare a make-shift copy of the intended database. RESTORE DATABASE MyEncryptedDB_Temp ......[(more)]( [] [Recover Data in SQL Server AWS RDS with the Help of BACPAC]( - Amazon Web Services (AWS) provides highly available, highly managed SQL Server instances through the Relational Database Service (RDS). Using RDS instances greatly reduces much of the administrative need for managing backups, minor upgrades, and monitoring. ...[(more)]( Azure SQL Managed Instance [] [Azure SQL Managed Instances Brain Dump on CPU, Memory, HA/DR, and Backups]( - Normally when we write blog posts, we try to explain something or tell a story. If you’re looking for a solid educational post, stop here, mark this one as read, and go on about your day. This post is just a brain dump of unorganized notes from our experimenting ......[(more)]( Azure SQL Database [] [Lesson Learned #33: How to make “cliconfg” to work with SQL Alias (on-premises) to Azure SQL Azure (PaaS)]( - I worked on a new situation, when we need to connect to a Azure SQL Database using SQL Alias in the same way that we have SQL Server on-premise. Unfortunately, there is not supported, but we are going to test and make it successfully. As the alias is ......[(more)]( [] [Backup and Restore Options for Azure SQL DB]( - I have decided to do a summary blog post on backup and recovery options for Azure SQL Database. If you have a DBA background, you will know the importance of securing backups for your SQL Server databases. We have many options to do such a thing; being ......[(more)]( Azure SQL Data Warehouse and Data Lake [] [Azure SQL Data Warehouse Costs vs AWS Redshift]( - Today I wanted to detail Azure SQL Data Warehouse costs vs AWS Redshift. This post is meant to follow up on two earlier posts (Azure vs. AWS Data Services Comparison and Azure vs AWS Analytics and Big Data Services Comparison), where I outlined the different ......[(more)]( [] [Database Has Been Paused]( - I was looking forward to do some work with Azure SQL Data Warehouse (DWH) when I hit the new query button and had the following message pop up – Cannot connect to database when it is paused. (.Net SqlClient Data Provider) I totally forgot that with ......[(more)]( Azure CosmosDB [] [Azure Cosmos DB Costs vs Dynamo DB and Neptune]( - Building on yesterday’s post, Azure vs. AWS Data Services Comparison, today I want to write about the Azure Cosmos DB costs vs DynamoDB and Neptune. I’m going to give an example today comparing only the NoSQL services offered by Azure and AWS. For Azure, ......[(more)]( Analysis Services / BI on the MS Stack [] [BI Survey 18]( - It’s that time again: the BI Survey (the world’s largest survey of BI tools and users) needs your input. Here’s the link to take part: As a reward for participating you’ll get a summary of the results ......[(more)]( AI/Machine Learning/Cognitive Services [] [Introduction to the Microsoft AI Platform]( - I recently recorded an introduction to the Microsoft Artificial Intelligence suite of tools and services you can use in your organization, from what is already built into Microsoft applications you own, through leveraging Cognitive Services, customizing ......[(more)]( [] [The Lifesaving Power of Data]( - Click to learn more about author Mackenzie Thompson . Data is a lifesaving resource. At its most basic level, it allows us to see when and where we can empower people with everything from life support training, medical supplies, education, and other ......[(more)]( Administration of SQL Server [] [SQL Script: - How to find the last access date of a database]( - Sometime we create databases on Test/ Development database servers for temporary uses for short duration and forgot to delete them after the work got completed. As a part of house keeping activity, we need to find out when a database is last accessed ......[(more)]( [] [Toolbox - Just the Basics]( - There are many query sets out there to collect varying groups of configuration data - the ones I most frequently use come from Glenn Berry of SQLskills (blog/@GlennAlanBerry) but I have found that there is more data than his basic set that I like to ......[(more)]( [] [a new command to find all of your sql instances]( - Nearly every time I inherit a SQL Server environment, I’m only given a partial list of SQL Servers that exist on the network. It’s my usual routine to get permission to sniff the network then run about five different programs including Idera’s SQL Discovery ......[(more)]( [] [Audit SQL Server stop, start, restart]( - In this article, Application Development Manager Steve Keeler outlines an approach for determining the domain identity of a user who has initiated a stop, start, or restart request on SQL Server services. Although SQL Server contains server and database ......[(more)]( [] [What’s the difference between Files and Filegroups?]( - tl;dr; Filegroups are a logical construct used to separate tables and indexes from each other, files are the physical construct used to store information (log & data) about the database. When creating a database you’ll notice that each database is built ......[(more)]( [] [Rotating TDE Certificates without re-encrypting data]( - I talked previously about why we have each of the layers in the encryption hierarchy used to support TDE (Transparent Data Encryption). Understanding Keys and Certificates with Transparent Data Encryption (TDE) At the time I mused about why we need the ......[(more)]( [] [DMV for Log Statistics in SQL Server]( - There aren’t too many changes to the Dynamic Management Views in SQL Server 2017, but one was mentioned that I worth a second look and often have a lot more going on than upon first look. Gif Credit: ancsy55.tumbr.com DM_DB_LOG_STATS This is an addition ......[(more)]( [] [SQLskills SQL101: Is the recovery interval guaranteed?]( - SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious ......[(more)]( [] [SQL Server 2017 Cumulative Update 5]( - On March 20, 2018, Microsoft released SQL Server 2017 Cumulative Update 5, which is Build 14.0.3023.8. I count 14 hotfixes in the public fix list. It is good to see Microsoft getting back on schedule for their first year CU releases for SQL Server 2017, ......[(more)]( [] [SSMS 17.6 is now available: Managed Instance and many bug fixes]( - This post is authored by Alan Yu, Program Manager, SQL Server. Download SSMS 17.6 and review the Release Notes to get started. SSMS 17.6 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now ......[(more)]( [] [So You Wanna Debug SQL Server Part 1]( - Start here! This is a gentle introduction to getting set up and started. I’m nowhere near as good at this as I want to be, but I’ve been inspired by a couple wizards: Bob the Ward and Paul the White. The first thing you need to do is go download WinDbg (or ......[(more)]( [] [Installation of SQL Server 2017 failing with ‘VS Shell installation has failed with exit code 1638’]( - Dear all, Depending on which products were installed on the server beforehand, a SQL Server 2017 setup may fail with the following error : TITLE: Microsoft SQL Server 2017 Setup ------------------------------ The following error has occurred: VS Shell ......[(more)]( [] [Query Store and Automated Cleanup]( - Query Store has mechanisms for automatically cleaning your data. It is possible to cause them to break down. While presenting a session about the Query Store recently, I was asked what happened if you set the size of the Query Store below the amount ......[(more)]( [] [Partitioned Tables: Rolling/Rotating/Round-Robining Partitions]( - If a log rotates in a forest alone, does anyone hear it? I recently received a terrific question about table partitioning: I’m writing up a proposal for my company to start partitioning a 2.5TB table. The idea/need is to control the size of the table ......[(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 ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com