How to clone a SQL Server database from Prod to Dev safely

ApexSQL Mask - Manual mask 1

When working as a SQL Server database administrator, one of the common tasks that will be requested from you is to clone the SQL Server database from the production and restore it to the development environment. This process can be performed easily by taking a backup from the production database, copy it to the development server and restore it to the development SQL Server instance there. But this happy scenario is not valid in all situations. This is due to the fact that the production database contains important and critical information about the customers or the employees that should be kept securely and away from the non-authorized user’s hands.

October 25, 2019

Regulatory SQL Server compliance using data masking

ApexSQL Mask - Script File

Compliance and regulatory

In general, the SQL Server Compliance concept is used to describe the ability to conforming to the ordered instructions, policies or the rules that are internally or externally drawn.

October 16, 2019

Recommended methods for SQL database scripting

ApexSQL Script - database scripting execution

There are numerous reasons why exists the need for SQL database scripting, i.e. transform objects to their T-SQL form. One can say that it comes natural from a need to recreate some objects on another database, some objects are needed to be sent to developers for editing, or at least to keep the scripts as one form of database backup.

July 4, 2019

How to set email notifications for SQL Server Index defragmentation jobs

ApexSQl Defrag_Configure schedule for SQL server Index defrag policy

SQL Server index is used mainly to retrieve data from the database tables quickly, by seeking the requested records in the table index directly, rather than scanning the overall table records. The database transactional tables are changing very frequently, and these changes will be reflected in the related table indexes. Due to these changes, the index becomes fragmented over time, with its pages stored in an unordered manner.

June 13, 2019

Online vs offline SQL Server index rebuild in SQL Server

Instance added and information about SQL Server indexes listed

SQL Server Index is the key solution for most of the performance issues that the T-SQL queries suffer from. It can be used to speed up the data retrieval process from your database tables, by seeking for the requested data in the index itself, rather than scanning the overall table rows.

In this article, we will discuss the difference between rebuilding the index using Online and Offline modes.

June 10, 2019

How to configure fill factor for SQL Server indexes

Setting the fill-factor at the index level

SQL Server index can be considered as a double-edged sword. When the index is designed and maintained well, it can be used to speed up the data retrieval operations from the database tables, by providing swift access to the table rows, with no need to perform a full scan on the table’s data. On the other hand, the index may cause performance degradation on your system and slow down the data insertion and modification operations, when this index is badly designed.

May 29, 2019

SQL index fragmentation reports

Top 10 index fragmentation report at the DB level

The purpose behind creating SQL indexes on database tables is to enhance the performance of the queries that read data from the base table by speeding up the data retrieval processes. But these transactional tables are not static and changing very frequently over time. These changes that are performed on the database table include adding new records, modifying or deleting the existing records. As these changes should be replicated to the related table indexes, the table index will become fragmented over time.

May 20, 2019

How to Manage SQL Agent Job Schedules

Enable and Disable Schedule

The SQL Server Database Administrator job is not a boring or relaxing job, as you will face new challenges every day. Although you may be required to drink multiple cups of coffee during your working hours, you still have the chance to sleep your nights without nightmares and enjoy your holidays without carrying your laptop everywhere as your baby … but only if you can master your tasks and responsibilities to be able to perform them efficiently.

April 25, 2019

How to automatically pull data from a shared script folder to a SQL Server database

Synchronization of data from a shared SQL script folder to developer database

Challenge

In previous article, it was explained how to automatically compare and synchronize a shared SQL script folder with SQL Server database data, while in this article the solution for the reverse process will be explained, in case when data changes needs to be pulled from a shared SQL script folder to SQL Server database. Since a “poor man’s source control repository” is already set up and a SQL script folder already contains data changes commits from other developers, it’s time to set the reverse process in order to pull data changes from SQL script folder to a developer’s local database.

April 4, 2019

How to get SQL Server Agent job history

The lower part of the SQL Agent jobs history window contains detailed execution history for the selected job

SQL Server Agent is a Windows service that is widely used by database administrators and developers to automate the different types of critical and complex business and administrative tasks, to be executed based on a regular predefined schedule. It provides us with the ability to schedule an operating system CMD command, PowerShell script, SQL Server Analysis Services command or query, SQL Server Integration Services package or simply a T-SQL script.

March 22, 2019

SQL blocking chains in SQL Server

View the full T-SQL for the query that is part of the blocking chain

SQL blocking means that one or multiple SQL queries are waiting for resources that are locked by another SQL Query. SQL blocking can affect data throughput and SQL Server responsiveness significantly. SQL Server response time is a measure that shows how long the end user that has executed a query must wait until he gets a response. So, in case that a query is doing nothing while waiting on another query to release the lock on the target resource, the response time increases thus making the SQL Server less responsive.

March 4, 2019

Remote database synchronization using a database installer package

Create new or open existing remote database synchronization projects

This article will cover remote database synchronization using a database comparison and synchronization tool that will create a standalone database installer to execute a script, to complete the a synchronization and update between two remote database

March 4, 2019

SQL auditing tools overview

SQL Auditing tools overview - SQL Server Audit

Many SQL auditing tools and solutions are available to help DBAs achieve change-auditing and compliance goals. To achieve high performance auditing fit for a specific environment, it is important to consider all of these different SQL auditing tools which use different approaches, techniques and mechanisms to audit various SQL Server operations and events in order to choose and implement a most suitable one. In this article, we are going to look and compare 5 different SQL auditing tools which leverage different SQL Server mechanisms for auditing, including embedded auditing, transaction logs, database triggers, SQL traces and more. These are:

March 1, 2019

SQL Server Agent job alert configuration

SQL Agent job properties

As a proactive database administrator, one of your main tasks is monitoring different activities happening in your SQL Server instances continuously.

February 28, 2019