Key Rotation in TDE (2019-08-29)

 SQLServerCentral Newsletter for August 29, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Key Rotation in TDE]( - [Reporting Services Basics: Parameters]( - [SQL in the City Streamed- the birthday edition]( - [From the SQL Server Central Blogs - More PowerShell Remoting coverage in dbatools]( - [From the SQL Server Central Blogs - New product: Azure Data Share]( Question of the Day - [Filtering the Audit]( Featured Script - [A DateRange Table Valued function]( [Redgate SQL Monitor]( The Voice of the DBA  Grounded Technology has brought some challenges to travel. There is [the bomb threat](, which I have no way to gauge how much of a potential problem this is on a daily basis. Certainly security at airports has changed over the years, and there is better scanning technology, but would anyone really be able to detect a bomb in a bag? Do we check all bags? I doubt anyone does, but perhaps I'm wrong. Either way, I hope this isn't anything I need to worry about in my travels. I do carry electronics, usually a laptop though sometimes two, along with the various supporting plugs and wires for charging it and my mobile. There was a time when I thought about getting [a BRIX](sr_1_2?hvadid=78271596063175%26hvbmt=be%26hvdev=c%26hvqmt=e%26keywords=gigabyte%2Bbrix%26qid=1566337044%26s=electronics%26sr=1-2) for demos, but with [the requirements that security might make me turn it on](, I decided not to get one. These days, with all the cloud options, I'm less worried about carrying a large amount of hardware, but I still need a laptop. When I saw a story about [laptops being banned from flights](, I wondered what I'd do. In this case, Macbooks are supposedly being banned if they have a certain battery issue. This came about quickly, and it's likely that there were some businesspeople in the middle of a trip when the ban was announced. That might have prevented them from flying, which would be quite a hassle. I remember when [the Note was banned from some flights](. I never met anyone that was inconvenienced, and likely plenty of people flew with their devices hidden in bags, so perhaps it wasn't a big deal. I wonder about two things here. One, are we building new devices and technology that aren't designed well from a safety standpoint? Most of us don't have large servers in our offices anymore, so we might not care, but our other devices, which we use to get work done, perhaps might not be robust. In that case, I hope that businesses might have programs to help employees quickly replace devices if there are issues. Being on call and not having a device would be a nightmare. The other item is one I wonder about both for software and hardware. Do we make it easy enough to know what version of a device we have? I've owned Macbooks, and trying to determine what model you have is a chore. I can't imagine that TSA or other security agencies could actually a) determine if you had a banned device, and b) know if you've had it repaired. I'd hate to think that companies will just build devices without making it easy to understand which generation of a device you own. This can already be a problem in not only computers, but autos, where changes might be implemented in the middle of a model year protection. With the move to cloud and other rented software, are we going to have issues here? Azure SQL Database hasn't changed the version in some time, despite there being changes to the code. Maybe it doesn't matter, especially if old features don't disappear. It does seem like something that we ought to do a better job of tracking, especially as it can be easy for us to have a model, but tag on a version number to easily allow a user to discern one from the other. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate SQL Compare](   Featured Contents [Key Rotation in TDE]( Emmitt Albright from SQLServerCentral Transparent Data Encryption (TDE) has been around for a long time. It first appeared in SQL Server 2008, and after a rocky start with some bugs, it has become a regularly used feature for many organizations. While not perfect, it does provide some protection and auditors like to see physical protection features being used. It's […] [Reporting Services Basics: Parameters]( Additional Articles from SimpleTalk Kathi Kellenberger continues her series on SSRS. In this article you learn hose to use parameters that let your report users control how the reports are filtered. [SQL in the City Streamed- the birthday edition]( Additional Articles from Redgate Wednesday September 4 14.00-19.00 BST/ 08.00-13.00 Central - Register for our free virtual learning event, to enjoy educational and entertaining sessions from Microsoft MVPs and celebrate 20 years of Redgate. From the SQL Server Central Blogs - [More PowerShell Remoting coverage in dbatools]( Cláudio Silva from Cláudio Silva Starting on dbatools version 1.0.31 we introduced better coverage for commands that make use of PowerShell Remoting. Which commands? Commands like Get-DbaComputerCertificate, Get-DbaPrivilege, Get-DbaClientAlias, just to mention a few... From the SQL Server Central Blogs - [New product: Azure Data Share]( James Serra from James Serra's Blog A brand new product by Microsoft called Azure Data Share was recently announced. It is in public preview. To explain the product in short, any data which resides in...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Filtering the Audit I have created a Database Audit Specification for tracking activity in my database. I choose the SELECT Audit Action type. Which of the following are ways that I can filter the Object Class to which the audit action applies to? 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) Splitting the time I have a time string that is formatted like this: myString = '28/08/2019' I have loaded the time module and want to change this to the struct_time format, the same as returned by time.localtime(). import time time.localtime() time.struct_time(tm_year=2019, tm_mon=8, tm_mday=22, tm_hour=8, tm_min=18, tm_sec=20, tm_wday=3, tm_yday=234, tm_isdst=1) What method should I use? Answer: time.strptime(myString, "%d/%m/%Y") Explanation: The strptime() method from the time module will return a struct_time format. Ref: strptime() - [Discuss this question and answer on the forums](  Featured Script [A DateRange Table Valued function]( Jonathan AC Roberts from SQLServerCentral The function is an inline table-valued function (or sometimes called a parameterised view) so it can be used just like a view or a table but needs parameters. It generates a single-column table of datetime2 with rows differing by any multiple of any of the dateparts that SQL Server provides. IF OBJECT_ID('[dbo].[DateRange]','IF') IS NULL BEGIN PRINT 'CREATE FUNCTION [dbo].[DateRange]' EXEC ('CREATE FUNCTION [dbo].[DateRange] () RETURNS TABLE AS RETURN SELECT 1 X') END GO /*-- ********************************************************************** -- FUNCTION: DateRange -- Returns a table of datetime values based on the parameters -- Parameters: -- @StartDate :Start date of the series -- @EndDate :End date of the series -- @DatePart :The time unit for @interval -- ns : nanoseconds -- mcs : microseconds -- ms : milliseconds -- ss : seconds -- mi : minutes -- hh : hours -- dd : days -- ww : weeks -- mm : months -- qq : quarters -- yy : years -- @Interval :The number of dateparts between each value returned -- -- Sample Calls: -- SELECT * FROM [dbo].[DateRange]('2011-01-01 12:24:35', '2011-02-01 12:24:35', 'ss', 2) -- SELECT COUNT(*) FROM [dbo].[DateRange]('2018-01-01 00:00:00', '2018-01-25 20:31:23.646', 'ms', default) -- SELECT * FROM [dbo].[DateRange]('2011-01-01', '2012-02-03', default, default) -- SELECT * FROM [dbo].[DateRange]('2012-02-03', '2011-01-01', 'dd', 7) -- SELECT DATEDIFF(ns,'2018-01-01 00:00:00.000', value),Value,* FROM [dbo].[DateRange]('2018-01-01 00:00:00.000', '2018-01-01 00:00:00.00001', 'ns', 100) -- **********************************************************************/ ALTER FUNCTION [dbo].[DateRange] ( @StartDate datetime2, @EndDate datetime2, @DatePart nvarchar(3)='dd', @Interval int=1 ) RETURNS TABLE AS RETURN WITH A(A) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A)), B(RowNum) AS (SELECT TOP(ABS(CASE @DatePart WHEN 'ns' THEN DATEDIFF(ns, @EndDate, @StartDate)/@Interval WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate, @StartDate)/@Interval WHEN 'ms' THEN DATEDIFF(ms, @EndDate, @StartDate)/@Interval WHEN 'ss' THEN DATEDIFF(ss, @EndDate, @StartDate)/@Interval WHEN 'mi' THEN DATEDIFF(mi, @EndDate, @StartDate)/@Interval WHEN 'hh' THEN DATEDIFF(hh, @EndDate, @StartDate)/@Interval WHEN 'dd' THEN DATEDIFF(dd, @EndDate, @StartDate)/@Interval WHEN 'ww' THEN DATEDIFF(ww, @EndDate, @StartDate)/@Interval WHEN 'mm' THEN DATEDIFF(mm, @EndDate, @StartDate)/@Interval WHEN 'qq' THEN DATEDIFF(qq, @EndDate, @StartDate)/@Interval WHEN 'yy' THEN DATEDIFF(yy, @EndDate, @StartDate)/@Interval ELSE DATEDIFF(dd, IIF(@StartDate < @EndDate, @StartDate, @EndDate), IIF(@StartDate < @EndDate, @EndDate, @StartDate))/@Interval END) + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 FROM A A, A B, A C, A D, A E, A F, A G, A H) -- A maximum of 16^8 (or 2^32) rows could be returned from this inline tally SELECT CASE @DatePart WHEN 'ns' THEN DATEADD(ns, T.AddAmount, @StartDate) WHEN 'mcs' THEN DATEADD(mcs,T.AddAmount, @StartDate) WHEN 'ms' THEN DATEADD(ms, T.AddAmount, @StartDate) WHEN 'ss' THEN DATEADD(ss, T.AddAmount, @StartDate) WHEN 'mi' THEN DATEADD(mi, T.AddAmount, @StartDate) WHEN 'hh' THEN DATEADD(hh, T.AddAmount, @StartDate) WHEN 'dd' THEN DATEADD(dd, T.AddAmount, @StartDate) WHEN 'ww' THEN DATEADD(ww, T.AddAmount, @StartDate) WHEN 'mm' THEN DATEADD(mm, T.AddAmount, @StartDate) WHEN 'qq' THEN DATEADD(qq, T.AddAmount, @StartDate) WHEN 'yy' THEN DATEADD(yy, T.AddAmount, @StartDate) ELSE DATEADD(dd, T.AddAmount, @StartDate) END [Value] FROM B CROSS APPLY(VALUES (IIF(@StartDate<@EndDate, @interval*RowNum, @interval*-RowNum))) T(AddAmount) GO [More »](  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 [SQL Login Issues between replicated VM's]( - The issue is that this server (BFS-DEV02) in a virtual clone of another server (BFS-DEV01). Within SQL Server, windows login BFS-DEV02\Administrator is being viewed by SQL Server AS BFS-DEV01\Administrator. Here are some screen shots shows what I mean about the logins…I believe what we ultimately want is to remove BFS-DEV01\Administrator and make it BFS-DEV02\Administrator.  […] [Always on]( - Planing to go with SQL Server 2017/2019 for Biztalk latest version of 2019/2020. So i would like to go with Always as DR option. Do you guys have seen any issues setting up always on for Biztalk DB server ? If yes, what are the issues have you seen for setting up always on with […] SQL Server 2016 - Administration [Extended events]( - I'm trying to teach myself ExtendedEvents (trying to move from profiler). I've attached a configuration and the output. I've got 'statement' selected in event fields and 'sql text' selected in global fields but the output doesn't show the sql statement that has been run and I'm not sure why?  [AOAG Secondary Checkdb]( - I'm looking into setting up a AOAG with a DR server which will have the option as NO for readable secondary. Under my understanding will mean I will not have to license the DR box. As best practise (Brent Ozar advice) indicates CHECKDB should be preformed all on nodes as they reside on different disks. […] [SQL Server 2016 Archive Strategy advice.]( - Greetings. I’m after some advice. I’m helping a colleague with a SQL Server 2016 database that is getting quite large and they are considering an archive strategy. The database essentially stores data from a number of sensors on machines that are building stuff. Readings are taken every 1, 5 and 10 seconds from a variety […] [Multiple tables created in CDC]( - Hello Team, I have enabled CDC for a particular database in SqlServer and enabled it for multiple tables. I see duplicate tables have been created for each of the table that I have enabled CDC. Is it by design or have I done anything wrong in configuration. Thanks [2 core machine with 4 Tempdb files]( - Just curious if there is a performance hit by having 4 tempdb files with a 2 core machine? running SQL 2016 (SP2 CU7 +GDR)  [User datareader access problem]( - I went to give a user (via AD account) datareader access to a number of databases. Everything seemed alright, her read permissions show up in server level security, and in each of the assigned databases. However she still cannot view them, getting a 'not accessible' message. If I try viewing tables in these databases 'execute […] SQL Server 2016 - Development and T-SQL [sp_executesql with multiple parameters]( - I'm trying to write a stored procedure that will select the entire table if all parameters are null, otherwise select the table filtered by the non-null parameters using an AND condition. Here is some skeleton code: DECLARE @ProjectID VARCHAR(100) = 300; DECLARE @ProjectName VARCHAR(100) = 'Some project name'; DECLARE @sql NVARCHAR(4000) = '' SET @sql […] Administration - SQL Server 2014 [Data source error - Security protocol not supported]( - Hi Experts, I have a report that fetches data from a cube .I have an issue with a data source which is returning an error "Security protocol not supported".This is only happening in SSRS reporting server after deployment but in visual studio the report is working perfect.I have been working on this without no success. […] Development - SQL Server 2014 [How to copy rows from the same table and update the ID column?]( - I appreciate it is utterly trivial question, and yet... When you have a small amount of columns, there is a simple solution, sort of INSERT INTO myTable SELECT MAX(table_id) + 1, column2, column3 FROM myTable WHERE table_id IN (SELECT list of table id's to be replicated); alas I have circa 200 columns, so I don't want […] SQL 2012 - General [Slower performance on SQL.]( - We are moving from one SQL server to newer SQL with better hardware. Both systems have an identical configuration. We won't like to change any configuration in a newer system. Currently, we are doing some tests to measure throughput, I/O, CPU, and a few others. Ideally, a newer system should work much faster than the […] [Export to CSV]( - Hi,  i am trying to export a table into csv, and it should be comma delimited with doublequotes around each value like this: "value1","value2","value3" this works fine, but one of the columns includes doublequotes as part of the value, so when its exported to csv, it breaks it into multiple columns. Anyone knows the […] SQLServerCentral.com Website Issues [Website search]( - Does SQLServerCentral have any advanced search features, or can anyone share any tips? Does everyone just use google with site:sqlservercentral.com? Administration [Different Execution plan and performance issues in SQL 2017 compare to SQL 2012]( - We have recently migrated from SQL Server 2012 Express to SQL Server 2017 Express and observed some of queries having performance issues in SQL Server 2017. We have compared execution plan and found that both versions generate different execution plan. As Microsoft claims SQL Server 2017 is faster than earlier version of SQL server but […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -