Press "Enter" to skip to content

Day: March 9, 2016

SQL Server On Linux Preview

Published 2016-03-09 by Kevin Feasel

James Anderson shows us where to sign up for the SQL Server on Linux preview:

Couple of thoughts on the sign up form:

  1. Red Hat is mentioned in the announcment but this form tells us that only Ubuntu or Docker images are currently available. Will it be available to all Linux distributions? What about OS X?

  2. As for Docker, does it ever make sense to have RDBMSs in containers? Containers are generally immutable.

Done.  I’m also excited about the continued Microsoft-Red Hat collaboration, not just because I live in Red Hat territory.

In the meantime, Denny Cherry and Joey D’Antoni give their thoughts.  Here’s one of Joey’s:

My favorite part of SQL Server on Linux? My Bash scripts for automation work again.

 

Comments closed

Text Search

Published 2016-03-09 by Kevin Feasel

Anders Pedersen discusses one method he used to implement fast text search in SQL Server:

Looking into what was needed, I quickly realized there was a LOT of data, guess 50+ years of news broadcasts will do this.  Consider this was in the early 2000s, some innovation was needed from anything I had coded before.  Obviously LIKE searches was out of the question, full text search was not available.  So what to do?

Basically I decided to break down each broadcast to words into a separate table, the entire application fit in 2 tables: Story and Words.

This is a case in which thinking about the grain of data helps solve an otherwise-intractable problem.

Comments closed

Why Use Instant File Initialization?

Published 2016-03-09 by Kevin Feasel

Erin Stellato shows the benefits to using Instant File Initialization in terms of file modification and database restoration:

The time to zero out a file and write data is a function of sequential write performance on the drive(s) where the SQL Server data file(s) are located, when IFI is not enabled.  When IFI is enabled, creating or growing a data file is so fast that the time is not of significant consequence.  The time it takes to create or grow a value varies in seconds between 15K, SSD, flash, and magnetic storage when IFI is enabled.  However, if you do not enable IFI, there can be drastic differences in create, grow, and restore times depending on storage.

There’s a huge performance benefit with turning IFI on.

Comments closed

Updating Non-Updatable Columnstore Indexes

Published 2016-03-09 by Kevin Feasel

Niko Neugebauer shows how to load data into non-updated non-clustered columnstore indexes:

I decided to make a serious step back and write about something that is concerning the current (SQL Server 2014) and the elder version of SQL Server that supports Nonclustered Columnstore Indexes – (SQL Server 2012).
The Nonclustered Columnstore Indexes in SQL Server 2012 & 2014 are non-updatable, meaning that after they are built on the table, you cannot modify the table anymore – you can only read the data from it.
The common solutions for this problem are:
– Using Partitioning
– Disabling Columnstore, modifying the data and Rebuilding the Columnstore Index then (thus activating it)

Sounds easy, doesn’t it ?
Well, like with everything in the real life, there are a couple of quite important gotchas here.

The “non-updatable” part is why I ignored non-clustered columnstore indexes.  With SQL Server 2016, I’m going to take another look at them.  But if you’re living on 2012 or 2014 for a while, this is a good post to give you an idea of how to load those tables.

Comments closed

Compile SQL Project Files Using MSBuild

Published 2016-03-09 by Kevin Feasel

Richie Lee shows how to use MSBuild to deploy a SQL Server database project:

What is happening is that when a project is built in Visual Studio some targets that are external of the whole process that were installed as part of Visual Studio are used as part of the process. Obviously, when we run MSBuild via cmdline, we are not setting the parameter “VisualStudioVersion”, because this is pretty much a “headless” build. So the sqlproj file handles this by setting the parameter to 11.0, which is pretty horrible. Given that projects are created in Visual Studio, I would’ve felt that the version a project was created in would be baked in as the default, as opposed to just some random version number. At the very least,  a warning that a default version number has been set would come in useful, especially as this is 2016 now and the sqlproj files default to 2010 targets.

Fortunately, Richie has the answer to this issue.

Comments closed

Failed To Deploy Project In SSIS

Published 2016-03-09 by Kevin Feasel

Ginger Grant helps resolve “Failed to deploy project” errors (Error: 27203) in Integration Services:

The message Failed to deploy project isn’t very useful, but the rest of the message is. The operation_messages view lives in SSISDB, and the operation identifier number is how to determine what the error is. Run this query, using the number provided in the error message, which in this case is 173

I’d prefer it if we actually could see the error in the dialog, but at least there’s an explanation somewhere.

Comments closed