SQL Server 2014 – Delayed Transaction Durability

Prior to SQL Server 2014, SQL Server used a Write Ahead Log (WAL) mechanism which means that changes are written to the transaction log before they are committed.  The major advantage with this mechanism is that there will be no data loss . However, since IO is the slowest component, there will be a high IO contention for large writes especially if you have a low performing disk configuration for log files.

Delay Transaction Durability is introduced in SQL Server 2014 to enhance write performances, read this article for more details.

Improved Online Operations in SQL Server 2014

Online indexing rebuilding was a major breakthrough from SQL Server to support users re-indexing while the clustered index (or table) and other indexes are available during the index rebuild operation.   However, in real world there are multiple issues with these online operations as it does not have much flexibility. In SQL Server 2014, there are few options included for these online operations which is discussed in this article.

Workshop on Data Mining

I will be doing the session on Microsoft.

Viewing Execution Plans While Queries are Running

Analyzing execution plans is a key tool when analyzing query performance. As most of you are aware there are two ways of analyzing query plans, namely ‘Include Actual Execution Plan’ and ‘Display Estimated Query Plan’

The Include Actual Execution Plan option will provide the execution plan after the completion of the query. However, this option is not very useful when dealing with long running queries.

Read more here

Using QUOTENAME() to Protect Against SQLInjection

This short article describes you to simple way of protecting from SQLInjection.

http://www.sql-server-performance.com/2014/using-quotename-protect-sqlinjection/

Bobby Tables: A guide to preventing SQL injection

 

Source: http://bobby-tables.com/

New Database Backup Options in SQL Server 2014

Short article on database backup options in SQL Server 2014.

Data Mining Cluster Analysis in SQL Server

Grouping is something we naturally do in our day to day life. We group foods depending on taste, we group friends depending on their different attributes. 

Clustering is an algorithm which finds natural groupings inside your data when these groupings are not obvious. It finds the hidden variable that accurately classifies your data.

Read the article on Clustering here.

Report SQL Server Percentage Backup Completion and Time Completion

When executing a backup from a t-sql command, it is difficult to know what percentage has completed and when the backup will be complete. This FAQ tells how to do it.

Cannot show the editor for this task.

I got this error in SSIS when I try to open Execute SQL Task.

 

Then I tried to open already configured Execute SQL Task in some other SSIS packages. Still the error exists. I was wondering this is an error in SQL Server 2014 so I installed SQL Server 2012 and tried but it was no difference.

So the work around is by clicking F4 and enter the configuration values in the Properties box.

Difference Between LEN and DATALENGTH

DATALENGTH - Returns the number of bytes used to represent any expression.

LEN - Returns the number of characters of the specified string expression, excluding trailing blanks

Output is 30, 3.

Important point to remember is is LEN function does not depend on the data type and it is storage mechanism. LEN function total depends on the number of string characters.

Output is 8 and 3.

So DATALENGTH is depends on the storage but not the LEN function.

APRIL 2014 MEET-UP

More at http://www.sqlserveruniverse.com/SSSLUG.aspx

By the Numbers: The VAR - Big Data Market Opportunity

If you've been following the trends, you already know that Big Data can be very good for your business. But do you know just how good it can be? Let's take a look at some numbers, courtesy of the analysts.

Read more at here

The Database Administrator is dead

This isn’t really a surprise. As developers have been burdened or perhaps graced with ever-faster development cycles, and as applications rush to market, the role of the DBA continues to slip away. Read more at http://thenextweb.com/kennygorman/2013/12/12/dba-dead/#!umHpW

SS SLUG January 2014 Meet-up Recap

The first SS SLUG meet-up for this year was held at Microsoft Sri Lanka on January 22. We had two sessions on database development and data mining, with 47 attendees in total. Here's a summary of the event, including pictures and downloads

• Session Summary: http://is.gd/ssslug
• Photo Gallery: http://is.gd/ssslug_pic201401
• Slides and Code Downloads: http://is.gd/ssslug_dls2014
• diGIT Live Blog: http://digit.lk/sql-server-sri-lanka-user-group-january-meetup/
• Video: Video of the entire event is being processed by diGIT magazine and will be available soon.

If you had missed the December 2013 meet-up or need to see the action, see these links:

diGIT Live Blog | Photo Gallery | diGIT Photo Gallery | Downloads | Video

Default Database Issues and Best Practices

Default database is a feature which is not used by many. This article is about default databases.

January 2014 Meet-up

Session #1

TITLE: Predictive Modeling with the Microsoft Naïve Bayes algorithm

Join this session where Dinesh showcases the capabilities of predictive analysis using the Microsoft Naïve Bayes algorithm. Naïve Bayes is a classification algorithm that ships with SQL Server Analysis Services and is used to mine for and predict outcomes based on selected parameters.

CATEGORY: Business Intelligence (Data Mining)

SPEAKER: Dinesh Asanka (MVP), Database Specialist (Pearson Lanka)
Linked.In | Blog | Facebook | @dineshasanka

40 minutes approx.

 

Session #2

TITLE: Writing Resilient T-SQL Code - Part II

Continuing from where he left off from November's session, Gogula will guide you through writing better T-SQL code that is more resilient to unexpected issues and common code failures. This session is based on the book Defensive Database Programming by Alex Kuznetsov.

CATEGORY: Development

SPEAKER: Gogula G. Aryalingam (MVP), Technical Architect (Navantis)
Linked.In | @gogula | Blog

40 minutes approx.

 

Time & Location

JANUARY 22, 2013 - 6:00 PM Onwards at MICROSOFT SRI LANKA

11th Floor, DHPL Building, No. 42, Nawam Mawatha, Colombo 2, SRI LANKA

An excellent opportunity to network and learn. Refreshments provided.

Entrance FREE

AI Search Samples

Just for a moment , I moved away from standard database discussions and thought of writing something about AI.

This is something I did for my one of the assignment calls Artificial Intelligence Search.

I used Indian Airlines as a scenario to demonstrate AI search features.

Breadth First Search

Depth First Search

Greedy Search

Uniform Cost Search

A* Search

Watch out for SQL Server 2012 licensing pitfalls

Licensing is little puzzling with all the technologies like virtualization and cloud around.

This article should give us some what understanding about licensing.

What is LocalDB?

It is a new version of SQL Server Express dedicated to developers to help them avoid a full installation of other editions of SQL Server.

Benefits.
Small installer. The 32-bit version has 28.2 MB and the 64-bit version has 33.7 .
Simplified. It does do not require configuration or administration.
Run as a low privileged user.
Simple installation.
Offers the same T-SQL language as SQL Server Express. It supports stored procedures,  geometry and geography data types, triggers, views.

Limitations.
It does not support Windows XP, Windows Server 2003, Window 2000.
It does not support WOW. LocalDB doesn't support installing 32-bit version on 64-bit Windows.
Allows only local connections. Only Named Pipes connections.
Only SQL Server 2012 Management Studio (on a computer updated with .NET Framework 4.0.2) can be used to manage LocalDB. SQL Server Management Studio of previous versions of SQL Server cannot be used.
Visual Studio 2010 RTM does not support LocalDB.

Read more at http://www.sqlcoffee.com/SQLServer2012_0004.htm