Annoying Date Formats, the future of Microsoft On-Premises, and Why Virtualize a SQL Server? (Database Weekly 7/15/2017)

The Complete Weekly Roundup of SQL Server News In this issue: Webinars - [24 Hours of PASS Summit Preview July 19-20](#29505) Virtualization and Containers - [Point Counterpoint: Why Virtualize a SQL Server?](#29511) Vendors/3rd Party Products - [Extending DevOps practices to SQL Server databases – Branching & Merging with VSTS](#29521) Testing Software - [Are your automated tests slowing you down?](#29503) Tech News : General Interest - [The Best and Worst Companies for Defending Your Data Privacy](#29519) SQL Server Internals - [Microsoft Defines Its Path Forward for On-Premises](#29504) Security news and thoughts - [Kaspersky's Ties To Russian Intelligence Questioned Once Again](#29520) - [The Alarming Prevalence of Data Breach Cover-Ups](#29496) Replication - [Add new articles to existing Transactional Replication without initializing old articles](#29514) - [Create a Staging Load with Biml](#29512) PowerShell - [DSC Resource Kit Release July 2017](#29506) Performance Tuning SQL Server - [What I Love About the Stack Overflow Database](#29518) Microsoft Resources - [Free Microsoft eBook Giveaway](#29493) Database Design, Theory and Development - [Annoying Date Formats](#29516) - [DML for memory-optimized tables in partitioned views](#29510) - [Secret Codes And SQL Server, Part 1: Writing Our Own Encryption Algorithm and Cracking it](#29508) - [Indexing a HUGE Table](#29507) - [IF Statement Branching and Parameter Sniffing](#29500) - [What’s in a name? How should SQL Server 2017 Graph Edge tables be named?](#29498) - [Learning the hard way – referenced objects or actual objects](#29491) Computing in the Cloud (Azure, Google , AWS) - [Creating SQL Server containers with docker compose](#29495) Career Growth - [Future-Proofing Your Career for the AI Revolution](#29501) - [The Choices We Make](#29492) Azure SQL Database - [Preview of Centralized Reporting for Azure Backup](#29517) - [Running SSIS in an Azure VM](#29513) - [Replicate Azure Virtual Machines to Another Region for Disaster Recovery](#29502) - [Public Preview of Compatibility Level 140 for Azure SQL Database](#29490) Azure SQL Data Warehouse and Data Lake - [Azure Data Lake Store encryption using Azure Key Vault for key management](#29497) Analysis Services / BI on the MS Stack - [Using the SELECTEDVALUE function in DAX](#29494) Administration of SQL Server - [Default reports in SSMS](#29515) - [Availability Group Extended Events](#29509) - [STOPAT And Date Formats](#29499) [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-07-17 [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]( [Database DevOps](www.red-gate.com/dlmdemo?utm_source=dbw&utm_medium=pubemailad&utm_content=devops-webinar&utm_campaign=databasedevops&utm_term=20170715-slot2) Database DevOps Demo Webinar Learn how to automate your database deployments alongside your app code in this free demo webinar. [Register now](www.red-gate.com/dlmdemo?utm_source=dbw&utm_medium=pubemailad&utm_content=devops-webinar&utm_campaign=databasedevops&utm_term=20170715-slot2) [SQL Clone]( SQL Clone: Now supporting databases up to 64TB Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. [Try it free]( Editorial - Potholes in the Road of Progress This week's editorial is a guest post from Phil Factor. There is always a chance that you, in your work as a technologist, will discover something new. If it turns out to be wonderful and useful, you may be tempted to sit back with a glow of satisfaction and wait for the acclaim. In fact, you’re just at the start of a long battle for your discovery to be recognised. It isn’t just that breakthroughs are frequently ignored, but once you get over that problem, that they are sometimes met with active hostility. Why would anyone be hostile to a scientific or technical advance? For every Einstein or Darwin, there are equal and opposite fools, eminent in their branch of science, who do their level best to delay advances. I find some of these people fascinating. The absurd Professor Dionysius Lardner (1793 –1859) decided that the great railway engineer Brunel was incompetent, and temporarily managed to convince scientists that the voyage directly from New York to Liverpool by steam liner would be impossible. His final ignominy was in saying at an inquest that a fatal boiler explosion happened because the affected engine was struck by lightning. Almost a decade after Joseph Lister discovered that surgery failed not through the ‘miasma’ but through unsterile surgical implements, many eminent surgeons such as Samuel Gross, distinguished professor of surgery at Jefferson University Medical College in Philadelphia, continued to wear street clothes, without masks and gloves, and using unsterile surgical equipment. We somehow get a sneaking admiration for someone like Sir George Biddell Airy (1801 – 1892), Astronomer Royal from 1835 to 1881, who put back the science of chronology and computing several decades by poisoning the British government against the work of Babbage and ‘Longitude’ Harrison. He even managed to bury Thomas Fowler’s discovery of the unique properties of the balanced ternary system for mechanical calculators. His most famous mistake was to contribute to the Tay Bridge disaster by giving entirely incorrect advice to the designer, Gouch, about the severity of the weather in that location. Dr A A Griffith (1893 - 1963), who headed the aircraft engine department at the Royal Aircraft Establishment after WW1, put back the development of the jet engine by several years. Griffith rejected Whittle’s proposals for a jet engine, reporting to the UK government that a jet engine could never work. In consequence, the government refused to fund its development. The list goes on and on. There seems to be a strong human instinct that is averse to new ideas and discoveries, and many who somehow believe that by vociferous opposition that they can "Photoshop" them out of existence. I wish I could say that discoveries always reach the light, but I believe that, in science or technology, it is only true if there exists a counter-force to the instincts to resist change: a healthy, altruistic community of peers. Phil Factor 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 [] [24 Hours of PASS Summit Preview July 19-20]( - The next 24 hours of PASS is coming up next week on July 19. This is the preview edition getting everyone ready for the PASS Summit coming up at the end of October....[(more)]( Virtualization and Containers [] [Point Counterpoint: Why Virtualize a SQL Server?]( - Why virtualize SQL Server if you're content with your physical servers today?...[(more)]( Vendors/3rd Party Products [] [Extending DevOps practices to SQL Server databases – Branching & Merging with VSTS]( - Join Microsoft Data Platform MVP Steve Jones for the next Database DevOps demo webinar. He’ll be plugging Redgate tools into Visual Studio Team Services and showing how you can branch and merge your database changes. Sign up here....[(more)]( Testing Software [] [Are your automated tests slowing you down?]( - Slow, unreliable tests prevent teams doing great work, and make continuous delivery impossible. So what can you do to improve your automated tests?...[(more)]( Tech News : General Interest [] [The Best and Worst Companies for Defending Your Data Privacy]( - The Electronic Frontier Foundation has released its "Who Has Your Back" list of companies that do the best and worst job of protecting customer data from government snooping or intrusive marketers. ...[(more)]( SQL Server Internals [] [Microsoft Defines Its Path Forward for On-Premises]( - There are two things you need to understand about Microsoft’s support for on-premises data centers, the new servicing model for Windows Server and Azure Stack....[(more)]( Security news and thoughts [] [Kaspersky's Ties To Russian Intelligence Questioned Once Again]( - Of particular concern is a program that saw Kaspersky Lab employees accompany FSB agents on physical raids. ...[(more)]( [] [The Alarming Prevalence of Data Breach Cover-Ups]( - The AA is the latest company to show an alarming lack of transparency after a breach of customer data....[(more)]( Replication [] [Add new articles to existing Transactional Replication without initializing old articles]( - Suppose you have Transactional Replication configured in your production environment. There is a business requirement to add a new article to the existing publication, and you would like to initialize only the newly added article instead of all articles in the publication....[(more)]( [] [Create a Staging Load with Biml]( - When designing the ETL architecture for new or changing systems, one of the more common needs is to create a process that copies all of the data from a set of tables (perhaps even all tables) from a source system to a destination database....[(more)]( PowerShell [] [DSC Resource Kit Release July 2017]( - The Microsoft PowerShell team have just released the DSC Resource Kit, including updates to 12 DSC resource modules....[(more)]( Performance Tuning SQL Server [] [What I Love About the Stack Overflow Database]( - Brent Ozar has a lot of reasons to love Stack's data dumps for test data....[(more)]( Microsoft Resources [] [Free Microsoft eBook Giveaway]( - Microsoft are giving away free eBooks again, including plenty of SQL Server, Azure, reporting and BI titles....[(more)]( Database Design, Theory and Development [] [Annoying Date Formats]( - Randolph West shows that even The Best Date Format can deceive you under certain circumstances....[(more)]( [] [DML for memory-optimized tables in partitioned views]( - Partitioned view support for DML with memory-optimized tables will properly handle INSERT and DELETE operations, but some UPDATE operations might fail, and that failure occurs in a most ungraceful way....[(more)]( [] [Secret Codes And SQL Server, Part 1: Writing Our Own Encryption Algorithm and Cracking it]( - There are plenty of encryption options in SQL Server but if you want to know how to build your own from scratch, this post will show you....[(more)]( [] [Indexing a HUGE Table]( - A half-billion row table with a missing clustering key needs a new index. What's the best way to fix it?...[(more)]( [] [IF Statement Branching and Parameter Sniffing]( - It can be tricky to remember that SQL Server doesn’t short circuit on IF statements in stored procedures. Even when you know this, sometimes it’s hard to recognize the impacts....[(more)]( [] [What’s in a name? How should SQL Server 2017 Graph Edge tables be named?]( - Greg Low takes a deep dive into naming practices in one of the newest parts of SQL Server, Graph Edge tables....[(more)]( [] [Learning the hard way – referenced objects or actual objects]( - This month’s T-SQL Tuesday is about lessons we’ve learned the hard way. Which, of course, is the way you learn best. Rob Farley looks at a hard-earned lesson....[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Creating SQL Server containers with docker compose]( - If you need to run multiple containers with SQL Server, Docker Compose can help....[(more)]( Career Growth [] [Future-Proofing Your Career for the AI Revolution]( - Are automation and artificial intelligence coming for your job? How can you prepare your professional skills for the coming AI revolution? ...[(more)]( [] [The Choices We Make]( - Grant Fritchey looks at priorities - the ones immediate to us, the rules and regulations we create and enforce around our jobs, and the ones that are at least a step removed from us, the service and services we supply to our “customers”....[(more)]( Azure SQL Database [] [Preview of Centralized Reporting for Azure Backup]( - Microsoft has started to address one of the big feature requests for Azure Backup: centralized reporting. A preview has been launched to allow you to centrally visualize and report on your usage of Azure Backup.   Feature Gap Azure Backup has ......[(more)]( [] [Running SSIS in an Azure VM]( - By running SSIS in an Azure VM, you can communicate move data into or out of Azure SQL Database, Azure SQL Data Warehouse, or a data store on another VM....[(more)]( [] [Replicate Azure Virtual Machines to Another Region for Disaster Recovery]( - In this post you will see how to enable disaster recovery replication of Azure virtual machines (VMs) from one region to another region. This will enable your services to survive a massive outage in an Azure data center or region....[(more)]( [] [Public Preview of Compatibility Level 140 for Azure SQL Database]( - Microsoft have announced the official public preview of compatibility level 140 in Azure SQL Database....[(more)]( Azure SQL Data Warehouse and Data Lake [] [Azure Data Lake Store encryption using Azure Key Vault for key management]( - Using this setup all data in your Data Lake Store will be encrypted before it gets stored on disk. To decrypt the data, a master encryption key is required....[(more)]( Analysis Services / BI on the MS Stack [] [Using the SELECTEDVALUE function in DAX]( - This article describes how the SELECTEDVALUE DAX function simplifies the syntax required in many scenarios where you need to read a single value selected in the filter context....[(more)]( Administration of SQL Server [] [Default reports in SSMS]( - As DBAs our stock in trade is information and there is certainly an impressive amount available. The diagnostic views are the most common place to get the information we need but every now and again it’s nice to get an organized/pretty view. To that end, you can write your own reports or you can use the default reports that Microsoft makes available through SSMS....[(more)]( [] [Availability Group Extended Events]( - Tracy Boggiano shows how to enhance the built-in Extended Events session for Availability Groups....[(more)]( [] [STOPAT And Date Formats]( - Date/time formats can throw unexpected spanners in the works, as Dave Mason shows with some experiments....[(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 ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com