Press "Enter" to skip to content

Month: September 2020

Five Methods to Take Backups

Published 2020-09-29 by Kevin Feasel

Lee Markum gives us five different methods for taking backups in SQL Server:

For those readers who like the SSMS GUI, there is good news.  SQL Server Management Studio offers a fairly straightforward method for backing up a database. Once you connect to the SQL Server instance containing the database you want to back up, left click on the “+” sign next to the Databases folder.  Then right click the name of the database you want to backup, choose Tasks, then choose the Backup option in the fly out menu.

If you are a DBA, it is really important to know how to take and restore backups using T-SQL or Powershell only. Especially restoring backups—in the highest-pressure situations, waiting for the UI to load can be painful.

Comments closed

The Dynamic SQL Hammer

Published 2020-09-29 by Kevin Feasel

Erik Darling talks about dynamic SQL:

I think it was Doug Lane who coined the stages of dynamic SQL. One of them dealt with the fact that once you start using it, you just wanna use it everywhere, even places where you think you don’t need it.

Most of the time, you don’t. A parameter is good enough. But like we saw last week, sometimes parameters can backfire, and you can use dynamic SQL to save query performance.

That’s one great use, but it’s one you’re gonna have to use constantly. Most of the time when you’re using dynamic SQL, it’s not going to be to correct performance.

Click through for Erik’s thoughts.

Comments closed

Managing Power BI Administrators

Published 2020-09-29 by Kevin Feasel

Melissa Coates has some guidance for us:

The Power BI administrator role is a high-privilege role which should be carefully managed. As with many aspects of administration and governance, this involves having a balance between people being able to get things done and risk of when too many people having elevated permissions.

Click through for the blog post as well as a video.

Comments closed

Using Tables for Visualization

Published 2020-09-28 by Kevin Feasel

Alex Velez takes us through one of the simplest visuals:

Tables are a common way to show data, but in my current work, I don’t create them frequently. Admittedly, when I come across a table, I often choose to visualize the data. I should clarify that I am not proposing that we never build tables. Instead, let’s understand their benefits and shortcomings so we can be thoughtful about when to use them and how to design good data tables.

Click through for some interesting thoughts around a sometimes-forgotten visual.

Comments closed

Visualizing Seinfeld

Published 2020-09-28 by Kevin Feasel

Andy Kirk walks through a brand new book:

This project is a compilation of visual analysis I’ve been working on for a couple of years to pursue a curiosity I had about the rhythm and texture of Seinfeld, looking into how it was written and how it was produced.

The musicality of language is something defines the signature comedic talent of Jerry Seinfeld and, co-creator, Larry David. Like music, situation comedy has rhythm and it has texture. The rhythm is shaped by its temporal structures and pacing; textures are formed from the arrangements of characters and locations which amalgamate into the comedy. All orchestrated by the storylines and dialogue emerging from the writers.

It is an exploration of the show’s musicality that forms the purpose of this book. It presents a portfolio of visuals produced, intended for people who share my Seinfeld fanaticism. It is a body of work akin to giving the show something of a sports analytics treatment, investigating how Seinfeld and David utilised their resources – the people, places, and the dialogue – to translate their creativity from the page and onto our screens.

It’s an interesting look at the show and there will be a limited run on physical copies of the book.

Comments closed

Finding Unused SQL Server Tables

Published 2020-09-28 by Kevin Feasel

Nisarg Upadhyay shares a few techniques to look for whether a table is currently in use:

In this article, I am going to demonstrate how we can identify the unused tables of a given SQL Database. As a database administrator, we must maintain the tables and objects of the SQL Database. In my organization, when we add a new column or change the data type of a column, we export the data of the existing table in the backup table. Often, we forgot to review and maintain those backup tables. So as a solution, we decided to create a SQL Job that populates the list of unused tables and email the list to the DBA Team for review.

The best way to decommission any table is to rename it first, and if it does not break the functionality of the application, we can drop the table and its dependencies. We decided that after a review of the unused table completes; we will rename the tables and later drop them.

Click through for the techniques and a script which checks tables. It does look fairly reasonable, with the concern being if you restart the SQL Server instance or look at a table which is only accessed at a particular time of year.

Comments closed

Using Stopwords and Stoplists with Full-Text Search

Published 2020-09-28 by Kevin Feasel

Haroon Ashraf walks us through stoplists and stopwords in SQL Server Full-Text Search:

First, let’s clarify the essence of Stopwords and Stoplist. Then we’ll proceed to use them to improve Full-Text Search.

A Stoplist

A stoplist, as the name implies, is a list of stopwords. When associated with Full-Text Search, the Stoplist can filter out meaningless words or terms, thus improving search results.

A Stopword

A stopword is a word that has a minor role in Full-Text Search, despite being important grammatically. Therefore, a stopword is not essential from the Full-Text Search perspective.

According to Microsoft documentation, a stopword can be a word with some meaning in a specific language, or it may be some token with no linguistic value. In both cases, it is useless for the Full-Text Search.

Read on to see examples and how to build your own stoplists.

Comments closed

Finding Loops in an Active Directory Hierarchy

Published 2020-09-28 by Kevin Feasel

Jana Sattainathan builds a recursive solution to an interesting problem:

Here is the example he gave me:

I am looking for a way to detect recursion loops in a specified AD Group.  Take this example of groups and members. I started with Group1, and found Group2, with group member Group2. Group2 has a group member Group3. Enumerating Group3 finds Group1, and loop!

He also said: Some groups are nested many levels down, and a single top level group can have more than one recursive loop. The script should be able to not only identify Group1>Group3>Group1, but also Group1>Group8>Group5>Group15>Group1.

This is a good use case for recursion.

Comments closed

Public Preview of SQL Server on Azure Arc

Published 2020-09-28 by Kevin Feasel

Sasha Nosov gives us an update on Azure Arc:

The preview includes the following features:

– Use Azure Portal to register and track the global inventory of your SQL instances across different hosting infrastructures. You can register an individual SQL instance or register a set of servers at scale using the same auto-generated script.

– Use Azure Security Center to produce a comprehensive report of vulnerabilities in SQL servers and get advanced, real time security alerts for threats to SQL servers and the OS.

– Investigate threats in SQL Servers using Azure Sentinel 

– Periodically check the health of the SQL Server configurations and provide comprehensive reports and remediation recommendations using the power of Azure Log analytics.

Click through for more information and documentation.

Comments closed