Infrastructure at your Service

All Posts By

Stéphane Haby

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
Stéphane Haby

SQL Server tips: How to migrate database-users in another domain without touching permissions

By | Database Administration & Monitoring, SQL Server | One Comment

By a customer, I must migrate a database from a domain to another. The goal is to keep the database-user permissions, take the new SID from the windows login of the new domain and change the database-user name. In my blog, I will do it with two dbi’s domains: – dbiservicech: The old domain – dbiservicecorp: The new domain I have two Windows groups dbiservicech\dbi-owner and dbiservicech\dbi-reader in my database test-dbi. I create two new…

Read More
Stéphane Haby

SQL Server 2019: Copy files through SQL Server

By | Database Administration & Monitoring | No Comments

Three new interesting extended stored procedures comes with SQL Server 2019. I was very interested to discover these new store procedures: Sys.xp_copy_file is to copy a specific files from a folder to another Syntax: exec master.sys.xp_copy_file ‘source folder+file’, ‘destination folder+file’ Example: exec master.sys.xp_copy_file ‘C:\Temp\Sources\File1.txt’, ‘C:\Temp\Destinations\File1.txt’ Before the command: After the command: As you can see in my test, you will have these 2 information where indicate the sucess of the query: Commands completed successfully. Time:…

Read More
Stéphane Haby

SQL Server Tool: MSSQL-CLI

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

MSSQL-CLI is a useful new command line tool. Not so new because this tool exist since 2018 but a little bit unknow. You find this tool on GitHub here. One of the big advantage is the number of platforms available, Windows of course but also macOS, Ubuntu, Debian, CentOs, Red Hat, OpenSuse, Suse Enterprise and Fedora. This interactive command-line query tool has many advantages like the full IntelliSense support, the auto-complete, the syntax highlighting, the…

Read More
Stéphane Haby

SQL Server Tips: Path of the default trace file is null

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

In addition of my precedent blog about this subject “SQL Server Tips: Default trace enabled but no file is active…”, I add a new case where the default path of the trace file was empty. The first step was to verify if the default trace is enabled with the command: SELECT * FROM sys.configurations WHERE name=’default trace enable’ It is enabled, then I check the current running trace with the view sys.traces SELECT * FROM…

Read More
Stéphane Haby

One day training @ Microsoft Azure Cloud Workshop

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

Today, with my colleague Christophe, we follow the Microsoft Azure Training Day: Data and Analytics @ Microsoft Zürich (Wallisellen to be exact). After a presentation of SQL Server 2019, we begin with the First Lab about SQL Server 2019 and new features and we going through intelligent query processing, Data Discovery and Classification for personally identifiable information (PII) and General Data Protection Regulation (GDPR) and secure enclave. One of my favorites part of the lab…

Read More
Stéphane Haby

SQL Server Tips: Orphan database user but not so orphan…

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

Beginning of this year, it is good to clean up orphan users in SQL Server databases. Even if this practice must be done regularly throughout the year of course. 😉 During my cleaning day, a new case appears that I never had before and enjoy to share it with you. To find orphan database-users, I use this query: SELECT *FROM sys.database_principals a LEFT OUTER JOIN sys.server_principals b ON a.sid = b.sid WHERE b.sid IS NULL…

Read More