How to implement error handling in SQL Server

June 15, 2018 by Bojan Petrovic

Error handling overview

Error handling in SQL Server gives us control over the Transact-SQL code. For example, when things go wrong, we get a chance to do something about it and possibly make it right again. SQL Server error handling can be as simple as just logging that something happened, or it could be us trying to fix an error. It can even be translating the error in SQL language because we all know how technical SQL Server error messages could get making no sense and hard to understand. Luckily, we have a chance to translate those messages into something more meaningful to pass on to the users, developers, etc.

SQL Server Database Backup and Restore reports

June 14, 2018 by Prashanth Jayaram

In the previous articles, we discussed several ways of taking backup and testing the restore process to ensure the integrity of the backup file.

In this article, we’re going to discuss the importance of the database backup reporting. This kind of reporting will be designed to eliminate any unnecessary risk that may arise with the safety and security of information. This is the reason, the high-level report; Daily Health Check report will be generated and sent to the SME or to the DBA responsible for IT Infra management.

How to handle the SQL Server WRITELOG wait type

June 13, 2018 by Nikola Dimitrijevic

The WRITELOG wait type is one of those wait types that can often be seen quite frequently on SQL Server, and that can cause a lot of headaches for DBAs. The WRITELOG wait time represents the time that accumulates while waiting for the content of the transaction log cache to be flushed to the physical disk that stores the transaction log file. To understand better the WTITELOG wait type, there are some basics of SQL Server mechanism for storing the data in the transaction log file is to be explained first

How to “debug” a Linked Server from SQL Server to an Oracle Database instance

June 11, 2018 by Jefferson Elias

Introduction

Intended audience

This document is intended for database administrators who would like to be prepared or who experience performance issues with one or more Linked Server(s) to an Oracle Database instance using the OraOLEDb Linked Server provider.

Partial stored procedures in SQL Server

June 8, 2018 by Gerald Britton

Introduction

SQL is an old language — almost 50 years! The first commercially-available version of SQL was released by Oracle way back in 1969. In its specifications and general, “standard” appearance, it resembles the other leading language of the day, COBOL. Language theory and computer languages have evolved considerably since then. Modern concepts such as object-oriented programming and functional programming are not well-represented in SQL. That’s a pity. It can lead to copying code which almost always increases maintenance costs and code fragility.

The Difference between CROSS APPLY and OUTER APPLY in SQL Server

June 6, 2018 by Ben Richardson

SQL Server supports table valued functions, what are functions that return data in the form of tables.

JOIN operations in SQL Server are used to join two or more tables. However, JOIN operations cannot be used to join a table with the output of a table valued function.

APPLY operators are used for this purpose.

SQL Server performance counters (Batch Requests/sec or Transactions/sec): what to monitor and why

June 5, 2018 by Nikola Dimitrijevic

When maintaining SQL Server, it is essential to get an accurate perception of how busy it is. Two metrics that are often considered as indicators of how busy SQL Server is are Batch Requests/sec and Transaction/sec. When those metrics trend higher, they often affect all other metrics and make them go higher as well. While they could look similar, they are using a different type of starting point for measurement; the batches and transactions. So, to correctly understand those important metrics, lets first try to understand what the batches and transactions in SQL Server are and what are the differences between the two

How to Sync Azure SQL databases and on-premises databases with SQL Data Sync

June 4, 2018 by Marko Zivkovic

SQL Data Sync allows you to synchronize data across multiple Azure SQL databases and on-premises SQL Server databases.

In this article, how to synchronize Azure SQL database with on-premises SQL Server database will be shown.

Data understanding and preparation – basic work with datasets

June 4, 2018 by Dejan Sarka

In my previous four articles, I worked on a single variable of a dataset. I have shown example code in T-SQL, R, and Python languages. I always used the same dataset. Therefore, you might have gotten the impression that in R and in Python, you can operate on a dataset the same way like you operate on an SQL Server table. However, there is a big difference between an SQL Server table and Python or R data frame.

What is SQL Data Sync

June 1, 2018 by Marko Zivkovic

SQL Data Sync is a service that allows synchronizing data across multiple Azure SQL databases and on-premises SQL Server databases.

In this article, a base concept of how the SQL Data Sync service works will be explained as well as what the requirements and limitations are when want to create data synchronization by using SQL Data Sync

Tail-Log Backup and Restore in SQL Server

May 31, 2018 by Prashanth Jayaram

A tail-log backup is a special type of transaction log backup. In this type of backup, the log records that have not been backed up are captured. This prevents data loss and maintains the log chain sequence.

Maintaining SQL Server indexes

May 31, 2018 by Ahmad Yaseen

In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of SQL Server tables and indexes, the guidelines that you can follow in order to design a proper index, the list of operations that can be performed on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes, the different types of SQL Server indexes (above and beyond Clustered and Non-clustered indexes classification), how to tune the performance of the inefficient queries using different types of SQL Server Indexes and finally, how to gather statistical information about index structure and the index usage. In this article, the last article in this series, we will discuss how to benefit from the previously gathered index information in maintaining SQL Server indexes.

Query optimization techniques in SQL Server: the basics

