Infrastructure at your Service

Tag

SQL Server Archives - Blog dbi services

SQL Server: Quickly clean backup history with dbatools

By | Database Administration & Monitoring | No Comments

I just had to restore a database in production for my customer. Before doing the restore I have the habit to query the msdb.dbo.backupset table to get an overview of the last backups. When running my query, I felt it was taking longer than usual. So out of curiosity, I looked at the SSMS standard report “Disk Usage by Top Tables”. Here is the output. This instance contains dozens of databases in Always On Availability…

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
Nathan Courtine

Restore S3 Object with AWSPOWERSHELL

By | Cloud | No Comments

AWS S3 offers different Storage Classes, allowing to optimize cost among others. For instance, some classes are used for archiving purposes: S3 Glacier and S3 Glacier Deep Archive. It means the storage cost is the lowest you can obtain, but your data is not available immediately and the access cost is increased. In the case of S3 archive classes, retrieving the data is not cost-effective because this is clearly not what it is aimed for….

Read More
Franck Pachot

Running SQL Server on the Oracle Free tier

By | Cloud, Oracle, SQL Server | 2 Comments

By Franck Pachot The Oracle Cloud is not only for Oracle Database. You can create a VM running Oracle Linux with full root access to it, even in the free tier: a free VM that will be always up, never expires, with full ssh connectivity to a sudoer user, where you are able to tunnel any port. Of course, there are some limits that I’ve detailed in a previous post. But that is sufficient to…

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

SQL Server – Collecting last backup information in an AlwaysOn environment

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

Introduction Sometimes you face interesting challenges with unusual environment. One of my customer needed a automated and flexible backup solution. Said like that nothing very complex you will say. But if I mention that some databases were 60TB big with more than 30 filegroups and around 600 database data files each and moreover synchronized in an AlwayOn availability group, it is not the same story and you can easily imagine that working with standard backup…

Read More
Microsoft Team

Using non-root SQL Server containers on Docker and K8s

By | Database Administration & Monitoring, DevOps, Docker, Kubernetes, SQL Server | No Comments

This is something that I waited for a while, in fact since SQL Server 2017 … and the news came out on Wednesday 09th September 2019. Running Non-Root SQL Server Containers is now possible either on the next version of SQL Server (2019) and it has been backported on SQL Server 2017 as well. Non-root SQL Server containers will likely be part of hidden gem of SQL Server new features, but this is definitely a…

Read More
Microsoft Team

When Read-Scale availability groups and Windows Failover Cluster are not good friends

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

A couple of days ago, with some fellow French data platform MVPS (@thesqlgrrrl and @Conseilit) we discussed an issue around Read-Scale availability groups and it made me think I had forgotten to share about a weird behavior I experienced with them.   Basically, Read-Scale availability groups are clusterless infrastructures meaning there is no need to install an underlying cluster. Obviously, you will not benefit from resource orchestration and automatic failover, but this is obviously the…

Read More