Stairway to SQL Server Security Level 9: Transparent Data Encryption (2023-03-15)

SQLServerCentral Newsletter for March 15, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to SQL Server Security Level 9: Transparent Data Encryption]( - [Recording What’s Changed when Running Oracle Migrations with Flyway]( - [Optimizing Queries in MySQL: Optimizing Reads]( - [From the SQL Server Central Blogs - Tales From The Field Weekly Wrap Up for the Week of 02-27-2023 & Some Changes]( - [From the SQL Server Central Blogs - Book Review – SQL Query Design Patterns and Best Practices]( - [SQL Server Execution Plans, Third Edition, by Grant Fritchey]( Question of the Day - [Decoding Set Options]( The Voice of the DBA  Daily Coping Tip If you find yourself rushing, make an effort to slow down 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. Better Writing This editorial was originally published on Jan 20, 2014. It is being re-run as Steve is at SQL Bits. Like it or not the majority of the ways we communicate in technology is through the typed word. Email, Twitter, Word, these are the methods by which we argue a point or ask a question. Our communication skills form impressions with our clients and coworkers. All too often, however, we also set ourselves apart from others with our writing. Not because we excel at it, but because so often we make mistakes that stand out. Many technical people aren't good at getting their point across because they either don't focus on their topic, or they make lots of silly writing mistakes. Grammatical mistakes don't have much to do with your technical ability, but they seem to give you less credibility. I ran across [this blog from Brian Kelley]( that talks about setting himself apart because of his writing skills. As [one of our authors here at SQLServerCentral](, I concur. I rarely need to edit anything Brian sends in. In this piece, he also links to [an article from CopyBlogger on silly mistakes](. Writing is a skill, and it's one that you can learn to improve. However it takes practice. It's one of the main reasons [I recommend that technical people blog](. You will improve your writing skills (if you work at it), and possibly impress someone else who could offer you a job. Learn to write better. It doesn't take a lot of time or effort, and it will pay off for you throughout your career. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to SQL Server Security]( [Stairway to SQL Server Security Level 9: Transparent Data Encryption]( Don Kiely from SQLServerCentral.com Even an otherwise well-secured database is susceptible to attack if an attacker is able to get access to the disk files that comprise the database. Cell-level encryption can protect some of the data, but for complete protection against this kind of attack it is necessary to encrypt the files and not just the data. That is exactly what Transparent Data Encryption (TDE) does, and in this stairway level you'll learn what TDE does, how it works, and how to make use of it to protect your database files. [External Article]( [Recording What’s Changed when Running Oracle Migrations with Flyway]( Additional Articles from Redgate This article uses Flyway and a PowerShell framework to generate a simple JSON model for each new version of an Oracle database, and then compares models to get a high-level 'narrative' of which tables, views or procedures were changed by each Flyway migration. [External Article]( [Optimizing Queries in MySQL: Optimizing Reads]( Additional Articles from SimpleTalk In this blog, we’re going to walk you through how to solve the issues posed by that concern. At the end of the article, we’ll also walk you through some DDL operations to load test data. From the SQL Server Central Blogs - [Tales From The Field Weekly Wrap Up for the Week of 02-27-2023 & Some Changes]( SQLBalls from SQLBalls  Hello Dear Reader! Change is the one constant in life and over here at Tales from the Field we've been hard at work making some changes. Over the last 5... [Blog Post]( From the SQL Server Central Blogs - [Book Review – SQL Query Design Patterns and Best Practices]( Koen Verbeeck from Koen Verbeeck Disclaimer: The publisher – Packt – sent me a free copy of the book for the intended purpose of a review. At the time of writing, the book is... [SQL Server Execution Plans eBook, Third Edition, by Grant Fritchey]( [SQL Server Execution Plans, Third Edition, by Grant Fritchey]( Grant Fritchey from SQLServerCentral Every Database Administrator, developer, report writer, and anyone else who writes T-SQL to access SQL Server data, must understand how to read and interpret execution plans. This book leads you right from the basics of capturing plans, through how to interrupt them in their various forms, graphical or XML, and then how to use the information you find there to diagnose the most common causes of poor query performance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Decoding Set Options The @@options variable stores the options set for a user. If this were set to the value 4, then all options would be off, except for the SET ANSI_WARNINGS option. If I want to use the new SQL Server 2022 function, GET_BIT() to decode this value, what should I run? 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) The Managed Instance Link The Managed Instance Link feature was announced in Nov 2022. This is designed to do what with SQL Server 2017 and 2019? (as of Feb 2023) Answer: One way replication from SQL Server to Azure SQL Managed Instance Explanation: The Managed Instance link does two things (as of Jan 2023): - One way replication from SQL Server 2017 and 2019 - DR with failover and fail back with SQL Server 2022 Ref: Overview of the Managed Instance Link Feature - [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 [Transaction has no commit error]( - All, I have an unusual scenario. A developer sent me some code that has a BEGIN / COMMIT and a rollback right before the commit. It's all wrapped in a reasonable TRY...CATCH. The script builds some dynamic SQL INSERT statements using OPEN_QUERY, dynamic because the WHERE clause depends on some variables. The INSERT statements they […] SQL Server 2016 - Development and T-SQL [How to remove special chars?]( - Hi everyone, So I have a xml file from which I am trying to import data into my sql tables. I noticed there are some special characters inside the file due to which I am unable to import data properly. Look at the below screenshot: Can anyone tell me what are there square boxes and […] SQL Server 2019 - Administration [How to resolve Login differences between Primary and Secondary cluster nodes]( - I'm a bit rusty with SQL clustering so please bear with me, thanks. I have a 2-server availabilty group. The other day it failed over during a patching reboot, and a Linked Server connection no longer worked while it was failed over to secondary. Initiating a manual failover back to the original primary resolved the […] [Azure SQL - Import Data-tier application]( - Hello! Where can I find log to see how long it took for Import Data-tier application to create new database from the bacpac file? Or is there any script that can be used to get the completion time?  Thanks in advance.  [Cannot bulk load because the file could not be opened error]( - This is a weird one. My user has two NAS shares. QC and Test and two SQL Servers, QC and Test. She has bulk admin permissions on her account and the NAS share (according to the windows group) also has her permissions. She's running the following code: select * FROM OPENROWSET( BULK '//My/nas/Share/QC/MyFile', SINGLE_BLOB) AS […] [SSMS is filling up our C: drive -- installed on E:]( - Good day. I didn't know where else to post this, so forgive my ignorance if this isn't the place. I have a situation that involves a single Windows Server with SSMS installed that has the only permissions to hit another organization for our Data Warehousing team. There may be as many as 4 BIDS plus […] [Trigger on update when changed only desired XML tag.]( - Hello! I have a task to configure for the Skype for business database logging of user status changes (Available/Away/Offline/etc). i found source for this data in XML field with content like: 15500 [To get a list of un-used columns in all tables in a database]( - I am looking to get a list of un-used columns in all tables in a database. I would like the output to look like something like this. DatabaseName, SchemaName, TableName, ColumnName, etc If anyone has a handy script or any link, please share. Thanks. SQL Server 2019 - Development [SELECT ROWS ON TIME CRITERIA]( - Hi, I would like to retrieve only the rows after 23:59:59 but I am getting the output as shown in the picture. Please advise how to write a correct where clause SELECT ID, STARTDATE, STARTTIME, ENDTIME FROM MACHINELOG WHERE (STARTTIME > CAST('00:00:00' as time(0)) AND ENDTIME < CAST('09:00:00' as time)) ORDER BY ID DESC [SELECT ROWS ON TIME CRITERIA]( - Hi, I would like to retrieve only the rows after 23:59:59 but I am getting the output as shown in the picture. Please advise how to write a correct where clause SELECT ID, STARTDATE, STARTTIME, ENDTIME FROM MACHINELOG WHERE (STARTTIME > CAST('00:00:00' as time(0)) AND ENDTIME < CAST('09:00:00' as time)) ORDER BY ID DESC [Iterate and assign Query help]( - Table:SchoolTeacher TeacherName Zip City State ------------ ---- ------ ------ Sam 12345 NJ NewYork Pat 12345 NJ NewYork Peter 12588 Spring MD School: SchoolLocationID Zip City State -------------- ------- ----- ----- 105 12345 NV NewYork 105 12345 NJ NewYork 106 12345 NJ NewYork 107 12588 Spring MD BusinessRule: 1) Match should be done on Zipcodes if […] [SQL Query to display having only values]( - I am having below -mentioned table1 and I would like to display it as shown in the below table Can someone help me with the query Thanks in advance [SELECT with computed values]( - From a huge and ever growing table (tbl_Values) I need to select all records which conform to rules defined in another table (tbl_Rules). Each record in tbl_Values has a time_stamp (datetime) and a fvalue (float); the rules defined in tbl_Rules have a WeekDay (int), a HourOfDay (int), a MinValue (float), a MaxValue (float). Records from […] SQL Server 2022 - Development [Case and When Query]( - Hi All, I want to rewrite the below query using the Case and When statement Query 1 update [SF_Title_Update] set [Title_Proper]='Human Resource Management System Manager' from [SF_Title_Update] where ( title like '%Human%' and title like '%Resource%' and title like '%Management%' or title like '%HRMS%' ) and (job_level='Manager Level' ) and( [Title_Proper]=' ' or [Title_Proper] is […] [Dynamic Query]( - Hi All, I have to generate a dynamic query for the below mentioned keywords to update them in Title_Update column Keywords Recruitment Employee Engagement Performance Management Training and Development Compensation and Benefits Human Resource Diversity and Inclusion Also it should have the below mentioned Job Level Chief Level Director Level Head Level Manager Level Managing […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -