Stairway level 3 : Configure Extensible Key Management Using Azure Key Vault (2020-06-24)

 SQLServerCentral Newsletter for June 24, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway level 3 : Configure Extensible Key Management Using Azure Key Vault]( - [When SQL Server Performance goes Bad: the Fill Factor and Excessive Fragmentation]( - [Solving Oracle Performance Issues in 4 Easy Steps]( - [Free eBook: Understanding SQL Server Concurrency]( - [From the SQL Server Central Blogs - SQL 2019 UDF (User defined function) inlining]( - [From the SQL Server Central Blogs - OPENJSON and CROSS APPLY]( Question of the Day - [More Means in R]( The Voice of the DBA  Daily Coping Tip Rediscover a fun childhood activity that you can enjoy today I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate.  For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. The Challenges of Being Safe This editorial was originally published on Mar 19, 2009. It is being re-run today as Steve is on vacation. One thing we recommend is to make sure that production data is always kept safe, which means that you keep control of where it lives and how it's used. I saw a post recently where someone needed to send data to a business partner and wanted to obfuscate it. I think that is a great thing to do and was glad to see someone asking for advice. Sending data to business partners is required, but you want to be sure that you are not necessarily giving away too much information, especially identity, privacy, or financial information that you shouldn't. However what about your test and development environments? I've seen people obfuscate data here, but not always. In fact, not usually. This is despite the fact that you might turn over developers often, expose that data to testers, or other people that might not normally have access, and the fact that these environments almost never have auditing enabled. We need to mimic production environments and data, but there are quite a few challenges with doing this in a safe and secure manner. Just having scripts to obfuscate data is a challenge in and of itself. I'd love to see a tool built that would do this automatically, though I wonder how many companies would actually buy it. I'm not sure there's a great market for tools here until insurance companies start require it for your "data loss insurance." But building those scripts is both hard and time consuming. How do you decide what to obfuscate? What values do you use? How do handle PK/FKs to ensure that things match up correctly if you've used personal information, like a SSN, as a key field? Beyond that, there's much more. What about ensuring that data matches up correctly? Can you really determine if there is an issue with some calculation or relationship if you have random data. After all people many times will have favorite accounts that they know well and understand what the data should look like. A developer may expect certain order details or address information, and use that as a benchmark when developing new code. If the data is random every time his environment is refreshed, does that slow his productivity? How do you test things like URLs and emails if data is randomized? And what about when you alter your schemas? How much work is there to update your scripts? And what about mistakes? How often has someone pointed development code at a production server? What happens if they accidently run an obfuscation script against production? If you've scrambled data around, would you catch it quickly enough to restore before too many transactions were sent through? Using some type of data obfuscation or randomization is a great way to help ensure that your production data is kept safe, but it definitely makes for a much more complex environment, and likely, more headaches for DBAs and developers. Steve Jones --------------------------------------------------------------- The Voice of the DBA Podcasts The podcast feeds are now available at [sqlservercentral.mevio.com]( to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes! - [Windows Media Podcast]( - MB WMV 20SqlservercentralcomArticlesTaggedEditorial-Wmv - [iPod Video Podcast]( - 27.4MB MP4 - [MP3 Audio Podcast]( - 5.7MB 20sqlservercentral/sKij Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com. I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, [post something here](. The boss will be sure to read it. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway level 3 : Configure Extensible Key Management Using Azure Key Vault]( VishnuGupthanSQLPowershellDBA from SQLServerCentral In Level 1 of this Stairway series, we discussed how to configure TDE in a user database using a Database Master Key (DMK) and a certificate. Level 2 showed the steps to restore the backup of this database on another instance. In this level, we will explain how to configure TDE in SQL Server with […] [When SQL Server Performance goes Bad: the Fill Factor and Excessive Fragmentation]( Additional Articles from Redgate Phil Factor on the fill factor, pages splits and index fragmentation, and how SQL Monitor can help you decide if a custom fill factor for certain indexes might help alleviate performance issues. [Solving Oracle Performance Issues in 4 Easy Steps]( Additional Articles from MSSQLTips.com Join us for a look at how SolarWinds Database Performance Analyzer (DPA) provides detailed performance data for Oracle environments, allowing DBAs to solve complex performance issues in less than four steps. [Free eBook: Understanding SQL Server Concurrency]( Press Release from Redgate When you can’t get to your data because another application has it locked, a thorough knowledge of SQL Server concurrency will give you the confidence to decide what to do. From the SQL Server Central Blogs - [SQL 2019 UDF (User defined function) inlining]( Daniel Janik from Confessions of a Microsoft Addict SQL Server 2019 brings a lot of great new features. Many are introduced by the IQP (Intelligent Query Processing) features and greatly improve query performance. Some time ago I... From the SQL Server Central Blogs - [OPENJSON and CROSS APPLY]( Martin Catherall from measure twice, cut once. OPENJSON is pretty central to manipulating JSON documents in T-SQL. As we’ve seen, we can use a default schema that will return metadata about the JSON document or we...   Question of the Day Today's question (by Steve Jones - SSC Editor):  More Means in R I have a dataframe in R, with a number of columns in it. This was created from a csv file. sales <- read.csv2(file="d:\\downloads\\sales_data.csv", sep=",") I run this code with the colmeans() function: colMeans(sales[sapply(sales, is.numeric)]) What does this do? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Greg Larsen) SQL Server 2019 Feature Which feature introduced with SQL Server 2019 improves the performance of large analytic queries? Answer: Batch mode on rowstore Explanation: The answer is 2, batch mode on rowstore. This feature decreases the CPU requirements of large analytic queries by processing the query in batches. Prior to 2019, batch mode worked only when a columnstore index was involved with the query. Answer 1 is incorrect because this feature provides a better cardinality estimate for table variables and works for any type of query that involves a table variable. Answer 3 is incorrect since this is a feature specifically for getting an and would not help most analytic queries. Answer 4 is incorrect because this feature improves the performance of many queries that involve a user defined function. References: - Intelligent Query Processing - - Reduce CPU in large analytics queries - [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 [SSMS: How to save multiple resultsets from same query into 1 result or 1 file?]( - I am submitting an official request for DBA of certain servers to run a script on those servers and give me back the resultset. However the script (the actual script is 3 times larger than the attached and returns 20 more results) returns multiple results, so a DBA will not be copy/pasting each resultset into […] [Availability Groups for failover AND mirror]( - I've got a 2-node failover AG setup. I want to add a 3rd node to be used as a reporting server. That could be read-only, but has to be available all the time. For reasons beyond my control, I can't use replication. The database has no primary keys indexes (3rd-party software design). So, it all […] SQL Server 2017 - Development [SQL Server Fragmentation- Defragment]( - The table consist of around 2 billion record on an average there are 1000K inserts happening daily , we see some slowness in recent days , so while checking the table we saw some fragmentation information. Above is the query i used against my dataware database and observed some fragmentation in the index for some […] SQL Server 2016 - Administration [Creating DB diagram]( - Hi, I was planning to create a DB diagram of a user DB to better understand the relationship however when I right-click on database diagram (please expand the user DB to see this option) I see an alert window saying 'This database does not have one or more of the support objects required to use […] [SQL server 2016 SP2 error]( - Hi All,  am trying to update my 2016 SQL server instance to the SP2. the current version is Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64) Jun 15 2019 19:20:12 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 18363: ) SP2 setup file : SQLServer2016-KB4549825-x64.exe for some […] [SQL Trace Not Capturing Database Events....EXEC, INSERT, UPDATE...]( - I want to see what Stored Procedures are run when users enter data in a 3rd party product we use. I would also like to see INSERTS & UPDATES to tables. I set up a trace that I have used elsewhere with success, but in this case, it's not capturing any of the events I […] SQL Server 2016 - Development and T-SQL [Help with T-SQL]( - I have a web interface for managing tool rentals for our chapter members. I have a display page that will list all of the tools we have and to flag those tools that are currently being rented out to include the expected return date. The important thing is to know which are available and which […] [How to optimise the table performanace (90 lacs records)]( - Hi, I have a table with 5000 users along with 6 years of attendance data. If I want to retrieve user data from the table it takes time to load. Table Structure Usercode, Rosterdate, Shift code, AttendanceCode Case 1 If i want to retrieve the shift information from 1-jun-2020 to 10-jun-2020 for 5000 users, would […] [Help in Date spans for continuoes and regular dates spans]( - Hello all, I would like to take input scripts and generate output data as given below. output is expected as if member is continuously enrolled without any gap in enrollment get min and max and if there is enrollment gap leave as is. for any given member there are no multiple spans just leave as […] Administration - SQL Server 2014 [OPENQUERY giving wrong result on linked server]( - I have setup linked servers on my SQL Server and choose Oracle as a linked server. SOMETIMES, When I run the query below under the SA username, I get different result. However when I run under my own username I get correct result. I Can't understand this anomaly. The below query returns only one row. The difference occurs in […] [Sum of all Memory Clerks and Total Server Memory (KB)]( - I am trying to understand the memory management of SQL Server. I have a Windows Server 2019 on virtual environment with 64 GB of RAM. I also have SQL Server 2019 installed with Max Server Memory 59776 MB. By using the following query I found the list of all memory clerks: SELECT [type] AS [ClerkType], […] SQL Server 2012 - T-SQL [? ON Parsing an XML Field]( - Hi, I know I've asked this before and got a good link , but can't figure out how to parse this XML field. I would usually parse something like Data.Value('Data/.../..) But not sure how to parse out say "Case" from the field below(SqlParameters)? SQL Server 2019 - Administration [How do I count the objects (tables,views,indexes) for a database?]( - Is there any simple script which I can use to count the user objects in a database? Reason is, I'm planning to do an upgrade from SQL 2008R2 to SQL 2019 & my plan is to take a count of the objects on the old SQL and 2019 so I can compare and see if […] Anything that is NOT about SQL! [SSC Traffic Declining ?]( - Are there fewer posts & replies on SSC these days ? It seems like it. Or are they just spread over more sub forums of the various SQL versions ? COVID-19 Pandemic [Daily Coping 16 Jun 2020]( - Today’s tip is to find the joy in music today: sing, play, dance, or listen.   [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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -