SQL SERVER – Transparent Data Encryption and Frequently Asked Questions

In the recent past, I have been traveling to Delhi to meet customers and also attend a few conferences which my company has been sponsoring. These travel experiences give me an opportunity to meet folks who read my blog regularly as a face-to-face interaction. Most of these interactions mean I get to spend time with really smart people who quiz me for which I get back to my blog for answers. Last week, when I was at a conference, one DBA walked up to me and said – “Hey Pinal, I am a DBA from a reputed banking company. We are in the process of deploying TDE for one of our databases which is in SQL Server 2008 R2. I had a few doubts, can I ask you?” Well, as simple as this interaction went, the whole conversation spanned for close to 30 mins and I am doing a summary of the conversation in this blog for your reference.

When does TDE encrypt the DB?

Once Transparent Data Encryption is enabled by issuing the “Alter Database” command, SQL Server performs basic checks such as Edition Check, Read-only Filegroups, and presence of DEK etc. Once the checks are complete, the command returns immediately with success. This does not mean that the database encryption is complete. Since Encryption is done in the I/O path, all the data pages that are already written to the disk prior to enabling the Encryption have to be read into the memory and then written back to the disk after encrypting the page. This process is also referred to as “Encryption scan”. This task is carried out by Background processes (system SPIDS). The encryption scan, which runs asynchronously to the DDL, takes a shared lock on the database. All normal operations that do not conflict with these locks can proceed without being blocked.

You mentioned READONLY DB, tell me more?

Transparent data Encryption does not work on a database that contains any filegroups that are marked Read-Only or any files that are marked as Read-only. Alter Database fails with an error message (33118) clearly indicating the reason for the failure. Users can enable read-only property on the filegroups once the encryption scan is completed. However, no DDL related to TDE (DEK change) can be run on the database until the read-only property is removed.

What happens to TLog files?

Encryption works differently on a Transaction Log and is complicated. Since Transaction Log is designed to be Write-Once fail safe, TDE does not attempt to Encrypt the contents of the Log file that were written to the disk already. Similarly, the log header cannot be re-written because of this write-once principle so there is no guarantee that log records written to the log even after TDE is enabled will be encrypted. The smallest unit of encryption for log files is a virtual log file (VLF). So either an entire virtual log file (VLF) is encrypted or it’s not. Also the entire VLF is encrypted with the same key. When encryption is turned on for a database, the next time the log manager moves to a new VLF, this new VLF will be encrypted. So there is no deterministic order between when data pages are encrypted by the scan vs. when the log is encrypted.

I thought these were some of the interesting conversations I have had in the recent past that are worth a mention to share. If you have used TDE in your environment, do let me know. I would love to know your experiences in working with it. Is there any catch that you want me to explain next time? Write it over as comments and would love to explore more.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SPID is KILLED/ROLLBACK state. What to Do Next?

I always recall my fun days after talking to my friends and blog readers. Recently killed/rollback discussion came back when my close DBA friend called me for help. Our discussion was worth blogging.

If you are a SQL DBA or developer, I am sure you must have seen something like below:

killed 01 SQL SERVER – SPID is KILLED/ROLLBACK state. What to Do Next?

The first question which would come to mind would be “what is killed/rollback command?” The answer is very simple – sometimes, DBA/Developer use “KILL” command to end processes that appear in a hung state. Once that happen, if SQL knows how to rollback that work, it would start “real” rollback process and would undo the work done. There are situations where SQL doesn’t know and has no control of work done of the work done and it has no control to roll back the work. These are the cases where its stays in KILLED/ROLLBACK state forever.

How would you decide which action to take? It completely depends on the action done by the SPID which was killed. There are only a few things, which you can do:

  1. Have patience and wait for rollback to finish. Some large operations may take a long time to rollback. Such SPID should show a change in CPU, Memory and IO column in size. sys.sysprocesses DMV (query in the next step).
  2. If the SPID is not showing any change in CPU, Memory and IO column then it is doing nothing. Here is the query which can tell us more about the SPID and from where the connection might have originated.

SELECT spid
,kpid
,login_time
,last_batch
,status
,hostname
,nt_username
,loginame
,hostprocess
,cpu
,memusage
,physical_io
FROM sys.sysprocesses
WHERE cmd = 'KILLED/ROLLBACK'

If the process is originated from different machine, then using task manager and doing end process on that machine might help. This decision has to be taken very carefully as it might be a process of a 3rd party application.

After figuring out the SPID we can run the DBCC INPUTBUFFER (<SPID>) to know what is the batch running under this session.

  1. Restart SQL Service if it was killed and doing nothing.

Can you reproduce it? The simplest way is to use xp_cmdshell and open notepad.exe using below command. DON’T DO THIS ON PRODUCTION

xp_cmdshell 'notepad.exe'

Now, it you kill this SPID it would go to KILLED/ROLLBACK state.

In summary, it is important to understand the cause. Killed/Rollback SPIDs are waiting for an event that will never happen. It could be a real “rollback” situation where SPID is rolling back and trying to get the database into a consistent state. By restarting the SQL Server service, you are postponing that rollback (or roll forward) work to the recovery step that runs before the database becomes available.

Have you ever come across such situation?

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #034 – What is the Difference Between Distinct and Group By

Question: What is the difference between DISTINCT and GROUP BY?

Answer:

A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates, then use DISTINCT. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster.

Example of DISTINCT:

SELECT DISTINCT Employee, Rank
FROM Employees

Example of GROUP BY:

SELECT Employee, Rank
FROM Employees
GROUP BY Employee, Rank

Example of GROUP BY with aggregate function:

SELECT Employee, Rank, COUNT(*) EmployeeCount
FROM Employees
GROUP BY Employee, Rank

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Remove All Characters From a String Using T-SQL

In this post, How to Remove All Characters From a String Using T-SQL? we have seen How to Remove All Characters From a String Using T-SQL. But it only removes characters from the string. What if the string has some other special characters? Here is the method that handles this

SET NOCOUNT ON
DECLARE
@loop INT
DECLARE
@str VARCHAR(8000), @output VARCHAR(8000)
SELECT @str = 'ab123ce23,4f$e', @output=''
SET @loop = 1
WHILE @loop < LEN(@str)
BEGIN
SET
@output=@output+CASE WHEN ASCII(SUBSTRING(@str,@loop,1)) BETWEEN 48 AND 57 THEN SUBSTRING(@str,@loop,1) ELSE '' END
SET
@loop = @loop + 1
END
SELECT
@output

Now here is the result:

123234

The idea here is instead of finding and removing non numerics, find only numbers and concatenate them. You can use if the ASCII value of a string is between 48 and 57 (which is a digit from 0 to 9). If it is in the range, it is a digit then concatenate it. This way we can remove anything which is not a number.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Different Methods to Extract Scale Part From Decimal Number

One of my blog readers asked me “Is there a way to extract only the number after the decimal point?”. For example, in decimal number 45.90, 45 is the precision and 90 is the scale value.

There are several methods to get this done

Let us create this dataset

CREATE TABLE #Numbers (value DECIMAL(16, 3));
INSERT INTO #Numbers (value)
VALUES (100.00);
INSERT INTO #Numbers (value)
VALUES (- 23.89);
INSERT INTO #Numbers (value)
VALUES (390.077);
INSERT INTO #Numbers (value)
VALUES (12.87);
INSERT INTO #Numbers (value)
VALUES (390.1);

Method 1 : truncate scale part and subtract it from the original number

SELECT VALUE , ABS(VALUE-CAST(VALUE AS INT)) AS DECIMAL_ONLY FROM #NUMBERS

Method 2 : Convert the number into a string and get the number after the decimal point

SELECT VALUE , SUBSTRING(STR(VALUE,16,3),CHARINDEX('.',STR(VALUE,16,3))+1,LEN(VALUE)) AS DECIMAL_ONLY FROM #NUMBERS

Note that str function is a string representation of a number. The second parameter 3 restrict the number to have maximum of 3 digits and identifying the position of a dot, we will be able to extract only scale part

Method 3 : Use pARSENAME function

SELECT VALUE , PARSENAME(VALUE,1) AS DECIMAL_ONLY FROM #NUMBERS

Parsename split the data based on the dot and parameter value 1 is used to extract the right most part which is scale in the decimal number

The result of the able queries are

decimal SQL SERVER   Different Methods to Extract Scale Part From Decimal Number

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Are Power Options Slowing You Down? – Notes from the Field #095

[Note from Pinal]: This is a 95th episode of Notes from the Fields series. When it is about tuning SQL Server, we always look at the configuration of the SQL Server. However, there are few settings of the operating system can also impact the performance of the SQL Server. I recently asked John Sterrett a very simple question – “Which is the one option from the OS, would you check first when you are tuning SQL Server?”

JohnSterrett SQL SERVER   Are Power Options Slowing You Down?   Notes from the Field #095

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very interesting story about how a simple setting of the OS impacts performance of SQL Server. Read the experience of John in his own words.


Doing several SQL Server health checks I have noticed that the operating system settings can slow you down. Today, we are going to focus on power options. By default many companies have the balanced power plan as the default configured option.  You will see that it is also the recommended setting. This is recommended to save power usage which reduces which reduces the amount of money required to power your servers.  Please keep in mind that your virtualization software or BIOS might also have Power Option settings that should be verified and adjusted. Today, we are focusing on the Windows Operating System.

notes 95 SQL SERVER   Are Power Options Slowing You Down?   Notes from the Field #095

The balanced power option is great for most servers where CPU power is not mission critical to the applications living on your server. SQL Server is an exception and you will want to get as much as you can out of your CPU Power.  In fact Enterprise edition licensing is now licensed by core so you want to make sure you are getting the most out of your SQL Server licensing.

How Do We Script Out The Change?

While you can load the power option GUI and manually change the setting I like to automate as much as possible.  The following script could be executed via PowerShell or Command Prompt to make sure High Performance power option is enabled.

Powercfg -SETACTIVE SCHEME_MIN

Conclusion

SQL Server places a different set of demands on the operating system. OS default settings are not always optimal and should be reviewed.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Script: Finding queries without JOIN Predicates

I have been in developer’s shoes for close to 5+ years before reinventing myself to do things that made me even better in coding. It is a tough challenge these days being a developer. The life around you and the technology is changing so rapidly that we seldom get time to take a pause from our busy schedule and learn something that has come new. This constant struggle to build new competencies, read documentations of newly released products is something I do religiously.

This blog can never come this far if I had not been doing this till date. The more I read and learn, the opportunities to share become better. To carry a baggage of legacy from time to time is inevitable and I see folks in the industry hold onto some of the practices even without knowing why they are doing so.

I am writing this code blog below because during a code review at one of the software companies, I saw a code that was not adhering to ANSI standards. I quickly jumped onto it and asked – why? The developer gave me the usual long answer of no time, release pressures and lastly – what is wrong, it works right?

I have respect for such pressures as you build your product, but writing a code block that performs and is easier to maintain is one of the critical step. After I explaining, I showed the warning in the execution plan that shows we have done something that can cause poor performance.

I quickly came up with this script to help the team identify all the queries that were written in this fashion and requested them to change the same at the earliest.

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT  st.TEXT,
qp.query_plan
FROM    (
SELECT  TOP 50 *
FROM    sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes( '//p:RelOp/p:Warnings[(@NoJoinPredicate[.="1"])]' ) AS q(n) ;

The warning is because, poorly performing queries are throwing warnings that there are operators without join predicates. That means somewhere in your query, you’ve logically written a CROSS JOIN without thinking about it. Maybe you are using an old style join syntax (SELECT * FROM a, b, c) or maybe you really did intend to write this code. Either way, a lot of rows can be produced as a result of this operation. It’s best to make sure this was your intent.

The only way out here is to re-write your query so you are always applying a join predicate. This is a script blog. If you run this in your environment, do you get anything? Find out and keep me posted.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Database Mirroring login attempt failed – Connection handshake failed. An OS call failed: (8009030c)

The best way I have learnt about building SQL Server AlwaysOn Availability Group is by contacting my close friends who have written the only book on this topic. It is not common that I ping them for such technical queries but that is what friendship is made off. Recently, while preparing a demo for AlwaysOn Availability Group, I ran into interesting issue and was able to fix it as well.

First, let us look at the configuration.

SERVER1              –              Primary Replica
SERVER2              –              Secondary Replica

They are synchronous and configured for automatic failover. I have shutdown SERVER1 and as expected, SERVER2 became primary. As soon as SERVER1 came back, it became secondary but databases were NOT synchronizing. That was a surprise to me as I was expecting to see the data movement from SERVER2 to SERVER1 now.

As always, my troubleshooting checklist has ERRORLOG is my first point of investigation. If you have not seen earlier, you can use below blog to know how to find ERRORLOG

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

On SERVER1, I found below messages multiple times.

2015-08-24 01:24:18.480 Logon        Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed). State 67.'.  [CLIENT: 10.0.0.2]

There are some interesting learning/finding from above messages.

  1. Database Mirroring and AlwaysOn uses same endpoint and that is why we are seeing Database Mirroring in error message even if I am using AlwaysOn Availability Group.
  2. IP Address in the message belong to SERVER2 (which is primary as of now).

This means that SERVER2 is not able to talk to SERVER1 via Mirroring port. I searched on hex code 8009030c is not helping much because it means – The logon attempt failed, which is already there is the same message.

Steps which I tried after searching on internet (and they didn’t help)

  1. I logged in to SERVER2 via remote desktop and tried making connection to SERVER1 via Management Studio and it was working fine.
  2. Restart endpoints on both replicas. No joy!

USE MASTER
GO
ALTER ENDPOINT hadr_endpoint STATE = STOPPED;
GO
ALTER ENDPOINT hadr_endpoint STATE = STARTED;
GO

  1. PING from and to each other. This also worked well which means DNS was not an issue.
  2. Verified that SQL was running under domain account and account was not locked.

None of the above helped. Taking last hint, Then I started backtracking to recall what I did with my SQL Service account. It did not take long to realize that I had change service account domain password in recent past. I rarely use AlwaysOn Availability Group so I changed the service account password on SERVER1 but forgot on SERVER2.

When I was looking at errorlog, I also found below

SQL Server failed to communicate with filter daemon launch service  (Windows error: The service did not start due to a logon failure.). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.

Above message came when SQL was trying to start full-text service automatically. Since I didn’t change the password for full-text service, I was seeing above message.

Solution (which worked for me): Correct the Service account password for SQL Server related service via SQL Server Configuration Manager.

handshake 01 SQL SERVER   Database Mirroring login attempt failed   Connection handshake failed. An OS call failed: (8009030c)

handshake 02 SQL SERVER   Database Mirroring login attempt failed   Connection handshake failed. An OS call failed: (8009030c)

When I corrected service account password, the databases were synchronized and I was happy again.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix @@ServerName Property Value When Incorrect

Our software systems are funny and sometimes give us strange values. I don’t quite venture out trying to fiddle around with system level properties on my servers, but having said that, this query had come from one of my colleagues who was working on his SQL Server instance and he found something strange.

When working on the server and when an error occurs, I generally ask for the @@Version. This is a standard practice for me to do it because I don’t have to ask about the version installed, what service pack, CU update has been done on the server etc. This output would give me all this information in black and white.

error SQL SERVER   Fix @@ServerName Property Value When IncorrectAs I was so used to this kind of output, my friend said that the value that is returned by his @@Servername was NULL. This was such a surprise and I wanted to see it in real life. He was kind enough to show me via Skype that this was indeed the case. This got me thinking and I needed to solve this trivial problem. I had gone to bed early as it was a lazy Sunday and I couldn’t put myself to sleep as this was plaguing my mind – I needed a solution. Finally, after an hour and getting some help from my SQL buddies, I found the deal to crack this problem. Strange but I am sure many of you have encountered this somewhere and were looking for a solution.

The @@ServerName refers to the local server that SQL Server is running on.  This property is set during installation, but there are instances where @@ServerName may be NULL or may not be correct.

Running the following will show the current TSQL value for this property:

SELECT @@SERVERNAME;
GO

To Fix the Problem

If the @@ServerName property is NULL, running the following will fix the issue- but only after you restart the SQL Server instance. Yep, fixing this requires an outage.

EXEC sp_addserver '<LocalServerName>', local;
GO

If the @@ServerName property is incorrect, run the following to correct the issue:

EXEC sp_dropserver 'old_name';
GO
EXEC sp_addserver 'new_name', 'local';
GO

Though the solution to this was so simple, I did find it strange about this behavior. I am yet to come to terms this can every happen. If you ever encountered this error on your server, can you please let us know how you landed to this situation. I am eager to learn the cause for this rather than just looking at solution to the problem.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #033 – How to Invalidate Procedure Cache of SQL Server?

icon clean Interview Question of the Week #033   How to Invalidate Procedure Cache of SQL Server?Question: How to invalidate all the stored procedure plans in SQL Server?

Answer: DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Remember not to run this on a production server without proper planning as once you run this DBCC command it will invalidate all the stored procedure plans. This means if the user runs this during pick time, many Stored Procedures will execute at the same time and create a resource bottleneck.

Reference: Pinal Dave (http://blog.sqlauthority.com)