Press "Enter" to skip to content

Category: Administration

SSIS Framework Manager Community Edition

Published 2021-01-27 by Kevin Feasel

Andy Leonard has a new product announcement:

I’m excited to announce SSIS Framework Manager CE (Community Edition) is available for download at DILM Suite! SSIS Framework Manager CE is designed to support SSIS Framework Community Edition, providing a GUI to facilitate SSIS Framework Application creation, configuration, and management.

Three views are supported in this initial edition: Catalog, Application, and Package. The Catalog view incorporates the same Catalog treeview used in SSIS Catalog Browser (also free) and SSIS Catalog Compare (not free):

Click through to see what’s included.

Comments closed

Using the Model Database

Published 2021-01-27 by Kevin Feasel

Garry Bargsley explains the utility of the model database:

Below I will outline the model database, which has a unique purpose for SQL Server.

The model database is an empty shell of a database that has the sole purpose of providing a database template for any new User Database created in SQL Server. With that being said, you can make configuration changes to the model database and then expect those baseline settings to be applied to all new user databases.

Read on to see some of the things you can set. I was a bit disappointed in some of the things which model doesn’t apply across to other databases, but it does carry over quite a bit.

Comments closed

App Locks and Read Committed Snapshot Isolation

Published 2021-01-27 by Kevin Feasel

Michael J Swart has a tip for those who have RCSI turned on and are using app locks:

The procedure sp_getapplock is a system stored procedure that can be helpful when developing SQL for concurrency. It takes a lock on an imaginary resource and it can be used to avoid race conditions.

But I don’t use sp_getapplock a lot. I almost always depend on SQL Server’s normal locking of resources (like tables, indexes, rows etc…). But I might consider it for complicated situations (like managing sort order in a hierarchy using a table with many different indexes).

Click through to see how it normally works and how you should switch things up if you’re using Read Committed Snapshot Isolation.

Comments closed

Change Tracking vs Change Data Capture with Partitioning

Published 2021-01-25 by Kevin Feasel

Erik Darling explains a difference:

One thing I’ve seen people run into is that these two technologies have very different relationships with Partitioning.

Now, I know this isn’t going to be the most common scenario, but often when you find people doing rocket surgeon stuff like tracking data changes, there are lots of other features creeping around.

Click through to see which one works better.

Comments closed

Memory Grant Internals

Published 2021-01-25 by Kevin Feasel

Deepthi Goguri starts a series on memory grant internals:

SQL Server have different types of components (Plan Cache, Buffer pool and other memory components) in the memory area and requires memory for many things. We also require memory to process our queries. Each operator in the execution plan requires memory to process the rows from one operator to the other operator. Some of the iterators need to be buffered as the rows come into them before sending them to the next iterator in the execution plan. These iterators needs more memory. These are called memory consuming iterators. The grants that we given to these memory consuming iterators are called Query memory grants.

Click through for more.

Comments closed

Copying a Database with dbatools

Published 2021-01-21 by Kevin Feasel

Jess Pomfret shows how we can copy a database using dbatools:

We’re working hard on the AdventureWorks2017 database, perhaps getting it ready for an upgrade – since it’s now 3+ years out of date.

dbatools has so many functions, and I know I’ve mentioned it before, but Find-DbaCommand is a great way of looking for what we need. I want to know what the default backup path is set to, and since I’m just backing up and restoring to the same server, we already know that the instance has the required permissions here. If only there was an easy button for this…

Spoiler alert: there is.

Comments closed

Enabling Instant File Initialization After Installation

Published 2021-01-21 by Kevin Feasel

Chad Callihan takes us through the benefits of Instant File Initialization, as well as how to enable it after you’ve installed SQL Server:

Instant File Initialization is especially helpful when large file growths are occurring. Without Instant File Initialization, SQL Server has to write zeros to the disk in order to initialize it before SQL Server can use it for new data. As with autogrowth, the amount of time this process takes depends on the size. The more disk space that is needed, the longer it’s going to take for the disk to be zeroed for use. Fortunately, IFI exists to skip the need to write out zeroes. Instead, disk space can immediately be used as needed.

IFI can also make a noticeable difference when restoring databases. If you’re working on getting your recovery time objective (RTO) down, check the status of IFI. Enabling IFI may help cut that number down to an acceptable value.

This is particularly interesting because, for the most part, I’ve only seen posts showing how to enable it at installation time.

Comments closed

DTUs and vCores in Azure SQL Database

Published 2021-01-20 by Kevin Feasel

Arun Sirpal gives us one take on whether to use DTUs or vCores for Azure SQL Database:

If you are building database solutions in Azure , using Azure SQL Database then you will know that you have a purchasing option decision to make. That being should you use a vCore model or DTU approach?

Arun prefers the vCore model for solid reasons. I agree with the sentiment when we’re talking about production databases. For small-scale, personal stuff, however, the bottom end of the DTU model is much less expensive.

Comments closed

Auto-Pausing Dedicated SQL Pools in Azure Synapse Analytics

Published 2021-01-18 by Kevin Feasel

Fonseca Sergio automates an important cost-saving measure when working with Azure Synapse Analytics dedicated SQL pools:

As Synapse engineer or Synapse Support Engineer you may need to start and test some Pools, and you want this to be the most cost efficient possible. Leaving some Synapse with a lot of DWU left turned on during the weekend because you forget to pause the DW after you shutdown your computers is not a good approach and we can quickly resolve this by using Powershell + Automation accounts.

This is also a good introduction to Azure Automation if you aren’t familiar with it.

Comments closed