SQL Server 2016 Features – JSON support

SQL Server 2016 now includes support that means developers can output their query results to a JSON format.

FOR JSON Clause

Format data for JSON with control over objects

SELECT ProductNumber, Name, ListPrice FROM Production.Product FOR JSON PATH;

Format data for JSON with automatic formatting

SELECT ProductNumber, Name, ListPrice FROM Production.Product FOR JSON AUTO;

SQL Server 2016 supports the following built-in functions for JSON:

• ISJSON tests whether a string contains valid JSON.
• JSON_VALUE extracts a scalar value from a JSON string.
• JSON_QUERY extracts an object or an array from a JSON string.

Technorati Tags: SQL 2016,JSON

SQL Server 2016 Features – Query Store

The Query Store is a feature new to SQL Server that helps database administrators (DBAs) and developers find the cause of troublesome queries. It works at the database level and is compatible with both on-premise and Azure SQL databases. Query plans have long been useful in helping to detect code that needs performance tuning. The Query Store retains historical plans, so comparisons can be made over time, and you can see when a plan has changed.

The Query Store cannot be enabled for the master, msdb, or tempdb system databases. It is not an option on the model database either, so you cannot set it as a default option for new databases

Enabling Query Store

ALTER DATABASE [TSQL] SET QUERY_STORE = ON;

When you enable the Query Store, SQL Server creates a Query Store subfolder under the database node, in the Object Explorer in SSMS containing the following views:

• Regressed Queries
• Overall Resource Consumption
• Top Resource Consuming Queries
• Tracked Queries

The views can be used immediately but, depending on the workload of the database, it will take approximately one day for the Query Store to gather accurate metrics. Query Store collates metrics for:

• CPU time
• Duration
• Execution count
• Logical reads
• Logical writes
• Memory consumption
• Physical reads

Technorati Tags: SQL 2016,Query Store

SQL Server 2016 Features – Operational Analytics

Operational Analytics is a new feature in SQL Server 2016. It changes the indexing on database table so it supports both OLTP and Analytical Operations.
Until now, running analytical functions against a live transactional SQL Server database has not been an option, because the design of the underlying model fits a transactional system and not for crunching big datasets. SQL Server 2016 offers the ability to run analytics against live data, this makes the analytical data up to date to the last transaction. Also this is done without negative performance effects.
SQL Server 2016 introduces a new type of Columnstore index for real-time Operational Analytics. It supports updateable Non-Clustered Columnstore Index (NCCI). This will allow OLTP and OLAP to running at the same time.

Technorati Tags: SQL 2016,Operational Analytics

SQL Server 2016 Features – Stretch Database

Stretch Database is a new feature in SQL Server 2016 that will allow to migrate old data from on-premises to Microsoft Azure and still provide the ability to access the data without making any query changes. Stretch Database provides the advantage of the lower costs of Azure Database and reduces the need to buy hardware and storage to support on-premise data archiving. Enterprise Storage is not always cheap.
Stretch Database is ideal for entire tables or part of a table. e.g. Orders that are older than a certain date or Customers who haven't made any orders for more than a year etc.
Stretch Database transfers data securely using Always Encrypted, so data is encrypted as it is being transferred from on-premises to Azure.
Note: Stretch Database only works between an on-premises SQL Server Database and Azure SQL Database, and cannot be used from one local SQL Server database to another.
Limitations of Stretch Database
Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

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

Continuous Integration vs Continuous Delivery vs Continuous Deployment

Continuous Integration (CI) is a development practice where developers are required to integrate their code to shared repository several times a day. Each time when the code is checked in, it is verified using an automated build so failures can be detected early, notified and fixed.
Continuous Delivery is a series of practices designed to ensure that code can be rapidly and safely deployed to production by delivering every change to a production-like environment and ensuring business applications and services function as expected through rigorous automated testing. Since every change is delivered to a staging environment using complete automation, you can have confidence the application can be deployed to production with a push of a button when the business is ready.
Continuous Deployment is the next step of continuous delivery: Every change that passes the automated tests is deployed to production automatically. Continuous deployment should be the goal of most companies that are not constrained by regulatory or other requirements.

Reference: Image from puppet.com website

Technorati Tags: Continuous Integration,Continuous Delivery,Continuous Deployment