Join us via Email

Introduction to System-versioned temporal tables – New database feature of SQL Server 2016

System-versioned temporal tables is new database feature of SQL Server 2016. Another improved version of table level auditing with some new features after trigger, CT & CDC. System-versioned temporal tables stores data of table data modification history. Temporal is a database feature that was introduced in ANSI SQL 2011.

Most common usage for temporal tables are:

  • Data Auditing
  • Repairing or recovering record level corruptions
  • Recovering from missing records
  • Trend Analysis

How System-versioned temporal tables works? –

System-versioned temporal table contains actual data and corresponding History table contains old data. When you perform any DML operation on table, Old version of data got moved to history table.

How to create System-versioned temporal tables? –

We have 3 possible ways to System-versioned temporal tables.

1. Creating a temporal table with an anonymous history table

2. Creating a temporal table with a default history table

3. Creating a temporal table with a user-defined history table

How to stop System-versioning and drop actual & history table? –

To stop system versioning and drop actual data and history table, we need to off system versioning on table before drop table execution.

USE <DB_Name>

GO

ALTER TABLE [dbo].<Table_Name> SET ( SYSTEM_VERSIONING = OFF )

GO

DROP TABLE [dbo].<Table_Name>

GO

DROP TABLE [dbo].<Table_Name_History_Table>

GO

How to query System-versioned temporal tables? –

You can perform normal select statement on both actual and history table like other normal tables. But System-versioned temporal tables got new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables. This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

Select * from Department_UserDefined

Select * from DepartmentHistory_UserDefined

select * from Department_UserDefined FOR SYSTEM_TIME AS OF ‘2018-01-21 09:34:44.4731356’

5 Clauses FOR SYSTEM_TIME:-

Expression Qualifying Rows Description
AS OF<date_time> SysStartTime <= date_time AND SysEndTime > date_time Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past. Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the <date_time> parameter.
FROM<start_date_time>TO<end_date_time> SysStartTime < end_date_time AND SysEndTime > start_date_time Returns a table with the values for all row versions that were active within the specified time range, regardless of whether they started being active before the <start_date_time> parameter value for the FROM argument or ceased being active after the <end_date_time>parameter value for the TO argument. Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values for all row versions that were active at any time during the time range specified. Rows that ceased being active exactly on the lower boundary defined by the FROM endpoint are not included and records that became active exactly on the upper boundary defined by the TO endpoint are not included also.
BETWEEN<start_date_time>AND<end_date_time> SysStartTime <= end_date_time AND SysEndTime > start_date_time Same as above in the FOR SYSTEM_TIME FROM <start_date_time>TO<end_date_time> description, except the table of rows returned includes rows that became active on the upper boundary defined by the <end_date_time> endpoint.
CONTAINED IN (<start_date_time> , <end_date_time>) SysStartTime >= start_date_time AND SysEndTime <= end_date_time Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.
ALL All rows Returns the union of rows that belong to the current and the history table.

How to check table type? –

SYS.TABLES system view got new columns to check table is SYSTEM_VERSIONED_TEMPORAL_TABLE or HISTORY_TABLE or NON_TEMPORAL_TABLE. You can check the list of all SYSTEM_VERSIONED_TEMPORAL_TABLE & HISTORY_TABLE from SYS.Tables.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.sqlservercentral.com/blogs/mssqlfun/

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

SQL Server 2016 – How to use Dynamic Data Masking (DDM)?

Dynamic Data Masking (DDM) is new feature of SQL Server 2016. DDM helps in preventing unauthorized access to sensitive data. DDM enables only privilege users to have complete data. Un-privilege users will have access of MASKED data only.

As an example, a call center support person may identify callers by several digits of their social security number or credit card number, but those data items should not be fully exposed to the support person. A masking rule can be defined that masks all but the last four digits of any social security number or credit card number in the result set of any query. For another example, by using the appropriate data mask to protect personally identifiable information (PII) data, a developer can query production environments for troubleshooting purposes without violating compliance regulations.

Benefits

1. Limited data access increase data security and reduce possibilities of unauthorized data access

2. No change at Application code.

3. Current complex logic in application to MASK sensitive data is no more needed. It greatly simplify the design and coding of security in your application.

4. Data is not changed at storage, It MASKED at runtime on basis of masked function used.

Limitations and Restrictions

1) A masking rule cannot be defined for the following column types:

· Encrypted columns (Always Encrypted)

· FILESTREAM

· COLUMN_SET or a sparse column that is part of a column set.

· Computed column

· A column with data masking cannot be a key for a FULLTEXT index.

2) Backup taken by user without UNMASKED permission, will have UNMASKED data. This is because data masking is dynamically done at time of data view. To secure backups, We have TDE or backup encryption.

3) Data Export done by user without UNMASKED permission will result MASKED data to be exported.

4) Use of SELECT INTO or INSERT INTO by user with UNMAKED permission to copy data from a masked column into another table will copy MASKED data in new table.

Permissions

1. No additional permission needed to create table with DDM columns. Only the standard CREATE TABLE and ALTER on schema permissions will be enough.

2. To Adding, replacing, or removing the mask of a column, requires the ALTER ANY MASK permission and ALTER permission on the table.

3. User with super permissions (like sysadmin, DB_Owner etc.) or UNMASK permission can view the unmaked data.

We have 4 functions provided by Microsoft to create dynamic masked columns for different data types and purpose.

Function Description
Default Full masking according to the data types of the designated fields.
For string data types, use XXXX or fewer Xs if the size of the field is less than 4 characters (char, nchar, varchar, nvarchar, text, ntext).
For numeric data types use a zero value (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, and real).
For date and time data types use 01.01.1900 00:00:00.0000000 (date, datetime2, datetime, datetimeoffset, smalldatetime, time).
For binary data types use a single byte of ASCII value 0 (binary, varbinary, image).
Email Masking method which exposes the first letter of an email address and the constant suffix “.com”, in the form of an email address. . aXXX
Random A random masking function for use on any numeric type to mask the original value with a random value within a specified range.
Custom String Masking method which exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix
Note: If the original value is too short to complete the entire mask, part of the prefix or suffix will not be exposed.

How to create table with DDM columns?

CREATE TABLE EMPINFO_DDM

(

EMPID INT IDENTITY(1,1),

FIRSTNAME NVARCHAR(50) NOT NULL,

SALART INT,

EMAIL VARCHAR(100),

JOINDATE DATETIME,

FNAME_DEFAULT NVARCHAR(50) MASKED WITH (FUNCTION = ‘DEFAULT()’) NOT NULL,

SALARY_DEFAULT INT MASKED WITH (FUNCTION = ‘DEFAULT()’) NOT NULL,

EMAIL_DEFAULT NVARCHAR(50) MASKED WITH (FUNCTION = ‘DEFAULT()’) NOT NULL,

JOINDATE_DEFAULT DATETIME MASKED WITH (FUNCTION = ‘DEFAULT()’) NOT NULL,

FNAME_EMAIL NVARCHAR(50) MASKED WITH (FUNCTION = ‘EMAIL()’) NOT NULL,

SALARY_EMAIL NVARCHAR(50) MASKED WITH (FUNCTION = ‘EMAIL()’) NOT NULL,

EMAIL_EMAIL NVARCHAR(50) MASKED WITH (FUNCTION = ‘EMAIL()’) NOT NULL,

JOINDATE_EMAIL NVARCHAR(50) MASKED WITH (FUNCTION = ‘EMAIL()’) NOT NULL,

SALARY_RANDOM INT MASKED WITH (FUNCTION = ‘RANDOM(999, 9999)’) NOT NULL,

FNAME_CUSTOM NVARCHAR(50) MASKED WITH (FUNCTION = ‘PARTIAL(0,”XXX-XXXX-XXXX-“,4)’) NOT NULL,

SALARY_CUSTOM NVARCHAR(50) MASKED WITH (FUNCTION = ‘PARTIAL(1,”XXXXXXX”,1)’) NOT NULL,

EMAIL_CUSTOM NVARCHAR(50) MASKED WITH (FUNCTION = ‘PARTIAL(0,”X-X-X”,14)’) NOT NULL,

JOINDATE_CUSTOM NVARCHAR(50) MASKED WITH (FUNCTION = ‘PARTIAL(1,”XXXXXXX”,1)’) NOT NULL

)

How to check if any columns is masked in table?

SELECT NAME COLUMNNAME, IS_MASKED FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = ‘EMPINFO_DDM’

How to get masked columns & used masked functions details?

SELECT C.NAME, TBL.NAME AS TABLE_NAME, C.IS_MASKED, C.MASKING_FUNCTION

FROM SYS.MASKED_COLUMNS AS C

JOIN SYS.TABLES AS TBL

ON C.[OBJECT_ID] = TBL.[OBJECT_ID]

WHERE IS_MASKED = 1;

How to check table with DDM column data?

You can read table data using simple SELECT command, No change in select command needed.

1) Data view by User with super permissions

2) Data view by User having only select permission on table

What happen if USER with limited access used “SELECT INTO or INSERT INTO to copy data from a masked column into another table “?

If User with limited access use SELECT INTO or INSERT INTO to copy data from a masked column into another table. New table will get masked data as well.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.sqlservercentral.com/blogs/mssqlfun/

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

Performance Dashboard Reports in SQL Server

SQL Server Management Studio comes up with multiple standard reports that show basic performance information. These reports are available without any additional installation and with no extra configuration.

But if you think this is not enough, Microsoft lunch Performance Dashboard Reports with SQL Server 2012.

Let’s deploy and configure Performance Dashboard Reports

a. You can download Microsoft SQL Server 2012 Performance Dashboard Reports setup http://www.microsoft.com/en-us/download/details.aspx?id=29063

b. Run setup with admin privileges

Click Next

Select “I accept the terms in the license agreement” and click Next

Enter your & your organization name and Click Next

Click Next

Click Install

Click Finish, Installation Completed

c. You have to execute setup.sql using SQL Server Management Studio (SSMS), open the setup.sql script from your installation directory (default of %ProgramFiles(x86)%\Microsoft SQL Server\110\Tools\Performance Dashboard) and run the script. Close the query window once it completes.

d. In the Object Explorer pane in SSMS, right mouse click on the SQL Server instance node, then choose Reports-Custom Reports. Browse to the installation directory and open the performance_dashboard_main.rdl file.

Select “performance_dashboard_main.rdl” and click open

Click Run, You will get your report.

e. Now your report is configured and you can get it through right mouse click on the SQL Server instance node, then choose Reports- performance_dashboard_main

f. Performance Dashboard Main Report

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.sqlservercentral.com/blogs/mssqlfun/

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

System.ConsoleColor error while running powershell in SQL agent job step

Issue: While running powershell in SQL agent job step, I am getting below error: –

A job step received an error at line 3 in a PowerShell script. The corresponding line is ‘$space.ForegroundColor = $host.ui.rawui.ForegroundColor’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Exception setting "ForegroundColor": "Cannot convert null to type "System.ConsoleColor" due to invalid enumeration values. Specify one of the following enumeration values and try again. The possible enumeration values are "Black, DarkBlue, DarkGreen, DarkCyan, DarkRed, DarkMagenta, DarkYellow, Gray, DarkGray, Blue, Green, Cyan, Red, Magenta, Yellow, White"." ‘. Process Exit Code -1. The step failed.

Solution: This error occurs while you are using clear screen option in script part of SQL agent job step. Please check your script and remove cls or clear-host keywords.

I removed it and it start working fine.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.sqlservercentral.com/blogs/mssqlfun/

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

SSISDB for SQL Server Integration Services

Microsoft introduced some major changes in SQL Server Integration Services with SQL Server 2012.

Brand new “SSISDB” database launched as SSIS catalog “A New repository database of Integration Services”. This new repository database brings a lot of abilities for developers, testers and administrators.

Prior to SQL 2012 all SSIS packages are stored either in MSDB or in file system. Many times it becomes critical decision to decide where should our package or config files store. This also makes Deployment & administration difficult.

With SSISDB, Packages can be directly deployed to SSISDB using SQL Server Data Tools for BI Studio. Packages keep a deployment history so you can even rollback some revisions of your package.

Creating SSISDB

SSISDB is not installed by default with installation of SQL Server 2012 or above. When you connect to SQL Server using SSMS, you will find new folder “Integration Services Catalogs”. The catalog is not set up by default, you will have to create it.

When you click on create catalog, below window will appear. You need to select enable CLR integration option to use SSIS catalog. Enter the password to protect the database master key that is used for encrypting the catalog data. This password is very important & sensitive, so make sure to remember this password or save it in a secure location. The catalog automatically encrypts the package data and sensitive values. The catalog also automatically decrypts the data when you retrieve it.

When you click “OK”, SSISDB catalog will be created along with SSISDB database. SSISDB created by restoring “<Installation Directory>\MSSQL\120\DTS\Binn\SSISDBBackup.bak”. SSISDBBackup.bak backup file is available in installation directory after successful installation of Integration services. Although SSISDB is created automatically while enabling SSISDB catalog but still it will be considered as User database not as System database,

SSISDB Catalog default reports

SSISDB catalog comes with 4 different reports along with dashboard.

SSISDB catalog Dashboard

Rename SSISDB catalog database

You should not rename SSISDB because it will cause SSISDB catalog inaccessible.

When you rename SSISDB it will delink all your SSIS catalogs & packages. After rename, you will not find any package under “Integration Services Catalogs”. The best part is, when you rename it back to SSISDB, all your catalogs and packages will be visible again without any additional efforts.

Catalogs and Packages unvisible under “Integration Services Catalogs” after rename

Catalogs and Packages visible under “Integration Services Catalogs” after renaming back to original

Manage Size of SSISDB catalog database

Microsoft introduced SSISDB catalog database to keep trace of each execution, project version and other details which is very typical activity prior to SQL Server 2012. But all this thing can make your SSISDB database very large.

You can change SSISDB database catalog configuration to change retention window and other parameters, impacting its size. Catalog properties effect SSISDB database size as below:-

· Clean logs periodically (set to True)

· Retention period (set to specific number of days –the larger the number of days the more prevalent the problem could be)

· Periodically remove old versions (set to true)

· Maximum number of versions per project

Check Catalog Properties using SSMS

Check Catalog Properties using T-SQL

SELECT * FROM SSISDB.CATALOG.CATALOG_PROPERTIES

You can change catalog properties in above SSMS window or like below by using T-SQL

EXEC SSISDB.CATALOG.CONFIGURE_CATALOG RETENTION_WINDOW, <NO.OF DAYS>

SSIS Server Maintenance job

To maintain SSISDB database size according to retention configured above, SQL Server creates “SSIS Server Maintenance job” when we enable SSISDB catalog feature.

The job is responsible of maintaining history as per the retention window and maintains a maximum number of versions per project. Ensure Job is enabled on server.

“SSIS Server Maintenance job” Job has 2 Steps:-

1) Remove operation records

2) Remove old versions per project on basis of set retention

Types of Logging for SSIS server through SSISDB catalog

Logging Level Description
None Logging is turned off. Only the package execution status is logged.
Basic All events are logged, except custom and diagnostic events. This is the default value.
RuntimeLineage Collects the data required to track lineage information in the data flow. You can parse this lineage information to map the lineage relationship between tasks. ISVs and developers can build custom lineage mapping tools with this information.
Performance Only performance statistics, and OnError and OnWarning events, are logged.
The Execution Performance report displays Active Time and Total Time for package data flow components.
Verbose All events are logged, including custom and diagnostic events.

Source: According to MSDN https://msdn.microsoft.com/en-IN/library/hh231191.aspx

You can change logging as per requirements using SSMS. If user need some specific logging, Customized logging option is also available.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.sqlservercentral.com/blogs/mssqlfun/

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

microsoftcommunitycontributor
%d bloggers like this: