Security has been always one the main concern by MS SQL Server. All the releases of SQL Server have some new security feature or enhancement of an existing feature. Similarly, the latest edition of SQL Server 2016 has many security features such as Always Encrypted, enhancement of Transparent Data Encryption, Dynamic Data Masking, and Row-Level Security are added.
If you download the July release of SQL Server Management Studio 2016 Please go and download the SQL Server Management Studio July 2016 Hotfix update from here https://msdn.microsoft.com/en-us/library/mt238290.aspx
It is a very important fix that resolve issues produced in the July update that causes you miss important commands in the right-click menu on tables and stored procedures
Here are the Linked customer bug requests:
There are many users, who are not aware about disaster recovery planning with always-on availability groups. Even they do not know the terms that come in the utilization of Always-On Availability groups for disaster recovery planning. In the following section, we will discuss about the always-on availability groups for resolving the disaster Read the rest of this entry »
We can kill a session by using KILL command. However, KILL command requires a positive number; executing KILL with negative number returns an error:
Msg 6101, Level 16, State 1, Line 1
Session ID -4 is not valid.
In order to kill the session ID, you need to find the unit of work (UOW) guid.
SELECT DISTINCT(request_owner_guid) AS UOW FROM sys.dm_tran_locks WHERE request_session_id IN (-2,-3,-4)
Now you can kill this using UOW:
Like all normal transactions, killing a session causes any work performed by it to be rolled back to bring the database back into consistent state.
The negative session ID are orphaned or stuck sessions that SQL Server; they are rare occurrences. Most often the only one I have seen is -2; what do they mean?
|-2||The blocking resource is owned by an orphaned distributed transaction.|
|-3||The blocking resource is owned by a deferred recovery transaction.|
|-4||Session ID of the blocking latch owner could not be determined due to internal latch state transitions.|
I remember my days before, Microsoft SQL Server PFE. I wanted to learn everything and know everything about SQL Server. However, getting hold of good resources was tough, as I didn’t have any mentor when I started down my journey to becoming a SQL Server Database Administrator.
Along the way I did pick up lots of books and references. One of such books is Dissecting SQL Server Execution Plans.
I read this book before becoming PFE, I read this now, and I recommend everyone read this book more then once.
SQL Central, Jeff Moden, Dissecting SQL Server Execution Plans
Amazon, SQL Server Execution Plans
SQL Central, Red Gate, EBook
It is 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 your .NET application prior to 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 on this key directly
Column Encryption Key:
But this one is stored in SQL Server and it used for encrypt/decrypt the Always Encrypted column at this time the scenario of the encryption will be first ADO.NET has decrypted the Column Encryption Key, using the Column Master Key then SQL Server use Encryption Key for encrypt/decrypt the Always Encrypted column.
- Expand your DB under security you will find “Always Encrypted Keys “
- Right click create new column master key
CREATE COLUMN MASTER KEY [Demo_Always_Encrypted_CMK]
KEY_STORE_PROVIDER_NAME = N’MSSQL_CERTIFICATE_STORE’,
KEY_PATH = N’CurrentUser/My/09D607EDCEC14A9E009FC59B67E7F423DBEE9C9E’
When the users of SQL Server stores data in table of their database, they use an identity column as primary key. The identity column will increase its value automatically whenever new row is added. However, in some cases users may need to determine the last inserted record in database. The blog will be explaining some of the possible ways on how to find the last inserted record in SQL Server.
Determine Last Inserted Record in SQL Server
While we work with the table in SQL Server database, we set identity column that act as an auto increment column in table to increase column ID value whenever new record is inserted. Suppose we want to insert a name of the employee in the table ‘Employees’, we will do that using the below command:
INSERT INTO Employees (FirstName) VALUES (‘Mellisa’)
Now, in order to get the lasted inserted record ID, we can use the following options:
- SELECT @@IDENTITY
- It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value and of the scope of the statement that produced the value.
- It is used to give the last identity value generated by the statement.
- If the statement did not affect any tables with identity columns, this command returns NULL
- If the table contains multiple rows generating multiple identity values, @@IDENTITY returns the last identity value generated.
- Though @@IDENTITY is limited to current session, it is not limited to current scope. Even if trigger on the table caused identity to be created, you will get identity that was last created, even if it is a trigger.
- SELECT SCOPE_IDENTITY()
- As the name suggests, it will return the last identity produced on a connection and by statement in same scope, regardless of the table that produced the value.
- It is limited to the current scope and in current session as well.
- It will return the last identity that was explicitly created, rather than any identity created by trigger or user-defined function.
- SELECT IDENT_CURRENT(‘TableName’)
- It returns the last identity value produced in a table, regardless of the connection and the scope of the statement that created the value.
- It is not limited by scope and session but is limited to a specified table.
- It will return the identity value generated for the specific table in any session or any scope.
In the blog, we have discussed about some of the approaches through which we can find the last inserted record in SQL Server database. Among the methods SCOPE_IDENTITY() is recommended as it avoids the potential issues associated with addition of trigger while returning identity of the recently inserted record. The manual methods of determining last inserted record using these commands may sometimes be time taking and difficult for non-technical users. One of the easy alternative for the same purpose is to use a third party tool to view SQL Server transaction log that is used to read and analyze SQL Server Log File transactions that will give detailed analysis of all transactions like insert, delete, update etc.