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

High Prices for High Security

I was excited to see the new Secure Enclave technology come to Always Encrypted (AE) in SQL Server 2019. I've thought that the way Microsoft implemented the AE technology in SQL Server 2016 was a start and a good step forward, but it had too many restrictions. Kind of like Availability Groups in 2012 and In-Memory technology in 2014. A good idea, but not really ready for most of us to use.

One of the biggest restrictions in SQL Server 2016, in my opinion, was the inability to run range queries on encrypted data. These are the greater than, less than, and LIKE items that many of us need to use in applications. This made sense, since the server doesn't know how to decrypt the data, but many applications need these queries.

That goes away in SQL Server 2019 with Secure Enclaves. I'm looking forward to testing and working with this, but there's one interesting limitation for me. The SQL Server computer that runs the secure enclave must meet one of these requirements. It has to run on Windows 10 or Windows 2019 Server – Datacenter Edition. There also has to be an HGS server for attestation, but this can be a WS2019 Standard Edition.

Is it a big deal? I don’t know. Windows Server 2019 lists  with Datacenter at $6155 and Standard at $972. That means it will cost me $5,183 for Always Encrypted on my SQL Server. Not a bad price for the encryption and additional security. An HSM appliance goes for quite a bit. One in Azure is $5k + $4.85/hour and most of the enterprise appliances I've priced at $10k+. And you need two.

In some sense, maybe this doesn't matter. The cost of $5k isn't much, especially when you consider the downside of not using encryption and having a data breech. You don't need this on all SQL Servers, just those that need AE, and even then you can run SQL Server Standard Edition. The cost of core licensing is likely going to already be quite a bit and this is just like having to pay for a few more cores. Is this an impediment for your organization?

At first this seemed like a burden, but the more I look at it, $5k isn't a lot for encryption on your server. I hope this doesn't deter organizations from adopting AE, and more importantly, I hope Microsoft continues to invest in this technology. I'd like to see multi-certificate support and the ability to easily revoke access for a compromised system while I deal with any security issues. There are other things, and we'll see how this evolves in the future.

Steve Jones - SSC Editor

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

 
Redgate SQL Provision
  Featured Contents

Connect to Sybase with a Linked Server

Ondrej Liptak-260768 from SQLServerCentral

Learn how to create a Linked Server to a Sybase database instance.

Enhance your database development by using coding standards

Additional Articles from Redgate

Establishing coding standards can help remove blockers to understanding code, improving quality and reducing maintenance. Faris Hilmi explains how having consistently formatted code plays a larger role in database development and how Redgate tools can best equip you for the task.

Bloor 2019 Market Update on Test Data Management

Additional Articles from Redgate

Discover the latest market trends and assess your approach to test data management. For example, the Bloor analyst recognizes “…an increased emphasis on test data provisioning, as opposed to merely test data management.” The report also covers key capabilities offered by vendors in the market, including Redgate.

From the SQL Server Central Blogs - Microsoft Build event announcements

James Serra from SQLServerCentral

Another Microsoft event and another bunch of exciting announcements.  At the Microsoft Build event last week, the major announcements in the data platform and AI space were: Machine Learning...

From the SQL Server Central Blogs - Find identical duplicate indexes

SQLPals from SQLServerCentral

The purpose of this query is to find identical duplicate non-clustered indexes i.e. indexes on same table with same columns in same order. If you have a need to...

 

  Question of the Day

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

 

Adding Messages

On my SQL Server 2017 server, I want to add a new user-defined error message to the instance. What command should I use that is not deprecated?

Think you know the answer? Click here, and find out if you are right.

 

Redgate SQL Provision
 

 

  Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Getting the Winners

I have created this data frame in R:

> champs <- data.frame(c('2019', '2018', '2017'), c('Virginia', 'Villanova', 'North Carolina'), c('Texas Tech', 'Michigan', 'Gonzaga'))
> champs
  c..2019....2018....2017.. c..Virginia....Villanova....North.Carolina.. c..Texas.Tech....Michigan....Gonzaga..
1                      2019                                     Virginia                             Texas Tech
2                      2018                                    Villanova                               Michigan
3                      2017                               North Carolina                                Gonzaga
> names(champs) <- c('Years', 1, 2)

I want to access the winner column, and try this:

champs$1

I get an error. What should I do?

Answer: champs$`1`

Explanation: The backticks around the number will escape this if you have a nonstandard name. You could also use

champs[2]

to get the champions. Ref: https://stackoverflow.com/posts/1316322/revisions

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
SQL Server Instance Failure - Monitoring - Hi We've had an issue this morning (and still ongoing) that our main SQL Server has failed. However, we at the beginning there was no indication that the failure was in SQL. Eventually when digging deeper, we noticed that our two instances on our main VM for SQL, both SQL instances were in a stopped […]
prod and stage comparison - Hi all, I can see that identical request is executing much longer on stage comparing to prod. How do I check if it is CPU, memory or hard disk bound? Please advise.
SQL Server 2017 - Development
SQL lookup tables - Hello, I am very new to SQL and would really appreciate some help.   I am using Microsoft SQL Server 2017 to design a database for matching product features to products.   To do this I have created the following 3 tables   Products Spec Lookup The lookup table joins the ID's from the product […]
SQL Server 2016 - Administration
SQL Agent. Run T-SQL in scheduled job against a DB in a different SQL instance. - Well, that's what I'd like to do! This is so I can keep the scheduling config and T-SQL on one admin SQL Server, whilst the databases reside on different servers. This'd mean I can email using SQL Mail from my admin instance too. I'm seeing a few things that might be of interest, in Linked […]
SQL Server Transactional Replication - We are uplifting one of our servers to SQL Server 2016. We are going a side by side install.  The machine we are doing the uplift is the Publisher. The distribution and subscriber server are not being touched. this is a push not a pull. So I'm thinking I will have to reinitialize the snapshot. […]
Linked server error 18456 - Hello Team, Iam getting the below error while creating the Linked server with windows authentication. Msg 18456, Level 14, State 1, Line 88 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.    
SQL Server 2016 - Development and T-SQL
PDF stored in table as varbinary max, how to convert and display it SSRS report - Hi , I have a table where  10000+ pdf stored in table as varbinary(max) How to convert them with T SQL  to display in SSRS Report as images? Thank you      
Indexing VARCHAR. Does Length Matter ? - We have some TXT file imports into SQL tables. As a default, the columns are set to varchar(500) to match the incoming definition. However, the data will never be 500. Is there a penalty to indexing a VARCHAR(500) column, if the max length will be 50, compared to defining a VARCHAR(50) column and migrating the […]
SQL 2012 - General
Table type - I just want to validate my use of a custom type. I'm doing a query on that I need to pass an unknown number of string values to. I created a custom table type, with a script that looks like, What length should I make the field, for it to be of use to others? […]
How to save collapse sections of the query window and save it like that - Hi, My manager would like it that when he collapses certain parts of the query and saves it, it will open that way; and not have it all expanded. Is there a way to do this? Thank you
How to list counts hour by hour - Hi, Below I have a sample of the output, and below that is a sample of how the data is stored in the database. I am trying to figure out how the queried this to get the calls per hour, I looked online but could not find anything exactly like this maybe ai am sating […]
SQL Server 2012 - T-SQL
sql server 2012 data column used for 7 different languages - In a sql server 2012 database, I want to store some messages in the database for the following languages: Karen, Arabic, Nepali, Burmese, and Kiswahili. This would be in a column called stringtemplate that is currently defined as varchar(max). **note: this column currently stores messages in English and Spanish. Can you tell me if I […]
SQL Azure - Administration
Azure data sync - Hi, I've automated the schema change propagation to the member database using this approach: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-update-sync-schema The schema changes go through fine. I've run : alter table mytable add blah varchar(20) go update mytable  set blah = 'blah' go I could see the column 'blah' on the member database, but the update did not go through. […]
SQLServerCentral.com Website Issues
What happened to marking as answer? - Hadn't noticed this before, as I don't I hadn't posted any topics that are asking a question, but I noticed that the mark as answer button has gone. Guessing that it wasn't used often and so removed, but just checking in case I was going crazy.
Job Postings
DBA vacancy at Sheet Music Direct - Milwaukee, WI - SheetMusicDirect.com, a division of Hal Leonard, is the world’s premier destination for digital sheet music. Offering musicians around the world access to instantly downloadable sheet music and all-access streaming sheet music via its popular subscription, PASS, SheetMusicDirect.com is looking for a talented and motivated SQL Server DBA to join us in revolutionizing the digital sheet […]
 

 

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

 

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