Know what is new in MS SQL Server 2016 ?
Microsoft has discharged SQL Server 2016, adding new safety features, improved question identification, Hadoop integration, hybrid cloud capabilities, and R analytics to its information server, at the side of various different enhancements.
Calling SQL Server a “database server” doesn’t do justice to its scope. the most parts are:
Database engine Runs on Windows with a UNIX operating system version secure. Supports Transact-SQL, Microsoft’s flavor of SQL supplemented with extensions specific to SQL Server. Includes high-availability options like replication and failover.
Reporting Services permits you to style and deploy reports via an online portal.
Analysis Services. Advanced data processing and coverage victimisation multi-dimensional information models. Integrates with the stand out program via the PowerPivot add-in.
R Services New in SQL Server 2016, integrates the R programing language, used for applied mathematics analysis, into SQL Server. supported Microsoft’s acquisition of Revolution Analytics.
Data Quality Services Analysis tools to enhance the standard of information by finding incorrect, incomplete, or duplicated information by relevance a knowledge domain, which can be either internal or external.
PolyBase New in SQL Server 2016. Integrates SQL Server with external information either in Hadoop or in Azure Blob storage.
Azure SQL Microsoft’s SQL Server-as-a-service, low maintenance with the convenience of auto-scaling, however supporting solely a set of the complete SQL Server. Microsoft conjointly recommends running SQL Server in Associate in Nursing Azure Virtual Machine (VM) for full options, although at a better price.
SQL information Warehouse a brand new Azure service that supports databases of up to 60TB, versions 250GB to 1TB for Azure SQL, however optimized for information storage with massively data processing for queries however a additional restricted set of T-SQL on the market.
It all adds up to a giant product, or family of merchandise. Everything is constructed in, Microsoft claims, not like different vendors that charge additional for various modules. this is often solely true, though, if you get the Enterprise Edition, that doesn’t return low cost.
SQL Server 2016 follows the 2014 edition, that the large new feature was in-memory OLTP (online dealings processing), codenamed Hekaton, in impact Associate in Nursing alternate information engine optimized for in-memory information and more increased by keep procedures compiled to native code, that once combined have potential for Brobdingnagian performance gains.
There is nothing quite therefore obvious in SQL Server 2016, although in-memory OLTP is truly additional usable than before, with a far wider set of T-SQL supported, as well as FOREIGN KEY, distinctive and CHECK constraints, and in native compiled keep procedures OR, NOT, choose DISTINCT, OUTER JOIN, and subqueries in choose.
Microsoft has other vital safety features in SQL Server 2016. “Always Encrypted” permits you to encipher information all the approach from server to consumer, since it depends on client-side keys. These ar distributed to purchasers. a brand new perpetually Encrypted-enabled driver is needed, however the secret writing is clear to applications.
The draw back is that new drivers ar needed. Microsoft has updated its .NET, ODBC and JDBC drivers however there ar some caveats; drivers vary in whether or not or not they support custom key store suppliers, as an example, and not all information varieties are tested with this ODBC driver. Thorough testing is usually recommended before going into production with this.
Also new is row-level security. the concept is that information access will be restricted at the information engine level in order that users solely see what’s relevant to them. as an example, a sales person would possibly solely be allowed to ascertain their own sales records, despite different records existing within the same tables. this is often enforced victimisation inline table functions that reference the user login, combined with a security policy. Care is required, otherwise it’s simple to make policies that enable users to make records they can’t scan.
Dynamic information Masking is another new feature. A typical use is with mastercard numbers. the total range should be keep, however several users solely got to see the previous couple of digits. will|you’ll|you’ll be able to} specify this within the table definition then solely users with the UNMASK permission can see the entire range.