SQL Server 2016 Features – Temporal Tables

SQL Server 2016 introduces support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.
Temporal tables are also called as System-Versioned tables and used to record all data changes.In this scenario, SQL Server creates 2 tables, one to store the current data and one to store historical data.Two datetime2 columns are added to both the current and historical tables where SQL Server stores the valid date range of the data: SysStartTime and SysEndTime. The current row will have a SysEndTime value of 9999-12-31, and all records inserted within a single transaction will have the same UTC time.
Temporal table considerations and Limitations
Use cases for temporal tables include:

• Auditing all data changes and performing data forensics when necessary
  
• Reconstructing state of the data as of any time in the past
  
• Calculating trends over time
  
• Maintaining a slowly changing dimension for decision support applications
  
• Recovering from accidental data changes and application errors

Create Temporal Data

CREATE TABLE dbo.Employee 
(  
     EmployeeID int NOT NULL PRIMARY KEY CLUSTERED, 
     ManagerID int NULL, 
     FirstName varchar(50) NOT NULL,
     LastName varchar(50) NOT NULL,
     SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
     SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL, 
     PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)   
)  
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Query Temporal Data
Use the FOR SYSTEM_TIME clause with one of the following four sub-clauses to return the data for a specific date or within a date range:

• AS OF <date_time>
• FROM <start_date_time> TO <end_date_time>
• BETWEEN <start_date_time> AND <end_date_time>
• CONTAINED IN (<start_date_time>, <end_date_time>

SELECT * 
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2015-12-21 09:00:00'
WHERE ManagerID = 3

SELECT * 
FROM dbo.Employee
FOR SYSTEM_TIME FROM '2015-07-01' TO '2015-12-31' 
ORDER BY EmployeeID, SysStartTime

Technorati Tags: SQL 2016,Temporal Tables

SQL Server 2016 – Stretch Database – Azure Pricing

I was so interested to see Stretch Database working between my home PC and Azure and got that set up and running and left to run for a few days. I got a notification from Azure saying that I have reached my credit limit for the month on Azure and plan got disabled.
Good that I set that up to alert me. I thought Stretch Database pricing would be typical to other Azure services but noticed that it is charged by hour also varying based on chosen compute usage.
Pricing looked interesting as minimum per month is around $2,800 per month. Need to think about how to use it efficiently even if it is for the Enterprise.
https://azure.microsoft.com/en-us/pricing/details/sql-server-stretch-database/

Technorati Tags: SQL 2016,Stretch Database,Azure SQL Database,Pricing

SQL Server 2016 Features – Dynamic Data Masking

Data privacy is a major concern for lots of organisations given services are out sources, moving data to the cloud and increase in volume of data. Examples of these include sensitive details like credit card numbers, Date of Birth, Financial and HR Data. Traditionally obfuscation happens at the application layer and still cannot avoid exposure of sensitive data.

SQL Server 2016 and Azure SQL DB now offer a built-in feature to obsfuscate sensitive data using Dynamic Data Masking. (DDM)

Define a Data mask at the column level when you CREATE or ALTER a table. Use one of the following masks to do either full or partial masks.

Full mask of column contents

ALTER TABLE [TableName] ALTER COLUMN [ColumnName] ADD MASKED WITH (FUNCTION = ‘default()’)

Partial mask of column contents

ALTER TABLE [TableName] ALTER COLUMN [ColumnName] ADD MASKED WITH (FUNCTION = ‘partial(2, "AA@AAA",3)’)

Mask type	Syntax	Alter syntax	Description
Default	PhoneNo varchar(15) MASKED WITH (FUNCTION = ‘default()’)	ALTER COLUMN PhoneNo MASKED WITH (FUNCTION = ‘default()’)	The complete value is masked with a character appropriate to the data type; for example, characters are shown as xxx, and numeric with 0.
Email	Email varchar(60) MASKED WITH (FUNCTION = ‘email()’) NULL	ALTER COLUMN Email WITH (FUNCTION = ‘email()’)	Exposes the first letter and replaces the .suffix with .com; for example, cxxxxx@xxxxx.com.
Custom String	Name nvarchar(50) MASKED WITH (FUNCTION = partial(2,”xxxx”, 1)’)	ALTER COLUMN Name ADD MASKED WITH (FUNCTION = ‘partial(5, “xxxx”, 0)’)	Partially masks the value, exposing a number of characters at the start and end with custom padding. Syntax is (FUNCTION = 'partial(prefix, [padding], suffix)').
Random	Ac_Number int MASKED WITH (FUNCTION = ‘random(0, 999999)’)	ALTER COLUMN [Year] ADD MASKED WITH (FUNCTION = ‘random(1, 9999)’)	For numeric data types. Replaces the actual value with a random value in a given range.

Technorati Tags: SQL 2016,Data Masking,Obfuscation

APIDays NZ 2016 – Day 2

Day 2 started with a great keynote from Markus Lanthaler from Google on JSON-LD and hydra. JSON-LD for Linked Data opens up lot of potential opportunities to share data on the Web and let other applications to consume them. Also he mentioned about schema.org to create, manage schemas for data. Good to know that schema.org is sponsored by Google, Microsoft, Yahoo and Yandex and has a huge support from them. Mark up your emails with JSON-LD and this is what the user experience will be on Gmail.

Next session was Expert Panel discussion facilitated by Dennis Ashby with members from Westpac, Certus and IBM. Really frank and open discussion and then attended Gareth and Rick's session on Hypermedia API Architecture Patterns. Learnt a lot about Hypermedia in the last couple of days.
Can we really create and use APIs in Legacy environments? Yes, it's possible and that's the talk from Paul Williams and it was a great session sharing the architecture for a real world client. Missed the API Design Aesthetics session but lots of tweets about the session. Keen to watch the session once published online. Instead I attended preparing an Organisation for Decentralisation presented by Ian from IntegrationWorks. Ian clearly talked about how culture, collaboration, automation are key for decentralisation. Great talk Ian got lots of insights.
Next is Axel's session on Implementing API platform, lessons learned from real world customers, yes of course real world is totally different complex and has to pass through lot of hurdles to achieve outcomes. Start something simple and prove to the business, key takeaway from the session.
How Telco industry will look in 2020, good presentation from Nisha Clark from Vodafone and explained how customer experience is key in Telco industry and how much they had to do behind the scenes with legacy systems still providing better experience.
Todd blown us away with his demo on Hypermedia clients and I really need to watch that again. Last it was my presentation on designing pragmatic RESTful APIs, more focusing on getting our basics right when thinking about designing APIs. APIs must be easy to use or you will loose your consumers. Also mentioned about the lessons I personally learnt from the conference on API Design. Thanks for APIDaysNZ and Saul Caganoff for providing me the opportunity to speak at the conference.
Great Speakers and over loaded with lots of information and met lots of people. Thoroughly enjoyed the conference and looking for the next year too. It worth mentioning again that it's a well organised event, great speakers, good food and of course thanks to all sponsors, volunteers and Apigee specially for keeping us awake with your coffees throughout the 2 days.
RESTful API Design - Second Edition [Courtesy: Apigee]

Technorati Tags: APIDaysNZ,API,JSON-LD,Hypermedia,schema.org,API Graph

APIDaysNZ 2016 – Day 1

Day 1 of the APIDays NZ 2016 conference started with 2 key notes. Started with Lillian Grace on figure.nz and totally impressed with the presentation and how much she was passionate about data. Didn't know about figure.nz before but will start using it now to know more about NZ. Next one was from Glenn Block from Auth0 on Hypermedia. After this session my thinking about Hypermedia changed completely. Initially I was thinking it was complex and is it really providing any Business value? Glenn cleared this all with his presentation.
Following on with that we had 2 streams of sessions. One focusing on Technical and another one on Strategy. Irresistible APIs was my next pick and thoroughly understood how to design APIs to make developers life easy. Then the session with Rajeev on Go Digital or Die Trying with the clear explanation on what Digital really mean for an Organisation. It's all out 3 things. Bits, Analytics and driving towards an Eco system along with sharing some resources from the Apigee website. I personally used the resources from Apigee website when I was thinking about designing APIs.
Then a great presentation on Auckland Museum and they use APIs to share the wealth of information they have with the wider world. It was really impressive to hear about the information that they hold currently. Steven Cooper from Xero took us through how to use Machine Learning and various patterns to analyse the API error logs and find the pattern and address them. Really cool to find an excellent use case for Machine Learning. Thomas Cully then explained us on what micro services really meant and how they shouldn't be compared with monolith applications.

Ian Randall from PushPay shared his experience around the development of their Public and Mobile APIs and also kindly shared on how they use continuous delivery to deliver new features for their customers. I was impressed when he mentioned about new features getting deployed every 2 hours.
Swagger or RAML what do we use for API specification. Did we come to a conclusion? The answer is it depends and what works for you. Justin and Giovanni gave a good comparison between Swagger and RAML. Personally, I like using Swagger than RAML. More a personal choice though. I will write about why in a separate blog post.
Final session for the day was from Abhishek Tiwari on Stored Procedures as a Service and it was great to have a open talk about how they are working on solving their issue with the legacy implementation through APIs. Really interesting for people to know that even if you are stuck in a legacy environment still there is always light at the end of the tunnel so nothing wrong in trying to incorporate APIs.
On the whole it was a great day with a good mix of speakers and of course kudos to the volunteers and sponsors. Really well organised event and of course no complaints about the food and I got my Vegetarian options too.

Technorati Tags: APIDaysNZ,API,figure.nz,Auth0,Swagger,RAML