Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Rediscover your favourite music that really lifts your spirits.

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.

Encryption Libraries

One of the ways we work to implement better security is through the use of encryption. I remember the early days of PGP, VPNs adding end to end encryption, IPSec becoming available, and more. I was excited to see column level encryption come in SQL Server 2005, but it was a bit complex to implement and had issues. The evolution over time with TDE and Always Encrypted were welcome additions, but we can do better.

Handling encryption in the database can provide some protection, but really, we'd like to see an end to end solution, that ensures data is protected on the wire as well as in the db. Microsoft does a lot of research, and released a Simple Encrypted Arithmetic Library (SEAL) as open source code. This is a C++ library that developers can use to protect their communications. Last year they also released a .NET wrapper to make it easier for a wide variety of developers to use.

There is a lot more to deal with when handling encrypted at scale. Indexing and search operations are difficult and complex, and can require lots of resources. I think that the Always Encrypted in SQL Server 2019 with Secure Enclaves is a good evolution, but it's complex to set up, tooling is poor, and maybe more disconcerting, there are potential vulnerabilities with secure enclaves on Intel chips.

Certainly a combination of developers being able to use something like SEAL and a linkage with a secure enclave on the server for set-based operations might be something we can look forward to in the future. I hope so, as security continues to be a challenge for many of us.

I'd also like to see more software that is built to ensure users can't access huge amounts of data, and perhaps easier ways to control the export of large amounts of data. We do need this capability, but we also want very few people, or no people and only a process, with rights to do so.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
 Featured Contents

Iterating Over Calculated Tables using Power Query

Gouri Shankar from SQLServerCentral

Problem Many of us are already aware of the loopin...

The Staging Phase of Deployment

Additional Articles from SQLServerCentral

Despite some humorous examples of deployments gone wrong, failures are not funny. William Brewer explains why staging is so important and how it can help avoid the types of disasters he recalls in this article.

Database DevOps is gaining popularity

Additional Articles from SQLServerCentral

Fourty-six percent of the State of Database DevOps survey respondents advised they are performing some form of deployment automation. This figure has risen year on year as the conversation of DevOps in Database development continues, and the benefits become more apparent. If you are interested in more insight, you can download a copy of the 2020 State of Database DevOps report here.

Download now

From the SQL Server Central Blogs - Azure Analysis Services – Processing Hot & Cold Data using Tabular model partitions

Joyful Craftsmen from Joyful Craftsmen Blog

Introduction Recently have been working on a “Proof of Concept” task where I wanted to test the capabilities of Azure Analysis Services Tabular model when loading data from different...

From the SQL Server Central Blogs - Extracting data from Common Data Services (Microsoft Dynamics 365) using Azure Data Factory

Rayis Imayev from Data Adventures

(2020-Mar-30) Little did I know that I can use Azure Data Factory (ADF) to extract data from the Common Data Services (CDS). This data platform enables you to securely store...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

The ordering of the data

I have this code: 
DECLARE @Test TABLE
(testString VARCHAR(1) COLLATE SQL_Latin1_General_CP1_CS_AS);
INSERT INTO @Test
    (testString)
VALUES
    ('a')
  , ('b')
  , ('c')
  , ('A')
  , ('B')
  , ('C');
Then I run this in the same batch. 
SELECT testString
 FROM @Test
 WHERE testString LIKE '[a-B]';
What is returned (each letter in the answer would be on a separate row)?

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)

Linux Support

Which of these Linux platforms is not supported for SQL Server 2019?

Answer: CENTOS

Explanation: CentOS is the distribution based on Red Hat. This not supported, however. Ref:

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
SSIS Deployment Issue - Hello: Problem:  Cannot deploy SSIS packages to AZURE VM/SQL Server Instance (or execute them via SSDT/VS) from a local laptop using a SQL Login. FYI:  Project Deployment Model is used. Background:  We moved our development SQL Server instance to an AZURE VM, and would like to avoid giving every developer RDP permissions access to the server. […]
query optimization -    
SQL Server 2017 - Development
count by week by start/end date - I'm trying to show a count by week but I am unsure of how to find dates between years? How do I show the Year, Week, and count for the example below? This ID_NUM should show 1 for every week for the entire year. I've shown what I've tried so far below. Thanks.   I'd […]
update by quantity - I'm writing an update statement that uses a quantity column to determine how many rows get updated.  I can accomplish this using RBAR, but performance is unusable and I can't figure out how to do this as a set operation. I have a sales table that contains a row for each individual item.  In the […]
SQL Server 2016 - Administration
Convert SQL HA (2 node) to Standalone - Hi, We have some of the SQL Server AlwaysOn environment (NonPROD) where we do not require alwayson any longer. Instead a standalone system will be sufficient. Without provisioning a new environment (standalone), how we can use the existing HA to a standalone and remove one of the other nodes? Does Microsoft has any clearly defined […]
Shrinking _log file for live database - I have a live database in SQL2016 (in Production, in use) that I need to shrink its l_log file which has grown to over 250GB. Can I use this type of command block safely?: ALTER DATABASE ExampleDB SET RECOVERY SIMPLE DBCC SHRINKFILE('ExampleDB_log', 0, TRUNCATEONLY) ALTER DATABASE ExampleDB SET RECOVERY FULL
Logical file name mismatch - Hi All I've picked up that the logical name for my transaction log is different between sys.database_files and sys.master_files. I know how to fix this up. My question is, how did it get that way? How can I recreate this issue? SQL Server 2016     Thanks    
SSRS report font settings - Dear team, Hope all are safe and doing well, We have a Windows Server 2016 STD server with SSRS installed. Question is where can I download Arial Narrow & Myriad Pro To use in SSRS reports? Is this available on Windows server 2016? User is having some issues with fonts while exporting reports to PDF on our […]
SQL Server 2016 - Development and T-SQL
Stored Proc slow after switching to Replication? - Hi First, let me apologize in advance for my ignorance on this... We are in the process of switching our reporting DB from backup and restore to transactional replication. I am testing stored procedures and noticing some SP's run much slower in transactional replication. I've done some googling and have tried the three below, but […]
Development - SQL Server 2014
Trouble with Finding Dependencies between Tables using Keys - In the past I could rely on the View Dependencies feature in SQL Server Mgt Studio to show me all objects that an Object depends on or that the object is dependent on. I don't know when this changed but it no longer works reliably.  The View Dependencies works for some parent/Child tables but not […]
How to find Last Record with multiple results - In Access, there is a LAST function, which simply allows you to do a group by, then select last, and you get the last record for each of your results.  This does not exist in SQL. I have a query I am running that will show me a full list of all Part ID's in […]
SQL Server 2012 - T-SQL
Finding a range of date within a range of date - I have a project that requires to display a list of patients within a specific period of dates but having problems building my statement the following is the sample data set. CaseNo     DateAdmitted   DateDischarge 1                 12/01/2019        12/02/2019 2                12/01/2019         12/03/2019 3                12/02/2019        NULL                   - not yet discharge Scenario 1:  getting patients from 12/02/2019 to 12/03/2019 […]
SQL Server 2019 - Administration
Unexcpted erorrs restoring large tables - Hi Please excuse me if this is a basic question, I have been given a .sql file which has a script to create and insert data into a table. The script around 100,000 records in it. Most of the content is in the form 'Insert into table ....' 'insert into table ...' GO Insert into […]
SQL Server 2019 - Development
Arithmetic overflow error converting expression to data type nvarchar. - Is there a way to find the field this error is being generated by? I am trying to bring data from an AS400 to SQL.  I am attempting to convert 3 fields to date formats. I have a staging table that converts them to nvarchar(10) then when inserting into the final table I do IDATE(field) […]
SQL Server Newbies
Force job to fail based on condition of IF - I am using SQL Server 2005 and I need to force a job to fail based on a condition of an IF statement. This is an overview what my code is doing: IF (SELECT '1') = 1 BEGIN PRINT 'Yes' END ELSE BEGIN PRINT 'No' END The above isn't my exact code but this gives […]
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -