Moving an Availability Group to a Different Subnet;Using WITH in an IF condition (Database Weekly 9/10/2018)

The Complete Weekly Roundup of SQL Server News In this issue: Webinars - [Gene Kim joins Redgate to discuss The 2018 Accelerate State of DevOps Report](#33006) - [Simplifying SOX Compliance in DevOps](#33005) - [How to create a living SQL Server data catalog](#33004) Virtualization and Containers - [The Evolution of Docker Container Security: Part 3](#33010) T-SQL - [Using WITH in an IF Condition](#33043) - [Dates and Times in SQL Server: AT TIME ZONE](#33023) - [Find the Next Non-NULL Row in a Series With SQL](#33012) SQL Server on Linux - [SQL Server on Linux: ADAuth issues 2: Common AD Login Issues](#33026) - [SQL Server on Linux: ADAuth issues 1: Issues with joining a VM to a domain](#33013) Software Development - [Connection String Generator](#33022) Security news and thoughts - [Azure SQL Database – Detecting Threats](#33020) - [Ransomware and Cryptomining Malware Remain Near Top of Threat List](#33009) Python in SQL Server - [Installing External Modules into SQL Server’s Python](#33027) PowerPivot/PowerQuery/PowerBI - [A Quick Look Some Power BI And SSAS-Related Products And Books](#33033) Performance Tuning SQL Server - [Single-Column-Key Missing Index Recommendations are Usually Wrong.](#33039) - [Does The Order Of Index Columns Matter?](#33017) NoSQL - [What is MemSQL](#33021) Microsoft News - [Microsoft South Central U.S. datacenter outage takes down a number of cloud services](#33036) - [Microsoft Blames "Severe Weather" for Azure Cloud Outage](#33008) HA/DR/Always On/Clustering - [Fix: When an Availability Group listener’s IP is stolen](#33030) - [Moving a SQL Server Availability Group to a different Subnet](#33018) ETL/SSIS/ELT - [CCI Bulk Load Pitfalls 1 – ETL Framework and up-to-date Statistics](#33003) DBA Tools - [The August release of SQL Operations Studio is now available](#33040) - [Getting started with SQL Server Tools](#33015) Data Visualisation - [Building Blocks for Visualizing Business Intelligence](#33025) Data Privacy, Complianace, and GDPR - [Security, Compliance, Data Ethics, and Breaking the “Not My Job” Mindset](#33042) - [Questions About HIPAA That You Were Too Shy to Ask](#33041) Computing in the Cloud (Azure, Google , AWS) - [How to get started with Azure and .NET](#33038) - [Introducing Microsoft Azure Sphere](#33037) - [How to Get Developers Using Azure](#33029) - [External Access to Azure Storage](#33028) - [Why Separate Your Compute From Your Storage](#33016) - [Data Governance in the Cloud](#33007) Career Growth - [Data Professionals at Work – a book-writing adventure](#33034) - [Data Professionals at Work – a book-writing adventure](#33014) Backup and Recovery - [SQL Homework – September 2018 – Backup and Restore continued.](#33019) Azure SQL Database - [Moving an Azure SQL Database across Resource Groups](#33032) - [Azure SQL Database Read Scale-Out](#33031) AI/Machine Learning/Cognitive Services - [Another use for Cortana (and Alexa): Controlling Microsoft's Xbox](#33035) - [7 Ways to Reap the Benefits of AI](#33011) [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-09-10 [SQL Monitor]( What’s the top challenge faced by SQL Server professionals in 2018? Learn how 626 SQL Server professionals monitor their estates in our new report on the State of SQL Server Monitoring. Discover the challenges currently facing the industry, and what is coming next. [Download your free copy of the report]( [SQL Prompt]( Become a more efficient SQL developer with SQL Prompt Learn how to write SQL faster and more efficiently with these exclusive short videos from Data Platform MVPs and SQL Server experts. With SQL Prompt you can strip out the repetition of coding and write SQL 50% faster. [Check out the tips]( [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]( Editorial - Is Forgiveness Possible? Recently, there was a prominent film director fired from directing an episode in a very entertaining and profitable movie franchise. His firing was for tweets he made that were in very bad taste. And bad doesn’t begin to cover it, they were about topics that were universally despicable. What made this interesting was not that someone was fired for their recorded words, but rather their words from 10 years ago. I don’t want to focus on this particular case, because we don’t know all of the details, and we probably never will. The question I want to ask is: depending on your age, right now, do you want your words from 10 or more years ago held against you by your employer? by the community? Everyone reading this blog (or sitting next to a person reading this blog), has done something that they would not want everyone they know as an adult, to see. I know I have. In high school I used words that would have gotten my mouth washed out with oh so many bars of soap if my parents had heard them. I was a typical stupid kid who didn’t know better but mimicked what were supposed to be the cool kids. A true friend of mine asked me “Why do you talk like that?” It hit me kind of hard, and since then my use of coarse language generally is limited to a car accident or two. (The overall process was not easy process… I replaced bad words with Bob for a very long time). Of course, luckily for people my age, we didn’t transmit our transgressions electronically. If you called someone a bad name, it hurt, it may have gotten you punched in the mouth, but we usually moved on quickly because there wasn’t this record hanging out there staring at us. We grew callous to the name calling and often forged friendships with these same people. Many forgave these transgressions, and often changed our ways because we learned about how we affected others, and how they affected us. Not always, of course, because some people are just jerks. Yet, what if there was a record uncovered of every time I hurled a certain word at someone back in high school? Would this be the end of my days in the SQL Community? What if high school had been just 10 years ago? Or 2 years ago? In the SQL Community, there have been quite a few people who have been ostracized over things they have done. The PASS Summit doesn’t have an Anti-Harassment policy only because it was in vogue. There are documented cases, and there are stories that worked their way around about certain people. Is there a path for these folks to ever return in complete full graces of the community? All of this is just data. I fear the day when someone with a grudge starts digging around in the history of social media to find the transgressions of the SQL Community to try to destroy someone, or perhaps even everyone. Will we be able to forgive things said back before they joined the community? What if it was strong hateful or disgusting things that people have said just to be funny? Or a just being a jerk? Or due to some strongly held conviction? It feels like only a matter of time that it happens somewhere because all social media history is what brings our community together… data. Louis Davidson (@drsql) 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. Webinars [] [Gene Kim joins Redgate to discuss The 2018 Accelerate State of DevOps Report]( - Microsoft MVP Steve Jones is joined by acclaimed author and researcher Gene Kim to discuss the latest in all things DevOps. They offer a closer look at the key findings in the 2018 Accelerate State of DevOps Report, and investigates the growing importance of the database in successful DevOps and IT performance....[(more)]( [] [Simplifying SOX Compliance in DevOps]( - Being compliant shouldn’t have to result in restricting your work or slowing down your release time. Discovering where you fit and how to be compliant can be confusing. Grant Fritchey will explore how IT Teams fit into this story and how to best protect your company and customers....[(more)]( [] [How to create a living SQL Server data catalog]( - Protecting data can be difficult. With data often spread across a growing number of different environments, it's hard to know what to restrict and where it's located. In this session you’ll learn about practical steps you can take to create a durable and living record of what you hold in your SQL estate....[(more)]( Virtualization and Containers [] [The Evolution of Docker Container Security: Part 3]( - When it comes to Docker container security, orchestration models favor integrated roles, network scrutiny and small attack surfaces with host protection. ...[(more)]( T-SQL [] [Using WITH in an IF Condition]( - One of the most exciting features of SQL Server 2005 was the inclusion of Common Table Expressions (CTE). Code that often needed a tangle of temp tables could be now be done in a single query (Derived tables can be used too, but I can’t remember when derived tables started in SQL Server, but it may have been 2005, or perhaps 2000)....[(more)]( [] [Dates and Times in SQL Server: AT TIME ZONE]( - Continuing the series on dates and times in SQL Server and Azure SQL Database, this week we look at the hint AT TIME ZONE. In Azure SQL Database, the regional settings of the database are set to UTC by default. It is also advisable to store dates and ......[(more)]( [] [Find the Next Non-NULL Row in a Series With SQL]( - I’ve stumbled across this fun SQL question on reddit, recently. The question was looking at a time series of data points where some events happened. For each event, we have the start time and the end time ...[(more)]( SQL Server on Linux [] [SQL Server on Linux: ADAuth issues 2: Common AD Login Issues]( - AD authentication is a popular mechanism for login and user authentication. It works very well in many scenarios, especially for enterprise applications. AD authentication is a supported scenario on SQL Server on Linux. Imagine a scenario where all ......[(more)]( [] [SQL Server on Linux: ADAuth issues 1: Issues with joining a VM to a domain]( - AD authentication is a popular mechanism for login and user authentication. It works very well in many scenarios, especially for enterprise applications. AD authentication is a supported scenario on SQL Server on Linux. Configuring the Linux VM to join ......[(more)]( Software Development [] [Connection String Generator]( - Start entering your settings and the connection string will appear here...[(more)]( Security news and thoughts [] [Azure SQL Database – Detecting Threats]( - At first I was nervous but then I realized it was me triggering “Someone has logged on to your server from an unusual location” message against my Azure SQL database. The process of investigating the “rouge” login didn’t help with … Continue reading ......[(more)]( [] [Ransomware and Cryptomining Malware Remain Near Top of Threat List]( - In spite of Meltdown, Spectre, and IoT vulnerabilities, ransomware and cryptomining malware remain a real and present danger to IT operations. ...[(more)]( Python in SQL Server [] [Installing External Modules into SQL Server’s Python]( - So Microsoft have integrated Python into SQL Server, from the moment I heard that I thought to myself, “wow, this is exciting stuff” and not just for the machine learning, BI guys that it was marketed at. It’s exciting for us DBAs too, my head was in ......[(more)]( PowerPivot/PowerQuery/PowerBI [] [A Quick Look Some Power BI And SSAS-Related Products And Books]( - I don’t like writing reviews of books or products here on my blog for a couple of reasons, the main one being that I don’t usually have the time to read/test/understand something properly so I can write a thorough review. That said I do get sent a lot ......[(more)]( Performance Tuning SQL Server [] [Single-Column-Key Missing Index Recommendations are Usually Wrong.]( - When you’re looking at an index recommendation – whether it’s in an execution plan or the missing index DMVs – it helps to understand Clippy’s blind spots. Let’s start with the small StackOverflow2010 database so you can follow along. (It’s just a 1GB ......[(more)]( [] [Does The Order Of Index Columns Matter?]( - When beginning to learn SQL, at some point you learn that indexes can be created to help improve the performance of queries. Creating your first few indexes can be intimidating though, particularly when trying to understand what order to put your key ......[(more)]( NoSQL [] [What is MemSQL]( - yeah, MemSQL for me its a combination of RDBMS and NOSQL and more of flexibility of both per our choice, most of the RDBMS going with such concept. NOSQL is compromise with ACID property and flexible for developers it has … Continue reading ? ...[(more)]( Microsoft News [] [Microsoft South Central U.S. datacenter outage takes down a number of cloud services]( - A cooling problem in Microsoft's South Central U.S. data center seems to be causing issues for a number of Microsoft Cloud services users in the U.S. and beyond. ...[(more)]( [] [Microsoft Blames "Severe Weather" for Azure Cloud Outage]( - Says weather-related voltage spike brought down data center cooling system in San Antonio. ...[(more)]( HA/DR/Always On/Clustering [] [Fix: When an Availability Group listener’s IP is stolen]( - When you can connect to the individual nodes of a SQL Server Availability Group (AG), but not the AG listener, you may have a duplicate IP situation. Resolving this isn't QUITE as straightforward as I'd hope, so here's our solution. The post Fix: When ......[(more)]( [] [Moving a SQL Server Availability Group to a different Subnet]( - Over the last few days I have reworked my home network design, and introduced a new subnet for all my SQL Server based VMs that are running in my VMware vSAN powered Home Lab. In this blog posting I want to describe the various steps that are necessary to move a SQL Server Availability Group into a different subnet....[(more)]( ETL/SSIS/ELT [] [CCI Bulk Load Pitfalls 1 – ETL Framework and up-to-date Statistics]( - Intro I would like to start a miniseries of blogs where I try to summarize lessons learned from large data loading into clustered column store index tables (CCI). In this blog I will describe an issue that would come as no surprise to most SQL experienced ......[(more)]( DBA Tools [] [The August release of SQL Operations Studio is now available]( - We are excited to announce the August release of SQL Operations Studio is now available. Download SQL Operations Studio and review the Release Notes to get started. SQL Operations Studio is a data management tool that enables you to work with SQL Server, ......[(more)]( [] [Getting started with SQL Server 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 Visualisation [] [Building Blocks for Visualizing Business Intelligence]( - Data analysts and business analysts are tasked with making sense of large amounts of data and putting it in useful formats that can help business stakeholders mak ......[(more)]( Data Privacy, Complianace, and GDPR [] [Security, Compliance, Data Ethics, and Breaking the “Not My Job” Mindset]( - Security, compliance, and data ethics are related concepts that everyone who works with software should know about, from the help desk to the C-level office… but almost everyone thinks that worrying about these things is someone else’s problem. ...[(more)]( [] [Questions About HIPAA That You Were Too Shy to Ask]( - As a data professional or developer, you will likely have questions about how HIPAA impacts you and your day to day responsibilities. Rebecca Edwards answers common questions about HIPAA that you were too shy to ask...[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [How to get started with Azure and .NET]( - Azure is a big cloud with lots of services, and for even the most experienced user it can be intimidating to know which service will best meet your needs. This blog post is intended to provide a short overview of the most common concepts and services ......[(more)]( [] [Introducing Microsoft Azure Sphere]( - As we continually see more and more consumer-grade internet devices, like appliances, baby monitors, thermostats, etc., we need a more robust way to manage these devices in a secure way. There are approximately 9 billion of these devices built every ......[(more)]( [] [How to Get Developers Using Azure]( - You know the talk out there, everyone is moving to the cloud and people are looking at Azure to get there. But many have concerns with going to the cloud or the unknown things around Azure. That’s why we created this blog series. Today, I want to tell ......[(more)]( [] [External Access to Azure Storage]( - There are multiple ways to allow external access to Azure storage accounts, some better (and more secure) than others. Today, I’d like to share with you 3 methods to access your storage accounts externally, as well as the preferred methods for doing ......[(more)]( [] [Why Separate Your Compute From Your Storage]( - There are many advantages to using Microsoft Azure. One big reason people look at Azure is the ability to separate storage from compute. This separation of storage and compute allows for cost reduction. If you don’t need to use a service, why should ......[(more)]( [] [Data Governance in the Cloud]( - IT center, on-premise infrastructure is becoming increasingly complex and costly, and requires highly skilled manpower, so businesses are now moving their IT and Data Science functions to the Cloud. Cloud Computing promises low-cost storage facilities, ......[(more)]( Career Growth [] [Data Professionals at Work – a book-writing adventure]( - I love to write. It is how I express myself best. During the last fall I was pondering starting on a book, my first one. I’ve heard many people talk of the experience. Some enjoy it, others think it is too much work in return for too little. (We don’t ......[(more)]( [] [Data Professionals at Work – a book-writing adventure]( - I love to write. It is how I express myself best. During the last fall I was pondering starting on a book, my first one. I’ve heard many people talk of the experience. Some enjoy it, others think it is too much work in return for too little. (We don’t ......[(more)]( Backup and Recovery [] [SQL Homework – September 2018 – Backup and Restore continued.]( - In the very first SQL Homework post you were asked to take a backup. In fact it asked you to both take a backup and restore it. Because, I’ll be honest with you, if you can’t restore a backup then you might as well not have taken it. That said, it’s ......[(more)]( Azure SQL Database [] [Moving an Azure SQL Database across Resource Groups]( - In my mind there are a couple of ways to move a database across resource groups. They vary from scripting to just using the Azure portal....[(more)]( [] [Azure SQL Database Read Scale-Out]( - Read Scale-Out is a little-known feature that allows you to load balance Azure SQL Database read-only workloads using the capacity of read-only replicas, for free. As mentioned in my blog Azure SQL Database high availability, each database in the Premium ......[(more)]( AI/Machine Learning/Cognitive Services [] [Another use for Cortana (and Alexa): Controlling Microsoft's Xbox]( - Cortana- and Alexa-powered devices soon will enable users to control many of their Xbox One console activities using their voice. ...[(more)]( [] [7 Ways to Reap the Benefits of AI]( - Here are seven ways to prepare for your artificial intelligence future and reap the benefits of AI. ...[(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