Tuesday, November 21, 2017

TFS 2018 and SQL Server 2017–Multidimensional server mode

Last week I did a test migration for a customer from TFS 2015 to TFS 2018. They already configured a SQL Server 2017 Database Services, Analysis Services and Reporting Services for me, so I thought I was good to go.

However halfway through the migration process I noticed the following warning appear:

[2017-11-15 14:18:43Z][Warning] An error was encountered while attempting to upgrade either the warehouse databases or the Analysis Services database. Reporting features will not be usable until the warehouse and Analysis Services database are successfully configured. Use the Team Foundation Server Administration console to update the Reporting configuration. Error details: TF400646: Team Foundation Server requires Analysis Services instance installed in the 'Multidimensional' server mode. The Analysis Services instance you supplied (<INSTANCE NAME>) is in 'Tabular' server mode. You can either install another instance of Analysis Services and supply that instance name, or you can uninstall this instance and install it in the required server mode.

Turns out that in SQL Server 2017 Analysis Services you can choose between 3 possible modes:

Relational modeling constructs (model, tables, columns), articulated in tabular metadata object definitions in Tabular Model Scripting Language (TMSL) and Tabular Object Model (TOM) code. This is the default value. OLAP modeling constructs (cubes, dimensions, measures). Originally an add-in, but now fully integrated into Excel. Visual modeling only, over an internal Tabular infrastructure. You can import a Power Pivot model into SSDT to create a new Tabular model that runs on an Analysis Services instance.

Value

Description

TABULAR Relational modeling constructs (model, tables, columns), articulated in tabular metadata object definitions in Tabular Model Scripting Language (TMSL) and Tabular Object Model (TOM) code.
MULTIDIMENSIONAL OLAP modeling constructs (cubes, dimensions, measures).
POWERPIVOT Originally an add-in, but now fully integrated into Excel. Visual modeling only, over an internal Tabular infrastructure. You can import a Power Pivot model into SSDT to create a new Tabular model that runs on an Analysis Services instance.

More information: https://docs.microsoft.com/en-us/sql/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas

Monday, November 20, 2017

PostgreSQL–Case insensitive search

By default when you use the LIKE operator in PostgreSQL, your query parameter is used in a case sensitive matter. This means that the query

SELECT * FROM “Products” WHERE “Name” LIKE ‘Beverag%’

will produce different results then

SELECT * FROM “Products” WHERE “Name” LIKE ‘beverag%’

A possible solution for this could be the use of regular expressions:

SELECT * FROM “Products” WHERE “Name” ~* 'beverag';
  

This query returns all matches where the name contains the word ‘beverag’ but because it is a case-insensitive search, it also matches things like ‘BEVERAGE’.

Friday, November 17, 2017

ADFS–Where to find issuer thumbprint for WIF(Windows Identity Foundation)?

To validate a new installation of ADFS, we created a small sample app that used Windows Identity Foundation to authenticate to the ADFS server.

We got most information from our system administrator, but it turned out that the Issuer Thumbprint was missing.

As the system administrator wasn’t in the office, we had to find a different solution to get the thumbprint.

Here is what we did:

image

  • To read out the certificate information(and the thumbprint) you have to
    • Create a new text file
    • Copy the certificate value into the file
    • Save the file with a .cer extension
  • Now you can open the file, and read out the thumbprint value:
    • Double click on the file
    • Go to the Details tab
    • Scroll to the thumbprint property

image

    Thursday, November 16, 2017

    TFS 2018– Remove ElasticSearch

    Here is an update regarding my post http://bartwullems.blogspot.be/2017/05/tfs-2017how-to-uninstall-elasticsearch.html.

    In TFS 2018, the command to remove your ElasticSearch instance changed a little and the steps became:

    • Open Powershell as an administrator
    • Go to the folder where ConfigureTFSSearch.ps1 is installed. In TFS 2018, this is typically C:\Program Files\Microsoft Team Foundation Server 2018\Search\zip
    • Run the ConfigureTFSSearch script with the remove option: ".\Configure-TFSSearch.ps1 -Operation remove"

    Wednesday, November 15, 2017

    ElasticSearch–Understand the query magic using ‘explain’

    Sometimes an ElasticSearch query is invalid or doesn’t return the results you expect. To find out what is going on, you can add the explain parameter to the query string:

    image

    In your results you get an extra explanation section

    image

    More information: https://www.elastic.co/guide/en/elasticsearch/guide/master/_validating_queries.html

    Tuesday, November 14, 2017

    Using GuidCOMB in SQL Server and PostgreSQL

    On a project I’m working on, we expect to have a really large amount of data. Therefore we decided to switch our ID strategy from Integers to GUIDs. Problem is that when you start using GUIDs as part of your database index, they become really fragmented resulting in longer write times.

    To solve this, you can use the GuidCOMB technique where a part of the GUID is replaced by a sorted date/time value. This guarantees that values will be sequential and avoids index page splits.

    NHibernate and Marten supports the GuidCOMB technique out-of-the-box but if you want to use it with other tools you can try RT.Comb,  a small .NET Core library that generated “COMB” GUID values in C#.

    Here is a sample how to use it in combination with Entity Framework:

    • Let’s first create an Entity Framework Value Generator that uses the RT.Comb library:
    • To apply this generator when an object is added to a DbContext, you can specify it in the Fluent mapping configuration:

    Friday, November 10, 2017

    Kestrel error: The connection was closed because the response was not read by the client at the specified minimum data rate.

    While running some performance tests on our ASP.NET Core application, after increasing the load to a certain level, we saw the following error messages appear on the server:

    The connection was closed because the response was not read by the client at the specified minimum data rate.

    This error is related to the Minimum request body data rate specified by Kestrel.

    From the documentation:

    Kestrel checks every second if data is coming in at the specified rate in bytes/second. If the rate drops below the minimum, the connection is timed out. The grace period is the amount of time that Kestrel gives the client to increase its send rate up to the minimum; the rate is not checked during that time. The grace period helps avoid dropping connections that are initially sending data at a slow rate due to TCP slow-start.

    The default minimum rate is 240 bytes/second, with a 5 second grace period.

    A minimum rate also applies to the response. The code to set the request limit and the response limit is the same except for having RequestBody or Response in the property and interface names.

    The problem was that we were simulating our load from one machine which was not capable of sending enough data at the expected request rate. After scaling out our load tests to multiple test agents on different machines, the problem disappeared…