Migrate from RDS to Managed Instance, Power BI refresh, and more in Database Weekly (2022-04-30)

Database Weekly for April 30, 2022 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  What will convince you to upgrade? You’ve probably heard that SQL Server 2022 is on the way, and, assuming the name doesn’t change, it will be later this year. That is great news for folks in the data platform community as features and improvements they’ve been hoping for are added. I’m not currently supporting any production SQL Servers, but I’m still happy to hear about these features and will probably put together some presentations about them once there is a public preview. Upgrades can be a lot of work. I don’t mean the actual upgrade process, but planning and testing can take time and effort. Many shops don’t upgrade every edition, skipping one or two versions each time. I’ve always preferred to upgrade with a fresh install on a new VM over upgrading in place. That new VM (or hardware in the old days) can be used for testing until it’s time to migrate the databases and jobs. I haven’t done a SQL Server upgrade in a DevOps organization, so I hope that the process is easier than manual migrations. Microsoft has announced quite a few exciting new features with SQL Server 2022. My favorite is the parameter sensitive plan optimization that’s part of intelligent query processing. This feature allows the cache to save more than one plan for the same query when one plan isn’t ideal for all parameter values. Yes, this takes care of some parameter sniffing issues, especially when the plan could be contain a clustered index scan or a nonclustered index seek with a key lookup. I’ve seen this scenario cause lots of performance issues for my customers back when I was consulting. Some of the other announcements include improvements in Query Store, tempdb, and security, the ability to use an Azure Managed Instance for failover from an instance, and Azure Synapse Link for real-time analytics. There is probably something for everyone in this upcoming release, but would one or two favorite features convince you to upgrade? Back in the early years after 2012 was released, a couple of folks told me that my T-SQL window function session convinced them to upgrade once they saw the LAG function. So, there might be some shops that need a key function to solve an issue and will adopt SQL Server 2022 soon after general availability. In the meantime, I encourage you to watch the announcements and download the public preview once it’s available. The features in this release of SQL Server just might convince you to do the work it takes to upgrade.   Kathi Kellenberger [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 [Simple Reporting with Flyway and Database Models]( If you can generate a file-based (JSON) model for each new version of a database, produced by a Flyway migration, then you have an easy way to run simple reports to help you search, list, and understand the structure of these databases. I'll show how to produce the models using PowerShell and then run some queries against them to generate the reports. [Sharing SQL Prompt Code Snippets and Styles via the Redgate Platform]( SQL Prompt users can now share formatting style or code snippets in designated Team spaces on the Redgate Platform. Louis Davidson explains how it works. Administration of SQL Server [SQL Server Connection Strings using SqlClient, OLDEDB and ODBC]( From MSSQL Tips In this article we look at different ways to define a connection string to connect to SQL Server using SqlClient, OLDEDB and ODBC connections. Azure DevOps [Keep your Azure Synapse secrets secret in Azure DevOps]( From Kevin Chant In this post I want to cover how you can keep your Azure Synapse secrets secret in Azure DevOps. Because you need to do this if you are working with production deployments... Azure SQL [Announcing General Availability of Change Data Capture (CDC) on Azure SQL Database]( What is Change data capture (CDC)? CDC provides historical change information for a user table by capturing both the fact that Data Manipulation Language (DML) changes (insert / update / delete) were made and the changed data. Changes are captured by using a capture process that reads changes from the transaction log and places them in corresponding change tables. [Short-term backup retention up to 35 days now available in Azure SQL DB Hyperscale]( From Azure SQL  We are excited to announce Preview of short-term backup retention up to 35 days in the Hyperscale tier of Azure SQL Database. Before this improvement, Hyperscale supported backup retention... [Lesson Learned #201: Performance comparison using INSERT with and without Begin Transaction]( From Azure Database Support Blog Today, I worked on a case that our customer is testing a bechmark script to measure the INSERT operation in an Azure SQL Database. This process will take different... Azure SQL Managed Instance [Migrate Amazon RDS for SQL Server to Azure SQL Managed Instance – Part 1 (Overview)]( From Azure SQL Applications are closely dependent on the database... Computing in the Cloud (Azure, Google, AWS) [Optimizing the Cost of Azure Storage]( From Dataversity Microsoft Azure Storage is a cloud-based storage service that provides organizations with a variety of storage solutions... [Azure Function with PowerShell and the Power BI REST API]( From SQLServerCentral Blogs In this blog post I’m going to show you how you can create an Azure Function that will interact with the Power BI REST API. I choose PowerShell as... The... [Understanding Azure BLOB storage and its uses in SQL Server]( From SQLShack This article will describe Azure blob storage, its features along with its uses in SQL Server. I have explored the basics of Azure storage in my last article, “Getting... Conferences, Classes, Events, and Webinars [Gain the competitive edge with a monitoring tool]( Join Redgate's James King in conversation with Chris Yates, Senior Vice President, Managing Director of Data and Architecture at Republic Bank to discover why monitoring your database environment helps your organization gain a competitive edge Database Design, Theory and Development [SMS: Relational Database and Set Theory]( From Database Debunkings Note: "Setting Matters Straight" is a new format: I post on LinkedIn an online Q&A involving data fundamentals that I subsequently debunk in a post here. This is to... ETL/SSIS/Azure Data Factory/Biml [ETL to Extract, Transform and Load Data for Business Intelligence]( From MSSQL Tips ETL stands for Extract, Transform and Load to allow you to take source data outside of the database and load into the database. Learn more about ETL in this... MDX/DAX [COMBIN, COMBINA – DAX Guide]( From Sqlbi COMBIN: Returns the number of combinations for a given number of items. COMBINA: Returns the number of combinations (with repetitions) for a given number of items. [Introducing CALCULATE in DAX]( From Sqlbi CALCULATE is the most powerful and complex function in DAX. In this article, we provide an introduction to CALCULATE, its behavior, and how to use it. CALCULATE, with its... Oracle/PostgreSQL/MySQL/other RDBMS [How to Accelerate Oracle to Azure Database Migration | Data Exposed]( From Azure SQL Oracle is one of the leading DBMS in the marketpla... [Microsoft Addresses Cross-Tenant Database Vulnerability in Azure PostgreSQL]( From Petri IT Knowledgebase Microsoft has released new patches to address crit... [A MySQL story: Can system engineers solve database issues using system tools?]( A story about how a system engineer solved a MySQL performance issue written by Rajeshkumar Sasidharan. Performance Tuning SQL Server [Common SQL Server Consulting Advice: Enabling An Optimistic Isolation Level]( From Erik Darling Data One of the very first performance problems that developers will run into when using SQL Server is locking, and often deadlocks... [Common SQL Server Consulting Advice: Enabling Resource Governor To Fix Memory Grants]( From Erik Darling Data With Or Without You One thing I really hate about Standard Edition is the lack of a way to globally reduce the allowed memory grant default. Out of the box, any... PowerPivot/PowerQuery/PowerBI [How to automate table level refresh in Power BI]( You can automate Power BI table refreshes using PowerShell, an Automation Account, and Runbook. Dennes Torres explains how in this article. [Side effects of rounding differences in Power BI]( From Sqlbi It is not common to face rounding differences in P... [Power BI tips from the Pros – LIVE (April 23, 2022) (Member Chat 2nd Half)]( From Guy in a Cube Join us live as we answer your questions about Pow... [Understanding Power Query Query Diagnostics Data With The Diagnostics.ActivityID M Function]( From Chris Webb's BI Blog I was looking at the output of Power Query’s Que... [Power BI Performance Features]( From SQLServerCentral Blogs It’s hard to believe but Power BI has now been a... [Power BI Last Refresh Time Visualization Tool]( From Simple Talk On the article Automating table refresh in Power BI I explained many methods to automate refresh of individual objects, which could be tables or partitions. This creates the need... [Diagnostics ID and ActivityID in Power Query]( From Curated SQL Chris Webb looks into activity IDs: I was looking ... T-SQL [SQL Practice – common questions and answers for SQL skills]( From SQLShack This article will show some important questions and answers to practice SQL. Introduction SQL is the base to handle different databases. Even some of the NoSQL databases use an... [SQL Aggregate Functions in SQL Server, Oracle and PostgreSQL]( From MSSQL Tips In this article we look at the similarities and differences of aggregate functions like sum, count, min, max, avg and more for SQL Server, Oracle and PostgreSQL. [Understanding the DATEADD SQL Function and its use cases]( From MSSQL Tips In this article learn about the SQL Server DATEADD function to add and subtract various date parts from the date and time from a date. [Using INSTEAD OF Trigger to Update View]( From Callihan Data I ran into a strange issue recently and thought it would make for a great blog post. I was notified of a standard application process failing with the following... [Revisiting Time Series Model Performance Assessment with T-SQL]( From MSSQL Tips This tip use T-SQL to build models that specify when to buy and sell a financial security with the objective of growing an account balance through successive trades over... Tools for Dev (SSMS, ADS, VS, etc.) [Use Ctrl+F1 as a “preview” button in SSMS]( From Sunday morning T Every time I set up SQL Server Management Studio, ...  [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -