Press "Enter" to skip to content

Category: Administration

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

ASYNC_NETWORK_IO and Execution Plans

Published 2020-03-05 by Kevin Feasel

Jonathan Kehayias dives into an interesting problem:

A few weeks ago, an interesting question was asked on the #SQLHelp hash tag on Twitter about the impact of execution plans on the ASYNC_NETWORK_IO wait type, and it generated some differing opinions and a lot of good discussion.

My immediate answer to this would be that someone is misinterpreting the cause and effect of this, since the ASYNC_NETWORK_IO wait type is encountered when the Engine has results to send over TDS to the client but there are no available TDS buffers on the connection to send them on. Generally speaking, this means that the client side is not consuming the results efficiently, but based on the ensuing discussion I became intrigued enough to do some testing of whether or not an execution plan would actually impact the ASYNC_NETWORK_IO waits significantly.

To summarize: Focusing on ASYNC_NETWORK_IO waits alone as a tuning metric is a mistake. The faster a query executes, the higher this wait type will likely accumulate, even if the client is consuming results as fast as possible. (Also see Greg’s recent post about focusing on waits alone in general.)

Click through for the things Jonathan tested.

Comments closed

Setting Up a SQL Server Lab with AutomatedLab

Published 2020-03-04 by Kevin Feasel

Jess Pomfret looks at a very interesting Powershell module:

There is a fantastic PowerShell module called AutomatedLab that can enable you to easily build out a lab for the  specific scenario you need to test. Even better is the module comes with 70 sample scripts that you can start with and adapt to meet your needs.

The module gives you the option to work with Hyper-V or VMWare. I will say most of the examples are using Hyper-V, and that is what I’ll be using also.

For my lab I want a SQL Server 2019 instance joined to a domain, and a separate client machine that I can manage the SQL Server from. On the client I would need to be able to connect to the internet as I want to be able to download PowerShell modules from the gallery easily.

It’s about time for me to rebuild my lab, so I’ll need to check that out.

Comments closed

Capturing Query Errors with Extended Events

Published 2020-03-03 by Kevin Feasel

Jack Vamvas shows how to capture query errors using Extended Events:

If you’re troubleshooting SQL Server query errors , you’ll already know Extended Events are highly useful and very effective method to capture SQL Server errors.

To use the script you’ll need appropriate privileges to create the Extended Event. You will also need to have some space available on the disk to store output files.

If you want something a little less permanent, you can use the ring buffer target. I put together something like this a long time ago and enjoyed IM-ing coworkers and saying “You forgot the join criteria” with no other context. Freaked them out the first couple of times…

Comments closed