What is different in first install of SQL 2016 CTP2

The two most visible changes to installation of SQL server are:

1. The installer now recognizes the number of cores in your serer and creates tempdb with the 

    number of cores if the cores are less than 8 or 8 if the number of cores is more than 8. At last 

   Microsoft addressed what SQL Server User community and professionals were recommending. 

  See image below. In my case, my server has four cores and it recognizes and create temp files.

2. Another exciting feature that was brought into main stream SQL server is Polybase. Polybase is a  

    T-SQL front end that allows users to query non-relational data.  Polybase was used to query an 

    external Hadoop cluster connected to APS. This is good news for queries non-relational data from 

    within SQL server engine. I personally cannot wait to play with it and finally if good may use it 

    for   real.  To use this feature you need to install Oracle JRE Update 51 or higher. Otherwise you 

    will get the following error and will not progress with installation.

 

Changes to in-memory OLTP in SQL 2016

What are the changes?  In this post, I will be summarizing the changes to in-memory OLTP component of the product.  This feature was first introduced in SQL server 2014. We all know the first release will have limitations. From my past experience with SQL server, new features tend to have limitations.  So, what are the improvements?

Schema and data changes:  The limitation of altering a table is now removed. You should be able to use alter table on memory-optimized tables to drop, add and alter columns.  You should also be able to drop or rebuild indexes.

There is a bit of improvement on parallel plans.  Operations that use hash indexes can now be performed in parallel unlike SQL 2014 were the optimizer never creates parallel plan for any operation

 SQL 2016 now supports TDE for memory optimized data filegroup.

Support for multiple log reader threads to read transactions that affects memory-optimized tables from transaction log. One more bottleneck removed and this operation is now faster than it was in 2014. SQL server 2016 now allows multiple threads for both recovery and checkpoint, read and apply logged transactions which can result in the ability to scale memory optimized operations.

Some improvements on filestream processing.  The removal of dependency on windows files stream processing.

Garbage collection in memory:  In SQL 2014 garbage collection was too slow. In some cases, even when memory optimized table was dropped the memory wasn’t immediately available to be used due to slower  garbage collection rate as opposed to  row version changes. The algorithms of collecting garbage are now improved and hope this is the case.

In SQL 2016, all data whether it from disk or memory optimized tables will be visible immediately on AlwaysOn secondary replica. So less work for developers.

The original recommendation of 250GB of memory for memory-optimized tables are now lifted to 2TB

The requirement for columns of Memory-optimized tables that uses an index to be BIN2 collation is also removed.

Support for more DML operations such as select distinct, different type of joins (such as Left and right outer join), union, union all, nested stored procedure calls, subqueries and OR and not are now added.

SQL Server 2016 public preview coming soon

Highlights of the upcoming public preview of SQL server 2016 features:

• Dynamic Data masking
• Row-level Security 
• Three synchronous replicas on AlwaysOn
• Native JSON support (Now supporting the world of Big Data)
• More improvements on hybrid backups (pushing more of cloud)
• Built-in advanced analytic, polybase and mobile BI
• Real-time Operational Analytics & In-Memory OLTP
• Stretch database. New technology that allows you to dynamically stretch your warm and cold transnational data to Microsoft Azure
• Always  encrypted : as per T.K. Ranga Rengarajan blog "Always Encrypted, based on technology from Microsoft Research, protects data at rest and in motion. With Always Encrypted, SQL Server can perform operations on encrypted data and best of all, the encryption key resides with the application in the customers trusted environment. Encryption and decryption of data happens transparently inside the application which minimizes the changes that have to be made to existing applications"

Get SQL Loginame when you know the password

Did you ever found yourself in a situation where a user knows their sql login password but not username? Well, I did.  I want to share this SQL statement that helps you to retrieve the name of sql login if you know the password.

--will return any name with the same password as in the Check text.

SELECT name FROM sys.sql_logins

WHERE PWDCOMPARE('', password_hash) = 1

SQL server 2012 SP2 is out

http://blogs.msdn.com/b/sqlreleaseservices/archive/2014/06/10/sql-server-2012-service-pack-2-sp2-rtm-has-released.aspx

Updatable columnstore clustered indexes, is it new feature for SQL 2014 ?

The new release of SQL server 2014 boosts on improvement of columstore index. As we all know, SQL Server 2012 introduced columnstore indexes. I haven't seen it widely used in data warehouse implementation due to the fact that you can not update. If you want to reload the data you have to drop and re-create the index. This might be possible if you are working with rows less than 1 billion. But if your table is more than 1 billion it is very difficult to use. So, the new release not only made the column store index updatable but also can be clustered index. It is all good news but is this a new feature ? I would say for normal version of SQL server it can be seen as new. But, Microsoft released this feature with SQL Server 2012 Parallel data warehouse. What they did is they moved the technology to new version of SQL server. To claim this is a new technology in my opinion is wrong and misleading. The technology was there in their special version of SQL server 2012 (for Parallel data warehouse).

Microsoft's SQL Server 2014 release code: First look

It come so fast after the release of 2012 but the new SQL server has arrived. Read more SQL server 2012 realease

SQL Server 2014 release date?

The exact date of release is still not set but insider from Microsoft thinks it will be released in the first half of 2014. or 3rd Quarter of 2014. Played with CP2 and looks pretty good. Waiting to play with when it is released. As usual, I will wait till SP1 is released before moving any production probably early next year