Press "Enter" to skip to content

Category: Administration

Managing Jobs in Availability Groups

Published 2020-03-27 by Kevin Feasel

Goncalo Cruz has a plan to run SQL Agent jobs on the primary node in an availability group:

In SQL Availability Groups the SQL jobs have to be created in all replicas and you need to add logic at the beginning of each relevant job to make it execute on the primary database. (this only applies when the local replica is the primary for the database)

If you do not add the logic they will execute with success in the primary replica but they will fail in the secondary replica.

Read on for a process which keeps jobs from running except on the primary.

Comments closed

Using relog to Make PerfMon Better

Published 2020-03-26 by Kevin Feasel

Erin Stellato shows us how to use relog:

Today I had to remember the steps for using relog. If you’ve never heard of relog, stay with me. It’s a tool for merging PerfMon files, and also for extracting smaller data sets from Perfmon. I still love PerfMon friends, I admit it. It’s been a staple for understanding performance on a Windows Server since I’ve started in IT. But I haven’t used relog in forever so I completely forgot the commands I needed.

Click through for usage instructions.

Comments closed

Monitoring with WhoIsActive

Published 2020-03-18 by Kevin Feasel

Hadi Fadlallah looks at one of my favorite stored procedures:

For this reason, Adam Machanic (a Microsoft MVP since 2004) developed a more powerful stored procedure called “sp_whoisactive” to fill in the gap between the actual needs of DBAs and the currently provided procedures (sp_who and sp_who2).

In the following sections, we will talk briefly about sp_who and sp_who2 stored procedure, then we will illustrate how to download and use sp_whoisactive stored procedure.

Though there is a nicer way to insert into a table based on WhoIsActive outputs.

Comments closed

Running SQL Server on a Windows Container

Published 2020-03-18 by Kevin Feasel

Jamie Wick takes us through the less-trodden path:

SQL Server containers are gaining popularity as a way of enhancing and standardizing development environments for Windows & Linux based SQL databases. SQL containers allow developers to have their ‘own’ dedicated copy of a database, usually without the need for extensive server infrastructures. Additionally, a single computer can host multiple containers, each with a different edition/version of SQL Server. This allows the user to quickly switch between environments, without the need to reinstall. Currently, a popular option for implementing containers on Windows-based computers uses Docker.

For those not familiar with containerization, here is a Microsoft article on Windows containers.

I’d definitely prefer to use Linux containers, even on Windows machines. But if Windows-based containers is your thing (or you need to use them for some reason), Jamie’s got you covered.

Comments closed

Restoring Databases Inside a Container

Published 2020-03-16 by Kevin Feasel

Aaron Bertrand has a quick-and-easy method of restoring databases in a Docker container:

I have been using Docker containers for local development and testing for some time now; I first blogged about my steps into this brave new world back in late 2016. Most of the time, I just need to create some throwaway database, with a throwaway table, to prove a point or to validate an answer I’ve supplied.

Sometimes, though, I need to work with a real database. It’s a little trickier to do this in a container, because it’s isolated — I can’t just attach or restore from my Downloads folder. I could fire up a VM and attach there, but I actually don’t even use Parallels on my work laptop, and I find that using VMs leads to response times that are a lot more sluggish across the board.

It’s not too difficult to get files into your containers, provided your instance will have a total size less than the size of the container, and this quick tutorial proved to be very helpful.

Read on for the walkthrough. Aaron notes that this is a Mac-specific walkthrough, though the Windows and Linux versions are pretty similar as well, as we still create persistent volumes.

Comments closed

Oracle’s Automatic Workload Repository Explained

Published 2020-03-12 by Kevin Feasel

Kellyn Pot’vin-Gorman explains to us what the Automatic Workload Repository is:

The Automatic Workload Repository, (AWR) had been around since Oracle 10g and requires the diagnostic and tuning management pack licensing to use all of its features in Oracle’s Enterprise Edition database. Versions before 10.2.0.4 had limited collections vs. the modern reporting schema and every subsequent release of Oracle has added to it’s content, which explains the size increase stored in the objects/number of objects in the SYSAUX tablespace.

By default and since version 11.2.0.4, the AWR retention is 8 days and takes an automatic snapshot once per hour. It’s common for DBAs to up this retention to at least 31 days to capture a month of workload information and these snapshot identifiers can then be used to identify workload intervals for querying and reporting. Oracle can be also be configured to lessen the intervals between snapshots to change the granularity of the AWR reports, or my preference, the DBA or privileged user can take manual snapshots to identify an important beginning or ending of a period.

Kellyn goes into a good amount of detail in this post and, based on the title, promises at least a part 2. Though this could be a History of the World: Part I trick Kellyn is playing on us.

Comments closed

Improving vCenter Performance Metric Logging

Published 2020-03-10 by Kevin Feasel

David Klee has some recommendations on settings for vCenter performance metric collection:

The default performance metric statistics collection interval within vCenter is to start rolling up data in an aggregation method starting at just one hour. Much of the data necessary for troubleshooting of performance challenges reported either same day or on the previously day is lost from the vCenter data and forces the administrator to revert to cumbersome and/or time-consuming tooling, such as vRealize Operations Manager. DBAs might not have access to such tools. Hopefully by now they have read-only access to vCenter!

The vCenter performance statistics collection and rollup settings can be customized to provide a longer window of time for critical metrics to be available to the administrator for management.

Click through for some recommendations of aggregation intervals and collection durations to help with virtual machine troubleshooting.

Comments closed

Finding Long-Running Queries with system_health

Published 2020-03-10 by Kevin Feasel

Grant Fritchey shows us where we can find long-running queries easily:

Wouldn’t it be great to just quickly and easily take a look at your system to see if you had any queries that ran for a long time, but, without actually doing any work to capture query metrics?

Oh, yeah, I can do that right now, and so can you.

All we need is something that is built into every single server you currently have under management (sorry, not Azure SQL Database) that is SQL Server 2008 or better: system_health

Grant then ties this into general benefits of Extended Events and shows how you can query and view the results.

Comments closed

Troubleshooting Azure SQL DB Elastic Jobs

Published 2020-03-06 by Kevin Feasel

Kate Smith wraps up a series on elastic jobs in Azure SQL Database:

This error means that the Elastic Job Agent cannot connect to the target server(s) because the target has some firewall rules blocking the connection requests.  Indeed – it is required that every target in the target group allows connections from Azure Services in order for Elastic Jobs to work.  To fix this, I go to the target server in the Azure Portal and click on the “Firewalls and virtual networks” item under “Security”.  Next, I toggle the “Allow Azure services” from OFF to ON, and save my changes.  

This has been an interesting series to read through, even though I don’t do much at all with Azure SQL Database.

Comments closed

Running and Scheduling Azure SQL DB Elastic Jobs

Published 2020-03-05 by Kevin Feasel

Kate Smith continues a series on Azure SQL Database Elastic Jobs:

In previous posts, I have demonstrated how to create an Elastic Jobs Agent, setup credentials for Elastic Jobs, create a target group of servers/databases for the agent, and how to create and define an elastic job using both PowerShell and T-SQL.

In this post, I drill down into how to run an Elastic Job both in an ad-hoc fashion and how to schedule a job to run regularly. I do this both for PowerShell and for T-SQL.

The Powershell version is a one-liner and the T-SQL version looks a good bit like it does with SQL Agent jobs.

Comments closed