Infrastructure at your Service

All Posts By

Stéphane Haby

Stéphane Haby

SQL Server 2019: What’s new in sp_configure and sys.configurations options?

By | Database Administration & Monitoring, Database management, SQL Server, Technology Survey | No Comments

SQL Server 2019 added new options in sp_configure and sys.configurations. First, how can we find the difference between these SQL Server Versions. This simple query will give us the number of options and the SQL Server Version: select count(*),@@version FROM sys.configurations In SQL server 2016, we have 74 parameters for the instance configuration: In SQL server 2017, we have 77 parameters for the instance configuration: In SQL server 2019, we have 84 parameters for the…

Read More
Stéphane Haby

SQL Server SCOM Alert Analysis: The Average Wait Time of SQL instance “xxx” on computer “xxx” is too high

By | Database Administration & Monitoring, Database management, Development & Performance, SQL Server | No Comments

I have an interested alert on SCOM for SQL Server by a customer two weeks ago. In the SCOM interface, the warning on the server is MSSQL2016: The Average Wait Time of SQL instance “xxx” on computer “xxx” is too high. After right-click on the properties, go to the tab Alert Context. On this part, you can see that the Object Name is MSSQL$xxx:Locks. My first step is to go in the Reports>Standard Reports> Performance…

Read More
Stéphane Haby

SQL Server SCOM: Many perf.perfHourly and State.StateHourly tables in OperationManagerDW

By | Database Administration & Monitoring, Database management, Development & Performance, SQL Server | No Comments

By a customer, the SCOM’s Database OperationManagerDW growth fast and becomes very big. To see what’s happens, in SSMS (SQL Server Management Studio), I right-click on the database open Reports>Standard Report> Disk Usage by Top Tables: You have many interesting reports with SSMS and I advise you to use it. In my case the “Disk Usage by Top Tables” report displays me many perf.perfHourly and State.StateHourly tables with a _guid(): After reading some blog’s, the…

Read More
Stéphane Haby

SQL Server: How to compare Server and Database Collation

By | Database Administration & Monitoring, Database management, SQL Server | No Comments

Sometimes during an SQL Server audit, I check the collation of the server and the databases but every time separately. The goal of this blog is to give you a script to have both and compare it. First step is to have the server collation and the code page. The code page will help to see if the character set is the same. For example, for us, the 1252 is Latin/Western European and the 1258…

Read More
Stéphane Haby

SQL Server: Analysis of SCOM Warning on the Health Check

By | Database Administration & Monitoring, Database management, SQL Server | No Comments

Sometimes, it’s hard to find how to analyze a Warning in the SCOM Interface and correct the problem. By a customer, we install new instances in SQL Server 2019 and change the version of SCOM. All new servers have a warning on the Health. The first step in the SCOM interface is to go to the SQL Server Roles: On this step, the most important is to open the Health Explorer and not double-click or…

Read More
Stéphane Haby

SQL Server Tips: Get back permissions lost for mount points!

By | Database Administration & Monitoring, SQL Server | No Comments

Recently by a customer, we are facing the case to lose all mount points on a secondary node in a HA SQL Server AlwaysOn infrastructure due to a big problem on the Datacenter. We need to build from scratch all mount points for the Data, Log and Tempdb. After multiple researches, I didn’t find how to give back correctly the permission for the SQL Server account on these mount points. The disk environment is very…

Read More
Stéphane Haby

SQL Server: Replace the old SCOM login by the new one

By | Database Administration & Monitoring, Database management, SQL Server | No Comments

It’s every time hard to replace a login who as role and permissions in every database like the service account for SCOM. In the previous version of Scom, you create a service account (dbi\scom) to access the SQL Server and check the health of the databases. Now it’s possible to use the System Center Operations Manager Health Service (NT SERVICE\HealthService). To do this change on all SQL Servers, I write a script. I create the…

Read More
Stéphane Haby

Ansible Basics Workshop day @dbi services

By | Ansible, Database Administration & Monitoring, DevOps, Technology Survey | No Comments

Today, I follow our Workshop Ansible Basics with Jérôme Witt. Why I follow this workshop? First, it’s to have the basics and Best Practices before begin myself and do “bricolage” by my customers. The second point is to develop templates to use and reuse by all customers if needed. Let’s start the course! My first remark is that the Control node cannot be installed on a Windows machine… Not good news for me! 👿 I…

Read More
Stéphane Haby

Copy or Migrate a SSISDB environment 2.0

By | Database Administration & Monitoring, Database management, SQL Server | No Comments

Based on the blog “Copy or Migrate a SSISDB environment” of my colleague Christophe, I create a new one named 2.0. In the precedent script, you need to give the folder name and environment name and he generates all environment variables for these 2 parameters. In my case, I need to generate all folders and what are in these folders. The 2.0 script will generate the creation of the folder and the environment name if…

Read More
Stéphane Haby

First steps on Kusto Query Language (KQL)

By | Database Administration & Monitoring, Database management, Development & Performance, SQL Server | No Comments

Do you know this query language?  No, it’s time to explain you and see what it is.   “A Kusto query is a read-only request to process data and return results.” dixit Microsoft Documentation here The KQL is very simple to understand and use. I can do it then you can also do it! 😛 The first thing to know is how to call the information on a table. In this case you  need to…

Read More