SQL Server 2016 – RTM on June 1st

Microsoft announced the general availability date for the next version of SQL Server:

SQL Server 2016 RTM

You can read more on the new capabilities of this version as well as download the free e-book from Microsoft site over here.

Blockchain

There is a lot of discussion going on in the recent years on the topic of Blockchain Technology.  Some are calling it a hype while others are cautiously evaluating the business cases and the ROI of this technology.  We have been working for the past couple of months with some of our clients as well as future prospects to dig deeper into the potential of this new technology.  Through a series of posts, we will cover the facts about this new technology, the adoption by some of the bigger firms in different verticals, the different key players, consortiums and our own experience and advice on how to go about evaluating whether this will work for your company.

These are the topics that we will cover:

1. Introduction
   1. What is Blockchain?
   2. Different Types of Blockchains
   3. Blockchain Technology vs The Bitcoin Blockchain
   4. Brief History and Timeline
   5. Key Players
   6. Blockchain Platforms
   7. Adoption in the Industry
   8. Consortiums
   9. Major Announcements and Pilot Programs
2. Concerns
   1. Data Regulations
   2. Standards
   3. Performance
   4. Privacy
3. Impact to different entities
   1. Consumers
   2. Organizations
   3. Developers
   4. Investments and M&A
4. Use Cases
   1. Consumer Payments
   2. FinTech
   3. Healthcare
   4. Insurance
5. Adopting Blockchain
   1. Taking a Phased approach
   2. Costs
   3. Testing

 

SSMS and .Net Framework 4.5.1

Ran into an issue yesterday.  Installed the .Net Framework 4.5.1 on my laptop and post that, SQL Server Management Studio failed to connect to any of the instances.  The error received was:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The message received was unexpected or badly formatted.)

You can uninstall .Net Framework 4.5.1 and install .Net Framework 4.5.2 in order to fix this.  Or if you need to keep the .Net Framework 4.5.1, there is a workaround available which is listed on Microsoft Connect page here.

SQL Server Virtualization

Excellent Tips by Michael Otey on virtualization for SQL Server:

Virtualization for SQL Server

 

Difference between UNICODE and UTF-8 files in Windows Notepad

While generating flat files through SSIS for a feed process at a client site, we noticed that the developer had left the file encoding to be UNICODE with the thought that it would be the best practice and the consumption of the file will not be an issue at the receiving system.  The file was being opened up in Notepad.

When generating a flat file in Windows, you have the option (just like you would when you are using Notepad) to use the encoding of ANSI, UNICODE, UTF-8 or Unicode big-endian.  What is important to understand is that in case you are using UNICODE, it is essentially UTF-16 little-endian and if you are using ANSI, it is Code Page 1252.

Microsoft’s Notepad writes UTF-16 with a Byte Order Mark (BOM) and also looks for that BOM when reading the file.  If you are un-aware of what a BOM is, read this entry in Wikipedia – here.  So, in the case of a UNICODE file, the BOM is what helps in determining whether the file is UTF-16 big-endian or little-endian. Now, if Notepad is not able to find the BOM, then it calls a library function called isTextUnicode and it looks at the data and attempts to determine the encoding.  If the interpretation of this function comes out wrongly, it will cause it to display wrong glyphs.

Best approach in our opinion is to use UTF-8 everywhere.  It is a universally accepted encoding and even if you are sharing files across different operating systems, you would still be assured of providing proper data.

A new iPhone app

Wanted to blog about both our 5 Million views milestone which happened yesterday as well as about a new iPhone app that is available in the market and created by our friends at iHelpPlusApps.com. Of late, we have been pretty busy with our projects and as a result have not been blogging that frequently but will start blogging again now with some of the lessons learnt on the new projects related to predictive intelligence projects.

Coming back to the app – it is called iHelp+ and is available in the iTunes AppStore.  Here is a brief description of the app:

iHelp+ is an iPhone application that allows users to alert a family member or a friend or a monitoring company with the touch of the screen.  The users have the ability to create an audible alert, silent alert or even a delayed alert when you want to make sure someone knows you should have arrived at a location and did not due to an un-forseen incident.  When an alert is triggered, the application will provide your contact/monitoring company with life saving information (Your GPS location and a phone number for the closest Public Service Access Point – PSAP).  iHelp+ also allows you to store your medical information and allows you to share this with first responders in-case of an emergency.  With 4 quick contact links you will also be able to reach out to your friends, family or care givers quickly all at the touch of a button.  This app is available only in the US right now.

This app can be used by any age group including teenagers as well as the elderly.  Here are the facebook, YouTube and twitter links for the app and the iTunes link:

• College FaceBook Page: http://www.facebook.com/SafeOnCampus
• College Twitter Link: https://twitter.com/SafeOnCampus
• Elderly Facebook Page: http://www.facebook.com/Safety4Seniors
• Elderly Twitter Link: https://twitter.com/Safety4Seniors
• You Tube Video:http://www.youtube.com/watch?v=WeNOvvO_8_A&feature=youtu.be
• iTunes Link: http://itunes.apple.com/us/app/ihelpplus-personal-alarm-panic/id537514084?ls=1&mt=8

