Advertisements

Archive

Archive for July, 2015

Memory Optimized “Table Variables” in SQL Server 2014 and 2016

July 31, 2015 Leave a comment

SQL Server 2014 provided you these new features to create [Memory Optimized Tables] and [Native Compiled Stored Procedures] for efficient and quick processing of data and queries which happens all in memory.

It also provided you one more feature to create Memory Optimized Table Variables, in addition to normal Disk Based Table Variables.

This new feature would provide you more efficiency in Storing, Retrieving and Querying temporary data from and in memory.

Normal Table Variables are created in tempdb and utilize it for their entire life. Now with these new Memory Optimized Table Variables they will become free from tempdb usage, relieve tempdb contention and reside in memory only till the scope i.e. batch of a SQL script or a Stored Procedure.

Let’s see how to use these and what performance gain you get out of these tables.
 

–> Enable Database for supporting Memory Optimized tables: To use this feature your Database should be associated with a FileGroup. So, let’s alter the database.


USE [TestManDB]
GO

-- Add the Database to a new FileGroup

ALTER DATABASE [TestManDB]
    ADD FILEGROUP [TestManFG] CONTAINS MEMORY_OPTIMIZED_DATA 
GO

ALTER DATABASE [TestManDB]
ADD FILE ( 
	NAME = TestManDBFG_file1,
    FILENAME = N'E:\MSSQL\DATA\TestManDBFG_file1' -- Put correct path here
)
TO FILEGROUP TestManFG
GO

Otherwise, while creating Memory Optimized objects you will get below error:

Msg 41337, Level 16, State 100, Line 1
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

 

You cannot create a Memory Optimized Table Variable directly with DECLARE @TableVarName AS TABLE (…) statement. First you will need to create a Table Type, then based upon this you can create Tables Variables.

–> Create a Table TYPE [Person_in_mem]

CREATE TYPE dbo.Person_in_mem AS TABLE(
	 BusinessEntityID INT NOT NULL
	,FirstName NVARCHAR(50) NOT NULL
	,LastName NVARCHAR(50) NOT NULL

	INDEX [IX_BusinessEntityID] HASH (BusinessEntityID) 
		WITH ( BUCKET_COUNT = 2000)
)
WITH ( MEMORY_OPTIMIZED = ON )
GO

The Memory Optimized Table Type should have an Index, otherwise you will see an error as mentioned below:

Msg 41327, Level 16, State 7, Line 27
The memory optimized table ‘Person_in_mem’ must have at least one index or a primary key.
Msg 1750, Level 16, State 0, Line 27
Could not create constraint or index. See previous errors.

 

Ok, now as we’ve created this table type, now we can create as many Table Variables based upon this.

–> Now, create a Table variable @PersonInMem of type [Person_in_mem] that is created above:

DECLARE @PersonInMem AS Person_in_mem

-- insert some rows into this In-Memory Table Variable
INSERT INTO @PersonInMem
SELECT TOP 1000
	 [BusinessEntityID]
	,[FirstName]
	,[LastName]
FROM [AdventureWorks2014].[Person].[Person]

SELECT * FROM @PersonInMem
GO

Here we successfully created a Table Variable, inserted records into it and retrieved same by the SELECT statement, and this all happened in memory.
 

Now how can we see we that how much benefits we got from this? What we can do is, we can create a separate Disk-Based Table Variable and do similar operation on it and compare the results by checking the Execution Plan.

–> Comparing performance of both In-Memory vs Disk-Based Table-Variables

– Enable the Actual Execution Plan and run below script to Create and Populate both:

1. In-Memory Table Variable

2, Disk-Based Table Variable

-- 1. In-Memory Table Variable
DECLARE @PersonInMem AS Person_in_mem

INSERT INTO @PersonInMem
SELECT TOP 1000
	 [BusinessEntityID]
	,[FirstName]
	,[LastName]
FROM [AdventureWorks2014].[Person].[Person]

select * from @PersonInMem


-- 2. Disk-Based Table Variable
DECLARE @Person AS TABLE (
	 BusinessEntityID INT NOT NULL
	,FirstName NVARCHAR(50) NOT NULL
	,LastName NVARCHAR(50) NOT NULL
)

INSERT INTO @Person
SELECT TOP 1000
	 [BusinessEntityID]
	,[FirstName]
	,[LastName]
FROM [AdventureWorks2014].[Person].[Person]

select * from @Person
GO

 

–> Now, check the Actual Execution Plan results below:

1. Check the Cost of INSERT operation with both the tables:

– It took only 8% cost to insert into In-memory Table Variable.

– But it took 89% cost to insert into a Disk-Based Table Variable.

> If You see the individual Operators in both the plans you will see that :
For @PersonInMem Table Variable the cost of INSERT was just 19% compared to the cost of INSERT for @Person Table Variable that was 92%.

SQL Server 2016 - Memory Optimized Table Variables 01

2. Check the Cost to SELECT/Retrieve rows both the tables:

– It took only 0% cost to retrieve rows from the In-memory Table Variable

– And it took 3% cost to retrieve rows from a Disk-Based Table Variable

SQL Server 2016 - Memory Optimized Table Variables 02

This proves that the INSERT and SELECT operations with Memory Optimized table are way more faster that normal Disk-Based tables.

Thus, using Memory Optimized Table Variables will provide you better performance for storing temporary data within memory and process with in Stored Procedure or your T-SQL Scripts.
 

Update: Know more about In-Memory tables:


 

Check & Like my FB Page


Advertisements

Obfuscate column level data by using “Dynamic Data Masking” in SQL Server 2016

July 20, 2015 6 comments

This time SQL Server 2016 has made good additions in area of Security by introducing features like:

1. Always Encrypted

2. Row Level Security, check my previous post,

3. Dynamic Data Masking, this post

4. and other security features, like Transparent Data Encryption (TDE), etc.
 

Dynamic Data Masking provides you support for real-time obfuscation of data so that the data requesters do not get access to unauthorized data. This helps protect sensitive data even when it is not encrypted, and shows obfuscated data at the presentation layer without changing anything at the database level.

Dynamic Data Masking limits sensitive data exposure by masking it to non-privileged users. This feature helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a Policy-based Security feature that hides the sensitive data in the result set of a Query over designated database columns, while the data in the database is not changed.
 

–> “Dynamic Data Masking” provides you three functions/options to Mask your data:

1. default(): just replaces the column value with ‘XXXX’ by default.

2. email(): shows an email ID to this format ‘aXXX@XXXX.com’.

3. partial(prefix,padding,suffix): gives you option to format and mask only some part of a string value.

We will see the usage of all these 3 masking functions below.
 

–> To setup Dynamic Data Masking on a particular Table you need to:

1. CREATE TABLE with MASKED WITH FUNCTION option at column level.

– Or ALTER TABLE columns by using this option if the table is already present.

2. Create Users and Grant Read/SELECT access for the above CREATED/ALTERED table.
 

–> 1. Create a sample table [dbo].[Customer] with masked columns:

CREATE TABLE dbo.Customer (
	CustomerID 	 INT IDENTITY PRIMARY KEY,
	FirstName 	 VARCHAR(250),
	LastName 	 VARCHAR(250) MASKED WITH (FUNCTION = 'default()') NULL,
	PhoneNumber  VARCHAR(12)  MASKED WITH (FUNCTION = 'partial(1,"XXXXXXXXX",0)') NULL,
	Email 		 VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NULL,
	CreditCardNo VARCHAR(16)  MASKED WITH (FUNCTION = 'partial(4,"XXXXXXXXXX",2)') NULL,
);

–> Now insert some test records (fictitious figures):

