Infrastructure at your Service

Category

SQL Server

Steven Naudet

SQL Server: How to delete the msdb backup history kindly

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

This week I noticed some slowness on queries related to backup history. The instance had a huge msdb database (more than 10GB size), the backup history had never been purged. I already wrote a post on this topic and provided a small PowerShell script using dbatools to clean the backup history. The issue with sp_delete_backuphistory or Remove-DbaDbBackupRestoreHistory is that the operation is done as one transaction and this could badly impact the msdb transaction log…

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
Steven Naudet

SQL Server: Audit changes on Instance Configuration, Linked Servers and Agent Jobs

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

Introduction Very often with our customers, there is only one person with the DBA role. The configuration of the instances is then under control and if anything strange has to be questioned, the culprit is quickly identified. 🙂 When many people, not necessarily having DBA knowledge, have high permissions (sysadmin) on instances it becomes important to know who does what. In this blog post, we will see how to get notified by email when certain…

Read More
Steven Naudet

SQL Server connectivity issue – troubleshoot TLS configuration

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

In the blog post, I will share a case of troubleshooting a connectivity issue with SQL Server. The problem occurred in a migration context. The SQL Server databases (in version 2008 to 2014) from multiple applications were consolidated on a new server with SQL Server 2019. Application servers were also migrated to new VMs. The new servers are hosted on a brand new infrastructure managed by a third-party provider which my customer has very little…

Read More
Steven Naudet

SQL Server: Create a Shared Storage for your Failover Cluster LAB

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

I’m currently working on the migration of a Failover Clustered SSAS instance from Windows Server 2012 to Windows Server 2019. The context is quite complex and in order to choose the right migration scenario I need to play with it on my LAB environment first. I usually work with AlwaysOn architectures and it’s not often that I need to set up an FCI with its Cluster Shared Volume. As a reminder, a Cluster Shared Volume…

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
Christophe Cosme

SQL Server – PolyBase Services when listening on all IP is disabled

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

Introduction You will find a lot of blogs explaining how to install the PolyBase feature with a SQL Server database instance. You will also learn how to configure it and how to use it in these blogs. But I could not find any solution for one case I was faced to. When you have several SQL Server instances on a server you will either allocate a fix port per instance or a dedicated IP per…

Read More
Nathan Courtine

Automate CNOs and VCOs for SQL Server AAG

By | SQL Server | 2 Comments

During the installation of a new SQL Server environment in a Project, we wanted to automate the whole process deployment and configuration when installing a new SQL Server Always On Availability Group (AAG). This installation requires to prestage cluster computer objects in Active Directory Domain Services, called Cluster Name Objects (CNOs) and Virutal Computer Objects (VCOs). For more information on the prestage process, please read this Microsoft article. In this blog, we will see how…

Read More
Steven Naudet

SQL Server: Control the size of your Transaction Log file with Resumable Index Rebuild

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

Introduction In this blog post, I will demonstrate how the Resumable capability of Online index rebuild operation can help you to keep the transaction log file size under control. An index rebuild operation is done in a single transaction that can require a significant log space. When doing a Rebuild on a large index the transaction log file can grow until your run out of disk space. On failure, the transaction needs to rollback. You…

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