Prashanth Jayaram

SQL interview questions on database backups, restores and recovery – Part II

July 25, 2018 by

In this article, we’ll walk through, some of the refined list of SQL Server backup-and-restore, (or recovery) interview Q&A. Also, it includes the high-level overview and links of the “stairway to backup-and-restore series” articles for detailed information. As you read through the lists of Q&A, you’ll learn most of the features and concepts of Backup-and-Restore operations.

Read more »
Timothy Smith

SQL Server – development practices with referenced views

July 24, 2018 by

We’ve recently had production failures because our developers changed an important reference. In this case, we had a view which several procedures and views referenced. A developer made a change to the referenced view by removing columns, which caused several procedures and a view that referenced it to fail. We’re considering whether we should stop this practice, or if there are other ways we can prevent changes to an object that’s being referenced by other objects (in our case, a view).

Read more »
Ahmad Yaseen

SQL Server Execution Plans types

July 23, 2018 by

In the previous article, we described, in detail, the different stages that a submitted SQL Server query goes through and how it processed by the SQL Server Relational Engine. The SQL Server Relational Engine generates the Execution Plan and the SQL Server Storage Engine performs the requested data retrieval or modification process. In this article, we will discuss the different types and formats for SQL Server Execution Plans.

Read more »
Prashanth Jayaram

Backup and Restore operations with SQL Server 2017 on Docker containers using Azure Data Studio

July 19, 2018 by

In this 18th article of the series, we will discuss the concepts of database backup-and-restore of SQL Server Docker containers using Azure Data Studio. Before proceeding, you need to have Docker engine installed and Azure Data Studio configured on your host machine.

This article covers the following topics:

  1. Overview of Azure Data Studio (ADS)
  2. How to use Azure Data Studio integrated terminal
  3. Definition of Docker containers
  4. Step by step instructions to initiate backup-and-restore of SQL Server 2017 Docker containers using the Azure Data Studio interface
  5. And more…
Read more »
Aamir Syed

Use cases for Query Store in SQL Server

July 18, 2018 by

Query store was introduced in SQL Server 2016. It is often referred to as a “flight data recorder” for SQL Server. Its main function is that it captures the history of executed queries as well as certain statistics and execution plans. Furthermore, the data is persistent, unlike the plan cache in which the information is cleared upon a server restart or reboot. You can customize, within Query Store, how much and how long the query store can hold the data.

Read more »
Robert Seles

Multiple methods for scheduling a SQL Server backup automatically

July 17, 2018 by

SQL Server backups are an essential part of every good disaster recovery strategy. That is good. But setting up such backups to run effortlessly, is the goal. In this article, we’ll review the types of backups, recommended practices and three different methods for automatically setting up SQL Server backups on a schedule. Note: these solutions can also be used in combination with one another

Read more »
Timothy Smith

Useful T-SQL techniques for development in SQL Server

July 17, 2018 by

When we’re developing solutions, we can sometimes forget useful commands we can use in T-SQL that make it convenient to remove data, eliminate objects, or carefully remove data. We look at three of these commands with a few examples of where we might consider using them in development, or in rare production cases. While they may offer us speed and convenience in some cases, we also look at some situations where they may not be the best tool to use.

Read more »
Ben Richardson

Identifying Object Dependencies in SQL Server Management Studio

July 16, 2018 by

In relational database systems, objects have different types of relationships with each other. Apart from table relationships (such as one to one, one to many and many to many), objects such as stored procedures, views, custom functions also have dependencies on other objects. It is important to understand object dependencies, particularly if you want to update an object that depends upon other objects.

Read more »
Brian Lockwood

Eating the frog: How to create a daily-deliverable sprint burn down graph in Excel

July 12, 2018 by

In the first article I wrote on how to create a SCRUM-based, sprint burn down in Excel, How to create an advanced sprint burn down chart in Excel, I showed how to easily set up a conventional burn-down with effort points as a percent, to create a pretty nice looking burn down chart, just by entering the IDs of each completed PBI on the date that it was finished.

Read more »
Brian Lockwood

How to create an advanced sprint burn down chart in Excel

July 10, 2018 by

SCRUM is a great way to manage all sorts of tasks including sprints, but you don’t have to purchase expensive software packages to burn one. Excel is a great tool for rolling your own sprint burn down graph or you can just take mine. In this article, I’ll walk you through a burn down spreadsheet I created, show you how to modify it and at the end you can download it for yourself and kick the tires.

Read more »
Dejan Sarka

Data science in SQL Server: Data analysis and transformation – grouping and aggregating data I

July 10, 2018 by

Previously, in this Data science series, I already tacitly did quite a few aggregations over the whole dataset and aggregations over groups of data. Of course, the vast majority of the readers here is familiar with the GROUP BY clause in the T-SQL SELECT statement and with the basic aggregate functions. Therefore, in this article, I want to show some advanced aggregation options in T-SQL and grouping in aggregations of data in an R or a Python data frame.

Read more »
Esat Erkec

Main Concepts of SELECT operators in SQL Server execution plans

July 9, 2018 by

One of the main responsibilities of a database administrator is query tuning and troubleshooting query performance. In this context, SQL Server offers several tools to assist. But among them, query execution plans are essential for query optimization because they include all of the vital information about the query execution process. At the same time as it provides this valuable information “under the hood”, SQL Server creates a graphical description of the execution plan. Read more »

Prashanth Jayaram

In-Memory Optimized database backup and restore in SQL Server

July 6, 2018 by

A SQL Server backup and restore strategy is an essential process to safeguard and protect critical data. The vital role of any DBA is to minimize the risk of data loss and preserve data modifications at regular intervals of time. A well-planned and well-tested backup-and-restore strategy always help to protect the organization with no data loss, or at least a minimum, from the many varieties of system and database failure. As such, it is recommended to understand the importance and implication of the backup-and-restore strategy.

Read more »
Nesha Maric

Various techniques to audit SQL Server databases

July 5, 2018 by

SQL Server auditing has gone from a nice to have to a legal requirement, especially following new legislation like HIPAA and GDPR. Organizations are now tasked with auditing access to records, reporting suspicious and potentially malicious activity, forensically auditing data changes, as well are tracking login attempts, security changes and much more.

Read more »
Brian Lockwood

Sharpen your ax

July 4, 2018 by

This is a popular story which illustrates the advantages of not just plowing away at your work, but improving your knowledge, planning, skills and preparation first to achieve a better outcome in the end

Once upon a time there were two men who lived in the same forest and decided to have a contest chopping wood.  The first man was in good physical shape and very muscular.  The second man was in good shape but smaller in statute and wiry.  They would chop wood all day and at the end of the day compare to see who had chopped the most wood.  The first man laughed to himself that there was no way this wiry little man would beat him and so they began the contest.  Every 45 minutes the second smaller man would take a break and seems to just wonder off somewhere.  The first man laughed again to himself and said “Yep there’s no way this wiry little man is going to beat me.”  This happens several times during the day.  At the end of the day the two men compare their piles of chopped wood and unbelievably enough the wiry little man has chopped twice as much wood as the more physically fit man.  He says “I don’t understand.  First I’m twice your size and twice your strength!  On top of that every 45 minutes you rolled off and took a break or a nap or something.  You must have cheated!”  The smaller man says “I didn’t cheat.  It was easy to beat you because every 45 minutes when you thought I was taking a break, I was out back sharpening my ax.” 1

Read more »
Ahmad Yaseen

SQL Server Execution Plans overview

July 4, 2018 by

In this series of articles, we will navigate the SQL Server Execution Plan ocean, starting from defining the concept of the Execution Plans, walking through the types, components and operators of Execution Plans analyze execution plans and we’ll finish with how to save and administrate the Execution Plans.

When you submit a T-SQL query, you tell the SQL Server Engine what you want, but without specifying how to do it for you. Between submitting the T-SQL query to the SQL Server Database Engine and returning the query result to the end user, the SQL Server Engine will perform four internal query processing operations, to convert the query into a format that can be used by the SQL Server Storage Engine easily use to retrieve the requested data, using the processes assigned to the SQL Engine from the Operating System to work on the submitted query.

Read more »
Nesha Maric

Reading the transaction log in SQL Server – from hacks to solutions

July 3, 2018 by

The SQL Server transaction log is akin to a ‘Black box’ in an airliner. It contains all of the records of transactions made against a database. This information is a proverbial goldmine for database audits, recoveries etc but it was never meant to be exposed to end users let alone visualized in an easy to read manner nor used for DBA tasks. As such, utilizing this information can be a challenge, to say the least.

Read more »