Showing posts with label SQL 2016. Show all posts
Showing posts with label SQL 2016. Show all posts

Friday 23 March 2018

An approach to building transactional systems in SharePoint

Overview:  It is common with modern SharePoint development to store transaction high volume data inside a SQL database and expose the application data using WebAPI or a WCF.  The application, e.g. SPA's, Angular or SharePoint pages itself merely calls the "web service" and viola you have an application that is fast and complex with the SharePoint world.


Problem:  When the WCF/WebAPI goes to the database we use a single account (single account principal).  This is an age-old problem in BI, and web applications.  The solution options are to have the security in the database, or each user needs to have a SQL login. 

Initial Hypothesis:  Generally in the last 20 years the majority of application go for the single principal data access approach.  This means there is no logging in SQL natively and you need to pass in the user's context (usually a username or email address).

My Solution:  I use the single access account principal, so I connect using the same account (either encrypt or use something like Azure Vault, in the old days this was the web.config entry with a username and password.  Each request needs to be unique so I pass in the username with the request, and my queries have users and roles and using these relationships I can validate that my user has rights to perform CRUD operations.  I am a huge fan of SQL 2016, as its performance is miles ahead of SQL 2014 and it supports "TemporalTables".  Now with other older SQL instances, you could build your own database logging (tomb tables is what I use to refer to it as).  Worth noting is that Entity Framework does not support Temporal Tables yet, but surely this will come. 
Summary
This solution provides a flexible, fast HA (assuming AOAG) transaction secured system with non-repudiation and full logging.  Overall I find this a great approach to building out complex solutions for my clients. 

This approach also provides an easy re-usable API that can be used to allow other applications and business partners to integrate with the solution.  It also allows for a mobile application UI to be easily added as the API are already in place.

Note: Temporal Tables have been available since SQL 2016 and are available on Azure SQL.

Sunday 13 August 2017

SQL 2016 thoughts - 5 reasons to consider it

A couple of good reasons to use SQL 2016:

  1. Temporal Tables - Use instead of Tomb tables/highlander tables.
  2. Row Level Security - specify access to a row for users and groups
  3. Always encrypted - encrypts data at rest on a column (don't confuse with column level encryption)
  4. Read data from queries in JSON object format - nice
  5. Performance - The jump to using SQL 2016 is massive.  I've even heard people recon double the performance on the same kit as SQL 2012.  Multiple Temp DB tables is a big part of this.  And ultimately the best reason.