Press "Enter" to skip to content

Author: Kevin Feasel

Undercover Inspector 1.4

Published 2019-05-21 by Kevin Feasel

Adrian Buckman takes us through recent changes in Undercover Inspector:

#119 When the backups check module reports backup issues for a database but the issue is with a FULL or DIFF and the LOG is ok, we now show just the primary server in the Preferred replicas column as a FULL and DIFF only applies to the Primary – this reduces the number of warnings raised within the report as it will no longer report for all replica nodes if the AG backup preference is set to Prefer secondary or Secondary Only. See Git issue for more details.

Click through for the full change set.

Comments closed

Distributed Computing Fallacies

Published 2019-05-21 by Kevin Feasel

Samir Behara takes us through a few fallacies with distributed computing:

The network is reliable
Service calls made over the network might fail. There can be congestion in network or power failure impacting your systems. The request might reach the destination service but it might fail to send the response back to the primary service. The data might get corrupted or lost during transmission over the wire. While architecting distributed cloud applications, you should assume that these type of network failures will happen and design your applications for resiliency.

To handle this scenario, you should implement automatic retries in your code when such a network error occurs. Say one of your services is not able to establish a connection because of a network issue, you can implement retry logic to automatically re-establish the connection.

There are some very good points in here.

Comments closed

Finding Three-Part and Four-Part Names

Published 2019-05-21 by Kevin Feasel

Pamela Mooney shows how you can find three-part or four-part naming on a SQL Server instance:

The script below searches the metadata for views, sprocs and functions for occurrences of 3 and 4 part names.  Three-part names consist of databasename.schemaname.objectname, and four-part names consist of servername.databasename.schemaname.objectname. Because the code searches metadata, it isn’t always perfect.  If your comments mention a servername followed by a period, for example, it will be caught.  Nevertheless, it’s a great place to begin looking, and a real help in getting rid of problems before they really bite you.

Click through for the script.

Comments closed

Modifying XML in T-SQL

Published 2019-05-21 by Kevin Feasel

Max Vernon takes us through the .modify function:

Determining the property syntax when modifying XML values in SQL Server can be time consuming if you don’t work with XML regularly. SQL Server includes a very flexible XML subsystem, called XML_DML, or XML Data Manipulation Language. XML_DML can be used to easily and effectively update XML values in an xml-typed column or variable. This question on dba.stackexchange.comasked about using the .modify function to change the value of an element, which in turn prompted this post.

Read on for a number of examples.

Comments closed

Azure SQL Database Serverless

Published 2019-05-21 by Kevin Feasel

Arun Sirpal takes us through Azure SQL Database Serverless:

This is best used for those single databases that are ever changing with unpredictable patterns. With the concept of being billed per second (based on the vcores used) rather than per hour means that pricing can become more granular especially now with auto-pause becoming possible. The auto-pause delay defines the period of time the database must be inactive before it is automatically paused (only charged for storage). You should only use this if  you can afford some delay in compute warm-up after idle usage periods, otherwise it is best to stick with provisioned compute tiers ( classic tiers). 

I could see this being useful for dev or test databases, or maybe a personal site with heavy external caching.

Comments closed

dbatools 1.0 Forthcoming

Published 2019-05-21 by Kevin Feasel

Chrissy LeMaire announces that dbatools will be out on June 19th by my count:

We’ve got about 30 issues left to resolve which you can see and follow on our GitHub Projects page. If you’ve ever been interested in helping, now is the perfect time as we only have 30 more days left to reach our goal.

If you’re a current or past dbatools developer, we’d love any help we can get. Just hit up the GitHub Projects page to see what issues are left to resolve. If someone is already assigned, please reach out to them on Slack in the #dbatools-dev channel and see if they can use your help.

Read the whole thing and see if there’s anything you can do to help.

Comments closed

Apache Avro 1.9.0 Released

Published 2019-05-20 by Kevin Feasel

Fokko Driesprong announces the release of Apache Avro 1.9.0:

Avro is a remote procedure call and data serialization framework developed within Apache’s Hadoop project. It uses JSON for defining data types and protocols, and serializes data in a compact binary format. If you’re unfamiliar with Avro, I would highly recommend the explanation of Dennis Vriend at Binx.io about an introduction into Avro.

Over 272 Jira tickets have been resolved, and 844 PRs are included since 1.8.2. I’d like to point out several major changes.

That’s a lot of tickets.

Comments closed

Loading Data Into SnowflakeDB

Published 2019-05-20 by Kevin Feasel

Dan Bilsborough shows a couple ways of loading data into SnowflakeDB from Azure:

Before being loaded into a Snowflake table, the data can be optionally staged, which is essentially just a pointer to a location where the files are stored. There are different types of stages including:
– User stages, which each user will have by default
– Table stages, which each table will have by default
– Internal named stages, meaning staged within Snowflake

Internal named stages are the best option for regular data loads, if you are thinking along the lines of your standard daily ETL process. One benefit of these is the flexibility in that they are database objects, so you can grant privileges to roles to access these objects as you would expect. Alternatively, there are external stages, such as Azure Blob storage.

Read on to see what comes next.

Comments closed

Proposed Max Server Memory Defaults

Published 2019-05-20 by Kevin Feasel

Randolph West has a proposal for default max server memory on a SQL Server instance:

As noted in the previous post in this series, memory in SQL Server is generally divided between query plans in the plan cache, and data in the buffer pool (other uses for memory in SQL Server are listed later in this post).

The official documentation tells us:
[T]he default setting for max server memory is 2,147,483,647 megabytes (MB).

Look carefully at that number. It’s 2 billion megabytes. In other words, we might think of it as either 2 million gigabytes, 2,048 terabytes, or 2 petabytes.

Randolph is writing this like we don’t all have multiple petabytes of RAM on each machine.

Comments closed

Bash Script Introductions

Published 2019-05-20 by Kevin Feasel

Kellyn Pot’vin-Gorman continues a series on Bash scripting:

For Part II, we’ll start with the BASH script “introduction”.

The introduction in a BASH script should begin the same in all scripts.
1. Set the shell to be used for the script
2. Set the response to failure on any steps, (exit or ignore)
3. Add in a step for testing, but comment out or remove when in production

For our scripts, we’ll keep to the BASH format that is used by the template scripts, ensuring a repeatable and easy to identify introduction.

Click through to see what that entails.

Comments closed