Press "Enter" to skip to content

Category: Administration

Organizing a Power BI Workspace: The Checklist

Published 2021-10-15 by Kevin Feasel

Melissa Coates has a plan:

How to organize workspaces in Power BI is one of those topics that comes up a lot. On one hand, it’s really easy to quickly create a workspace and keep moving. At the same time, it’s also really useful to have a strategy for how you scope your workspaces so they don’t get out of hand over time.

In this post & video we’re going to cover 4 sets of criteria to consider when planning for workspaces in the Power BI Service.

Click through for the video, as well as a post with the details.

Comments closed

Generating Markdown from SQL Server Metadata

Published 2021-10-15 by Kevin Feasel

Thomas Williams has built a documentation engine:

Sometimes there’s a need to create database documentation – an old fashioned list of tables, columns, views etc. – as part of delivering a project, or for analysts, project managers and end users.

In these situations I tend to automate. SQL Server is very “meta” in that it contains tables and records that describe, well, other tables and records. Coupled with the MS_Description extended property, it’s a powerful way to keep up-to-date documentation in close proximity to the code itself.

The script below will generate Markdown for database tables, views, stored procedures and functions. Column names and data types are listed for tables. View definitions are output. For stored procedures and functions, only the MS_Description extended property will be output.

Click through for a big disclaimer and a bigger script.

Comments closed

Scheduling Tasks in SQL Server Express Edition

Published 2021-10-14 by Kevin Feasel

Steve Stedman & co don’t need your SQL Agents:

One of the feature differences between SQL Server Express Edition and the other paid versions is the ability to schedule jobs or tasks using the SQL Server Agent.

Even if you are running SQL Server Express Edition, there is a need to run scheduled jobs.

I’ve used this option and yes, it does work. But it’s definitely a few steps down from using SQL Agent jobs. Still, if that’s not available to you, this is a viable fallback option.

Comments closed

Best Practices for SQL Server on Physical Machines

Published 2021-10-14 by Kevin Feasel

Anthony Nocentino has some practices for us, the best practices:

The intent of this post is a quick reference guide based on the recommendation made on Pure Storage Support page in the Microsoft Platform Guide . The target audience for this blog post is for SQL Server DBAs introducing them to the most impactful configurations and settings for running SQL Server on physical machines on Pure Storage.

Click through for a checklist of recommendations.

Comments closed

Building a DBA Database

Published 2021-10-13 by Kevin Feasel

Andy Mallon wants a database all of his own:

I’ve written about my DBA Database quite a few times. Some of the code in that database is written recently, and some of it was originally written a decade ago. Regardless of how old it is, or how good of a DBA I was when I wrote it, these little scripts are life-savers, and they are always at my fingertips.

And Andy has made them available to your fingertips as well. And the rest of your hands, I suppose.

Comments closed

Performing a Restore to SQL Managed Instance

Published 2021-10-13 by Kevin Feasel

Arun Sirpal shows us how to perform a backup and restoration from an on-premises SQL Server to Azure SQL Managed Instance:

So in the last blog we confirmed that we could move to SQL MI via some analysis, this is now time to actually do a backup and restore via URLs to move data.

Quite simply you need to BACKUP to URL (Azure Storage container) and the setup requirement is that you need to create a SQL credential that holds the SAS token – this is what allows authentication to the container to take place. 

Click through for the process.

Comments closed

Scaling Out vs Scaling Up

Published 2021-10-12 by Kevin Feasel

Jordan Braiuka compares two models for scaling:

We often get questions from customers about the best way to add capacity to their cluster. Is it better to add nodes, or simply to increase the capacity in their nodes? Unfortunately, the truth is there is no best way—like all complex issues in distributed systems, there are benefits and drawbacks to each scaling approach. 

While each of our highly distributed systems (Apache Cassandra, Apache Kafka, etc.) have slightly different implementations of scaling, the concepts remain consistent across most distributed systems. 

Click through for a comparison between the two approaches. As the article indicates, both are meaningful strategies and your choice might come down to a combination of the technology stack and the problem at hand.

Comments closed

Testing sp_ineachdb

Published 2021-10-12 by Kevin Feasel

Aaron Bertrand takes us to the Island of Misfit Databases:

The only database that requires extra handling is the one that contains a tab, because SQL Server doesn’t know how to generate file names when that character is present. I am sure there are a bunch of other less common but equally exotic characters that may cause the same problem.

This is how I actually tested sp_ineachdb, to make sure it was ready for just about any bad idea anyone used to name a database, and could handle various possible database states (for a lot more background on this procedure, and why it is better than the undocumented, unsupported, and buggy sp_msforeachdb, see this and this). Here you can see that the procedure works against all these poorly-named databases, and skips databases that are inaccessible (rather than raise an exception).

Click through to see the list of databases Aaron uses. Technically, I think Aaron’s blog post also counts as a Halloween post because some of those databases are spooky.

Comments closed

Compatibility Mode and Upgrades

Published 2021-10-08 by Kevin Feasel

Tom Collins explains how database compatibility mode may be a way to reduce the risk of a SQL Server version upgrade:

Microsoft’s recommended SQL Server upgrade  workflow is to upgrade to the latest SQL Server but keep the source DB compatibility level, assess the workload impact via establishing a baseline and based on testing move the compatibility level  to the latest. 

Upon creation of a new user database – the create  database sets the compatibility level at the default compatibility level of the SQL Server. Keep in mind – if the model database level is set lower than the create database will set the compatibility level based on the model db

You can also change the database compatibility level at any time

Read on for more information about compatibility mode and some inbuilt guard rails around upgrades. Those guard rails aren’t perfect by any means, but over the past couple of editions, we’ve seen a fair amount of movement toward this ideal of compatibility mode being a guarantee of behavior between versions.

Comments closed