INSERT INTO dbo.Customer (FirstName, LastName, PhoneNumber, Email, CreditCardNo)
VALUES
('Manoj',   'Pandey', '4442889882', 'manojp@gmail.com', '4563234576547834'),
('Saurabh', 'Sharma', '9812446452', 'sausha@gmail.com', '1243096778653487'),
('Vivek',   'Singh',  '6745239856', 'viveks@gmail.com', '8756341209876735'),
('Keshav',  'Singh',  '9867452387', 'keshav@gmail.com', '2938713685372618');

–> Let’s check the rows on [dbo].[Customer] table in context of my User:

SELECT * FROM dbo.Customer;
GO

SQLServer2016 - DDM 01

Here, I can see all the row/column values as I have full access to read the masked/sensitive data.
 

–> 2.a. Now let’s create a Test Account and just Grant Read access to [dbo].[Customer] table:

CREATE USER AnyUser WITHOUT LOGIN;
GO

GRANT SELECT ON dbo.Customer TO AnyUser;
GO

–> Let’s execute the SELECT statement on [dbo].[Customer] table in the Context of this new user account:

EXECUTE AS USER = 'AnyUser';
SELECT * FROM dbo.Customer;
REVERT;
GO

SQLServer2016 - DDM 02

And you can see that the user is not able to see the masked data as he is not authorized to see it.
 

–> Removing Masking from a column by simple ALTER TABLE/COLUMN statement:

ALTER TABLE dbo.Customer 
ALTER COLUMN LastName DROP MASKED;
GO

-- Let's check the table data again:
EXECUTE AS USER = 'AnyUser';
SELECT * FROM dbo.Customer;
REVERT;
GO

SQLServer2016 - DDM 03

Here, now you are able to see contents of LastName columns, as the masking has been removed from this column by using simple ALTER TABLE/COLUMN statement.
 

–> 2.b. Granting the UNMASK permission to “AnyUser”:

GRANT UNMASK TO AnyUser;
GO

-- Let's check the table data again:
EXECUTE AS USER = 'AnyUser';
SELECT * FROM dbo.Customer;
REVERT; 
GO

SQLServer2016 - DDM 01

He is able to see all data unmasked when the UNMASK permission is granted to this user.
 

–> 2.c. Revoking back the UNMASK permission form the same user:

REVOKE UNMASK TO AnyUser;
GO
EXECUTE AS USER = 'AnyUser';
SELECT * FROM dbo.Customer;
REVERT; 
GO

After Revoking UNMASK permission he is again not able to see complete data.
 

This way you can control access to your precious or PII data by masking the columns/fields that you don’t want to show to the external world or some set of users.
 

–> Final Cleanup:

DROP TABLE dbo.Customer;
GO
DROP USER [AnyUser]
GO

 

–> Check the same demo on YouTube:


 

Check & Like my FB Page


SQL Basics – What are System databases | master, model, msdb, tempdb, resource

July 17, 2015 Leave a comment

While working on SQL Server Management Studio you might have seen some default databases in System Database folder. And you would have observed the same set of databases on other machines or database servers you might have worked on or seen, as shown in the image below (red-circled):

SQL System Databases

There are total 5 System Databases, out of these you can see 4 under the System Databases folder (above image, ignore the blue circled). The 5th database is Resource which does not appear on SSMS.
 

–> Let’s check about these databases individually here:
 

1. master: database records all the system-level information for an instance of SQL Server, which includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.

It records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.

SQL Server cannot start if the master database is unavailable. If in case you would like to move the master database or have moved it and ran into issues, check this post Move Master database.

Starting SQL Server 2005 and ahead, system objects are no longer stored in the master database, instead, they are stored in the Resource database, mentioned below (5th type).
 

2. model: database is used as the template for all databases created on an instance of SQL Server. The entire contents of the model database, including database options, are copied to the new database. Thus, if you modify the model database, all databases created afterward will inherit those changes.

Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
 

3. msdb: database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SSMS, Service Broker and Database Mail.

SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb, which includes the name of the party that performed the backup, the time of the backup, and the devices or files where the backup is stored.
 

4. tempdb: database is a workspace for holding temporary objects or intermediate result sets.

tempdb is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

– Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.

– Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

– Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.

– Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database.

Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Thus, there is never anything in tempdb to be saved from one session of SQL Server to another.
 

5. Resource: database does not appear on SSMS, and is a read-only database that contains all the system objects that are included with SQL Server.

SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

The ID of the Resource database is always 32767.

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf, and are located in :\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Binn\ folder.

Check the Resource Database version and the last updated date:

SELECT SERVERPROPERTY('ResourceVersion') AS ResourceVersion;
GO
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime;
GO

SQL System Databases Resource

Check my other post on Resource Database.
 

–> You can also query all these system databases, except the Resource database.

SQL System Databases files

And you can see above first 4 database IDs, 1-4 are reserved for master, tempdb, model and msdb databases.
 

–> There are two more databases created while you install SQL Server with Reporting Services (SSRS). You can see these databases appearing in SSMS in the image above (circled blue):

1. reportServer: stores following:

– Items managed by a report server (reports and linked reports, shared data sources, report models, folders, resources) and all of the properties and security settings that are associated with those items.

– Subscription and schedule definitions.

– Report snapshots (which include query results) and report history.

– System properties and system-level security settings.

– Report execution log data.

– Symmetric keys and encrypted connection and credentials for report data sources.

2. reportServerTempdb: Each report server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the report server. Background server processes will periodically remove older and unused items from the tables in the temporary database.
 


SQL DBA – Configure multiple TempDB Database Files while installing SQL Server 2016 (new feature)

July 15, 2015 2 comments

SQL Server 2016 allows you to scale up your database with Enhanced Database Caching, using support for multiple TempDB files per instance for multi-core environments. This reduces metadata- and allocation contention for TempDB workloads, improving performance and scalability.

Now you can configure the number of TempDB Database Files during the installation of a new Instance. While installation process in the Database Engine Configuration page you will see an extra option to set number of TempDB files.
 

Or, you can specify the number of files by using the new command line parameter: /SQLTEMPDBFILECOUNT

setup.exe /Q /ACTION="INSTALL" /IACCEPTSQLSERVERLICENSETERMS 
 /FEATURES="SqlEngine" /INSTANCENAME="SQL15" .. 
 /SQLTEMPDBDIR="D:\tempdb" /SQLTEMPDBFILECOUNT="4"

 

While installing via UI the label besides the Input Control below mentions: “The default value is 8 or the number of cores, whichever is lower. This value can be increased up to the number of cores”.

SQL Server 2016 Install 06
 

Update: Now with CTP 2.4 release the installer provides a separate tab for tempdb files configuration.

SQL Server 2016 CTP 2.4 Setup 02

So, in my [C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER2016\MSSQL\DATA\] folder I could see 8 data files, with 1 log file:

SQL Server 2016 Install 07
 

Check & Like my FB Page


Implementing “Row Level Security” (RLS) with “Filter Predicate” in SQL Server 2016 – Part 1

July 13, 2015 18 comments

For an Organization its Data is the most important thing, and the Org’s IT department takes lot of measures to make sure data does not fall in wrong hands. The DBA’s and Application programmers setup different layers of security on top of data so that the user is only able to see the Filtered data/rows from a table that he has access to. DB programmers normally create abstracted Views or Stored Procedures with complex logic on top of the Tables by using other master-mapping tables that contains the user-data access key. Sometimes these security logics are not upto the standards, and may have various versions on a database and thus it becomes difficult to track, update and make them fail-proof. So, with SQL Server 2016 the new Row Level Security feature is going to handle this Out of the Box and as a Standard.

RLS or Row Level Security is a feature that enables fine grained control over access to rows in a table, allowing you to easily control which users can access which data with complete transparency to the application.

With this feature rows are filtered based on the Execution Context of the Query, rather than the current user access rights. A secure logic can be created to determine which user can see which rows and restrict any kind of data (rows) by designing a flexible and robust Security policy for a table.

SQL Server 2016 - Row Level Security 04
 

–> To setup Row Level Security (RLS) on a particular table it needs only these simple steps:

1. Create Users and Grant Read/SELECT access for a Particular table.

2. Create a new Inline Table-Valued Function that will contain the Filter Predicate for that table. This Function Predicate can be a sophisticated business logic with multiple JOINs or a simple WHERE ManagerCode = ‘userHR’.

3. Create a new Security Policy for this table and add the above Function (Filter) Predicate to it.

Please note: that these Functions & Security Policies should be unique for a table. So to create RLS for an another table, you will need to create separate Function & Security Policy.
 

–> Step 1.a. Let’s create some test accounts: I will create three users for:

1. The CEO, over-all admin of the company data.

2. HR department head

3. Finance department head

CREATE USER userCEO WITHOUT LOGIN;
GO
CREATE USER userHR WITHOUT LOGIN;
GO
CREATE USER userFin WITHOUT LOGIN;
GO

 

–> Create a sample table [dbo].[Employee]: with a self-referencing Manager ID column.

CREATE TABLE dbo.Employees (
	[EmpCode] NVARCHAR(50),  -- Employee ID
	[EmpName] NVARCHAR(250), -- Employee/Manager Full Name
	[Salary]  MONEY,		 -- Fictious Salary
	[MgrCode] NVARCHAR(50)   -- Manager ID
);
GO

-> Now insert some test records:

-- Top Boss CEO
INSERT INTO dbo.Employees VALUES ('userCEO'	, 'CEO Top Boss'  , 800, NULL)

-- Next 2 levels under CEO
INSERT INTO dbo.Employees VALUES ('userHR'	, 'HR User'		  , 700, 'userCEO');
INSERT INTO dbo.Employees VALUES ('userFin'	, 'Finance User'  , 600, 'userCEO');

-- Employees under Kevin
INSERT INTO dbo.Employees VALUES ('manojp'	, 'Manoj Pandey'  , 100, 'userHR');
INSERT INTO dbo.Employees VALUES ('saurabhs', 'Saurabh Sharma', 400, 'userHR');
INSERT INTO dbo.Employees VALUES ('deepakb' , 'Deepak Biswal' , 500, 'userHR');

-- Employees under Amy
INSERT INTO dbo.Employees VALUES ('keshavk'	, 'Keshav K'	  , 200, 'userFin');
INSERT INTO dbo.Employees VALUES ('viveks'	, 'Vivek S'		  , 300, 'userFin');
GO

–> Let’s check the records before applying “Row Level Security”:

SELECT * FROM dbo.Employees; -- 8 rows
GO

As a normal SEELCT and without RLS, it just ignores my Execution Context and execute the Query and return all the 8 rows.

–> The Traditional way to setup the Row Level Security till now was as follows (a simple example):

-- Stored Procedure with User-Name passed as parameter:
CREATE PROCEDURE dbo.uspGetEmployeeDetails (@userAccess NVARCHAR(50))
AS
BEGIN
	SELECT * 
	FROM dbo.Employees
	WHERE [MgrCode] = @userAccess
	OR @userAccess = 'userCEO'; -- CEO, the admin should see all rows
END
GO

-- Execute the SP with different parameter values:
EXEC dbo.uspGetEmployeeDetails @userAccess = 'userHR'  -- only 3 rows
GO
EXEC dbo.uspGetEmployeeDetails @userAccess = 'userFin' -- only 2 rows
GO
EXEC dbo.uspGetEmployeeDetails @userAccess = 'userCEO' -- all 8 rows
GO

The above method is prone to issues, like SQL Injection and any other user can apply other user’s User-Name and get the information that he is not allowed to see. With this type of method you have to apply another security layer at the application level so whenever a user executes the SP it gets executed with the same user’s User-Name.
 