We believe that this app can be very useful for the security and personal safety conscious individuals.

Sqoop graduates from Apache Incubator to a Top Level Project

Sqoop – the Big Data Tool has moved out of the Apache Incubator to a Top Level Project (TLP).  In case you are not aware of Sqoop, it is the key data tool to transfer volumes of data between Hadoop and structured data stores like RDBMS (Relational Database Management Systems).  This project provides connectors for many popular RDBMS – Oracle, SQL Server, MySQL, DB2 and PostgreSQL.  This is a significant step towards the adoption of Hadoop in the enterprise solutions.

You can read more on this in the eweek article here. And here is the link for learning more on sqoop: http://sqoop.apache.org/

Bug: Scope_Identity() and @@Identity can return wrong values during parallel execution plans

Ran into a weird issue today at a client site and upon further researching it, found out that it is a known issue in the SQL Server version that was being used at the client site.  The issue was that at times, we were getting wrong values returned by the Scope_Identity() function.  Upon further diagnosis, we found that the query in question was using a parallel execution plan and when that happens, there is a known issue in some versions of SQL Server because of which you can get wrong Identity value returned.  The KB article from MSFT that talks about this is available here:

http://support.microsoft.com/default.aspx?scid=kb;en-US;2019779

It states that the issue is fixed as part of the Cumulative Update Package 5 of SQL Server 2008 R2 SP1 and any new releases post it.  However, this was a production system and getting any such kind of patch update/upgrade would have taken time so we adopted the workaround of using the OUTPUT clause.   Example of the OUTPUT clause usage is in that KB article and you can also read up more on it in our blog post here and here.  You can also opt for Method 2 mentioned in the workaround in the article.  Method 1 and Method 3 would be sub-optimal for your scenario if the parallel plan was desired to begin with with Method 3 being applied at the Server level and hence effecting all queries.

Online Index Operations involving certain large data types are allowed in SQL Server 2012

Prior to SQL Server 2012, when you did online index operations (available in Enterprise Edition), if the index contained certain large data types (specifically varchar(max), nvarchar(max), varbinary(max)), then those online operations were not allowed on that index.  That restriction does not exist anymore in SQL Server 2012 so there is no need to take such indexes out of your online operations.

Example:  Let’s create a sample table which contains two columns – an ID column of type INT and the second column of data type NVARCHAR(MAX) and then let’s try to create an index on it with ONLINE option set to ON.

CREATE TABLE tblTestOnlineIdx
(
ID INT IDENTITY NOT NULL
, COL1 NVARCHAR(MAX) NOT NULL
, COL2 XML NOT NULL
)
GO
CREATE INDEX tblTestOnlineIdx_IND_1 on tblTestOnlineIdx (ID) INCLUDE (COL1) WITH (ONLINE = ON)
GO


We will see that we will get an error in SQL Server 2008 telling us that:

Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index ‘tblTestOnlineIdx_IND_1’ because the index contains column ‘COL1’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

And this same code will run fine on SQL Server 2012 because this limitation has been removed.  However, even though the BOL link above states that the same is true for indexes containing a XML data type column, that is not the case.  ONLINE=ON is not a valid option for the XML indexes and it will still give an error.  That is reflected in the BOL link here which covers the creation of XML indexes.

Editions in SQL Server 2012 and Licensing

SQL Server 2012 is available in these editions:

• Enterprise Edition
• Standard Edition
• Business Intelligence Edition
• Developer Edition
• Web Edition, and
• Express Edition

Out of these, the Enterprise Edition is of course the one that has all the high end features for meeting the needs of a Tier-1 large scale deployment.  You would recall that previously there was a DataCenter Edition available – that is no longer available now and all of it’s capabilities are now included in the Enterprise Edition itself.

The Business Intelligence Edition is a new Edition that is offered now.   This is available to offer clients the full suite of powerful BI features in SQL Server 2012 like Power View, PowerPivot, enhanced reporting and analytics.  Any company specifically looking at SQL Server from the perspective of BI and not interested in the full OLTP features that are found in the Enterprise Edition would opt for this one.

The Standard Edition is aimed towards customers who do not need the high end features that are available in the Enterprise or the Business Intelligence Editions.

Out of the remaining three specialized Editions, the Developer Edition (like in previous versions) has all the features of the Enterprise Edition but is meant only to be used in a Dev/QA environment and does allow for a direct upgrade to the Enterprise Edition.  The Web Edition is essentially intended for the hosting service providers.  It is scaled down version in terms of support for processors (max: 4) and the feature sets but is good enough to be used if you are looking for an edition to host web-sites and small scale web applications.  It supports up-to 64GB of memory.  The Express Edition is a free edition and has restrictions on the DB size (max: 10GB), 1 processor and 1GB of memory.

Here are some links comparing the different feature sets across the different editions available in SQL Server 2012:

SQL Server 2012 Editions: http://www.microsoft.com/sqlserver/en/us/sql-2012-editions.aspx

Article on MSDN with the Feature Comparison between the different Editions: http://msdn.microsoft.com/en-us/library/cc645993

And here is the information from MSFT in regards to licensing: http://www.microsoft.com/sqlserver/en/us/get-sql-server/licensing.aspx