May 30, 2018 by Ed Pollack

Description

Fixing and preventing performance problems is critical to the success of any application. We will use a variety of tools and best practices to provide a set of techniques that can be used to analyze and speed up any performance problem!

Real-time streaming in Power BI

May 29, 2018 by Esat Erkec

The concept of the IOT (Internet of Things) is that every object that you might think of is somehow accessed and connected to other devices on the internet. Nowadays every electronic device is now becoming smart and these devices are now able to communicate with each other. Many ideas and technology on IOT are being developed and are even being used today.

SQL Server Database Backup and Restore operations using the Cloud

May 29, 2018 by Prashanth Jayaram

This article discusses the concept of SQL Server backup and the various components required to use the Microsoft Azure Blob storage service as a target for the backup. As we all know, disk and tapes were the default destinations of choice until the evolution of the cloud platform. In addition to this, now we can expand the native functionality of SQL Server backup to cloud storage; the Windows Azure Blob storage. This functionality was added first in SQL Server 2012. In general, backup and restore functionality to and from the cloud are similar to using disk or tape, with very few differences.

Gathering SQL Server indexes statistics and usage information

May 24, 2018 by Ahmad Yaseen

In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of both the SQL Server tables and indexes, the best practices that you can follow in order to design a proper index, list of operations that you can perform on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes, the different types of SQL Server indexes, above and beyond Clustered and Non-clustered indexes classification and finally how to tune the performance of the bad queries using the different types of SQL Server Indexes. In this article, we will discuss how to gather statistical information about the index structure and the index usage information.

Connecting to Azure SQL Database

May 23, 2018 by Istvan Martinka

The below screenshots and code samples are valid at the time of writing (May 2018) but as things in Azure can change so quickly, please check the latest documentation if a code sample doesn’t work as expected!

Step by step installation of SQL Server 2017

May 23, 2018 by Daniel Calbimonte

Introduction

The new SQL Server 2017 comes with new features in the installation. It now supports Machine Learning Services that support R and Python. It also includes SSIS Scale Out Master and Scale Out Worker. It also includes scale out options in PolyBase.

Backup Linux SQL Server databases using PowerShell and Windows task scheduler

May 22, 2018 by Prashanth Jayaram

This article is an in-depth guide on how PowerShell can be used to maintain and manage SQL backup on Linux and Windows machines.

Here’s an outline of what this article is all about:

Introduction
Technical specifications
How to load SQL Server modules on Windows machine
Security – Credential Management
The objectives of Backup and Restore
Library Linkage
How SQL Server 2017 backup feature is used on Linux
And more …

Understanding benefits of Graph Databases over Relational Databases through Self-Joins in SQL Server

May 21, 2018 by Sifiso Ndlovu

Earlier this year, I published several articles on SQLShack with an aim of demonstrating tools available for visualising SQL Server 2017 graph databases. I was so caught up in the excitement of having SQL Server finally support graph databases that I forgot that some people still do not have a good grasp of how graph databases work let alone consider replacing their relational databases models in favour of graph. Although there are several ways that one can go about explaining the usefulness of graph databases over its relational counterpart, I have opted to focus on the benefits and strengths of graph databases by demonstrating the differences in which graph and relational databases deal with hierarchical datasets.

Securing access for SQL Server auditing

May 21, 2018 by Timothy Smith

Our organization must restrict permissions and prove to an independent party that we investigate access and restrict permissions. We restrict permissions to objects using least permissions and give full access to an exceptional few based on an organizational design that follows best practices. What can we do additionally that will help us prevent unauthorized access or catch when someone who shouldn’t have access is able to infiltrate our systems, especially in the context of showing a third party that we track this.

Tracing and tuning queries using SQL Server indexes

May 18, 2018 by Ahmad Yaseen

In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of the SQL Server tables and indexes, the best practices to follow when designing a proper index, the group of operations that you can perform on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes and finally the different types of SQL Server indexes, above and beyond Clustered and Non-clustered indexes classification. In this article, we will discuss how to tune the performance of the bad queries using SQL Server Indexes.

How to perform a page level restore in SQL Server

May 18, 2018 by Prashanth Jayaram

In this article in our stairway series on SQL Server backup and restore, we shall discuss the importance of understanding the internals of the page restoration for a database administrator. Most of the time, performing a page-level restore suffices for database availability. A good backup strategy is a key to recovery or restoration, and SQL Server provides us with an option to fix database pages at a granular level. Performing a page level restore in SQL Server is a clever option wherein the restoration of the entire VLDB database backup is not required.

Restricting and monitoring SQL Server data access with SQL views and stored procedures

May 17, 2018 by Timothy Smith

This article explains data security for accessing sensitive data and restricts access in application using SQL Views and stored procedures.

We recently faced a leak of information for one of our employees that involved data which caused a conflict within our company, even if it was not personally identifiable information (PII data). When we investigated the issue, we uncovered that we need to organize data access for our teams and review who has access to what information. As of right now, all our users either have access to all tables directly or a subset of all our tables. What are some practices we can use in SQL Server to avoid giving direct table access to our users? Read more »