Mustafa EL-Masry Dedloack, Performance MSSQL

Monitoring and Tracking SQL Server Blocking & Deadlocks process


Introduction

SQL Server deadlock one of the issues that can be happened in any SQL Server, today in this article I will not explain what is Deadlock and How we can solve it but the main purpose of this article is sharing the scripts I am using it for monitoring this kind of process let us start

It will be great if you share with us your experiences and your scripts in the comment

  1. Demo Preparation
  2. Database Configuration
  3. Extended Event Implementation
  4. SQL Server Custom Jobs Implementation
  5. Deadlock Simulation
  6. Capture the Deadlock information using SQL Server profiler
  7. Capture the Deadlock information using SQL Server Error Log
  8. Capture the Deadlock information using SQL Server Extended Event
  9. Capture the Deadlock information using System_Health Default Extended event
  10. Capture the Deadlock counts per Day
  11. Capture the Deadlock information using SQL Server Custom Jobs
  12. Check the Row Lock Blocking Info
  13. Other Scripts for Monitoring
Continue reading “Monitoring and Tracking SQL Server Blocking & Deadlocks process” →
Mustafa EL-Masry 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 

devmosman SQL Server 2016

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’
)

Continue reading “SQL SERVER 2016 Always Encrypted” →

Mustafa EL-Masry General topics

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

 

Mustafa EL-Masry SQL Server 2016

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