Press "Enter" to skip to content

Category: Tools

The Basics of tSQLt

Published 2020-04-15 by Kevin Feasel

Jess Pomfret walks us through the basics of tSQLt:

Getting started with tSQLt is really easy- you download a zip file, unzip the contents and then run the tSQLt.class.sql script against your development database.

There are a couple of requirements, including CLR integration must be enabled and your database must be set to trustworthy.  These could open up some security considerations, but for my development environment it’s no issue.

This is where I’d say putting the database in a container would be extremely helpful, as then you can destroy it afterward. I’m not sure if that’d work, as SQL Server on Linux doesn’t support unsafe or external access assemblies (and I’m not sure what tSQLt requires there).

Comments closed

Replaying Workloads to a Different Database with WorkloadTools

Published 2020-03-31 by Kevin Feasel

Gianluca Sartori takes us through workload replay ability in WorkloadTools:

One of the features I was asked to implement for WorkloadTools is the ability to replay commands to a database name different from the one recorded in the source workload.

This is something that I had been planning to implement for a while and it totally makes sense. Usually, you have two identical environments for the workload capture and replay, both with the same databases. Sometimes it makes sense to have two different databases as the source and target for the workload, for some particular reasons: resources constraints, ease of testing and so on.

WorkloadTools now supports replaying commands to a different database, using the DatabaseMap property of the ReplayConsumer.

Setting this up is pretty simple, though Gianluca does lay out a caveat.

Comments closed

sqltxls Now Supported Cross-Platform

Published 2020-03-26 by Kevin Feasel

Randolph West has made sql2xls work on Linux and MacOS:

Last year I released sql2xls, a free open-source tool which lets you throw a bunch of scripts into a folder, run them automatically against SQL Server and get the results back in a nicely-formatted Excel file, one tab per script.

A year later, I am happy to announce that the tool now runs cross-platform. You can compile and run it with .NET Core 3.1 LTS to run on Windows, macOS, and Linux. It uses the new Microsoft.Data.SqlClient library from NuGet, as well as the latest beta of ClosedXML.

Read on for a few more tips and go check the project out.

Comments closed

Real-Time Replay with WorkloadTools

Published 2020-03-04 by Kevin Feasel

Gianluca Sartori shows us how to perform a real-time replay with WorkloadTools:

Before we jump to how, I’d better spend some words on why a real-time replay is needed.

The main reason is the complexity involved in capturing and analyzing a workload for extended periods of time. Especially when performing migrations and upgrades, it is crucial to capture the entire business cycle, in order to cover all possible queries issued by the applications. All existing benchmarking tools require to capture the workload to a file before it can be analyzed and/or replayed, but this becomes increasingly complicated when the length of the business cycle grows.

I’m not sure how frequently I’d use real-time replays, but it’s nice to know that it’s pretty easy to pull off with WorkloadTools.

Comments closed

Disaster Recovery for Your Workstation

Published 2020-02-20 by Kevin Feasel

Randolph West explains that disaster recovery isn’t just for your servers:

I just completed a chapter for another book where I spoke about the Recovery Point Objective (how much data you are prepared to lose) and Recovery Time Objective (how long you have to bring your environment up again) after a disaster, and while I never get tired of repeating myself, that’s SQL Server. What happens if your development environment — or workstation — experiences a catastrophic failure?

Or what if, say, you’re on a cruise ship in the middle of the ocean with Internet access and a phone (but no laptop) and your on-call person just died? (I’ll leave this as an exercise for the reader to decide if this really happened.)

The answer is, if we do a careful bit of planning using the same disaster recovery principles we already know, the impact could be minimal. Note that this post assumes that you have Internet access and are using Microsoft Windows as your environment.

Click through for some useful suggestions.

Comments closed

Diagnosing and Resolving CPU Issues

Published 2020-01-29 by Kevin Feasel

Jeff Iannucci has a story to tell:

Anyhow, [Erik] Darling’s tool is designed to provide five data sets showing a quick view of memory and processor usage based on several dynamic management views (DMVs). It’s gives you a quick look into what’s going on when the CPU and/or memory of your SQL Server instance are feeling discomfort.

Why do I mention this? Because little did I know the very next day I would be presented with a fine opportunity to use this tool. And by opportunity I mean a server on fire. (Well, not literally, but still…)

Click through for a description of sp_pressure_detector and a testimonial of its utility.

Comments closed

Creating a UI in Powershell

Published 2020-01-24 by Kevin Feasel

Michael Berthold walks us through a useful example of using POSHGUI’s UI editor:

Some time back, a customer and I were working with the SentryOne PowerShell Module. Our PowerShell Module lets you manage the targets you are monitoring with SentryOne using a script or command line rather than the UI. This is a great time saver when you’re administering performance monitoring for hundreds or thousands of database servers.

The customer and I worked together to type up the commands they wanted for their script. They mentioned how it would be great if there were a GUI for this. This seemed odd initially, because the reason we were doing this in the first place was to automate these actions outside of a GUI. We spoke on it for a bit, and their meaning become clear. They envisioned a simple GUI used to guide in defining the commands for the PowerShell Module. I agreed that this would be helpful in getting a head start on scripting automation. I decided to find a way to fill this need.

This post explores one way to create a GUI using PowerShell. I’m using the SentryOne PowerShell Module for this example, but this method can be used for any PowerShell script.

Click through to see the example.

Comments closed

Undercover Catalogue 0.4

Published 2020-01-09 by Kevin Feasel

David Fowler announces a new release of the Undercover Catalogue:

The first major change that 0.4.0 brings is centralisation. With previous versions of the Catalogue, it’s been a requirement to have the Catalogue schema and procs installed on every server that you want to monitor.

0.4 changes that, there is now no need to have anything installed on any of the target instances. Simply install the Catalogue in one place, on your central configuration server and add any instances that you require monitored to the Catalogue.ConfigInstances table.

This makes it much easier to add in instances to the Catalogue.

There are a few other updates as well, so check them out.

Comments closed

Tools for Using SQL Server on Linux

Published 2019-12-12 by Kevin Feasel

Kellyn Pot’vin-Gorman has a list of tools you can use to make working with SQL Server on Linux a bit easier:

Along with the above versions of Linux distributions, SQL Server 2019 is supported in a container scenario using a Docker image.  Running a SQL Server database inside a Docker engine with Linux offers more flexibility, faster recovery, and quicker deployments, including deployments into the Azure cloud. For those becoming familiar with Linux, Docker for Windows or Mac gives you the option to run a Docker engine on your workstation with SQL Server 2019 on Linux.

Along with Docker technology, orchestration can be achieved, both managing and deploying SQL Server containers on Linux using Red Hat Open shift or Kubernetes. This includes SQL Server 2019 Big Data Clusters (BDC), fully scalable clusters with SQL Server, Spark, and Hadoop File System (HDFS). BDCs provide the ability to read, write, and analyze big data with T-SQL or Spark, and you can combine big data and relational data, too.

The set of tools just happens to be almost exactly the same set of tools as for Windows, but there are a few differences.

Comments closed