Press "Enter" to skip to content

Category: Administration

In-Place SQL Server Upgrades

Published 2022-09-28 by Kevin Feasel

Garry Bargsley rolls the dice:

In my experience, two options exist to get the desired result. One, create a new server, install the latest supported version of SQL Server, and migrate your data. Two, upgrade SQL Server on the existing server.

There are pros and cons to each of these options. My preference is to go with option number one as it allows you more flexibility in your migration plan. However, many smaller shops might not have the hardware resources for this option, so they are forced to option number two.

In-place upgrades have improved considerably, though certain ancillary services (like Machine Learning Services) have breaking changes between versions, so you may be forced into the first route regardless.

Comments closed

Using the DAC with Dockerized SQL Server

Published 2022-09-27 by Kevin Feasel

Joey D’Antoni needed to use the Dedicated Administrator Connection:

Because as shown in the image above, the table in question is a system_table, in order to query it directly, you need to use the dedicated administrator connection (DAC) in SQL Server. The DAC is a piece of SQL Server that dedicates a CPU scheduler, and some memory for a single admin session. This isn’t designed for ordinary use–you should only use it when your server is hosed, and you are trying to kill a process, or when you need to query a system table to answer a twitter post. The DAC is on by default, with a caveat–it can only be accessed locally on the server by default. This would be connected to a server console or RDP session on Windows, or in the case of a container, by shelling into the container itself. However, Microsoft gives you the ability to turn it on for remote access (and you should, DCAC recommends this as a best practice), by using the following T-SQL.

Read on to see how, as well as what else you’d need to do to get it working.

Comments closed

Auto-Scaling SQL Managed Instances

Published 2022-09-20 by Kevin Feasel

Amanda Ibrahim answers a customer request:

If the customer needs to configure auto scaling for SQL Managed Instance, where the vCores can be increase and decrease automatically on specific time.

Read on to learn how. This can be useful for dev environments or places where your use case has “office hours”—that is, 90% of your activity takes place during a block of 4-12 hours—and you can afford the time in which the service is unavailable due to scaling.

Comments closed

Getting Started with SQL Server

Published 2022-09-20 by Kevin Feasel

Lee Markum helps out people new to SQL Server:

I see a lot of questions on data related Reddit forums and data science groups on LinkedIn about how to get started with SQL. Certainly these people mean that they want to learn the SQL language. I don’t think you can do that long term without setting up a home lab. You need your own place to experiment beyond what you can do in an online tutorial where you’re typing into a web browser, for example.

By the time you finish this post, you will be able to install SQL Server for use as a home lab for learning T-SQL and administration of the server environment.

Click through for a step-by-step guide. There’s a lot to learn after walking through this guide but you’ve got to start somewhere.

Comments closed

SQL Server Health Checks with SQLMonitor

Published 2022-09-19 by Kevin Feasel

Ajay Dwivedi performs a server health check:

Working as a DBA, I often get pulled into issues where application teams complain about “database server is slow”.

This general statement “server is slow” requires an equally robust approach that can help us figure out if there is an issue with CPU, memory, io, or regressed query. At this point, I prefer to use my SQLMonitor dashboard. This tool is entirely free and open source, and can be deployed on SQL Server 2014+ irrespective of any environment or edition.

Read on to see how it works and check out the GitHub repo as well as a one-off script you can run if you don’t have SQLMonitor set up.

Comments closed

SQL Server and the Slow Registry

Published 2022-09-15 by Kevin Feasel

Michael J. Swart diagnoses issues when the Windows registry slows down operations:

I want to describe some symptoms that SQL Server may display when its Windows Registry is non-responsive or slow. From the symptoms, it’s hard to know that it’s a slow registry and so if a web search brought you here, hopefully this helps.

Read on for some of the various operations which request data from the registry, as well as thoughts from Michael on some of the effects of a slow registry. It sounds like there’s not a whole lot we can do about it and this is rare.

1 Comment

Try out Chocolatey

Published 2022-09-13 by Kevin Feasel

Grant Fritchey makes a recommendation:

Chocolatey, as it says on the web site, is a package manager for Windows. But what does that mean? Basically, a package manager is a central place to install, upgrade and remove software. You get a database of the software you have installed, then it can help you to manage your software. Package managers are built into most operating systems. For example, apt or yum on a Linux box. However, not Windows. That’s where Chocolatey comes in.

I do like Chocolatey a lot as well and not just on VMs that I regularly need to rebuild.

Comments closed

Deploying an Arc-Enabled SQL Managed Instance

Published 2022-09-12 by Kevin Feasel

Warwick Rudd continues a series on Azure Arc-enabled data services:

Now that we have our Azure Arc-enabled Data Controller configured and available, we can now deploy our first Arc-enabled SQL Managed Instance into our environment. As previously mentioned depending on the type of configuration required for your environment with your Arc-enabled Data Controller (Directly connected or Indirectly connected modes) this will dictate the approach available for you to setup / configure your Arc-enabled SQL Managed Instance.

Click through for a step-by-step guide.

Comments closed