What’s New in SQL Server 2014 since SQL Server 2005

What’s New in SQL Server 2014 since SQL Server 2005

PERFORMANCE & SCALE

o        In-Memory OLTP

o        Enhanced In-Memory ColumnStore for DW

o        Support for 640 logical proc. & 4 TB memory

o        Support to 15,000 partitions

o        Resource Governor IO governance

o        Buffer Pool Extension to SSDs

o        Query optimization enhancements

o        SysPrep at cluster level

o        Predictable performance with tiering of compute, network, and storage with Windows Server 2012 R2

o        Data Compression with USC-2 Unicode support

o        Backup Compression

HIGH AVAILABILITY

o        SQL Server AlwaysOn

o        Database Mirroring

o        Failover Clustering

o        Database Snapshots

o        Delayed Durability

o        Recovery Advisor

o        Windows Server Core

o        Live Migration

o        Online Operations

o        Clustered Shared Volume support, VHDX support (Windows Server 2012 R2)

o        Manage on-premises and cloud apps (System Center 2012 R2)

SECURITY

o        User-Defined Server Roles

o        Default Schema for Groups

o        SQL Server Audit

o        Transparent Data Encryption

o        Extensible Key Management

o        Standards-based Encryption

o        SQL Server Fine-grained Auditing

o        Enhanced separation of duty

o        CC certification at High Assurance Level

o        Backup encryption support

PROGRAMMABILITY

o        SQL Server Data Tools

o        Local DB runtime (Express)

o        Data-tier Application Component project template

o        Data-Tier Application Framework (DAC Fx)

o        Query optimization enhancements

o        Interoperability support (ADO.NET, ODBC, JDBC, PDO, ADO APIs and .NET C/C++, Java, Linux, and PHP platforms)

T-SQL ENHANCEMENTS

o        Enhanced support for ANSI SQL standards

o        Transact-SQL Static Code Analysis tools

o        Transact-SQL code snippets

o        Intellisense

DATA SUPPORT

o        FILESTREAM data type

o        FileTable built on FILESTREAM

o        Remote Blob Storage with SharePoint 2010

o        Spatial data support

o        Full Text Search for unstructured files

o        Statistical Semantic Search

o        Large user-defined data types

PROGRAMMABILITY SUPPORT

o        Support for LINQ and ADO.NET Entity Framework

o        CLR Integration and ADO.NET Object Services

MANAGEABILITY

o        Distributed Replay

o        Contained Database Authentication

o        System Center Management Pack for SQL Server 2012

o        Windows PowerShell 2.0 support

o        Multi-server Management with SQL Server Utility Control Point

o        Data-Tier Application Component

o        Multi-server Management with SQL Server Utility Control Point

o        Data-Tier Application Component

o        Policy-Based Management

o        SQL Server Performance Data Collector

o        Query enhancements

o        SMTP mail for secure DB email w/o Outlook

ACCESS ANY DATA

o        Power Query

o        Windows Azure HDInsight Service

o        Analytics Platform System (PDW V2)

o        Mash up data from different sources, such as Oracle & Hadoop

INSIGHTS WITH FAMILIAR TOOLS

o        Power BI in Office 365

o        Power Map for Excel

o        Mobile interfaces for Power BI

COMPLETE BI SOLUTION

o        SQL Server BI Edition

o        StreamInsight

o        BI Semantic Model

o        SQL Server Data Tools

o        BI Development Studio

o        Microsoft Visual Studio-based report dev tools

o        Change Data Capture for Oracle

ANALYSIS SERVICES

o        Import PowerPivot models into Analysis Services

o        Enhancements on productivity, performance

o        Cube design tools, block computations, and write-back to MOLAP

REPORTING SERVICES

o        Power View

o        Configurable reporting alerts

o        Reporting as SharePoint Shared Service

o        Report Builder 3.0

o        Report Designer

o        Report Manager

DATA QUALITY SERVICES

o        Build organizational knowledge base

o        Connect to 3rd party data cleansing providers

MASTER DATA SERVICES

o        Master Data Hub

o        Master Data Services Add-in for Microsoft Excel

INTEGRATION SERVICES

o        Graphical tools in SSIS

o        Extensible object model

o        SSIS as a Server

o        Broader data integration with more sources; DB vendors, cloud, Hadoop

o        Pipeline improvements

o        Persistent lookups

o        High-performance connectors

o        Data profiling tool

HYBRID CLOUD SOLUTIONS

o        Simplified backup to Windows Azure

o        Support for backup of previous versions of SQL Server to Windows Azure

o        Cloud back-up encryption support

o        Simplified cloud Disaster Recovery with AlwaysOn replicas in Windows Azure VMs

EASY ON-RAMP TO THE CLOUD

o        New Windows Azure Deployment UI for SQL Server

o        Larger SQL Server VMs and memory sizes now available in Windows Azure

o        DAC enhancements: Import/export with Windows Azure SQL Database

COMPLETE AND CONSISTENT FROM ON-PREM TO CLOUD

o        SQL Server Data Tools

o        License Mobility (with SA)

o        Resource Governor enhancements

o        Snapshot backups to Windows Azure via SQL Server Management Studio

This list also includes some technologies that work together with SQL Server such as Azure, System Center, Excel 2013, Power BI for Office 365 and others.

Difference between SQL SERVER 2000,2005,2008 and 2012

SQL SERVER 2000:

1.Query Analyser and Enterprise manager are separate.

2.No XML datatype is used.

3.We can create maximum of 65,535 databases.
4.Cant compress the tables and indexes.
5.Datetime datatype is used for both date and time.
6.No varchar(max) or varbinary(max) is available.
7.No table datatype is included.
8.No SSIS is included.
9.CMS (Cenntral Management Server) is not available.
10.PBM (Policy Based Management) is not available.
11.High Availability Clustering or Log Shipping require Enterprise Edition. Expensive hardware.
SQL SERVER 2005:

1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is introduced.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Cant encrypt the entire database.
13.Can Compress tables and indexes.(Introduced in 2005 SP2)
14.Datetime is used for both date and time.
15.Varchar(max) and varbinary(max) is used.
16.No table datatype is included.
17.SSIS is started using.
18.CMS is not available.
19.PBM is not available.
20. High Availability Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware. 

SQL SERVER 2008:

1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is used.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.TDE (Transparent Data Encryption) to encrypt the entire database introduced in 2008.
13.Can compress tables and indexes.
14.Date and time are separately used for date and time datatype,spatial and timestamps with internal timezone is used.
15.Varchar(max) and varbinary(max) is used.
16.Table datatype introduced.
17.SSIS avails in this version.
18.Central Management Server(CMS) is Introduced.
19.Policy based management(PBM) server is Introduced.
20. Auditing - monitoring of data access.
21. Resource Governor. Restrict users or groups from consuming high levels or resources.
22. Activity monitor: consolidates this information by detailing running and recently executed processes, graphically.
23. Backup compression.

SQL Server 2008 to SQL Server R2• Backup compression is available in R2 standard (was only in Enterprise)
• SQL Server 2008 R2 Express Edition database size limit increased to 10 GB (from 4GB)
• PowerPivot for SharePoint introduced for loading, querying, and managing PowerPivot workbooks that you publish to a SharePoint 2010 server
• PowerPivot for Excel is used to assemble and create relationships in large amounts of data from different sources, and then use that data as the basis for PivotTables and other data visualization objects that support data analysis in Excel.
• Utility Explorer introduced for centralized management of managed SQL instances.
• Master Data Services introduced to manage master (reference) data and maintain an auditable record of that data as it changes over time.
• Enhanced security to prevent an authentication relay attack.

SQL SERVER 2012:

1.The SQL Server 2012 uses 48 bit precision for spatial
2.In SQL server 2012 has unlimited concurrent connections
3.By default it supports 15,000 partitions
4.Available new string functions CONCATE and FORMAT
5.Available new conversion  functions are PARSE ,TRY_CONVERT,TRY_PARSE
6.Microsoft.SqlServer.Dac and Microsoft.SqlServer.Dac.Extensions are introduced to perform  operations on Packages.

SQL Server 2008R2 to 2012• Inclusion of Visual Studio 2010 (BI edition)
• AlwaysOn technology – allowing automatic replication and switching to mirrored database/server in the event of problems
• In most instances, the performance is 10 times faster than the predecessor (Microsoft claim)
• Buffer rate is high in SQL Server 2012 because of data compression.
• Data visualization tool is available in SQL Server 2012.This allows snapshots of data.
• Support for persistent computed columns and extra geographical approach is possible with spatial features in SQL Server 2012.
• Allows for warm Service Patch patching – meaning SQL or server does not have to be rebooted/restarted to apply patches
• New ‘Contained Databases’ feature allowing easier movement of database instances between servers
• New ‘ColumnStore’ indexes – which turn indexes 90 degrees in use (instead of one index per row, you get multiple column indexes per row which has a major impact on performance)
• New TRY_CONVERT T-SQL command, which will perform data type conversion, but will NULL rather than error where conversion fails.
• New OFFSET/FETCH command which allows automatic capture of pages of rows (like SELECT TOP 90 * from…, but gives a starting row number for the page)
• Redesign of SQL Server management studio – providing new features, better intellisense, new viewing tools and cleaner interface
• Custom user server roles – meaning you can define roles with specific access (such as BI data reader) and assign these custom roles to defined users (therefore faster user maintenance)
• New virtual Windows FileTable commands in T-SQL lets you manage a folder like a table of documents, but still have external control over the contents: UPDATE C:\Docs\*.* SET ReadOnly = 1 WHERE Author = ‘Bob’ AND Created < ’20100101′;)
• NEGATIVE – SQL Server Management Studio will no longer run on XP. Needs Windows Vista, 7 or 8.
• NEGATIVE – SQL Server is no longer licenced on server, but either CALS or Cores in server CPU which could increase costs for large user connection scenarios.
• NEGATIVE – AWE is no longer supported, meaning your SQL Server instance on x86 with 32GB of RAM is only going to be able to use 4GB. Note – SQL now designed to run on 64bit hardware.

During the CCTV / Access Management Software installation the mail Database is on SQL based. So you must know Difference between SQL SERVER 2000,2005,2008 and 2012.

MS SQL SERVER VS ORACLE 10G COMPARE

COMPARE MS SQL SERVER VS ORACLE 10G

SQL Server:

Pros:

Its easy installation, self-tuning capabilities, graphical administration, integrated business intelligence and extensive collection of help wizards

The total cost of ownership (TCO) of SQL Server 2005 is lower than that of Oracle

Specialized index on a computed column

Indexed View

Cons:

Locking and concurrency: SQL Server has no multi-version consistency model, which means that "writers block readers and readers block writers" to ensure data integrity

Performance and tuning:

a. DBA has no "real" control over sorting and cache memory allocation. The memory allocation is decided only globally in the server properties memory folder, and that applies for ALL memory and not CACHING, SORTING, etc.

b. All pages (blocks) are always 8k and all extents are always 8 pages (64k). This means you have no way to specify larger extents to ensure contiguous space for large objects.

c. In SQL Server, no range partitioning of large tables and indexes. In Oracle, a large 100 GB table can be seamlessly partitioned at the database level into range partitions.

With SQL Server 2005, INSERT, UPDATE, and DELETE statements are executed serially (MERGE is not supported).

Oracle:

Pros :

you can use Oracle on multiple platforms. Whereas Microsoft created SQL Server to be used on the Microsoft platform only, Oracle is available on multiple platforms, including Windows, Unix and now Linux, which is the foundation of Oracle's Real Application Clusters (RAC) strategy.

Locking and concurrency: "readers don't block writers and writers don't block readers." This is possible without compromising data integrity because Oracle will dynamically re-create a read-consistent image for a reader of any requested data that has been changed but not yet committed. In other words, the reader will see the data as it was before the writer began changing it (until the writer commits).

function-based indexes

Oracle will execute INSERT, UPDATE, DELETE, and MERGE statements in parallel when accessing both partitioned and non-partitioned database objects

Cons:

"Implementation of something similar to MSSQL Identity by using Oracle sequence would require reflecting the sequence name in the application or creating a trigger for each table/sequence pair.

Cost is higher

Required skilled DBA.

CONCURRENCY MODEL

concurrency control. The main differences are summarized in the table below:

Oracle Database 10g SQL Server 2005

Multi-version read consistency Always enabled. Not by default.

Must be enabled.

Non-escalating row-level locking Yes Locks escalate

SQL Server 2005 introduces two new isolation levels3:

read committed with snapshots (statement-level read consistency)

snapshot isolation (transaction-level read consistency)

These isolation levels correspond to Oracle’s READ COMMITTED and SERIALIZABLE isolation levels, respectively.