Problems displaying this newsletter? View online.
Database Weekly
The Complete Weekly Roundup of SQL Server News
Hand-picked content to sharpen your professional edge

Why Tools Can Help You Do Your Job

Back in my days as a SQL server DBA, I didn't have many third-party tools at my disposal. I remember having a monitoring tool with licenses for only three or four SQL server instances. Whenever there was a problem with one instance that wasn’t currently being monitored, I would have to stop monitoring another instance and switch the license over. This was perfectly fine according to the license agreement, however, it caused me to lose history and, of course, was not very efficient at all while I sometimes had to wait to see the problem happen again.

I was allowed to purchase a backup tool for all my 40 or 50 SQL Server instances. I proved to my manager that this backup tool would save SAN space because of the built-in compression (before backup compression was included with SQL Server) and pay for itself in no time. That was a great investment which made my job easier.

DBAs can spend a lot of time writing scripts, and I was no exception. Many of these scripts were specific to the job and workload, but others could have been replaced by free or purchased tools. I remember writing a script to compare the data in tables between two databases in preparation of an upgrade. I’m not sure I knew that there was a tool available that could have been purchased to automate the work for me instead, but I bet that it would have been less expensive in the end compared to how much time I spent.

I’ve recently had discussions with some friends about tools for maintaining indexes. Many of us started out by writing our own index maintenance scripts, modifying them whenever we needed to add some new options. The trick is keeping the scripts organized and synchronized across all the instances. And, of course, so much time was spent writing and testing. Luckily, there is a free tool for optimizing indexes (and other maintenance) written and maintained by Ola Hallengren (@olahallengren) that most of us use now that has just about every option you could ever need.

As DBAs become more involved with areas like DevOps and hybrid data centers, they will need even more tools for tasks such as source control and provisioning. They may be tempted to write scripts to do everything, but the right tools can do a better job in many cases. Even though managers may balk at the expense, tools will save time and money and allow the DBAs to focus their efforts where their expertise is really needed.


Kathi Kellenberger

Join the debate, and respond to the editorial on the forums

The Weekly News
All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit.
Artificial Intelligence (AI)

The Future of Artificial Intelligence for Small Businesses

Many businesses are now turning towards the buzz of ‘artificial intelligence’ (AI) as they strive harder to put their specific needs...


4 steps to standardize your database development processes

Adopting standardized processes in database development can free up the time of development teams for innovation and value adding work. Find out the 4 steps you can take to lay the foundations for standardized development in your team and pave the way for full automation.

Azure Cosmos DB

SQL For Cosmos DB – Handling Complex JSON Structures

The JSON documents that are part of Cosmos DB document collections can be complex with arrays and nesting. In this article, Adam Aspin shows you how to query them...

Azure Data Studio

Azure Data Studio May Release

Alan Yu announces the May release of Azure Data Studio: Since its release two months ago, the community continues to love SQL Notebooks. This month, we had a laser-eyed...

Azure SQL

Moving to Serverless for Azure SQL DB

After the announcement of Serveless Azure SQL Datbase at //build/, I decided to give it a try. I have some Basic databases, so what would Serverless mean for me?... The...

Azure SQL Database Serverless

Quite a significant change has taken place within the Azure SQL Database space, more specifically the development of Azure SQL Database Serverless. Currently in preview mode this “compute” tier changes...

Azure SQL Managed Instance

Memory Pressure and Azure SQL Managed Instances

Jovan Popovic takes us through determining whether we have enough memory on an Azure SQL Managed Instance: Managed Instance has memory that is proportional to the number of cores....


Short Cuts to Biml Mastery

I’ve been giving Biml related presentations for many years, and shared what I believe are the most efficient ways to learn it. It’s possible to get enough Biml knowledge...

Backup and restore

Using Extended Events To Capture Backup and Restore Progress

If you are running a DATABASE BACKUP or RESTORE command manually, SQL Server will show you the progress at a specified % completion interval. For the BACKUP, the default is... The...

CDC Change Data Capture

Four Methods of Change Data Capture

As its name suggests, Change Data Capture (CDC) techniques are used to identify changes. CDC can be the basis to synchronize another system with...


Creating Your First SQL Server Docker Container in macOS

The use of macOS by developers and IT professionals has gained popularity the past few years. Running a sandboxed SQL Server, however, has been difficult. That changed when Microsoft...


Getting Prior Year’s Year-To-Date with DAX

Kasper de Jonge takes a look at how to calculate a prior year’s year-to-date over the same period as the current year: Well maybe.. what happens here is that...


DMV’s for the Beginner

I give performance presentations at many different events to all levels of SQL Server professionals. Over time I’ve noticed that some DBAs and developers have never looked at the... The...

Data Science

All Data is Dirty: Subjective Data, Likert Scales, and More

One of the things I like to point out in my Launching a Data Science Project talk is that all data is, by its nature, dirty. I figured I...

How to show characteristics of study population in R with a single line of code

Interested in guest posting? We would love to share your codes and ideas with our community. Category Data Management Tags Data Management Tips & Tricks Another advantage of R, in addition to visualization, is the...

Database Administration

SQL 2016 Won’t Sent Mail

One of the more challenging components to configure on SQL Server is SQL Mail. I don’t know how many times I have had to try it multiple times before...

Automating Log File Expansion

Max Vernon shows how you can automatically expand log files to optimize VLF counts: SQL Server Database Log file expansion can be fairly tedious if you need to make...

Database Performance

Should Optimistic Be The Default Isolation Level?

Two Words Alright, I’ve lied to you already. There are way more than two words involved, here. I recently had…

Queries with RECOMPILE and Query Store

  Last week in our IEPTO2 class I was asked about queries with RECOMPILE and Query Store. Specifically: Do queries that have the OPTION (RECOMIPLE) hint go into Query...

SQL Server 2017: here a NUMA, there a NUMA...

The first unexpected behavior is one that may already be familiar to many.  [Max server memory] is not as often a maximum for SQL Server [total] memory as it...

Redgate Database Devops
DevOps and Continuous Delivery (CI/CD)

Six reasons to version control your database

For application developers it is unthinkable to work without version control. Yet, only 55% of database developers are version controlling their database changes? Mary Robbins provides six reasons why you should version control your database.

DevOps Is About Communication

I spend a lot of time showing how to use tools to automate database deployments in support of DevOps. However, the one message that I always try to deliver...

Database Delivery with Docker and SQL Change Automation

Phil Factor demonstrates the basics of how to automate database builds into a Linux SQL Server container running on Windows, and then backup the containerized database and restore it...

Disaster Recovery (DR)

How Climate Change Impacts Disaster Recovery and Security

Severe weather events cause disruptions that could lead to security vulnerabilities.

Integration Services (SSIS)

SSIS Script Task Error

Has this ever happened to you? You’re tooling along building an SSIS package. You configure a Script Task, press the F5 key, and BAM! Error! DTS Script Task has...

Jupyter Notebook

Embedding Notebooks on a Website

Eduardo Pivaral shows how to embed the results of a Jupyter notebook created in Azure Data Studio on a website: Notebooks are a functionality available in Azure Data Studio, that...

Machine Learning

Automated Machine Learning: Just How Much?

An interview with three data scientists and guided automation experts There is currently a lot of talk about automated machine learning....

How AI and machine learning are improving customer experience

From data quality to personalization, to customer acquisition and retention, and beyond, AI and ML will shape the customer experience of the future. What can artificial intelligence (AI) and machine...

Microsoft News

Microsoft Reveals First Hardware Using Its New Compression Algorithm

The Corsica ASIC offloads compression and encryption to accelerate storage performance.


Big-Data Skills Earn High Pay For Today's College Graduates

Data scientist was the highest-paying entry-level job last year, according to Glassdoor research. Young adults in this field earned a median annual base salary of $95,000. That’s higher than...

Facebook sues app maker, says it made millions misusing Facebook user data

Facebook suit says Rankwave used data for ads, ignored cease-and-desist letter.


PolyBase Revealed: SQL Server to SQL Server

Today I’m going to cover something I’ve been waiting to do for just about three years: use PolyBase to connect one SQL Server instance to another SQL Server instance....

Power BI

Streamlining a Power BI Solution

Power BI is an excellent tool for departments or small organisations getting started with their BI solution. Unless some time is spent organising the solution, however, it can become...

Power BI: Use DAX to get relative dates

Patrick looks at how you can get relative dates, in Power BI Desktop, without using the relative date slicer or relative date filters. You just need to sprinkle a...

Basics of Modeling in Power BI: Fact Tables

Fact tables are the core of analysis in a data model. In the previous article, I explained what a dimension table is, and why we cannot have everything in...

Power BI Updates, tips and tricks and more… (May 13, 2019)



Scripting SQL Server objects with dbatools – Beyond default options

