How to implement error handling in SQL Server

June 15, 2018 by

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 […]

How to handle the SQL Server WRITELOG wait type

June 13, 2018 by

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 […]

The Difference between CROSS APPLY and OUTER APPLY in SQL Server

June 6, 2018 by

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

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 […]

What is SQL Data Sync

June 1, 2018 by

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 […]

Maintaining SQL Server indexes

May 31, 2018 by

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 […]

Gathering SQL Server indexes statistics and usage information

May 24, 2018 by

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 […]

Securing access for SQL Server auditing

May 21, 2018 by

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 […]

How to perform a page level restore in SQL Server

May 18, 2018 by

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 […]

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

May 17, 2018 by

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 HashBytes function in T-SQL

May 16, 2018 by

One of the paramount ways to guard data within a database is to utilize database encryption. However, no one encryption solution is perfect for all databases. Which encryption solution you select totally depends on the requirements of your application. Note that more powerful encryption for larger amounts of data requires a healthy amount of CPU. So, […]

Logging SQL Server database errors

May 15, 2018 by

We receive many database alerts with many of the alerts logging some of these same alerts or information to files or tables. What we’ve found over time is that the logging is now costing us quite a bit of resource. Our logging server (where both files and table logging are stored) has had a few […]

Smart database backups in SQL Server 2017

May 14, 2018 by

So far, we’ve discussed several phases of backup that starts with planning, creating, strategizing and implementing. In this article, we are going to see how database administrators can define the strategy to improve backup performance and efficiently manage backups in SQL Server 2017. The following are the topics of discussion: Discuss checkpoints Discuss the enhancements […]

Working with different SQL Server indexes types

May 8, 2018 by

In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of both SQL Server tables and indexes, the main guidelines that you can follow to design a proper index, the list of operations that can be performed on the SQL Server indexes, and finally how to […]

Designing effective SQL Server non-clustered indexes

May 7, 2018 by

In the previous articles of this series (see below for the full index of articles), we went through the internal structure of SQL Server tables and indexes, listed a number of guidelines that help in designing a proper index, discussed the operations that can be performed on SQL Server indexes and finally showed how to […]