Press "Enter" to skip to content

Category: Administration

The Benefits of CNAMEs

Published 2022-07-13 by Kevin Feasel

Deepthi Goguri wants a CNAME record:

If you are using the Servername and database name in the connection strings in all the applications connecting to your database being migrated, this process gets tough and tedious to update once the database is migrated to a different server. It is always advised to use database DNS in the connection strings instead of the IP address or the Servername.

This also applies to the report server datasources as well.

I know it is hard to make the change but if the change is good, it is worth the effort.

I think this starts to get tricky as the number of servers and instances increases, especially if your DNS records differ considerably from your server names. Still, I completely agree: using CNAMES makes life a lot easier.

Comments closed

The Risks of “Unused” Databases

Published 2022-07-13 by Kevin Feasel

Chad Callihan reminds us about things in use:

You may have been updating data someone needed for validating a fix. The “_OLD” table that you assumed could be dropped may still have been useful to the person who created it. There might be a database covered in cobwebs that should have been dropped years ago but it could also be a database that’s used for some type of reporting every few months.

Yeah, that’s a pretty common problem. A couple of things which help mitigate this issue:

  • Check wherever you can to see if the database (or database object) is in use: cached plans, stored procedure calls, application calling code, SQL Agent jobs, SSIS packages, etc.
  • Take (and test!) backups of databases before you drop tables or get rid of them.
  • Keep those database backups around for quite a while.
  • Take databases offline for a while before dropping them. That way, if somebody really does use it on occasion, it’s easy to bring back online rather than needing to restore from a backup.

At the end of the day, however, you shouldn’t be afraid to drop things. Do the appropriate amount of diligence and make it a controlled demolition.

Comments closed

Test Those Backups

Published 2022-07-12 by Kevin Feasel

Kevin Chant comes at us with important advice:

In reality, not being able to restore a database can damage your company’s reputation and you can end up losing colleagues for various reasons. For example, if they ask for restores to be tested and it never happens.

To save your colleagues from a database restore nightmare you must test restoring your database backups at some level. Otherwise, it can lead to serious issues.

As the saying goes, you don’t have a backup until you’ve successfully restored it.

Comments closed

Gateways and the CPU Cost of Power BI Dataset Refresh

Published 2022-07-12 by Kevin Feasel

Chris Webb continues experimenting:

After last week’s post on measuring Power Query CPU usage during dataset refresh, someone asked an obvious question that I should have addressed: does using a gateway change anything? After all, if you’re using a gateway to connect to an on-premises data source then all the Power Query queries transforming the data from that source will be executed on the gateway machine and not in the Power BI Service.

Let’s do a quick test to find out. 

Read on to see what Chris found out.

Comments closed

Building Custom Widgets for Azure Data Studio

Published 2022-07-08 by Kevin Feasel

Esat Erkec builds a widget:

One of the most advantageous features of ADS is that it allows the creation of customized widgets. With the help of the widgets, we can easily visualize the result of the queries using different graph types. In this context, building the performance monitoring widgets can be a reasonable approach so that we can track the performance metrics readily. Now, let’s learn how to build a custom widget with a very straightforward example.

I haven’t tried this before in Azure Data Studio but I can see the benefit, especially if you have a common set of queries you intend to run to observe the status of a given server.

Comments closed

Buffer Pool Parallel Scans in SQL Server 2022

Published 2022-07-08 by Kevin Feasel

David Pless talks about an internal optimization in SQL Server 2022:

Operations such as database startup/shutdown, creating a new database, file drop operations, backup/restore operations, Always On failover events, DBCC CHECKDB and DBCC Check Table, log restore operations, and other internal operations (e.g., checkpoint) will all benefit from Buffer Pool Parallel Scan.

In SQL Server 2019 and previous releases, operations that require scanning the buffer pool can be slow, especially on large memory machines such as the M-series Azure SQL virtual machine and large on-premises SQL Server environments. Even log restore operations and availability group failover operations can be impacted. Currently, there’s no way to eliminate this issue prior to SQL Server 2022, and dropping buffers using DBCC DROPCLEANBUFFERS would likely result in some degree of performance degradation as any subsequent query executions will have to reread the data from the database files increasing I/O.

Read on to understand why these operations can be slow on high-memory boxes and how much of a benefit you might get on certain administrative activities.

Comments closed

Removing a Data Disk from a Running Azure VM

Published 2022-07-07 by Kevin Feasel

Joey D’Antoni tightrope walks without a net for fun:

I was working with a client recently, were we had to reconfigure storage within a VM (which is always a messy proposition). In doing so, we were adding and removing disks from the VM. this all happened mostly during a downtime window, so it wasn’t a big deal to down a VM, which is how you can remove a disk from a VM via the portal. However, upon further research, I learned that through the portal you can remove a disk from a running VM.

Read on to see how. Though I’d generally still recommend shutting the VM off first just to be sure.

Comments closed

Finding Unique Key Violations with Extended Events

Published 2022-07-06 by Kevin Feasel

Grant Fritchey points out another use for extended events:

Most of the time when I talk about or demo Extended Events, I spend more time talking about query tuning (I have a problem). However, there are tons of things that you can do with Extended Events. Here’s a little one that came up, auditing unique constraint violations.

It can also handle most other types of errors, making this a robust way of tracking issues. Back in the 2008 days, I built a little WPF program to watch for all of the errors on the couple of production SQL Server instances I managed. At one point, I saw one of the devs trying to write a query and getting an error. I IM’d the dev and said “You forgot the GROUP BY clause” (or whatever the problem was—it was something minor like that). He came over with a bit of panicked excitement, trying to figure out how exactly I was able to see what he wrote given that I was nowhere near his cube. Good times.

Comments closed

Finding and Documenting SQL Server Instances

Published 2022-07-06 by Kevin Feasel

Tracy Boggiano continues a series on things to do at a new job as a DBA:

In my previous post, I expounded on my first 30 days I had at four jobs in the last four years. and how to setup your jobs box. I commented and got quoted on the fact that if it’s documented I don’t support it. So, these are methods of getting things documented, some including just having to have meetings, others running code.

One I believe in having a Central Management Server (CMS) where you can register your servers. Put them in as many groups as you desire but have core group such as Dev, Test, QA, UAT, Prod, Prod Sec, etc. The rest could be by application name if needed. I always have a set of names that are for the DBAs to use to do our work, other teams can have theirs for their work, i.e., deploying code.

If you aren’t using dbatools yet you should be. While not every shop can use to manage everything it is works every well for most tasks and that includes scanning the network for SQL Instances. Because unless you could into a well oiled machine there will be instances they don’t know about and one day someone will come knocking asking to fix it. Warn your security team before you run this.

Read on for examples of how you can find instances (assuming the security team is okay with it!), some of the information you’d want to document, and more. I would also recommend the most recent episode of the SQL Data Partners podcast, in which we talk to Jen and Sean McCown about documenting and managing your SQL Server inventory.

Comments closed

Cumulative Updates and GDRs

Published 2022-06-27 by Kevin Feasel

Aaron Bertrand clarifies two concepts:

The underlying problem is that servicing complex software is, well, complex. Microsoft simplified this for our little corner of the world when they announced that SQL Server 2016 would be the last release to get service packs. We still have Cumulative Updates (CUs) and General Distribution Releases (GDRs) to deal with, but they tend to only cause confusion around Patch Tuesday (or the – cough – odd time a CU breaks things). Before I explain, let’s define these:

Read on for the definitions and why the GDR path exists.

Wait, I thought the German Democratic Republic (GDR / DDR) re-unified with the Federal Republic of Germany (FRG / BRD) in 1990… Ah, the lengths I go to for an awful joke.

Comments closed