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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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 suit says Rankwave used data for ads, ignored cease-and-desist letter.

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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.

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.

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.

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.

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.

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

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.