Probably you had the need to script out some objects from a SQL Server instance/database and this is quite easy. You just need to right click on the object... The...

Professional Development

How to recognize the early stages of burnout, and my treatment plan

I recently realized that I’m in the early stages of burnout. This isn’t an unfamiliar place for me, but it is new for me to recognize the early signs...

When Your Options Are Limited

In my role I am still on-call several times each month, and many of the escalations that come are simply things that newer DBA's and Help Desk staff have... The...

SQL Server security

SQL Vulnerability Assessment

SQL Vulnerability Assessment is a feature available in the latest versions of SQL Server Management Studio (SSMS). This feature is very easy to use and it will show you...


How to Keep Data, Devices Secure When Mobile Is the Norm

When "work" can happen in any place, security breaches can happen any time and any way.


Find identical duplicate indexes

The purpose of this query is to find identical duplicate non-clustered indexes i.e. indexes on same table with same columns in same order. If you have a need to... The...

How to delete a lot of data from a SQL Server database

How to delete data efficiently When we delete data from a table in SQL Server, we can often find that the delete is slower than even the original insert,... The...

Testing Maximum Rows in Table Value Constructors

Solomon Rutzky dives into how big a table value constructor can be in terms of rows: On 2019-05-08, a helpful individual, Michael B, commented on my answer saying that the...

Dropping Tables in Bulk

Let’s assume that you have lots of tables that need to be dropped according to some...

T-SQL Challenges

May 2019 T-SQL Tuesday #114: Puzzles

This month’s T-SQL Tuesday is hosted by Matthew McGiffen. His challenged is to come up with a puzzle.  My entry involves a simple request that is more difficult to...

Animating a Magic 8 Ball in SQL Server Management Studio

This post is a response to this month’s T-SQL Tuesday #114 prompt by Matthew McGiffen.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database... The...

T-SQL Tuesday #114 – An Unsolved SQL Puzzle

This week’s T-SQL Tuesday invitation is all about puzzles. I’ve got an accidental puzzle that I’ve never quite solved, from one of my demos. I’m sure the answer will...


Testing Databases: What’s Required?

Phil Factor reviews the various types of database test that need to run during development work, what sort of test data they require, and the challenges with managing this data, and in keeping the test cell stocked with the correct database, and data, in a way that allows rapid cycles of database testing.


A Clean Values Clause with SQL Prompt

I’ve had this code in a snippet for a long time: I appreciate the markup to prevent SQL Prompt from doing this, which used to always happen. I can’t... The...

Vendors/3rd-party Products

How to Provision a Set of Databases to Multiple Azure-based Servers

Grant Fritchey shows how to provision a group of interdependent databases, masked to protect sensitive or personal data, to each machine in an Azure-based test cell. The post How to...

The Sins of SELECT * (BP005)

If Prompt warns you of use of the asterisk, or 'star' (*), in SELECT statements, consider replacing it with an explicit column list. It will prevent unnecessary network load and query performance problems, and avoid problems if the column order changes, when inserting into a table.

SQL Clone for Unit Testing Databases

Phil Factor demonstrates how to use SQL Clone to create 'disposable' SQL Server databases, for development and testing work. You can spin up a clone, use it to unit test your code, messing up the clone in the process, then reset the clone in seconds, ready for the next test.

Monitoring your servers and databases with SCOM and SQL Monitor

SCOM is good at monitoring the status of your servers. SQL Monitor give you a detailed view of your SQL Server instances, and databases, right across your network, however they are hosted. By using tools appropriately, for the tasks they do well, you benefit from a simpler and more comprehensive overall strategy.


Global Data Privacy Tales: The GDPR Effect

Monday May 20 4-5 PM BST / 10-11AM Central - Description: Discover how data privacy legislation is changing around the world since the introduction of the GDPR one year ago.

Addressing Global Data Privacy at PASS

Tuesday May 21 4-5 PM BST / 10-11 AM Central - Hear from the IT team at PASS, how they introduced compliant database DevOps to meet advancing data protection legislation worldwide.


Modifying XML values

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...


Bloor 2019 Market Update on Test Data Management

Discover the latest market trends and assess your approach to test data management. For example, the Bloor analyst recognizes “…an increased emphasis on test data provisioning, as opposed to merely test data management.” The report also covers key capabilities offered by vendors in the market, including Redgate.

RSS FeedTwitter
This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the This newsletter was sent to you because you signed up at Note: This is not the daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -