Creating a Testing Hadoop Cluster

Once I wrote a post on Hortonworks HDP Sandbox (Looking for a Hadoop Cluster for testing? Let's configure Hortonworks HDP Sanbox) which can be used for learning and testing many Hadoop related sub projects. Now the new version is available and bit different, since I did a session on Hive yesterday (https://www.meetup.com/LKBigData/events/236812354/) and used it for demonstration, thought to make a new post it; How to get the Sandbox configured in your machine.

Hortonworks Sanbox is available as a Virtual Machine. It is indeed a portable Apache Hadoop 2.5 with many sub projects and can be downloaded as a Sandbox for VMWARE, VIRTUALBOX or DOCKER. In order to download it, you need to visit http://hortonworks.com/products/sandbox.

I will make the post over Oracle VirtualBox. If you download the Sandbox for VirtualBox, follow below steps for adding the downloaded Sandbox for VirtualBox.

1. Open VirtualBox and click on File menu -> Import Appliance menu.

2. Browse the downloaded HDP_2.5_virtualbox.ova file and click on Next.

3. Change the configuration as you need. You may increase the CPU and Memory. If you need to change, double click on the default value and change.

4. That's all. Click on Import. It will add Sandbox to your virtual environment. This is what you should see finally.

Now you can start the virtual machine. Select it and click on Start or just double click on it. 

Once it is started, you can log in by pressing Alt+F5 and using root as user and hadoop as the password.

However, not like the older version, we can actually work with this without log in to this. As the image says, we can use the Browser for start a session on this using given URL, which is http://127.0.0.1:8888/.

You can open the dashboard that can be used to find out various information related components running inside but before that you need to get the password for each components. Here is the way of accessing some of the components.

1. Click on Quick Links for opening ADVANCE HDP QUICK LINKS.

2. Move the mouse over Ambari and note the user id and password.

3. Go back to main page and click on Dashboard.

4. Use the noted user id and password and click on Sign In.

5. This shows all running services and metric related to Hadoop components. If you need to see HDFS or need to open a window for executing Hive queries, click on the button next to Admin and select the menu item need.

6. This is how you see the Files View.

7. This is how you see the Hive View.

As you see, it is very easy to set it up and start using Hadoop. Of course, you have no way of setting many nodes but this is good for learning and testing.

SQL Server Identity value suddenly became 1000+

I noticed that values of a column related to newly inserted records which is an identity property enabled column in one of the tables, suddenly started from 1000+. The last values before this sudden jump were 27, 28, 29 but suddenly it started from 1000+ values. How did it happen without resetting the seed or increment of the identity property?

This is not a bug or something related to SQL Server 2016. It a result of a new implementation related to SQL Server 2012. As per my readings, since SQL Server 2012, it caches 1000 values for an identity property if the data type is int and it caches 10,000 values for big int and numeric. If an unexpected restart happens (in my case, I believe that an update caused the restart), there is a possibility to lose the cache, hence it starts from next available value. This is normal and you do not need to worry if you experience it.

Enabling Trace flag 272, you can get the old behavior or you can use Sequence instead. Read this thread for understanding these two options:  http://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server

SQL Server - Adding an Authenticator when encrypting data

Among multiple methods given for securing data stored in the SQL Server database, even though the latest one which is Always Encrypted is available, we still use Keys. Passphrases and Certificates for encrypting data. When keys such as Symmetric Keys or Asymmetric Keys, or Passphrases are used for encrypting data, an additional parameter can be supplied which is called Authenticator. Since I recently used this for one of my database solutions, thought to make a note on it.

What is the usage of Authenticator? Why we should use it. Let's take an example and understand with it.

The following code creates a database and a table that holds Customers. The Security Code of the customer will be encrypted.

-- Create a database
CREATE DATABASE Sales;
GO

-- Connect with newly cerated database
USE Sales;
GO

-- Create a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO 

-- Create a certificate for protecting the our key 
CREATE CERTIFICATE SalesCertificate WITH SUBJECT = 'Sales Certificate';
GO

-- Create the key for encrypting data
-- Note that the created certificate protects it.
CREATE SYMMETRIC KEY SalesKey
WITH ALGORITHM = AES_128  
ENCRYPTION BY CERTIFICATE SalesCertificate;
GO

-- Creating a table that holds customers
-- Note the Securiry Code Column, it is varbinary 
-- because code will be encrypted and stored
CREATE TABLE dbo.Customer
(
 CustomerId int identity(1,1) PRIMARY KEY
 , Name varchar(100) NOT NULL
 , SecurityCode varbinary(256) NOT NULL
);
GO

Let's insert some records.

OPEN SYMMETRIC KEY SalesKey DECRYPTION BY CERTIFICATE SalesCertificate ;
GO

INSERT INTO dbo.Customer
 (Name, SecurityCode)
VALUES
 ('Dinesh', ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS005XZ'))

INSERT INTO dbo.Customer
 (Name, SecurityCode)
VALUES
 ('Yeshan', ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS02ANX3'))

Once inserted, data will be looked like below;

And if I try to retrieve records, I need to decrypt encrypted values;

So far so good. Now let's try understand the usage of Authenticator. Assume that Yeshan needs to access some Securables that can be accessed only by Dinesh through an application. For that, all Yeshan needs is, log in to the application using Dinesh's Security Code. Since he does not know Dinesh's Security Code, one way of accessing the application using Dinesh's account is, replacing the Dinesh's code with his code. Let's see whether it is possible.

The following code updates Dinesh's account with Yeshan's code. And as you see, it gets updated and now Yeshan can use Dinesh account as he knows the code.

This should not be allowed and even if it is possible, what if we make sure that encrypted code cannot be replaced like that. That is what we can do with the Authenticator.

Look at the following code. It passes two additional values for encrypting. The third one which is 1 says that this has an Authenticator. The forth parameter is the data from which to derive an Authenticator.

OPEN SYMMETRIC KEY SalesKey DECRYPTION BY CERTIFICATE SalesCertificate ;
GO

-- Update Security codes with CustomerId as the Authenticator
UPDATE dbo.Customer
 SET SecurityCode = ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS005XZ', 1, Convert(varbinary(256), CustomerId))
WHERE CustomerId = 1;

UPDATE dbo.Customer
 SET SecurityCode = ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS02ANX3', 1, Convert(varbinary(256), CustomerId))
WHERE CustomerId = 2;

Just like the previous code, values are encrypted now. However, if Yeshan tried to do the same, see the result;

As you see, even though Dinesh's code has been replaced with Yeshan's code, when try to decrypt value of Dinesh, it results null because Authenticator is different. This is the usage of the Authenticator.

Note that we used CustomerId as the Authenticator but you can use something else, something uncommon as the Authenticator to make it more secured and avoid malicious activities like this.

Incorrect syntax near 'TRIPLE_DES'. - SQL Server throws an error when try to use algorithms

Assume that you use SQL Server 2016 and trying to create a Symmetric Key or Asymmetric Key for encrypting data. If you try use an algorithm like TRIPLE_DES, you will get the mentioned error;

Msg 102, Level 15, State 1, Line 20

Incorrect syntax near 'TRIPLE_DES'.

Here is a sample code for seeing this error;

-- Create a database
CREATE DATABASE Sales;
GO

-- Connect with newly cerated database
USE Sales;
GO

-- Create a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO 

-- Create a certificate for protecting the our key 
CREATE CERTIFICATE SalesCertificate WITH SUBJECT = 'Sales Certificate';
GO

-- Create the key for encrypting data
-- Note that the created certificate protects it.
-- However, this will throw an error
CREATE SYMMETRIC KEY SalesKey
WITH ALGORITHM = TRIPLE_DES  
ENCRYPTION BY CERTIFICATE SalesCertificate;
GO

Now, what is the reason for this. The reason for this is, this algorithm is deprecated in SQL Server 2016. Not only that, All Algorithms except AES_128, AES_192, and AES_256 are deprecated in SQL Server 2016. 

What if you still need one of these deprecated algorithms? Yes, it is possible, but you need to downgrade the database by changing the Compatibility Level 120 or below.

This code shows the way of doing it.

USE master;
GO

-- Change the compatibility level to 120
ALTER DATABASE Sales
SET COMPATIBILITY_LEVEL = 120;

-- And check again
USE Sales;
GO

-- This will work now without any error
CREATE SYMMETRIC KEY SalesKey
WITH ALGORITHM = TRIPLE_DES  
ENCRYPTION BY CERTIFICATE SalesCertificate;
GO

Even though you can use the algorithm after changing the Compatibility Level, remember lower Compatibility Level might not let you to use all functionalities available with SQL Server 2016. Therefore, if possible, use allowed algorithms only.

Table Design Considerations - SQL Server Brain Basher of the Week #063

He is the Brain Basher or the interview question of the week. This is something every database engineer should know and it is not limited to database administrators.

What would be the considerations you make when you are asked to create a table for holding data?

Let me elaborate the question more. How do you start designing of the table when you are asked to design it on a particular entity. If you are an ordinary engineer who thinks traditionally, your answer could be;

"Need to check the requirements and create a script with relevant columns, and apply normalization as need."

Do you think that the answer is similar to cartoon shown above? 

Being an interviewer, I prefer to hear something more, something that explains the interviewee's experience. Let's point out some valuable considerations we have to make when a table has to be designed.

• Read the attributes identified by the BA and understand the business requirement - yes, you can think that this not something we should concern but trust me, you need to know.
• Analyze and see whether this needs as a normalized table or denormalized table. Based on the requirement, decide the level like how far we need to go through normalization rules.
• Decided the data types of the attributes and size of them. Need to pick the best, thinking present and future too. Should we think about past data as well. Yes, we should if we need to load legacy data.
• Consider the datetime format and whether it needs to maintain Unicode characters.
• Need to analyze whether it needs partitions and partitions need to be distributed among multiple file groups (preferably multiple volumes).
• Constraints required. In addition to the Primary Key, we may add additional constraints such as Foreign Key, Unique Key and some rules.
• Whether Artificial or Surrogate Key is required.
• Number of indexes required, whether we need Columnstore or not, order of them, whether they need any filters.
• Whether compression is required.

There are few other tiny things as well but these things show that the Interviewee is smart and knowledgeable. Remember, it is not just these points, knowing details of them is a must.

SQL Server - Adding Code Snippet and Using Existing Code Snippet

Once I wrote a post on SQL Server Template Explorer that describes available TSQL templates and how they can be used. Just like templates, we have been given some ready-made Code Snippets that help us to construct the statements easily. Not only that, it allows us to add our own snippets using adding Code Snippet Manager.

First of all, let's see how we can use existing code snippets. Assume that you need to create a SQL Login and you cannot remember the syntax. What you can do is;

    1. Either select Insert snippet... context menu in the Query Window or press Ctrl+K and Ctrl+X  (Press Ctrl and hold, and then press K and X).

    

    2. Select Login folder and then select Create SQL Authentication Login.

    

    3. Change the code as you need.

    

Note that, like adding codes using Template Explorer, you do not get another interface for changing values. Values need to be manually changed.

If you need to add your own code snippet, you can take copy of an existing one, change as you want and save with your own name. Assume that you need to add Azure Firewall Setting as a code snippet. If so, here are the steps;

    1. Open the SQL Server Code Snippet folder. If you have selected the default location when installing SQL Server, the path would be C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\SQL\Snippets\1033.

    2. Create a folder called Azure (or name as you need).

    3. Update the SnippetsIndex.xml. Add the following node to the file. This node is for the newly created folder. (** Note that this file cannot be modified if you have not opened the editor as Administrator. If change this using Notepad, open the Notepad as an Administrator and then open the file for modifictions).

    4. Take a copy of existing snippet and place in Azure folder. I have taken Create SQL Authentication Login.snippet and renamed as Create Azure Server Level Firewall Rule.

    5. Open Create Azure Server Level Firewall Rule file (Open as an Administrator) and modify -->

SQL Server - Adding IF, BEGIN/END, WHILE statement easily to the code

Do you know that you can add, specifically surround your code with BEGIN/END, IF, and WHILE statements using a menu item without writing them? Yes, with Management Studio, it is possible. Here are the steps for doing it;

Assume that you have the below code that needs to be repeated based on a condition;

All you have to do is, select the code needs to be surrounded and press Ctrl+K and Ctrl+S (Just press Ctrl and hold, and press K first and then S). This is what happen when you do it;

Select WHILE and press Tab key. This is what you should see;

Set the condition as you need now. This shortcut can be opened using the context menu as well.

SQL Server - Rounding Off Decimal to 0.5

This is something I had to use with one of my projects today. As a part of the implementation, an average value had to be rounded off to 0.5 and result should be something like 3.0, 3.5, 4.0, etc. For example, if the value is 3.6, it has to be saved as 3.5, if the value is 3.8, it has to be saved as 4.0.

Since it is not something we always do, thought to share the code. Here is the code I wrote using few functions;

DECLARE @Number decimal(2,1) = 3.2

-- This will return the value as 3.0
SELECT CASE WHEN @Number % 1 IN (.3, .4, .6, .7) THEN Convert(decimal(2,1), Floor(@Number) ) + .5
   WHEN @Number % 1 IN (.8, .9) THEN Convert(decimal(2,1), Ceiling(@Number))
   WHEN @Number % 1 IN (.1, .2) THEN Convert(decimal(2,1), Floor(@Number))
   ELSE @Number END;

SQL Server Job History is missing

If you are 100% sure that the job configured with SQL Server Agent has been executed successfully but you do not see the execution record when the Job History is opened, what could be the reason?

This is something I noticed yesterday with one of companies I consult. The client has many jobs configured with SQL Server Agent and all work fine without any issue. When the job history of the job is viewed using View History context menu, for some jobs, it shows the history but not for all. If the jobs have been successfully executed, it means that execution records should be exist in the msdb database as it is the database that stores all these information. So, we immediately checked the database;

USE msdb;
GO

select * from sysjobhistory;

It returned only 1000 records, means it maintains only thousand records. This is based on a setting of SQL Server Agent which is called Current job history log size (in rows). When it was checked, the setting was something like below;

This setting deletes all old records to make sure that it maintains only 1000 records. That is the reason we see the history for some records, not for all. What we saw from the above query is, details of jobs executed recently.

Best is, select Remove agent history checkbox and set to 4 weeks or more without using Limit size of job history log. This makes sure that we see the history for last one month. However, if the msdb database is getting larger, then you need to configure a lower value.

Should we use Compress function on string values to reduce the storage cost?

While designing my new database in Azure as an Azure SQL Database, was thinking to manage the cost for the storage, hence thought to apply COMPRESS function where-ever possible even though it does not drastically reduce the cost in terms of finance. However, there are two main things to consider if data is going to be compressed using COMPRESS function;

• Compressed data cannot be indexed.
• Compressed data has to be stored in a varbinanry(max) column

If above facts violates the business requirements, then it is not possible to use the function for compressing data. But assume that the column does not need to be indexed and no harm of storing data in compressed format, then it can be simply applied. Now the question is, will it be useful with all values stored in this particular column?

For that, I made a simple test. See code below and the output of it.

DECLARE @Value1 varchar(8000) = 'Compresses the input expression using the GZIP algorithm.'
DECLARE @Value2 varchar(8000) = 'Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max)'
DECLARE @Value3 varchar(8000) = 'Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max). Compressed data cannot be indexed. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. For automatic compression at the row or page level during storage, see Data Compression.'
DECLARE @Value4 varchar(8000) = 'Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max). Compressed data cannot be indexed. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. For automatic compression at the row or page level during storage, see Data Compression.Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max). Compressed data cannot be indexed. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. For automatic compression at the row or page level during storage, see Data Compression.Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max). Compressed data cannot be indexed. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. For automatic compression at the row or page level during storage, see Data Compression.Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max). Compressed data cannot be indexed. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. For automatic compression at the row or page level during storage, see Data Compression.'
 
 SELECT DATALENGTH(@Value1) LengthOfOriginalValueOfValue1
 , DATALENGTH(COMPRESS(@Value1)) LengthOfCompressedValueOfValue1
 , DATALENGTH(@Value2) LengthOfOriginalValueOfValue2
 , DATALENGTH(COMPRESS(@Value2)) LengthOfCompressedValueOfValue2;

 SELECT DATALENGTH(@Value3) LengthOfOriginalValueOfValue3
 , DATALENGTH(COMPRESS(@Value3)) LengthOfCompressedValueOfValue3
 , DATALENGTH(@Value4) LengthOfOriginalValueOfValue4
 , DATALENGTH(COMPRESS(@Value4)) LengthOfCompressedValueOfValue4;

This clearly shows us that we do not get benefits with all types of values stored and it shows and compression works well only with larger values. Therefore I decided to apply this only for few columns. If you have the same requirements, make sure you apply the compression only for columns that has larger values.

Another new year for dinesql.blogsposts.com……… HAPPY NEW YEAR 2017

I have been blogging for years but I think that the most important and success year is 2016. I have made 171 posts in 2016 based on my experiences had with SQL Server, based on questions asked by many and based on some experiments I did. Comparing with 2015 that has 200 posts, I still think that 2016 is the best.

I can see more than 50,000 unique visits per month (excluding subscriptions), making it as over 600,000 unique visits per year. When comparing with other blogs by SQL Server experts, still the number is small but with the feedback and compliments received on my blog, I can see how significant and useful some of the posts I have made and it always encourages and makes me happy. 

Thank you very much for reading my posts and of course helping me to share something I know. Not only that, thank you very much for supporting me for improving my knowledge by giving feedback with your replies and solutions.

I wish my readers Very Happy New Year 2017! Let’s learn more on Microsoft SQL Server, Azure, Database Management System, Business Intelligence and Data Analytics with Big Data.