I use columnstore indexes on SQL 2014. What would be the advantages of upgrading to SQL 2016?

I use columnstore indexes on SQL 2014. What would be the advantages of upgrading to SQL 2016?

SQL Server 2016 really represents a drastic change for the better in regards to general performance. And the proof is that, thanks to the engine changes, this is the first SQL Server version where Microsoft actively provides a widespread increase in performance levels by simply upgrading. But what impact can it have in your daily loads if you use columnstore indexes in SQL server 2014 datawarehouse? There was already an amazing increase in performance levels with SQL Server 2014 and the columnstore indexes. Is it worth upgrading to SQL 2016? (more…)

Integration Services (SSIS) vs. POWERCENTER. ETL tool comparation (I)

Integration Services (SSIS) vs. POWERCENTER. ETL tool comparation (I)

Below is a comparison table between ETL tools: SQL Server Integration Services aka SSIS vs PowerCenter. What tool should I choose? What are the differences? What are the advantages and disadvantages? Do they meet my requirements and particular needs? These are some of the questions that you may have asked yourself at the start of any new project, and we will aim to answer them in this post by providing you with some key facts regarding two very potent tools: SQL Server Integration Services a.k.a. SSIS versus Informatica PowerCenter.


Virtual SANs and the trend towards SDS

Virtual SANs and the trend towards SDS

Lately, we are noticing a trend whereby traditional SANs are increasingly losing ground. This can be due to various reasons, such as forced migrations to the cloud, limited budget environments or even customers who are getting annoyed with their storage providers’ draconian terms and conditions. A common example is to find very costly maintenance agreements, higher-than-market disk size upgrade costs, as well as difficulties to expand/ improve hardware without having to completely replace it. There is also a strong trend to continue going virtual and trading servers like they were commodities. All the above is therefore contributing to an increase in this trend towards SDS, while the VSANs continue to attract new supporters.


Creating an installer for your SSIS-assemblies

Creating an installer for your SSIS-assemblies

When you develop SSIS packages with custom functionality, either self signed assemblies or script components/script transformations that implement assemblies you got from codeplex, it sometimes become a nuisance to get them into GAC on production machines. What often happens is that when you try to run the package in earnest via the SQL Agent, it fails to find the assembly.

In my case I’ll be adding ionic.zip to my SSIS package dataflow in order to stream data into encrypted zip files.
If you would like to try out a cool way to deal with zip files in dotnet, I recommend it: DotNetZip – Zip and Unzip in C#, VB, any .NET language

So back to trying to run custom components and assemblies in SSIS packages. I bet you have seen something like this at some point in your career:

Error: System.IO.FileNotFoundException: Could not load file or assembly ‘Ionic.Zip, Version=, Culture=neutral, PublicKeyToken=edbe51ad942a3f5c’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Ionic.Zip, Version=, Culture=neutral, PublicKeyToken=edbe51ad942a3f5c’

This tells us that our dll can’t be found when the SISS execution reaches the dataflow that uses it.

I wanted to find a solution that helps me deal with this in a good manner that is pretty future proof.
The first thing that came to mind is doing what I always recommend: use powershell. The problem is that sometimes you don’t have powershell, or an older version or aren’t allowed to run what scripts you want in production.

The next thing that was suggested to me was to copy gacutil.exe over to the production environment and do my deployment that way. That is not recommended though as it violates the license of Visual Studio SDK.

I tried out a couple of different plug ins for Visual Studio to be able to create an installer and the one that worked with the least hassle for me was WiX.

These are the steps I took to get my first working installer that added dll-files to gac:

  • Make sure that your assembly is signed. If you are unsure about whether your own assembly is signed, then it probably isn’t. Since the example is of someone else’s assembly then it probably is signed.
  • Make sure that your installation of Visual Studio is compatible with WiX (I use Visual Studio 2015 Developer)
  • Download the latest stable release of the WiX toolset that works with your version of VS.
  • Put the assembly (dll file) in any folder on your computer and have the corresponding path at hand. It doesn’t matter where the file is as long as you can access it when building the project.
  • Create a new VS project for WiX as shown in this picture: create wix project in VS
  • Put an XML fragment into Project.wxs that points to your file:the important stuff is now in the xml file
  • Set the project to release mode, build it, and go to the bin file to take a look.
  • The three files are all needed for your installer, so zip them together and you are basically done: set build to release, open the bin folder and look for three files - add them to a zip and you got your installer
  • Move your zip file with the three files to the machine you need the DLL on and run the installer. If you want a prettier installer with more options and so on, put more effort into the WiX project!

Here is the excerpt of the xml that worked for me, mileage might vary so look up the documentation and the comment sections of other articles you find.

  <ComponentGroup Id="ProductComponents" Directory="INSTALLFOLDER">
<Component Id="Ionic.Zip">
  <File Name="Ionic.Zip.dll" KeyPath="yes" Source="C:\Import\Ionic.Zip.dll" Assembly=".net" AssemblyManifest="Ionic.Zip.dll"/>


That time I used ON UPDATE CASCADE in production


While working with a invoicing system back in 2013 I was asked by my customer if it would be possible to enable them to have some more special handling of their biggest client. The way their system was set up, one client accounted for over half of the accounts receivables. The client and my customer were also very close in their operations. What they needed from the system was the ability to split the client into four legal entities while remaining all the custom logic and prices. They asked me if a quick-fix was possible.

“A quick-fix solution is as fast, as easy and as well-documented as its explanation from the customer” – me just now.

The system had a procedure which would work out all the numbers for each client and for this particular one handle subsidiaries together on all invoices. With this reorganization coming, all units would be connected to one of four new client subsidiaries and each subsidiary would instead receive its own invoices for its units.


What the options for foreign key constraints do

You can add two clauses (ON UPDATE and ON DELETE) when creating a foreign key constraint. Both can have one of four options.

NO ACTION – this is standard, an error is raised and the execution halts if the new value would violate referential integrity.
CASCADE – data integrity is conserved by updating or deleting records in the related tables in the same transaction. In other words completely hazardous when dealing with deletes. Use with caution.
SET NULL – If the relation between records is broken, the integrity of the referencing record is upheld by setting the value of the foreign key column to NULL.
SET DEFAULT – Same as above except the value becomes the default for the column instead of NULL, there has to of course be a default value for the column for this to work.

From rough draft to the final solution

On the back of an envelope I wrote down these nine steps that would re-route the client logic and make sure everything would still sum up nicely:

  1. Begin a transaction and run the stored procedures to create the invoices as normal
  2. For each unit represented for the four new subsidiaries: create a new invoice header with a temporary and made up sequence starting from -1 and going backwards to avoid collisions.
  3. For each invoice line: map invoiceId by subsidiary (-1 through -4)
  4. Drop the now empty and unused invoice headers created in step 1.
  5. Drop the foreign key constraint between headers and lines
  6. Re-create this foreign key but set it with action ON UPDATE CASCADE
  7. Re-run the stored procedure that fixes invoiceIds for headers, this procedure is a sub-procedure when creating invoices. When the headers are updated, the lines inherit the information through our foreign key constraint.
  8. Drop and re-create the foreign key once again as it existed before the operation.
  9. Commit transaction. Done! No lines removed, all accounted for and all connected nicely.


Big changes are under way at SolidQ

I am excited to announce to our community and partners that I have moved on from my North American leadership role to a new role as our Corporate Chief Technology Officer. My new job is to align the long-term vision of what SolidQ does and how we do it, across all our international markets. Joining me will be Antonio Soto as Corporate COO, dedicating himself to our global operations and driving delivery support in both established and growing markets. (more…)

Connect Azure to your local network

Connect Azure to your local network

There is much focus from Microsoft on Azure right now, but Azure is reachable via Internet only. A typical company on the other hand, has all its resources on an internal LAN which is shielded from Internet for security reasons. So how can you connect the two and integrate Azure with your local network? (more…)

Pyramid Analytics & Power BI

With a recent announcement from the folks at Pyramid Analytics of a strategic alliance with Microsoft, and the recent release of Microsoft’s PowerBI Desktop into a data hungry environment, the market for Self Service BI just became much more interesting.