Hi, I am Gareth N - a SQL Server DBA in the UK. I have started to blog over at ifexists.blog - today I want to share some information around how dbatools can cache connection information, and how to reset it.
dbatools is smart. It can do things in the background when you’re using the commands, like cache connections and also cache the results of those connections. This can help speed things up as it will re-use the existing object.
We are so super excited to announce that after 5 long years, dbatools 1.0 is publicly available!
Our team had some lofty goals and met a vast majority of them 🏅. In the end, my personal goal for dbatools 1.0 was to have a tool that is not only useful and fun to use but trusted and stable as well. Mission accomplished: over the years, hundreds of thousands of people have used dbatools and dbatools is even recommended by Microsoft.
Recently, a colleague asked me to assist with the migration of some older, customized application databases to a SQL Server 2017 instance. Most of the migrations I perform are rather vanilla, but this one was a bit more involved.
Setup Imagine this scenario:
Source (APPSQL1) Dedicated SQL Server 2008 R2 failover clustered instance with a non-default collation (SQL_Latin1_General_CP1_CI_AI) Custom .NET application with an intense database containing a non-default collation (also SQL_Latin1_General_CP1_CI_AI), multiple CLR assemblies, and thousands of tables, views, stored procedures, functions Nearly 30 SQL Agent jobs, many of which had to be disabled over the years due to compatibility issues and scope changes Out-of-support for both Microsoft and the application vendor Destination (APPSQL2) Shared server SQL Server 2017 Default collation (SQL_Latin1_General_CP1_CI_AS) There was even a linked server in the mix, but our biggest concerns revolved around the changing collation and the Agent jobs, which were known to be brittle.
Today marks the 30 day countdown to dbatools 1.0, which we will be debuting at Data Grillen in Lingen, Germany! These next 30 days are important and I’m writing to ask for your help.
Integration Tests We could still use a hand getting in those last few Integration tests. If you’re interested in adding a couple tests, I did a quick lil livestream on Twitch about writing integration tests for dbatools that could be useful to you.
I am Andreas Schubert and I am working as a Principal Consultant and Database Reliability Engineer for SQL Server & Azure for multiple national and international companies. My focus is on implementing and operating complex 24/7 SQL environments with tens and hundreds of servers and multi-terabyte databases.
Welcome to a quick post that should help you operate your SQL Server environment more consistently and reduce manual, repetitive work.
The Problem When you are running SQL Server Availability Groups, one of the most cumbersome tasks is to ensure that all logins are synchronised between all replicas.
The Situation Hey all, I am Andreas Schubert and I am working as a Principal Consultant and Database Reliability Engineer for SQL Server & Azure for multiple national and international companies. My focus is on implementing and operating complex 24/7 SQL environments with tens and hundreds of servers and multi-terrabyte databases.
With the multitude of environments that I am operating, it’s impossible to remember every server, every database or the multiple different ways they are interacting with each other.
Recently, dbatools contributor Jess Pomfret teamed up with Bert Wager for a super cool video about dbatools.
Check it out!
They also wrote accompanying blog posts too! Jess wrote a post titled dbatools with Bert where she talks more in-depth about Backing up your databases and changing your recovery model. Bert penned a post titled Automating Database Maintenance with Jess Pomfret and dbatools 😄👍
How incredibly cool! Thank you Jess and Bert for the awesome and fun video 🍕
This post originally appeared in two parts on my personal blog at flxsql.com and has been reposted here by request.
A bit over a year ago, I blogged about my experience migrating a test SQL Server instance from a VM to a physical machine with a little help from my friends. That migration went well and the instance has been running trouble-free ever since. But it’s small potatoes. A modest instance, it’s only about 5% the size of production.
In this age of many companies adopting the ideas of either DevOps or SRE (Site Reliability Engineer) roles, there is constant need for automation. Mundane tasks like collecting metrics, alerts, server and database asset info into a central database on which you can do reporting is now a common task that many DBAs or system admins are performing.
In SQL Server world, we are fortunate to have a vibrant community of PowerShell enthusiasts that have open sourced dbatools - a community driven PowerShell module to automate database development and administration.
Splatting in PowerShell makes code easier to read. Instead of typing a bunch of parameters allllll across the screen, you can use an easy-to-read hashtable or array. Argument splatting was introduced in PowerShell v3 and works with all PowerShell commands, not just dbatools.
Note: I’ve only used splatting with hashtables, as they allow me to be explicit about which parameters I’m passing. It appears that arrays would employ positional parameters, which is less wordy but leaves room for error.
dbatools