Starting SQL, Sizing Up Columns, Singing Hedgehog Songs, in Database Weekly (08/22/20)

 Database Weekly for August 22, 2020 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News Hand-picked content to sharpen your professional edge Editorial  Of Hedgehogs and Database Design One of the finest songs of the sixties had the following lines … Sitting one day by myself, And I'm thinking, "What could be wrong?" When this funny little Hedgehog comes running up to me, And it starts up to sing me this song. "Oh, you know all the words, and you sung all the notes, But you never quite learned the song", she sang. "I can tell by the sadness in your eyes, That you never quite learned the song". (Mike Heron: The Hedgehog's Song) I once spent a fascinating couple of years auditing and inspecting the databases of Her Majesty's Government. I often hummed the Hedgehog Song to myself as I stared at the database scripts and listened to DBAs in charge of the databases that affected the lives of millions of people. I've seen many databases; a few great ones, some good'uns and many gor'blimey ones. After a while, it was easy to detect the difference. Mike Heron's sad song about love applies just as appropriately to database design. There is a world of difference in knowing the words of relational theory, and being able to hum it, but the practice of doing it properly is another stage. The well-constructed relational databases just seemed to work. They didn't require endless tinkering and maintenance. Tables were well normalised, narrow, appropriate, and ingenious. Constraints and keys abounded. The data model fitted the business like a glove. The SQL was simple because it just needed to declare what result was required, no hints, no fancy stuff, and only rarely extra indexes. The effort was focused on maintaining an immaculate, normalized representation of the organization's data. On the rare occasion that a query was slow, the dev teams assumed that the fault lay in the data model, and they'd fix it. There was one such database I knew that was responsible for the clearing of cheques for a major retail bank. It processed millions of business transactions a day. The team, a handful of people that created and maintained it, were nearing retirement so two major IT projects, involving hundreds of bright young developers, aimed to replace the system. Both failed. The original team became used to being called away from tending their roses to maintain the system that kept the organization functioning. As always, it isn't so much the algorithms but the data structures that determine how effective a relational database will be. If your database consists of a few poorly designed "Godzilla" tables, it is much harder to re-engineer them without the lights flickering, raised voices, and the sounds of approaching sirens. How can one justify iteratively developing a database model when it is easier to keep things running by hints, heuristics, and hardware? My experience tells me that disguising the problem in a relational database always ends badly, because, by doing so, it escalates complexity, and that is a scary trend for any IT system. Instead, narrow, well-designed relational tables will support nimble, iterative development, but it requires knowing how to sing the song. Phil Factor [Join the debate, and respond to the 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. Vendors/3rd Party Products [Reverse-Engineering the Production Database into Source Control]( Starting out a new database development with source control is relatively easy. To introduce source control into an existing database application can be more challenging. Phil Factor explains some of the fundamental steps. [Deployment Suite for Oracle: What’s New? Git Integration in Redgate Change Control v3.0]( In Redgate Change Control v3.0, you can now commit your database changes to your local Git repository and collaborate with your team by pushing and pulling changes from the remote Git repository. If you’re using branches, you can also create and switch Git branches from within Redgate Change Control. .NET Related Articles [Securing Web Application Secrets Through Azure Key Vault]( Azure Key Vault is a service for storing securely certificates, credentials, connection strings, and more. In this article, Rishit Mishra walks through how to use the service to secure a connection string for an application. Administration [Simple DBCC CHECKDB process to report on database corruption for all SQL Server databases]( A custom solution that lets you check the consistency of all the databases under your care, save only the end result (without that much overhead) with a few additional important values, while having to modify only a couple of things that are very specific to your use case. [How To Migrate SQL Server Jobs From One SQL Server Instance To Another]( We can easily migrate jobs and similar objects from one SQL Server instance to another. For that, we need a backup and restore process of msdb. Similarly, we can recover these objects on an SQL Server instance if lost for some reason. [Using SQL Server 2016 Upgrade Advisor]( Using Upgrade Advisor, you can determine whether your existing databases are suitable for upgrade to SQL Server 2016 or any other higher version. [Sometimes you CAN upsize a column in-place]( Usually, we build a shadow table with the new schema, create triggers to keep both copies in sync, and then batch/backfill at that team's own pace until they are ready to swap in the copy as the real deal.. However, when brute force is valued over surgical precision, and especially if you can take a slice of downtime, there can be value in the simpler approach for certain table shapes. [A Faster Alternative to sp_helpdb]( On the surface sp_helpdb looks great, and for the most part it is. It returns some great information with one line of code. However, when looking at the actual execution plan, this is where it starts to get ugly. [Cleanup Pesky SQL Error Logs]( Garry Bargsley tackles the final frontier of a tidy SQL Server system: standardized SQL Server Error Log configurations Analysis Services / BI on the MS Stack [How to kill idle SSAS sessions and connections in a batch]( While connections and sessions don’t take much memory compared to what the actual SSAS data does, they still take some memory that can be valuable to free up if a server is under resource pressure. Azure SQL Database [Managing Statistics in Azure SQL Database Serverless]( One of the only things platform as a service databases like Azure SQL Database do not do for you is actively manage column and index statistics. [Creating Azure SQL Database AAD Contained Database Users with an SPN using PowerShell, Secrets Management, Azure Key Vault, and dbatools]( Following on from my posts about using Secret Management Good bye Import-CliXml and running programmes as a different user, I have another use case. After creating Azure SQL Databases... Community Interests and PASS [How to survive and thrive when watching online conferences]( Redgate’s Events Marketing Manager, Annabel Bradford, describes how to get the most out of your next online event. Conferences, Classes, Events, and Webinars [The future of DevOps: Fully left-shifted Deployments with Version Control and Automatic Cloning]( Discover how Redgate's latest database DevOps innovations empower developers to code in the IDEs of their choice, version control database changes in plain SQL, and easily validate their changes against a masked copy of production as soon as they make the change. [Redgate Streamed Global]( On Wednesday, August 26, join world-class speakers including Donovan Brown, Abel Wang, Kendra Little and Hamish Watson for a full day of educational content. There's also the opportunity to network with your peers across the global community. So, wherever you are in the world, join us live. DMO/SMO/Powershell [How to Check SysAdmin Role in SQL Server with PowerShell]( All DBAs understand the risks of having logins with a high privilege level, so the objective of this module is to give you a quick glance across all the instances under your support, so that you can act accordingly. [Using PowerShell for Lazy Maths]( Even though Shane O'Neill can’t do maths before coffee, he can at least write PowerShell. [User input and menus in Powershell]( Fully automated hands-off PowerShell scripts are useful for any DBA or System Administrator, but what if you need to get input from the user, or maybe you want to implement a menu system? [How to Use Parameters in PowerShell Part II]( PowerShell is a basic skill any administrator working in Windows or Azure should know. After writing his first article about PowerShell parameters, Greg Moore realized that there is even more to talk about. This article covers using pipelined parameters and more. Data Mining / Data Analysis [Benford’s Law: Applying to Existing Data]( Nagdev Amruthnath argues that while Benford’s Law is widely used to detect accounting fraud, manipulations in income tax filing, and so on, there are still many more cases where it could be used to our advantage, like identifying bot accounts on Twitter and Facebook or identifying modified images or music. Database Design, Theory and Development [TYFK: Relations, Tables, Domains and Normalization]( Industry misconceptions nowithstanding, a database relation is by definition in both first normal form (1NF) and fifth normal form (5NF), otherwise it is not a relation and all bets are off. Performance Tuning SQL Server [Starting SQL: Key-dependent Relationships]( Single-column clustered indexes make a lot of sense. Single column nonclustered indexes often make less sense. It’s like the difference between a chef knife and a Swiss Army knife. You want one to be really good at one specific task, and another to be pretty useful to a bunch of tasks. [Starting SQL: The Outs Of Nonclustered Indexes]( Clustered indexes are essential, but of limited overall value for searching for data. As soon as we want to search by any other columns without searching for a specific Id, we need some non-clustered indexes to make it easier for SQL Server to find that data. [Identifying Blocking Chain Using Sp_WhoIsActive]( Ajay Dwivedi tries to devise a more versatile script for returning information about blocking queries. [Setup Perfmon for 24×7 Collection (video)]( Recording ongoing performance information is vital as it provide a history of the system state in case issues arise, and a baseline of your system’s resource consumption. [Using Extended Events as a Buffer]( What if you need to know not just what’s happening while you’re running a script to sample the database, but also everything that has happened since the last time that you’ve run the script? Extended Event sessions with a ring buffer target give you all this, plus the ability to minimize your observer overhead. [Introduction to Analyzing Waits using SQL Monitor]( Phil Factor sets out with the modest aim of giving you enough of an introduction to waits to better understand the wait information you get from a SQL Server monitoring tool like SQL Monitor, and the rather overwhelming amount of information available in the underlying DMVs and Extended Events. PowerPivot/PowerQuery/PowerBI [Creating KPIs in Power BI Desktop]( This article describes how to create Key Performance Indicators (KPI) in a Power BI Desktop data model by using Tabular Editor. [Power Query Geography And Geometry Functions In Power BI And Excel]( Power BI Desktop recently added some new functions to make it easier to work with geographic and geometric data. Chris Webb explains the basics of how the functions work. [Power BI visual customization – using perspectives]( The visual customization feature allows any Power BI user that is consuming the report to customize they way it looks, without affecting the centrally managed report. [Doing Power BI the Right Way: 2. Preparing, shaping & transforming source data]( Your data is rarely going to be in the right format for analytic reporting. It may need to be consolidated into related fact and dimension tables, summarized, grouped or just cleaned-up before tables can be imported into a data model for reporting. [Clarifying Data Warehouse Design with Historical Dimensions]( The standard data warehouse design from Kimball with facts and dimensions has been around for almost 25 years. In this article, Vince Iacoboni describes another way to design slowly changing dimensions. T-SQL [SQL Server Regex CLR Function]( You would like to extend the capability of a scalar function to be apply to apply regex expressions in your SQL Server database. Advantages to implementing this in a scalar function can help utilize regex search patterns to identify, clean and parse existing SQL Server data. [Half Of You Don’t Understand Variables and Transactions]( Do table variables and variables respect transactions? If I set the value of a variable during a transaction, and I roll it back, what happens? [Explaining SQL Server Statistics with Playing Cards]( When you run a query, how does SQL Server estimate the number of rows? Brent Ozar explains in this half-hour video demo, using playing cards, then shows the same issues in SQL Server Management Studio. [Starting SQL: Rethinking Key Column Order]( Could it be that conventional index design wisdom is based on a faulty algorithm? [Starting SQL: Sorts and Memory]( For SQL Server queries that require additional memory, grants are derived for serial plans. If a parallel plan is explored and chosen, memory will be divided evenly among threads. [Trigger Validations and Isolation Levels]( Louis Davidson explains a "weird occurrence" that can happen when your trigger validation code queries another table in the default isolation level. You expect that your data is protected until your transaction ends, but it may not be. [Row Count Spool]( Hugo Kornelis explains when you might encounter the Row Count Spool operator in your SQL Server query execution plans, what is does and how it works. [Alter Table Modify Column ONLINE Issues in SQL Server]( We recently performed a DDL operation against a SQL Server table – simply increasing the size of a varchar column – which should have been instantaneous. Instead, we killed it after observing 20 minutes of HARD_SYNC_COMMIT waits and a blocked replication log reader. Could this issue have been avoided? What went wrong? [Heaps in SQL Server: Part 3 Nonclustered Indexes]( Uwe Ricken continues his series on heaps. This time he demonstrates a common scenario where the query against a heap is faster than a clustered index. Virtualization and Containers/Kubernetes [The Two Ways Containers Will Revolutionize Database DevOps]( Containers have already transformed the way application development works, but adoption has been slower for databases. Finally, the revolution is beginning. In this post, Kendra Little shares the two ways in which containers will dramatically change the way teams develop and deploy database changes.  [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. 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 follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -