Transparent Data Encryption Using Certificates and EKM - Level 1 of the Stairway to TDE (2020-04-29)

 SQLServerCentral Newsletter for April 29, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Transparent Data Encryption Using Certificates and EKM - Level 1 of the Stairway to TDE]( - [SQL Server 2019 New Performance Features]( - [Free eBook: Defensive Database Programming]( - [From the SQL Server Central Blogs - IRL #7: Test Restores and CheckDB]( - [From the SQL Server Central Blogs - Audit Logons with Extended Events]( Question of the Day - [The R Select]( The Voice of the DBA  Where AI Shines There is plenty of controversy about where Artificial Intelligence (AI) or Machine Learning (ML) actually work well. Lots of organizations are trying to implement some type of AI/ML solution to either save on labor, or find new insights in their data. The success of these projects seems to be somewhat hit and miss. A few hits noted in the media, and quite a few more misses told anecdotally among friends. There is a place where I think AI/ML is shining, however, and that's in the medical field. I think image recognition is one of those spaces where ML really does a great job, avoiding the fatigue, boredom, and other factors that lead to inconsistency among humans. Radiology has seen strides using ML, first as a verification tool for diagnosis, and later as the primary tool, reducing the load for humans who only look at those cases warranting additional review. During this Covid-19 pandemic, there is [some triage being done in the UK](, based on ML reading lung scans. With some initial resistance all over the world, the rising caseload has made AI/ML systems more attractive to all sorts of health professionals that are struggling to get their work done in a timely manner. There are certainly concerns over rapid adoption, and likely more and more companies will try to sell systems that aren't as well tested. While there is a lot of potential here, we need good vetting of AI/ML techniques, both by humans, through trials over time, and then later to help train new generations of humans. AI/ML can help, but it can't replace humans completely, and we certainly can't use the skills that humans will use to verify the machine's work. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Transparent Data Encryption Using Certificates and EKM - Level 1 of the Stairway to TDE]( VishnuGupthanSQLPowershellDBA from SQLServerCentral Introduction Transparent Data Encryption (TDE) is one of the key security features available in SQL Server from SQL Server 2008 onwards. Using this feature, the ‘data at rest’ in the physical files for the database, are protected from unauthorized access if the files are copied, or the physical media is stolen. TDE is available with […] [SQL Server 2019 New Performance Features]( Additional Articles from SQLServerCentral SQL Server 2019 has introduced several new features that offer improved performance. The optimizer continues to evolve and get smarter. [Free eBook: Defensive Database Programming]( Press Release from Redgate Resilient T-SQL code is code that is designed to last, and to be safely reused by others. The goal of defensive database programming, the goal of this book, is to help you to produce resilient T-SQL code that robustly and gracefully handles cases of unintended use, and is resilient to common changes to the database environment. From the SQL Server Central Blogs - [IRL #7: Test Restores and CheckDB]( Kevin3NF from Dallas DBAs My Pluralsight course for new SQL Server DBAs Problem: My client asked me for a way to automate test restores and CheckDB either ad hoc or on a schedule,... From the SQL Server Central Blogs - [Audit Logons with Extended Events]( SQLRNNR from SQL RNNR This article shows how to audit the logon events for SQL Server 2012 and beyond through the use of XEvents. Related Posts: Life Support 2008 - Audit Logons July 17, 2019...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The R Select I have loaded the dplyr library in R. I have a dataset, called counties, with census data. I run the head() function, and see this: > head(counties) # A tibble: 6 x 40 census_id state county region metro population men women hispanic white black 1 1001 Alab~ Autau~ South Metro 55221 26745 28476 2.6 75.8 18.5 2 1003 Alab~ Baldw~ South Metro 195121 95314 99807 4.5 83.1 9.5 3 1005 Alab~ Barbo~ South Nonm~ 26932 14497 12435 4.6 46.2 46.7 4 1007 Alab~ Bibb South Metro 22604 12073 10531 2.2 74.5 21.4 5 1009 Alab~ Blount South Metro 57710 28512 29198 8.6 87.9 1.5 6 1011 Alab~ Bullo~ South Nonm~ 10678 5660 5018 4.4 22.2 70.7 # ... with 29 more variables: native , asian , pacific , # citizens , income , income_err , income_per_cap , # income_per_cap_err , poverty , child_poverty , # professional , service , office , construction , # production , drive , carpool , transit , walk , # other_transp , work_at_home , mean_commute , employed , # private_work , public_work , self_employed , family_work , # unemployment , land_area I want to select just the state, county, and population values for the first few rows. Which of these statements will work? 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) Tempdb Default Size I've installed SQL Server 2017 with all the defaults (clicking next, next, next where I can). I took the recommendations for tempdb. This gave me 8 data files. What is the total size of my tempdb data space by default? Answer: 64MB Explanation: The default size for data files in tempdb is 8MB, so with 8 files, this is 64MB. Ref: tempdb database - [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 - Development [Find percentage of column]( - Hi, I'm trying to find the percentage of a column but I'm not sure how to get the percentage correct. The attachment is how I'm getting the "Have" part. I've tried adding this column but when I do a group by rollup it does the percentage on the overall total and not by county total. […] [Slow Trigger performance when big batches]( - I have an update trigger that inserts into auditing tables. We had no problem until someone decides to update over 1 million records... (That's my bad. I didn't think it would be a problem when developing). Now facing reality, I need to find a solution... This is a simplified version of the real thing -- […] [How to Join two tables with conditions and insert into new table]( - Hi,  I have two tables, both the tables have 4 common columns, First Column: LinkingID Other four columns(Name,Department,City). In few instances, we will get LinkingID row values, and few instances we don't have LinkingID column, so we left to use other 3 common columns), I want to see the best way to handle this […] SQL Server 2016 - Development and T-SQL [Parent-Child Hierarchy - Finding the root]( - Hello, what I am trying to do is to find the root for every id. declare @tab table (id nvarchar(3), Father_id nvarchar(3), Flag_Root bit ) insert into @tab values ('A1','A1',1) insert into @tab values ('A2','A1',0) insert into @tab values ('A3','A2',0) insert into @tab values ('B1','B1',1) insert into @tab values ('B2','B4',0) insert into @tab values ('B3','B1',0) […] [Update duplicate values to 0]( - Hi, I need some help in my query. Want to change numbers to 0 if the row is a duplicate records based on uniqueness of 2 columns Condition 1 ) ID and Market Condition 2 ) ID,PL For the Same ID and Market , I want to retain one row of original values in MarketComplete […] [SQL - queries]( - Hi, Based upon the attached table relationships, could somebody please help in the SQL queries for the following questions - Question 1 - Select time elapsed in days between the case date for cases created in the second half of 2010 and its first quote. If no quote has been created yet find elapsed time […] Administration - SQL Server 2014 [Backup and log files]( - I am a newby here. My data was destroyed and I was able to find another backup on a drive. I do have a complete database from January. However, I have two additional files that do not show with a file extension. One for each of the following months. I think these are log files […] SQL Server 2012 - T-SQL [Encryption option in 2012]( - Hi Experts , Our database have sensitive information like SSN , Tax_id etc. There are many such tables and don't want to use the column encryption and decryption by opening the symmetric key etc. Is there any other way to encrypt the data in sql server 2012.  [Trying to find the date of a day for each month]( - Hi, I have been looking allover and trying different things for this but cannot see hot to do this. In an VB.Net application the users will select 1st, 2nd, 3rd, or 4th. from one dropdown box and the day from another. this is a payment application, where the users enter when an customer is going […] SQL Server 2019 - Development [Count the number of Code Types with a Subquery]( - I need help with a query to select against the following table: CREATE TABLE dbo.Claims ( [Organization Name] nvarchar(64) NOT NULL, [POS Code] nvarchar(8) NOT NULL ); GO -- In the real dataset there are many organizations -- ORG 1 Inserts INSERT INTO dbo.claims ([Organization Name], [POS Code]) VALUES ('ORG 1','02:B:1'); INSERT INTO dbo.claims ([Organization […] [Conversion failed when converting the varchar value ' ' to data type int]( - Hello, What I am trying to do is to replace the null values that I initially populated my table with using the update statement. I receive the error: 'Conversion failed when converting the varchar value 'THISWILLBETHESTRING' to data type int'. Keep in mind that in the update statement the TEST.ORDERCODE comes from the same table […] [EF Code-First]( - What is the DBA world consensus on using this to implement databases for an application? An application developer presented this as the "future of EF" but I do not know much about "EF Code-First" but it looks like its coding the database as classes in a .NET app and then applying it to a database. […] SQL Server 2008 Administration [Merge Replication problem after table deletions]( - Hi Overnight we deleted 4 million rows out of on table on the subscriber database with merge replication enabled on it. The deletion took 2 hours but only 1.6 million records replicated across. There was a query timeout error in replication monitor. We restarted replication and this query runs and eventually times out, even when […] SSRS 2014 [Extra Row in Report Save as CSV]( - Using SSRS 2014, I'm getting an extra row at the bottom of the file when I save a report as a CSV file. Is there something I can add to the reportserver.config file? Thank you  SSDT [Cannot browse cube from within Visual Studio -The \'Tabular View\' feature is not]( - Hi expects I am getting the below error when i am trying to browse a cube from visual studio cube browser. "Errors related to feature availability and configuration: The 'Tabular View' feature is not included in the '64 Bit Standard' edition of Analysis Services. (Microsoft SQL Server 2014 Analysis Services)" Why is it trying to […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -