SQL Server Query Store–How to use it in older SQL versions

One of the (not-so hidden) gems in SQL Server is the availability of the Query Store. Unfortunately you need SQL Server 2016 or higher to be able to use this feature.

The good news is that there is an alternative for older SQL Server versions; OpenQueryStore.

OpenQueryStore (OQS) is a collection of scripts that add Query Store like functionality to pre-SQL Server 2016 Instances!

OQS is being built from the ground up to allow all versions and editions of SQL Server from 2005 up to and including 2014 to have a Query Store like functionality. The data collection, retention and cleanup will be easily configurable to allow for complete control of the OQS data storage.

Installation

1. Download the latest release of Open Query Store from the releases page and unzip the file.

2. Open a PowerShell console and navigate to the location of the unzipped files. Copying the command below change the values of <Instance>, <dbName> and <path> and OQS will be installed for you.

.\Install.ps1 -SqlInstance <Instance> -Database <dbName> -OQSMode Centralized -SchedulerType "Service Broker" -CertificateBackupPath "<path>"

Configuration

After the installation is done, you have to enable the data collection by setting the collection_active bit to true in the [oqs].[collection_metadata] table. When enough data is collected you can check the generated data through one of the available reports.

Reports

The OQS Dashboards can be viewed directly from SSMS (v16. or V17.).

1. Download the OpenQueryStoreDashboard.rdl and OpenQueryStoreWaitStatsDashboard.rdl files from the GitHub page and store it on a machine with SSMS installed.
2. Right-click on a user database that has the OQS enabled and select "Reports - Custom Reports".
3. Navigate to the location were you stored the .rdl files and open it.

Dotnet format

I find code consistency important. Naming conventions, code formatting, … should all be aligned to make the code readable and consistent.

In .NET you can force this consistency through the .editorconfig file. (If you don’t have one in your projects, please stop reading and go add one first). This is not the first time I’m mentioning the .editorconfig file:

• https://bartwullems.blogspot.com/2017/04/visual-studio-2017editorconfig.html
• https://bartwullems.blogspot.com/2019/10/visual-studiogenerate-editorconfig-file.html
• https://bartwullems.blogspot.com/2019/12/editorconfig-let-private-fields-start.html
• https://bartwullems.blogspot.com/2019/10/visual-studio-2019code-cleanup.html

Today I want to take it one step further and enforce the coding style through our build pipeline. We’ll do this through a dotnet cli tool dotnet-format.

Let’s first install it:

dotnet tool update -g dotnet-format

Now you can browse to your solution or project folder invoke the tool using:

dotnet format

This will apply the code formatting rules defined in our .editorconfig to our code.

If we want to use it inside our builds, we probably don’t want to change the code itself. Instead we only want to check if the rules are followed. This can be done by adding an extra --check and --dryrun parameter:

dotnet format --check –dryrun

This is how the output should look like:

C:\Projects\mestbankportaal\MestbankPortaal\Mestbank.Core>dotnet format --check --dry-run
  Formatting code files in workspace 'C:\Projects\mestbankportaal\MestbankPortaal\Mestbank.Core\Mestbank.Core.csproj'.
  Warnings were encountered while loading the workspace. Set the verbosity option to the 'diagnostic' level to log warnings.
  Domain\IE_ROL_FUNCTIE.cs(3,35): Fix whitespace formatting.
  Services\GebruikerService.cs(25,37): Fix whitespace formatting.
  Services\GebruikerService.cs(25,38): Fix whitespace formatting.
  Services\MestbankAuthorizationService.cs(27,39): Fix whitespace formatting.
  Services\MestbankAuthorizationService.cs(27,64): Fix whitespace formatting.
  Services\MestbankAuthorizationService.cs(27,66): Fix whitespace formatting.
  Services\MestbankAuthorizationService.cs(40,50): Fix whitespace formatting.
  Services\MestbankAuthorizationService.cs(42,62): Fix whitespace formatting.
  Services\MestbankAuthorizationService.cs(42,77): Fix whitespace formatting.
  Services\MestbankAuthorizationService.cs(42,79): Fix whitespace formatting.
  Services\MestbankAuthorizationService.cs(61,9): Fix whitespace formatting.
  Formatted code file 'IE_ROL_FUNCTIE.cs'.
  Formatted code file 'GebruikerService.cs'.
  Formatted code file 'MestbankAuthorizationService.cs'.
  Format complete in 3695ms.

A non-zero exit code is returned if any files would have been changed. Thanks to this we can easily use it in a dotnetcli task on our build agent.

Pluralsight–Stay home and skill up for free

With the current lockdown in most countries around the world, a lot of people have to work from home. To keep growing your skills, Pluralsight is offering free access during the entire month of April. Anyone that does not have a current subscription to Pluralsight can take advantage of this offer. No credit card is required to sign up and there will be no obligation beyond April.

Go here to subscribe and enjoy your free access in April.

Expose Kibana to the outside world

By default Kibana is configured to only be accessible from ‘localhost’. If you want to expose it outside your server, you’ll have to update the configuration.

• Go to the config folder and open the kibana.yml file.
• Find the following section:

# Specifies the address to which the Kibana server will bind. IP addresses and host names are both valid values.

# The default is 'localhost', which usually means remote machines will not be able to connect.

# To allow connections from remote users, set this parameter to a non-loopback address.

# server.host: "localhost"

• Uncomment the ‘server.host’ section and specify a non-loopback address. An example:

server.host: "0.0.0.0"

• Don’t forget to also open up the necessary ports in your firewall(by default port 5601)

Installing Kibana as a windows service

For Kibana(part of the ELK) stack no out-of-the-box script exists to install and run it as a windows service. (This in contrast to ElasticSearch which has a batch file that allows you to install it as a windows service).

As a workaround you can use NSSM; the Non-Sucking Service Manager.

With NSSM you can take any executable and run it as a windows service.

Here are the steps to use it:

• Download NSSM.
• Extract the zip and put the nssm.exe executable on a location of your choice.
• Run nssm install <servicename> ; .e.g. nssm install kibana
• This will open up a configuration window where you can specify the executable you want to run and configure some other service related settings

• Click on Install service.

ElasticSearch– was created with version [5.3.0] but the minimum compatible version is [6.0.0-beta1]. It should be re-indexed in Elasticsearch 6.x before upgrading to 7.6.1.

As mentioned in some of the previous posts I am migrating an ‘old’ 5.3 instance of ElasticSearch to 7.6.1. In my first (too optimistic) attempt I directly migrated to ElasticSearch 7.6.1.

When starting the ElasticSearch cluster this resulted in the following error message:

[myindex/gTTtwHT3ShiAz-eR94eKmw]] was created with version [5.3.0] but the minimum compatible version is [6.0.0-beta1]. It should be re-indexed in Elasticsearch 6.x before upgrading to 7.6.1.

               at org.elasticsearch.cluster.metadata.MetaDataIndexUpgradeService.checkSupportedVersion(MetaDataIndexUpgradeService.java:113)

               at org.elasticsearch.cluster.metadata.MetaDataIndexUpgradeService.upgradeIndexMetaData(MetaDataIndexUpgradeService.java:87)

               at org.elasticsearch.gateway.GatewayMetaState.upgradeMetaData(GatewayMetaState.java:240)

               at org.elasticsearch.gateway.GatewayMetaState.upgradeMetaDataForNode(GatewayMetaState.java:223)

               at org.elasticsearch.gateway.GatewayMetaState.start(GatewayMetaState.java:154)

               at org.elasticsearch.node.Node.start(Node.java:705)

at org.elasticsearch.bootstrap.Bootstrap.start(Bootstrap.java:273)

               at org.elasticsearch.bootstrap.Bootstrap.init(Bootstrap.java:358)

at org.elasticsearch.bootstrap.Elasticsearch.init(Elasticsearch.java:170)

               at org.elasticsearch.bootstrap.Elasticsearch.execute(Elasticsearch.java:161)

               at org.elasticsearch.cli.EnvironmentAwareCommand.execute(EnvironmentAwareCommand.java:86)

               at org.elasticsearch.cli.Command.mainWithoutErrorHandling(Command.java:125)

               at org.elasticsearch.cli.Command.main(Command.java:90)

               at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:126)

               at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:92)

For complete error details, refer to the log at C:\Program Files\ElasticSearch\7.6.1\logs\elasticsearch.log

The trick is to first migrate to ElasticSearch 6.8 and Kibana 6.8. Kibana 6.8 offers an update wizard which guides you through the required steps to upgrade your indices to a supported format.

Here are the steps:

• Go to Kibana
• Click on the Management icon on the left
• Click on the 7.0 Upgrade Assistant
• Check all the listed issues and apply the suggested solutions
• When everything is fixed you can safely install ElasticSearch 7.x

ElasticSearch - Error when using latest OpenJRE

While migration from ElasticSearch 5 to 7.6.1 I had to take an extra step and first migrate to ElasticSearch 6.8.

When I tried to run the 6.8 version, it failed with the following error message:

2020-03-30 10:18:32 Commons Daemon procrun stderr initialized

OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.

Exception in thread "main" java.security.AccessControlException: access denied ("java.lang.RuntimePermission" "accessClassInPackage.jdk.internal.vm.annotation")

               at java.base/java.security.AccessControlContext.checkPermission(AccessControlContext.java:472)

               at java.base/java.security.AccessController.checkPermission(AccessController.java:1036)

               at java.base/java.lang.SecurityManager.checkPermission(SecurityManager.java:408)

               at java.base/java.lang.SecurityManager.checkPackageAccess(SecurityManager.java:1324)

               at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:174)

               at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:521)

               at java.base/java.lang.Class.forName0(Native Method)

               at java.base/java.lang.Class.forName(Class.java:416)

               at java.base/sun.reflect.generics.factory.CoreReflectionFactory.makeNamedType(CoreReflectionFactory.java:114)

               at java.base/sun.reflect.generics.visitor.Reifier.visitClassTypeSignature(Reifier.java:125)

               at java.base/sun.reflect.generics.tree.ClassTypeSignature.accept(ClassTypeSignature.java:49)

               at java.base/sun.reflect.annotation.AnnotationParser.parseSig(AnnotationParser.java:440)

               at java.base/sun.reflect.annotation.AnnotationParser.parseAnnotation2(AnnotationParser.java:242)

               at java.base/sun.reflect.annotation.AnnotationParser.parseAnnotations2(AnnotationParser.java:121)

               at java.base/sun.reflect.annotation.AnnotationParser.parseAnnotations(AnnotationParser.java:73)

               at java.base/java.lang.reflect.Executable.declaredAnnotations(Executable.java:614)

               at java.base/java.lang.reflect.Executable.declaredAnnotations(Executable.java:612)

               at java.base/java.lang.reflect.Executable.getAnnotation(Executable.java:582)

               at java.base/java.lang.reflect.Method.getAnnotation(Method.java:696)

               at <<<guice>>>

               at org.elasticsearch.repositories.RepositoriesModule.lambda$configure$1(RepositoriesModule.java:68)

               at java.base/java.util.HashMap.forEach(HashMap.java:1338)

               at java.base/java.util.Collections$UnmodifiableMap.forEach(Collections.java:1505)

               at org.elasticsearch.repositories.RepositoriesModule.configure(RepositoriesModule.java:68)

               at <<<guice>>>

               at org.elasticsearch.node.Node.<init>(Node.java:492)

               at org.elasticsearch.node.Node.<init>(Node.java:245)

at org.elasticsearch.bootstrap.Bootstrap$5.<init>(Bootstrap.java:212)

               at org.elasticsearch.bootstrap.Bootstrap.setup(Bootstrap.java:212)

               at org.elasticsearch.bootstrap.Bootstrap.init(Bootstrap.java:322)

               <<<truncated>>>

The problem was that I already installed the latest JAVA OpenJRE. This version is not compatible with ElasticSearch 6.8.

To fix it I had to downgrade to Java 12.