–> The new Row Level Security feature let you:
– apply this security at the database level
– and there is no need to apply the WHERE clause filter for the User-Name.

This makes the security system more reliable and robust by reducing the surface area of your security system.

–> Step 1.b. Grant Read/SELECT access on the dbo.Employee table to all 3 users:

GRANT SELECT ON dbo.Employees TO userCEO;
GO
GRANT SELECT ON dbo.Employees TO userHR;
GO
GRANT SELECT ON dbo.Employees TO userFin;
GO

 

–> Step 2. Let’s create an Inline Table-Valued Function to write our Filter logic:

CREATE FUNCTION dbo.fn_SecurityPredicateEmployee(@mgrCode AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_SecurityPredicateEmployee_result
	-- Predicate logic
	WHERE @mgrCode = USER_NAME() 
	OR USER_NAME() = 'userCEO'; -- CEO, the admin should see all rows
GO

This function returns value 1 when:

– a row in the MgrCode (i.e. the Manager Code) column is the same as the user executing the query (@@mgrCode = USER_NAME())

– or if the user executing the query is the Top Boss user (USER_NAME() = ‘userCEO’)
 

–> Step 3. Create a security policy adding the function as a filter predicate:

CREATE SECURITY POLICY ManagerFilter
ADD FILTER PREDICATE dbo.fn_SecurityPredicateEmployee(MgrCode)  -- Filter Column from dbo.Employee table
ON dbo.Employees
WITH (STATE = ON); -- The state must be set to ON to enable the policy.
GO

The above Security Policy takes the Filter Predicate Logic from the associated Function and applies it to the Query as a WHERE clause.
 

–> Now let’s again check the records after applying “Row Level Security”:

SELECT * FROM dbo.Employees; -- 0 rows, 
GO

The simple “SELECT *” statement will fetch me zero rows after applying RLS, as my user ID is not configured to have access to any of those rows & Table.

–> And if you check in the Execution Plan of above SELECT statement without WHERE clause, it will show you the Filter Predicate that is added by the Security Policy defined in Step #3 for applying RLS on this table, which looks like this:

[TestManDB].[dbo].[Employee].[MgrCode]=user_name()
OR user_name()=N’userCEO’

SQL Server 2016 - Row Level Security 10
 

–> Let’s check the 3 users we created and provided them customized access to the dbo.Employee table and rows in it:

-- Execute as our immediate boss userHR (3 rows): 
EXECUTE AS USER = 'userHR';
SELECT * FROM dbo.Employees; -- 3 rows
REVERT;
GO

-- Execute as our immediate boss userFin: 
EXECUTE AS USER = 'userFin';
SELECT * FROM dbo.Employees; -- 2 rows
REVERT;
GO

-- Execute as our Top boss userCEO (8): 
EXECUTE AS USER = 'userCEO';
SELECT * FROM dbo.Employees; -- 8 rows
REVERT;
GO

–> The results of the above 3 SELECTs looks like this:

SQL Server 2016 - Row Level Security 01

So, as you can see the three users we created resulted in different results:

– The HR & Finance users got just 3 & 2 rows, respectively.

– But the admin CEO user got all the 8 rows.
 

–> Thus, by using RLS Filter Predicate feature in SQL Server 2016 you can create your own customized Security by creating an Inline Table-Valued Function that is linked to the Security Policy for your Table.
 

–> Check Block Predicates with RLS in my [next post, Part 2].
 

–> Final Cleanup

DROP SECURITY POLICY [dbo].[ManagerFilter]
GO
DROP FUNCTION [dbo].[fn_SecurityPredicateEmployee]
GO
DROP TABLE [dbo].[Employee]
GO

DROP PROCEDURE dbo.uspGetEmployeeDetails
GO

 

Check the same demo on YouTube:


 

Check & Like my FB Page