DTU in SQL Azure Database
DTU is called “Database Throughput Unit”. It is a unit of measure in SQL Database. It also represents the relative power of the database transaction.
It is an abstraction of,
1. CPU
2. Memory
3. Read
4. Write
We have different service tiers based on DTU.
Check the below URL to compare the DTUs in each tier
https://azure.microsoft.com/en-us/pricing/details/sql-database/?b=16.50
SQL Server Service Accounts in 2016
SQL Server 2016 has two new service accounts to enable PolyBase feature. There is a configuration page added to setup PolyBase.
Service Accounts:
PolyBase Config
Temporary Tables & Table Variable
In SQL Server, temp tables are created at run time. It is just like a normal table and it will support all the operations but these tables are created inside temp database. The scope of the table is only on session & connection.
There are two types of temporary tables,
- Local Temp Table
- Global Temp Table
Local Temp Table
It will be available only on SQL Server session or connection. It means, if the session is closed or connection is out then this temp table will not be accessible, it will be deleted.
This will be accessible only one user who creates the table.
Local Temp table name is started with “#” sign.
Syntax
CREATE TABLE #LocalTemp
(
…Columns
)
Example
–Create a local Temp Table
CREATE TABLE #LocalTemp
(
UserID int,
Name varchar(50),
EmailId varchar(50),
Phonenumber varchar(15)
)
GO
–Insert values directly
Insert into #LocalTemp values (1,‘Hari’, ‘Test@Test.com’, ‘123-245-356’)
Go
–Insert value from another table
Insert into #LocalTemp
(UserID, Name, EmailId, Phonenumber)
(SELECT UserId, Name, EmailId, Phonenumber from dbo.USERS)
Go
Try to open a new query window and access the above table then see the result by yourself.
Global Temp Table
This table will be available for all SQL Server sessions. This can be accessible by multiple users with the same connections. It will be deleted when a connection is closed or all the sessions are closed.
Global Temp table name is started with “##” sign
Syntax
CREATE TABLE ##GlobalTemp
(
…Columns
)
GO
Example
–Create a local Temp Table
CREATE TABLE ##GlobalTemp
(
UserID int,
Name varchar(50),
EmailId varchar(50),
Phonenumber varchar(15)
)
GO
–Insert values directly
Insert into ##GlobalTemp values (1,‘Hari’, ‘Test@Test.com’, ‘123-245-356’)
Go
–Insert value from another table
Insert into ##GlobalTemp
(UserID, Name, EmailId, Phonenumber)
(SELECT UserId, Name, EmailId, Phonenumber from dbo.USERS)
Go
Open multiple query window and access this table then close the connection and connect again. Then try to access the table by yourself.
Table Variable
Apart from temp tables, we have table variable and the scope of this table variable only with the batch of script.
It will be dropped once it comes out of the batch.
This is also created on temp database but not on memory. This allows us to create primary key on table as like normal table when declare a table variable but the difference is we cannot create cluster index.
Example
–First Batch Start
–Create a table variable
DECLARE @Users TABLE
(
UserID int
identity (1,1),
Name varchar(50),
EmailId varchar(50),
Phonenumber varchar(15)
)
–insert values from another table
Insert into @Users
(Name, EmailId, Phonenumber)
SELECT Name, EmailId, Phonenumber from dbo.USERS
–Select the records
SELECT * FROM @Users
–First Batch End …………..
–Second batch start
SELECT * FROM @Users –gives error
Go
–Second Batch End….
Install R Studio 0.99.491
You can download the latest R Studio 0.99.491 and install for FREE in this link. However if you need support then you need to agree to a monthly term to support you and your production system, and you can compare it from this link about the pricing for your desktop and server.
I have downloaded the RStdio-0.99.491 and have started installing and provided you the below screenshots for your reference.
In here, you have option to choose your folder and it also gives you the amount of space required for this installation. I know, I am running out of space, but I am doing this in my virtual machine.
Here you get option to add to the start menu and create shortcuts. I chose the default options in below screenshot.
As you can see below, the extraction and installation takes place for about couple of minutes.
That’s it. We are now done with the installation of R Studio.
Test Drive on Always Encrypted in SQL Server 2016
To test the always encrypted feature in SQL Server 2016, Follow the below test steps,
- Create a table with encrypted column, refer here to set up.
- Insert a data into the table
- Check the table result
Tables used in my test,
- Employees – Always Encrypted on TaxID & Salary fields
- Employees_NoEncrypt
Insert a data into Encrypted Table (Always Encrypted)
Following my previous post, you can insert a data into an encrypted table from another table using Export and Import option.
Create a table with same structure without encrypt settings- Compare the table (Employees) structure of encrypted table here
CREATE TABLE [dbo].[Employees_NoEncrypt]
( [UserID] int IDENTITY(1,1),
[TaxID] varchar(11) NOT NULL,
[FirstName] nvarchar(50)NOT NULL,
[LastName] nvarchar(50) NULL,
[Designation] nvarchar(50) NULL,
[Email] nvarchar(50) NOT NULL,
[Phone Number] nvarchar(10) NOT NULL,
[Salary] decimal(10,2) NOT NULL
PRIMARY KEY CLUSTERED ([UserID] ASC) ON [PRIMARY] )
Insert a data into Employees_NoEncrypt table using “Insert” statement or “Edit Top 200 Rows”. Once done then go to next step.
Use Import and Export Data wizard – This will be installed when you install SQL Server.
In my case, both the tables are residing in same database and server.
Column Level Encryption using Always Encrypted in SQL Server 2016
Always encrypted feature in SQL Server 2016 is the best option who wants to encrypt the certain column values in a table.
Steps to implement the always encrypted option,
- Set up column encryption key with the help of column master key – Go through column master and encryption article here to set up.
- Create a table with column level encryption setting
- Column encryption key
- Algorithm – Currently only one algorithm is supported which is AEAD_AES_256_CBC_HMAC_SHA_256
- Type of encryption – DETERMINISTIC or RANDOMIZED. Visit here to know detailed information
Syntax to apply encryption on a column,
CREATE TABLE <tablename>
(<column name> <data type>
ENCRYPTED WITH (ENCRYPTION_TYPE = <Type of Encryption>,
ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, COLUMN_ENCRYPTION_KEY =<column encryption key name>)
)
In this below table, I want to encrypt employee Tax ID and Salary fields,
CREATE TABLE [dbo].[Employees]
( [UserID] int IDENTITY(1,1),
[TaxID] varchar(11)
COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, COLUMN_ENCRYPTION_KEY = MyColumnKey) NOT NULL,
[FirstName] nvarchar(50) NOT NULL,
[LastName] nvarchar(50) NULL,
[Designation] nvarchar(50) NULL,
[Email] nvarchar(50) NOT NULL,
[Phone Number] nvarchar(10) NOT NULL,
[Salary] decimal(10,2)
ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’,
COLUMN_ENCRYPTION_KEY = MyColumnKey) NOT NULL
PRIMARY KEY CLUSTERED ([UserID] ASC) ON [PRIMARY] )
By using the above script, we can create a table with encrypted columns.
If you try to insert data into the table using insert statement then you will get an error message like below,
Msg 206, Level 16, State 2, Line 16
Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = ‘DETERMINISTIC’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘MyColumnKey’, column_encryption_key_database_name = ‘Databrain’) collation_name = ‘SQL_Latin1_General_CP1_CI_AS’
It will not allow you to insert a data directly because of applied encryption. This can be done through a client application. Refer Microsoft article here for the detailed explanation.
[Step by Step] Column Master & Encryption Keys in SQL Server 2016
Following my previous article, this article will help you to understand how to create encryption keys which are the prerequisites for encrypting the table columns.
Steps to create Column Master Key
- Open SQL Server Management Studio
- Choose the database where you want to apply encryption for tables
- Expand the (+) sign
- Go to Security > Always Encrypted Keys > Column Master Key Definition
- Right click Column Master Key Definition folder and choose New Column Master Key Definition
- Provide the name
- Select the Key Definition Source from drop down (if no value then refresh)
- Select your machine
- Click Ok.
Screenshots for your reference,
Steps to create Column Encryption Key
- Open SQL Server Management Studio
- Choose the database where you want to apply encryption for tables
- Expand the (+) sign
- Go to Security > Always Encrypted Keys > Column Encryption Keys
- Right click Column Encryption Keys folder and choose New Column Encryption Key…
- Provide the name
- Choose the Column Master Key Definition
- Click Ok.
Screenshots for your reference,
