Updating a Cryptographic Provider for EKM with Azure Key Vault - Level 5 of the Stairway to TDE (2024-07-17)

SQLServerCentral Newsletter for July 17, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Updating a Cryptographic Provider for EKM with Azure Key Vault - Level 5 of the Stairway to TDE]( - [Configure Microsoft Fabric Database Mirroring for Snowflake]( - [Four steps towards tackling the complexity of managing multiple database platforms]( - [From the SQL Server Central Blogs - Copilot in Microsoft Fabric]( - [From the SQL Server Central Blogs - Real-World SQL Mastery: Your Hands-On, Mess-Free Sandbox]( - [Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database]( Question of the Day - [Query Store for DDL]( The Voice of the DBA  Cloud vs On-premises The cloud has been a controversial concept for much of its existence. While the idea has been around for many decades, AWS started selling IT services in 2006, with Azure following suit in 2008. Since then, the use of cloud services has grown tremendously. While some applications and organizations have embraced the idea from the beginning. I found many of you at SQL Server Central were very hesitant at first. I guess some of you are still skeptical about the value of a production database in a public cloud. From the beginning, I've felt that cloud computing has a place in the world, but in a way that is more appropriate for some situations than others. In terms of database (and maybe compute services), if you have a very well-known and predictable workload, the cloud can be very expensive. It might still be a good choice, but I think it often isn't. If you have a variable or growing workload, then the cloud might serve you better than trying to keep up with new hardware in your own data center. Bluesky has had a tremendous amount of growth since its founding. Twitter invested in this as a distributed project and when Elon Musk purchased the site, many users moved away. A lot of them went to Bluesky, which had to deal with a quickly changing workload. They started in AWS, but eventually [decided to move to an on-premises setup](. Why? One would think their continued growth would mean AWS (or another cloud) would be a natural fit. However, they hired someone that provided an analysis showing they could invest in their own hardware, overprovision what they needed for growth, and keep up with the demands as they had developed a fairly accurate method of forecasting future needs. The savings in purchasing their own hardware allowed them to buy more than they needed and handle short-term spikes. To be clear, this doesn't mean the cloud is worse for most or even many organizations. Bluesky knows they need to continue to invest in hardware, and they are prepared to keep adding resources. They also architected a distributed system that still allows them to scale into AWS if needed in the short term. I don't know many organizations that would prioritize those things alongside the rest of their business. Most of us do a poor job of forecasting load. Even if we do, often the difficulties of purchasing new resources mean that we can struggle to meet increased demands. The companies that have moved to the cloud with success, and those that have left the cloud with success, are those that measure, monitor, and make appropriate decisions based on operational data, not opinions and feelings. They aren't afraid to make a decision one way or the other, choosing what's best for the organization, not what someone wants to do or thinks will be better. The cloud might be better for you, or it might be worse, but you ought to have a way to measure and analyze the options. You also need a talented staff that isn't afraid to try new things and adapt their architecture to take advantage of modern hardware and software. Too many of us aren't as flexible as Bluesky and might not have the success they have, in or out of the cloud. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to TDE icon]( [Updating a Cryptographic Provider for EKM with Azure Key Vault - Level 5 of the Stairway to TDE]( VishnuGupthanSQLPowershellDBA from SQLServerCentral This next level of the Stairway to TDE shows how to upgrade your EKM cryptographic provider. [External Article]( [Configure Microsoft Fabric Database Mirroring for Snowflake]( Additional Articles from MSSQLTips.com In this tip, we explore how to configure mirroring for a Snowflake database to be available in Microsoft Fabric. [External Article]( [Four steps towards tackling the complexity of managing multiple database platforms]( Additional Articles from Redgate 79% of us are now using two of more database platforms - fantastic for leveraging a range of benefits, but not so great when it comes to levels of complexity. Looking for ways to overcome this? Here are four steps to take towards multi-database simplicity. [Blog Post]( From the SQL Server Central Blogs - [Copilot in Microsoft Fabric]( James Serra from James Serra's Blog Microsoft Copilot is an app that uses AI to help you find information, create content, and get things done faster (see What Is Copilot? Microsoft’s AI Assistant Explained). Copilot is... [Blog Post]( From the SQL Server Central Blogs - [Real-World SQL Mastery: Your Hands-On, Mess-Free Sandbox]( Tracy McKibben from RealSQLGuy - Helping You To Become A SQL Hero Are you ready to take your SQL skills from theoretical to tactical? If you've been yearning for a risk-free environment to test, experiment, and refine your SQL queries against... [Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database]( Site Owners from SQLServerCentral Use this practical guide to analyze and troubleshoot SQL Server performance using wait statistics. You'll learn to identify precisely why your queries are running slowly. And you'll know how to measure the amount of time consumed by each bottleneck so you can focus attention on making the largest improvements first. This edition is updated to cover analysis of wait statistics current with SQL Server 2022. Whether you are new to wait statistics, or already familiar with them, this book provides a deeper understanding on how wait statistics are generated and what they mean for your SQL Server instance’s performance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Query Store for DDL How do I configure Query Store to capture plans for CREATE and ALTER DDL commands in SQL Server 2022? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) A Valid Type? What happens when I run this code in SQL Server 2019? DECLARE @variable CURSOR; Answer: It runs with no error Explanation: This runs with no error. There is a cursor variable, which can be used to store information returned by certain system stored procedures. Ref: DECLARE - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [What component of I/O subsystem caused database corruption]( - As per Error 824 that has occured in my SQL error Log, I have found that the I/O subsystem is the cause of the database corruption. However, I want to dig further and understand which specific component of the I/O subsystem caused the corruption. Is there a methodology to identify this? How can I […] SQL Server 2016 - Administration [Extended events issue]( - I have this extended event set up: CREATE EVENT SESSION [Performance monitoring] ON SERVER ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1) ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle) WHERE ([package0].[greater_than_int64]([duration],(30000)) AND [package0].[not_equal_unicode_string]([sqlserver].[session_nt_user],N'NT SERVICE\SQLTELEMETRY'))) ADD TARGET package0.event_file(SET FILENAME=N'...',max_file_size=(5)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO The issue I'm having is that it's not excluding results for the SQLTelemetry account as shown in the example below: Does […] SQL Server 2016 - Development and T-SQL [the workaround for procs truncating varchar and nvarchar max output fields]( - Hi , assuming the answer at is correct and my peer's version of sql is Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) Aug 7 2021 01:20:37 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor) , and assuming he can set […] [Order By on a single table raises Ambiguous column name error]( - How in the world can the below query for a singular table produce the error msg "Ambiguous column name hMy"? The error goes away if I A) Alias th3 table and prefix the hMy column with that alias B) change SELECT so it's either just hMy or just * I understand how the ambiguous error […] SQL Server 2019 - Administration [AlwaysON AG]( - Hello guys, I have a WSFC cluster with AlwaysON AG two replicas(syncro) in one DC1 and the third replica (asynchronous)in other DC2.The connection between DC's are quite slow. Management want to start an disaster scenario : Failover to DC2 and shutdown completely one week DC1 and come back to DC1. My question is how I […] SQL Server 2019 - Development [Database Project or SSMS]( - Hi Do you write your code in a database project in visual studio or directly on SSMS? Just weighing up the benefits of starting to use the database projects exclusively. Thanks in advance [email recipients in agent notifications]( - as you can see at we have a couple of folks being emailed hopefully upon completion of tha job. how can i add my name/email to that list? do i have to script the job 's definition and rerun the script? [notifications in sql agent]( - Hi, i went to look at notifications for my sql agent job and came away confused. I changed the email notification to "on completion" but when i scripted it, it appeared to be focused on failure. i didnt write the script, i just asked sql to script what it had while i was in notifications. […] SQL Azure - Administration [Failover Group Error]( - Hi Experts, I have about 15 Azure databases in one region and the replica of the same in another . THe configuration is such that the RG is different in both region and are in separate SQL Pool. THe issue is that when I tried to add a new database to failover group after creating […] Reporting Services [SSRS Migrate / Upgrade -- Dead End in Microsoft Instructions]( - I'm planning to migrate an SSRS 2016 server (with scale-out deployment) to a new set of hardware in order to upgrade the OS from Win 2012 to a modern OS. I would like to upgrade from SQL/SSRS 2016 to 2019 in the process. I've found and read all of the Microsoft documentation on migrating SSRS. […] Integration Services [Looping Through Excel Files for Correct Year/Period]( - Please look at my Variables and the code used for establishing periods/year. Currently i am in P01 Year 2025 according to the calendar below in pics. I am picking up already data for P02 Year 2025 which is wrong.  SQL Server 2022 - Administration [Error: 18456 + SQL Server 2022 New Install]( - We are seeing the below error after the SQL Server 2022 install. Not sure what is the purpose of this account “'DOMAIN\XX-RPT-P-DB$”. Looks like this was created/used automatically as part of the installation and is used by some background process. We haven’t seen this issue in older versions.  I see this domain account, but […] [SELECT PERMISSION DENIED ON Scalar function]( - I have given examples of two ways of getting results from scalar function - one works the other doesn't --GRANT EXECUTE ON WDM_TSE.fnRule_JobNumber_Validation_multiple_jobcodes TO [\] DECLARE @result INT; SET @result = WDM_TSE.fnRule_JobNumber_Validation_multiple_jobcodes(N''); SELECT @result; --GRANT SELECT ON WDM_TSE.fnRule_JobNumber_Validation_multiple_jobcodes TO [\] SELECT WDM_TSE.fnRule_JobNumber_Validation_multiple_jobcodes(N''); For a user with permissions, the first works, the second doesn't work including […] SQL Server 2022 - Development [Calendar & Fiscal Year Table]( - I have a Calendar table that need to be displaying Fiscal Start and End Date as in below: The Calendar is currently have data for years 2023 - 2028: -- Calculate the number of days per year in the IMETA_Calendar table SELECT YEAR([Date]) AS [Year], COUNT(*) AS [NumberOfDays] FROM [Prod].[IMETA_Calendar] GROUP BY YEAR([Date]) ORDER BY […] [SSRS report Query Optimization]( - How to optimize below Query, Could you Please guide me.  SELECT    selected_sfs.sfName  AS 'service_form_name' ,c.agingDays      AS 'age' ,documents.document_id ,selected_sfs.contactor ,selected_sfs.lob ,doc_event_orig_actor.creator_full_name ,selected_sfs.originator_full_name ,qic.queue_name ,qic.work_item_status ,CASE WHEN --selected_sfs.sft_definition_id  IN (@category1_sftDefID) selected_sfs.sft_definition_id  IN (54,85,90,136,142,146,155,156,175,177,188,202,210,212,217,227,229,256,258,274,288,327,330,332,336,339,343,356,387) OR b.cat3 = 1 OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('dispute')) OR (selected_sfs.sfName='Contact […]   [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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -