How to track data changes using database auditing techniques

ApexSQL Audit - before after data changes report

Tracking data changes helps us to understand moving parts in a database, learn about changing trends, easily recover from unwanted data changes or data loss, and adds more context to the auditing documentation. In this article, we will introduce ways of tracking database changes by exploring some out of many database auditing mechanisms in the SQL Server ecosystem.

November 27, 2020

How to automatically revert database changes based on SQL auditing alerts using SQL Server transaction logs

Generating CLI script for generating Undo script from SQL Server transaction log

One of the problems that can be solved with SQL auditing technologies in conjunction with SQL Server transaction logs is finding specific events, i.e. specific database or data change, capturing the event, and resolving it as wanted or unwanted. Depending on methodology and tools this can be an easy or complicated task. The ApexSQL Audit tool provides the solution wherein a few easy steps a specific event can be audited, with high granularity and precision. The additional problem surfaces out in case unwanted change occurs, how to easily revert those changes? The usual methodology consists of creating SQL Server transaction log backups and restoring them. But restoring implies reverting the database to a specific moment in time, thus reverting even wanted changes.

November 17, 2020

How to collect information about SQL audits and database mails using SQL documentation

Database Mail object type

Manual replication of a SQL Server is still nothing out of the ordinary, so keeping some SQL documentation about the ‘original’ SQL Server is recommended to use as a reference. SQL Documentation can come in handy especially when server level objects have to be reconstructed manually on the new instance, for example, SQL audit settings or database mails. Having the T-SQL scripts also with this documentation for recreating those objects will certainly simplify the actions needed for the task.

November 17, 2020

How to search for SQL Server data

Database text search pop-up window

In day-to-day work, DBAs and developers often have to search SQL Server data especially tables that contain specific data. In SQL Server Management Studio (SSMS), or Visual Studio, there is no simple solution to performing such tasks.

November 16, 2020

How to mask SQL Server data using Dynamic data masking

The main grid of ApexSQL Mask, in which will be selecting columns to mask SQL Server data

In the world of today, information can be classified into sensitive and non-sensitive data. The sensitive data is classified as data that contains private, personally identifiable, financial, and security information, whose discovery can be misused and damage the reputation of a person or an organization.

November 2, 2020

Different methods to search SQL Server data

Text search command from ApexSQL main menu

One of the most basic and repetitive operations in the SQL world is to search SQL Server data. Usually used to locate specific row items, sometimes for exporting purposes, or check if dependent values are properly populated.

October 22, 2020

GDPR survival kit for SQL Server DBAs – SQL Server auditing and best practices

SQL Server Audit report

In the current digital age, security controls and data privacy and protection has been an influential factor in how organizations over the globe store and protect business data, especially since the EU General Data Protection Regulation (GDPR) establishes a new bar for compliance, security, and privacy rights of the individuals.

October 2, 2020

How to perform a custom SQL Server security health check analysis

Results of Acceptable Analysis Services Login Account rule execution

The overall security of SQL Server instances is a broad topic. Security breaches happen often, and, in most cases, they are resolved quickly, and the level of security is tightened, but the best and most desirable scenario is to avoid them entirely. This can be done by taking proactive actions in analyzing possible security risks and patching them before the least desirable scenario happens. With SQL Server environments being dynamic, there is an almost constant need for better security and management of SQL Server instances.

September 25, 2020

How to perform a health check analysis of SQL Server accounts

Result summary in ApexSQL Manage

When it comes to using the SQL Server instance, a DBA must ensure no one unauthorized, or outside of the organization, should have any privileges that will allow him to change any SQL Server configuration. Preventing this scenario will provide an additional layer of security that will result in decreasing the level of SQL Server’s vulnerability and its exposure to malicious attacks.

September 25, 2020

How to export MySQL data to HTML

The Object search pane

Many developers, during their work, are required from their superiors to export MySQL data to various file formats like Excel, CSV, JSON, or HTML file.

August 28, 2020

Top things you need in a SQL Server coding productivity tool

Executed queries option in SSMS

SQL complete is one of the best time-saving tools for any DBA who works on database objects and data stored inside these objects using T-SQL. It’s in the job description to be very familiar with T-SQL both from a DDL and DML perspective. Data Definition Language requires writing statements like CREATE, ALTER, DROP, etc. and Data Manipulation Language applies to commands like SELECT, INSERT, UPDATE, etc.

August 26, 2020

How to perform a SQL Server security health check analysis

Health check result summary statistics

Maintaining the security of dedicated SQL Server instances and its environment is the most important duty of any DBA. Various problems, including the unwanted external breaches, may arise that could heavily impact the safety of a SQL instance and could potentially harm the company as well.

August 20, 2020

How to perform a SQL Server health check analysis of the TempDB

Configuring TempDB data files location

TempDB is a section of SQL Server instance that is used to store temporary data of tables, views, objects, functions, and indexes and is often overlooked in the grand scheme of performance optimization; hence it is left on default by DBAs. It is not an overly complicated area, but in everyday DBA tasks, this area gets a little less attention. Leaving the TempDB configuration on default will not result in a production environment crash, but it will take a toll on the overall SQL Server performance and, in worse case scenarios, jeopardize contingency plans in disaster recovery scenarios. With a little optimization management and customization in key areas, the aforementioned scenarios can be averted, and the performance of a SQL Server instance increased to mitigate the toll.

August 20, 2020

How to export/import MySQL data to Excel

Imported MySQL data to Excel

One of the main aspects of modern business is gathering and sharing information with others.

In this article, several ways to export/import MySQL data to an Excel file will be explained. The first method will show the exporting process when the SELECT INTO … OUTFILE statement is used. After that, the Excel features for export/import MySQL data, as well as MySQL add-in for Excel, will be explained, and in the end, the Export to Excel option from the ApexSQL Database Power Tools for VS Code extension will be shown.

August 12, 2020

Executing a database health check analysis of SQL Server instances

Best practice for SQL Server database collation

Daily obligations in a DBA life can be time-consuming, depending on the type of tasks needed to maintain a SQL Server. Problems that can occur range from SQL Server instance configuration to the actual databases it is facilitating. Allowing these issues to happen and persist can interfere with the overall performance of an environment and take away much needed time that can be spent differently.

August 6, 2020