How Lakehouse Architecture is Revolutionizing Business Intelligence (2023-05-27 DBW)

Database Weekly for May 27, 2023 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News by SQLServerCentral.com Hand-picked content to sharpen your professional edge Editorial  Validating Password Expiration I would guess that the majority of instances I've had to manage in my career were those that I didn't initially install and configure. I've inherited more instances than I would bother to count, and I often need to double-check what's been done in the past. As noted in the series on new jobs from [Tracy]( and [Josephine]( there are a lot of settings to check and adjust to meet your standards. While backups are often my first priority, security is second. I usually want to know who the sysadmins are and ensure systems are patched and configured to reduce the attack surface area. There is one other security check that I think I haven't always been overly concerned about checking: password expiration. There was [a post from Steve Stedman]( recently that mentioned the way to alter logins and ensure they have CHECK_EXPIRATION set ot on, which ensures that passwords expire and need to get changed. This is especially important for sysadmins. I try to ensure those accounts in that role are secured with AD, but there have been times when SQL accounts are used. Usually, I disable sa, but I've seen other accounts, especially those used by monitoring systems who seem to think sysadmin is required. [It's not](. I don't know that I've run queries to check the value in the is_expiration_checked column is appropriately set. If it's not, then Steve's post above will help you change those logins. That's a handy script to have set up and use to ensure that all logins have this set. In fact, this is one of those areas where new logins could be created by junior administrators and not set the option. Perhaps this is something you want to run on a regular basis, perhaps weekly, to ensure that if any new SQL logins are created, they are done so with the password expiration set. Ideally, no one would ever create logins without expiration set, but sometimes things happen. I've seen monitoring systems set up with sysadmin privileges and passwords that never expire. A surefire way to dramatically increase the risk to your database systems. It would be better to have a known, consistent process for setting up accounts. Some companies have specific scripts, or [snippets]( that administrators use when tickets are filed. One customer of mine had even linked a script to a Slack command in a sysadmin channel. Only admins could use this channel, but they could use Slack to kick off scripts to create logins, add roles, and force password changes. No matter how you choose to handle security at a process level, it is important to include monitoring and remediation for issues. Mistakes will get made, settings altered, and exceptions approved. Sometimes we can fix things, sometimes we cannot, but knowing what our environment looks like and where we have potential issues is important not only for getting the work complete but getting the approvals to make changes that ensure better security. My recommendation is that you ensure you have a way to regularly check your systems, automatically fix issues where appropriate, and report on those that need additional approvals. Steve Jones - SSC Editor [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 [Flyway for SQL Server Data Tools Users]( If you are using SSDT for authoring, building, debugging, and publishing a database project, how do you change to, or preferably migrate towards, a Flyway-based database development? Flyway doesn't need to replace any code part of SSDT, but if allowed to manage every release candidate, it does allow for much cleaner branching, merging, and deployments. AI/Machine Learning/Cognitive Services [Meta’s new AI models can recognize and produce speech for more than 1,000 languages]( From Technology Review Feed - Tech Review Top Stories Meta has built AI models that can recognize and produce speech for more than 1,000 languages—a tenfold increase on what’s currently available. It’s a significant step toward preserving languages... Administration of SQL Server [Lesson Learned #357:Login failed due to client TLS version being less than minimal TLS version allow]( From Azure Database Support Blog Today, we got a new service request with the follo... [SQL Server DBCC Commands: DBCC OPENTRAN]( From Steve Stedman DBCC OPENTRAN is a DBCC command in Microsoft SQL S... [Create Extended Events Session to Capture a Single Stored Procedure]( From Greg's Ramblings SQL, Programming and IT Life Do you ever have a stored procedure that you know ... [SQL Server Backup Encryption and Compression]( From SQLServerCentral Blogs In SQL Server you can also compress your encrypted backups. Unlike TDE this has been possible with Backup Encryption since the feature was first made available, and there have... The... [Lesson Learned #353: Sync failed with Inner exception: Exception of type System.OutOfMemoryException]( From Azure Database Support Blog Our customer reported today that Data Sync failed is failing with the following inner exception: System.OutOfMemoryException error. Sync failed with the exception 'An unexpected error occurred when applying batch file... [Lesson Learned #352: Could not update the metadata that indicates database enabling CDC.]( From Azure Database Support Blog Today, we got a error message while trying to enable cdc for a database using the sqladmin user. Our customer got the error message: Msg 22830, Level 16, State... [How to Choose the Right Database Monitoring Tool]( From Journey to SQL Authority with Pinal Dave Choosing the right database monitoring tool can be quite challenging, considering the multitude of options available. Right Database Monitoring Tool First appeared on How to Choose the Right Database Monitoring... Analysis Services / BI on the MS Stack [SSAS Tabular: “SortByColumn property set to invalid column ID”]( From Joyful Craftsmen – The Business Intelligence Agency Recently I was doing a cleanup of one model, and I used Best Practice Analyzer (BPA) in Tabular Editor as a helper. Later, I realized I forgot to set... [SSAS Tabular: “SortByColumn property set to invalid column ID”]( From SQLServerCentral Blogs Recently I was doing a cleanup of one model, and I used Best Practice Analyzer (BPA) in Tabular Editor as a helper. Later, I realized I forgot to set... The... Azure CosmosDB [Public Preview: Vector search in Azure Cosmos DB for MongoDB vCore]( From Azure Updates Vector search in Azure Cosmos DB for MongoDB vCore enhances AI-driven applications using OpenAI API or custom-built solutions by incorporating vector similarity search into your database. Azure Databricks, Spark and Snowflake [Apache Spark DataKickstart: First Spark SQL Application]( Get hands on with Spark SQL (no Python or Scala) to build your first data pipeline. In this video I walk you through how to read, transform, and write the NYC Taxi dataset with Spark SQL. Azure SQL [Lesson Learned #356: Transaction log full in Azure SQL due to CDC job.]( From Azure Database Support Blog Today, we faced a service request where our custom... Azure SQL Managed Instance [Configure SQL Auditing to Storage Account using Managed Identity for SQL Managed Instance.]( From Azure SQL With User Managed Identity support for SQL Managed Instance, Auditing to Storage Account target can be configured with the following authentication methods for SQL MI: Managed Identity  Storage Access Keys Managed Identity can... Backup and Recovery [Backup Encryption Performance]( From SQLServerCentral Blogs Unlike TDE, there is some extra CPU overhead when you take an encrypted backup as the data has to be encrypted before being written to disk – whereas with... The... Career, Employment, and Certifications [Time Machine Advice: What I Wish I Knew When Starting with Databases]( From Journey to SQL Authority with Pinal Dave I was asked a thought-provoking question: "If I could time travel, what would I have wished to know when starting with databases?" First appeared on Time Machine Advice: What I... [Career navigation in the time of tech layoffs]( From Kendra Little's Blog Reorganizations and layoffs are now commonplace in the tech industry: a daily occurrence. It’s increasingly necessary for tech workers mentally prepare for potential job loss and uncertainties that arise....  Cloud - Azure [Optimize your reservation purchases with reservation utilization alerts.]( From Azure Updates Activate reservation utilization alerts and take control of your Azure reservation strategy. Conferences, Classes, Events, and Webinars [Overcoming Organization Challenges in Cloud Migration: A Webinar for Senior IT Leaders]( Is your team looking to modernize and migrate your data but have questions about the complex organizational challenges? This webinar panel on June 29th covers all aspects of data modernization and migration that senior IT leaders should be aware of, including organization culture, communication, and politics. Why not send it to your boss? [PASS Data Community Summit 2023]( In 2023, connect, share & learn with like-minded peers, speakers, and industry leaders during the full week of data celebrations. Summit happens in person, from November 14th to 17th in Seattle. Check out the blog post to learn more. DMO/SMO/Powershell [Fun With PowerShell – Opening Websites with Start-Process]( From Arcane Code Fun With PowerShell - Opening Websites with Start-Process Data Privacy, Compliance, and Governance [7 Best Practices for Data Collection in 2023]( From Dataversity Digital procedures play an important role in modern business, as they generate lots of valuable information that can be used to improve organizations and advance their goals. Thus, in... Data Visualisation [From Pie Chart to Stacked Bar Chart]( From Curated SQL Simon Rowe gives a multi-pie chart a once-over: Th... [don't hide the crucial findings]( From Storytelling with Data When choosing a particular graph type, there are no hard and fast rules. With so many options available, it can be tempting to select a visual that you’ve used... [Stacked Bar Charts vs. Line Graphs–Which is Better?]( From SQLServerCentral Blogs I ran across an interesting post from Rita Fainshtein that looked at the different types of graphs for a set of data. I thought that was interesting, so I... The... Data Warehousing [How Lakehouse Architecture is Revolutionizing Business Intelligence]( From Paul Turley's SQL Server BI Blog The Lakehouse is the evolution of the earlier cloud data platform in many pieces that came with "some assembly required". All of the components are modern, mature and capable... ETL/SSIS/Azure Data Factory/Biml [Getting Started with Data Pipelines in Fabric Data Factory]( From RADACAD Microsoft Fabric comes with many objects that can ... HA/DR/Always On/Clustering [A Lot of Companies are Activating their DR Plans Today… If They Have Them]( From DCAC Years ago, DCAC put a bunch of equipment in a Colo (and did a massive server upgrade last year). The facility we’re in is massive taking up an entire... Hardware [Intel Delivers 10,000 Aurora Supercomputer Blades, Benchmarks Against Nvidia and AMD]( From Tom's Hardware US Intel announced details of its Falcon Shore refocus and announced that it had delivered 10,000 blades for the Aurora supercomputer. [2TB WD Black SN850X Back Down to Its Lowest Ever Price: Real Deals]( From Tom's Hardware US The start of this week sees a return to form with price drops across the board for a lot of Gen 4 and Gen 3 M.2 SSDs. MDX/DAX [MATCHBY for DAX Window Functions]( From Curated SQL Jeffrey Wang plays matchmaker: In this final insta... [Introducing dynamic format strings for DAX measures]( From Sqlbi Introduction to dynamic format strings feature released in Power BI May 2023, including a few technical details of its implementation. Microsoft Fabric ( Azure Synapse Analytics, OneLake, ADLS, Data Science) [Microsoft Fabric vs Synapse]( From Curated SQL Nikola Ilic shares some thoughts: I’ve already ... [What is Microsoft Fabric? (Power BI + Synapse + DW + DataLake + ML)]( From Purple Frog Systems At today’s Build conference, Microsoft announced Fabric. What is this? In simple terms, think of taking Synapse Analytics, Data Warehousing, Data Lakes, Data Factory, Spark Notebooks and Machine Learning,... [What is Microsoft Fabric, and Why it is a Big Deal!]( From RADACAD You might have heard the news of the announcement at Microsoft Build by Arun Ulag and the rest of the Microsoft Data and Analytics Platform team. The announcement was... [Welcome Microsoft Fabric – Most significant change in Microsoft BI]( From The Bit Bucket Microsoft has had a strong lead in BI and analytics for a long time now. The introduction of the tabular data models in 2012 was a watershed moment. Today,... [Enabling Microsoft Fabric in Your Tenant]( From RADACAD If you have heard about Microsoft Fabric in the Microsoft Build 2023 event’s announcement, Here are explanations for enabling it for your tenant. This setting will be automatically enabled... [Microsoft Fabric – the first look]( From SQLServerCentral Blogs Microsoft has just announced Microsoft Fabric during the Microsoft Build conference. This is a unified analytic solution of the era of AI. Microsoft Fabric is an end-to-end, unified analytics... The... [Spreading your SQL Server wings with Microsoft Fabric]( From Kevin Chant Reading Time: 7 minutes In this post I want to cover spreading your SQL Server wings with Microsoft Fabric . As part of a series of posts about... [Spreading your SQL Server wings with serverless SQL Pools]( From Kevin Chant Reading Time: 4 minutes In this post I want to cover spreading your SQL Server wings with serverless SQL Pools. As part of a series of posts about... Oracle/PostgreSQL/MySQL/other RDBMS [Optimizing MySQL: Adding Data to Tables]( From Simple Talk Welcome back to the MySQL optimization series! In case you haven’t been following this series, in the past couple of articles we have discussed the basics of query optimization,... Performance Tuning SQL Server [SQL Server 2022 Degree of Parallelism Feedback Is Wack]( From Erik Darling Data SQL Server 2022 Degree of Parallelism Feedback Is ... [Statistics Parser]( From SQLServerCentral Blogs Using Statistics Parser to easily read output from STATISTICS IO and STATISTICS TIME. The post Statistics Parser appeared first on SQLServerCentral. [A Little About Why Parallelism Doesn’t Make Every SQL Server Query Faster]( From Erik Darling Data A Little About Why Parallelism Doesn’t Make Every SQL Server Query Faster Going Further If this is the kind of SQL Server stuff you love learning about, you’ll love... [SQL Server 2022 Performance – Buffer Pool Scan]( From Journey to SQL Authority with Pinal Dave One aspect that can significantly impact performance is scanning the buffer pool in Microsoft SQL Server. This blog explores buffer pool scan. First appeared on SQL Server 2022 Performance –... [A Little About Improving Modification Query Performance In SQL Server]( From Erik Darling Data A Little About Improving Modification Query Performance In SQL Server Going Further If this is the kind of SQL Server stuff you love learning about, you’ll love my training.... [A Little About Date Math In SQL Server Queries]( From Erik Darling Data A Little About Date Math In SQL Server Queries Going Further If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m... PowerPivot/PowerQuery/PowerBI [Reusable deployment pipelines for Power BI]( From Richard Swinbank Azure DevOps pipeline templates allow you define common functionality once, then reuse it in many pipelines. A great use case for this is publishing #PowerBI reports automatically -- templated... [How Does Microsoft Fabric Change the Enterprise BI Game?]( From Paul Turley's SQL Server BI Blog Microsoft Fabric (#MicrosoftFabric), the new Unified Analytics platform, was announced this morning, literally moments before the Build Conference. Satya made the official announcement in the keynote address. Why is... [Overcoming the Fear: Exploring Microsoft Fabric from a Power BI Angle]( From Data – Marc I can’t be more excited! After the first time I heard about Microsoft Fabric, it directly took my interest. Especially as I see this as a nice bridge in... [Using Power BI DirectLake in Microsoft Fabric]( From FourMoo I am super excited to release this blog post and I cannot wait to start using Microsoft Fabric and all the different components. The goal of my blog post... Product Reviews and Articles [Self Review: Practical Graph Structures in SQL Server and Azure SQL]( From Simple Talk I just finished writing this book a few weeks ago, and as I read it, I thought “why not review my own book?” Of course, the question that first... [Book Review: SQL Query Design Patterns and Best Practices]( From SQLServerCentral Blogs This book will provide you with a good foundation ... Product Upgrades and Releases [Azure Data Studio 1.44 Released]( From Curated SQL Erin Stellato has an update for us: It seems like ... [Azure Machine Learning - General Availability for Build]( From Azure Updates New features now available in GA include the ability to enable MLOPs across dev, test, and prod environments to promote assets across multiple workspaces. [Database Migration Assessment for Oracle - GA]( From Azure Updates Database Migration Assessment for Oracle extension analyzes an organization's Oracle database, identifying performance requirements and code compatibility with the target Azure database for a smooth migration. [Available: Azure Database for MySQL connector for Power Platform and Azure Logic Apps]( From Azure Updates Azure Database for MySQL connector for Power Platform, Azure Logic Apps is now available and allows you to connect to any MySQL Flexible Server database to read and write... [General Availability – Database Schema Conversion Toolkit for Oracle]( From Azure Updates An extension that automates and provides code conversion from Oracle to SQL to simplify and accelerate the migration process. [Generally Available: Azure Cosmos DB serverless container with 1 TB storage]( From Azure Updates Azure Cosmos DB serverless 1-TB is now generally available. Enjoy expanded storage capacity, allowing you to store up to 1 TB of data in a single container. [Generally Available: Azure Cosmos DB hierarchical partition keys]( From Azure Updates Now generally available, hierarchical partition keys in Azure Cosmos DB enables you to partition your data with up to three levels of keys. [Generally Available: SQL Bindings for Azure Functions]( From Azure Updates With input and output bindings for SQL, you can now can quickly write Azure Functions that read and write from your databases. [Generally Available: Azure Cosmos DB burst capacity]( From Azure Updates Now generally available, Azure Cosmos DB burst capacity helps improve performance by allowing you to use idle throughput to handle temporary spikes in traffic. [Announcing general availability of the Azure SQL bindings for Azure Functions]( From Azure SQL Integrate Azure Functions and Azure SQL easily with the Azure SQL bindings for Azure Functions, now generally available. Input and output bindings speed up development time by reducing boilerplate... [Generally Available: Exactly Once Delivery to ADLS Gen2 Output]( From Azure Updates Azure Stream Analytics supports for end-to-end exactly once semantics when writing to Azure Data Lake Storage Gen2. [Azure Stream Analytics is Launching a New Competitive Pricing Model!]( From Azure Updates Up to 80% price reduction in Azure Stream Analytics Python [How to Visualize Timeseries Data with the Plotly Python Library]( From MSSQL Tips Learn how to use the Plotly library to visualize time series data in Python in this step-by-step article. [Getting matplotlib’s default colors]( From Statistical Odds & Ends When you make plots with Python’s matplotlib package, the package chooses some default colors for you. In the code below, we draw the lines for . Notice that the... [Lesson Learned #354: Why is Python using a lot of CPU of Azure SQ Database?]( From Azure Database Support Blog Today, we got a service request that our customer reported a high CPU usage in Azure SQL Database. Following I would to share with you my lessons learned here.  Our... R Language [Why should I use R: Handling Dates in R and Excel: Part 3]( This is part 3 of an ongoing series on why you should use R. Future blogs will be linked here as they are released. SQL Server Security and Auditing [Unmasking SQL Server Dynamic Data Masking, Part 1, Intro]( From Simple Talk This is the beginning of a series on SQL Server Dy... [How to Enable Transparent Data Encryption for Databases in Your Availability Group]( From StraightPath Solutions SQL Blog Transparent data encryption is the built-in method for at-rest data encryption in SQL Server. Taking steps towards security has always been important, but it is becoming even more important... [Securing SQL Server: CHECK_Expiration for SQL Server logins]( From Steve Stedman The CHECK_EXPIRATION option is a security feature ... T-SQL and Query Languages [Checking Cross-Database Referential Integrity in SQL Server - Part 1]( From MSSQL Tips Learn a way to build referential integrity for tab... [What is the SQL Server CLR Integration?]( From Callihan Data I was setting up a dev environment for a new application recently. All seemed to be well until I went to actually run the application. I was getting a... [Compute Statistics using T-SQL for Arithmetic Mean, Geometric Mean and Median]( From MSSQL Tips This article drills down on how to compute and compare statistics using T-SQL for the arithmetic mean, the geometric mean, and the median. Tech News [Amazon Employees Plan To Walk Off the Job As Tech Worker Tension Rises]( From IT Pro - Microsoft Windows Information, Solutions, Tools The walkout is part of a greater wave of anxiety s... Tools for Dev (SSMS, ADS, VS, etc.) [Setting up a Local http Server with Node]( From SQLServerCentral Blogs Setting up a local web server was something that I haven’t done in a long time and this was really easy. This post shows how to do this with... The... [Introducing GitHub Copilot for Azure Data Studio]( From Azure SQL GitHub Copilot, an AI-powered pair programmer, is now available for Azure Data Studio to assist you with context-aware code completions, suggestions, and even entire code snippets. Get started today with... Virtualization and Containers/Kubernetes [How Kubernetes Can Help You Weather Regional Disaster Recovery]( From Dataversity If you’re lucky, you’ve only had to worry about managing a data disaster recovery effort once or twice in your career, if at all. However, as the rate and...  [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -