RSS

Tag Archives: SQL Server 2016

Concept and basics of Temporal tables in SQL Server 2016

In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including:

  • Introduction
  • What is a temporal table?
  • Why Temporal table?
  • How does temporal table work?
  • Consideration and limitation
  • Temporal tables vs CDC
  • Creation and configuration
  • Clean up and removal
  • References

Introduction

AS we know, Microsoft released SQL Server 2016 RTM version (13.00.1601.5) and in November of 2016 updated it by the latest CU (Security Bulletin MS16-136 (CU) KB #3194717) (13.0.2186.0) you can check this update from here.

To complete this artcile please check it here in SQLShack 

 
Leave a comment

Posted by on November 25, 2016 in SQL Server 2016

 

Tags: , , , ,

SQL SERVER 2016 Always Encrypted

It is the new way of Data encryption introduced with SQL Server 2016 used for encrypting the sensitive date encrypted at the application layer via ADO.NET. This means you can encrypt your confidential data with the yours.NET application before the data being sent across the network to SQL Server.

Column master key:

The Column Master Key is stored on an application machine, in an external key store. This key used for protecting the column encryption key and SQL Server doesn’t have any access to this core directly

Column Encryption Key:

But this one is stored in SQL Server and it used for encrypting/decrypt the Always Encrypted column at this time the scenario of the encryption will be the first ADO.NET has decrypted the Column Encryption Key, using the Column Master Key then SQL Server use Encryption Key for encrypting/decrypt the Always Encrypted column.

1

Technical Demo:

  • Expand your DB under security you will find “Always Encrypted Keys
  • Right click create new column master key

USE [SQL2016DEMO]
CREATE COLUMN MASTER KEY [Demo_Always_Encrypted_CMK]
WITH
(
KEY_STORE_PROVIDER_NAME = N’MSSQL_CERTIFICATE_STORE’,
KEY_PATH = N’CurrentUser/My/09D607EDCEC14A9E009FC59B67E7F423DBEE9C9E’
)

Read the rest of this entry »

 
Leave a comment

Posted by on May 30, 2016 in SQL Server 2016

 

Tags: , , ,

WordPress Annual Report to my SQL Server Community

I completed 4 Years as SQL Server blogger with my small Community SQL Server Consultation Community (http://mostafaelmasry.com)

I started it from 2012 and I will not stop sharing the information ad the new Technology in SQL Server

You can check the WordPress annual Report for the last 4 years from here

 

WordPress Annual Report to my SQL Server Community

 

 
Leave a comment

Posted by on January 1, 2016 in General topics

 

Tags: , , , , , , , , ,

SQL Server 2016 Community Technology Preview 3.1 is available

Configurations for all Microsoft SQL Server 2016 followers and for the guys are interested to know more and more about this new technology Microsoft released version no 3.1 CTP from SQL Server 2016 and it released new improvement in this version (In-Memory OLTP) i will explain it later in detail but now for more information and for downloading this new version you can check it from Here

Also to cover all the new updates in SQL Server 2016 you can check it from Here

To know More about new feature in SQL Server 2016 you can check it from HERE

Follow Us :

LinkedIn Slideshare ,Youtube Channel.MSDN POSTS ,Facebook WHO WE ARE

 

 

 
Leave a comment

Posted by on December 8, 2015 in SQL Server 2016

 

Tags: , ,

Live Query Statistics in SQL Server 2016

Today we have very good features it will help us in our daily work (Performance troubleshooting and for the debugging of queries) as we know to tune any SQL Server query you should Check first the Execution plan to decide from where the problem from SQL Server 2005 to SQL Server 2014 we have two types of Execution plan:

  • Estimated Execution Plan: Giving you an idea of how SQL Server will most likely perform query execution
  • Actual Execution Plan       : This will show you things that might hint at “out-of-date” statistics etc. But to get this, you must run the query – which can take a long time.

So before if we have case one Query returned Millions of record and we need to check the Query execution plan for it we will go directly for the Estimated Execution plan because actual Execution plan if we need to see it we will wait more time to the query finish and return all the result but Now in SQL Server 2016 we have new Execution plan type ” Live Query Statistics ”  this new features it will he;p us to see the Execution plan when the Query running and the Effect of the Query on the Execution plan step by step from more points:

  • Execution Query Percentage
  • Actual Number of Rows
  • elapsed time
  • operator progress

What I use here in my demo is simple Query return more than 8 Millions of records if I wait for this query to return all this data to see the Actual Execution plan I will wait around 20 Minutes but now in SQL Server 2016 I will See the Execution plan the Actual Execution Plan Live with Live Query Statistics 

SELECT *
FROM sys.all_columns tmp1
Cross JOIN sys.all_columns tmp2

Live Query3

Live Query1 Live Query2

To know More about new feature in SQL Server 2016 Keep following us and to check the previous posts in SQL Server 20116 you can check it from HERE

 
Leave a comment

Posted by on November 8, 2015 in SQL Server 2016

 

Tags: , , , , , , , ,

What is New in SQL Server 2016 Management Studio

Hello my followers today i will speak in very good Point in SQL Server 2016 all we are working on SSMS (SQL Server Mangment Studio) and we should now the options in this tools and what is the new on it so today we will show the new features in SQL Server 2016 Management Studio then later will explain deeply this features if it needed

  • In the installation you find new tape for Tempdb Configuration 
  • Check for Update : ( Now we can update the SQL Server Management Studio alone you will find it under tools )
  • 1- SSMS Update
  • Save Open Queries in SQL Server Management Studio (Tools > options > Query Execution > Prompt to save unsaved T-SQL Query Windows on Close)

Save unsaved T-SQL

Read the rest of this entry »

 
7 Comments

Posted by on November 5, 2015 in SQL Server 2016

 

Tags: , , , , , ,

SQL Server 2016 installation Error Rule “Oracle JRE 7 Update 51 (64-bit) or higher is required” failed.

Hello my Followers as we know Microsoft is going very fast for SQL Server 2016 RTM , Recently Microsoft announced about SQL Server 2016 CTP 3 “SQL Server 2016 Community Technology Preview 3.0” and this force us to learn more about the new features of this new SQL Server Release and to know ” What’s New in SQL Server 2016 “.

Problem

When i installed SQL Server 2016 CTP 2.4 i faced the below issue and this one we can solve it be the below solution

—————————
Rule Check Result
—————————
Rule “Oracle JRE 7 Update 51 (64-bit) or higher is required” failed.

This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from http://go.microsoft.com/fwlink/?LinkId=526030.
—————————
OK
—————————

SQl Server 2016

Solution

  1. Go to http://www.oracle.com/technetwork/java/javase/downloads/index.html
  2. Click on Download on JRE as highlighted below

Untitled

Follow Us :

LinkedIn Slideshare ,Youtube Channel.MSDN POSTS ,Facebook WHO WE ARE

 
Leave a comment

Posted by on November 5, 2015 in SQL Server 2016

 

Tags: , ,