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

Top things every SQL search tool should have

Object search command from ApexSQL main menu in SSMS

Ask yourself one question, why do we need a SQL search tool? You might not think of it as a big deal, but imagine not having a web search engine. How would anyone surf the web, right? Having the ability to find information quickly is a must-have in today’s world because it makes our lives easier.

July 24, 2020

How to get SQL Server instance information

SQL instance information divided in two categories

As known, the Microsoft SQL Server is a relational database management system with the function of storing and retrieving data requested by other corresponding software applications.

July 23, 2020

How to export MySQL data to JSON

JSON_OBJECT function

This article explains different ways to export MySQL data to JSON file format. JSON stands for JavaScript Object Notation. It is a lightweight file format for storing and transferring data, commonly used when data is sent from a server to a web page.

July 23, 2020

Exporting MySQL data to CSV file format using different methods

ApexSQL Database Power Tools for VS Code extension

In this article, four methods will be shown on how to export MySQL data to a CSV file. The first method will explain the exporting process by using the SELECT INTO … OUTFILE statement. Next, the CSV Engine will be used to achieve the same. After that, the mysqldump client utility will be used and in the end, the Export to CSV feature from the ApexSQL Database Power Tools for VS Code extension will be used to export MySQL data.

July 10, 2020

Top critical events in any SQL Server audit strategy

Auditing configuration

Building sound SQL Server audit strategy is as important as establishing database security in your organization. Database security has always been pitched battle against the database usability and accessibility on the other side. Establishing database security is a complex and paradox endeavor asking to follow various security technologies and practices. The less vulnerable database, the more it is difficult to use and access it, and the question is, how to build the balance between the two?

June 26, 2020

How to create custom health checks in SQL Server instances

In various scenarios, DBAs need to write T-SQL queries to check numerous states of their respected SQL Server instances. These queries can be run separately or with a fairly large amount of time invested in optimizing and modifying each one to run as a single power-query that will check a variety of conditions. On top of the time invested in creating such a script, additional time will be required to modify it and add new checks which can cause the whole script to break.

June 18, 2020

How to perform a high-level analysis of remote SQL Server instances

Health check result summary

To a proactive DBA, it might be of crucial importance to have its SQL Server instances regularly checked for possible issues as there certainly are the multiple benefits to be gathered from this practice. At the long run and when not addressed timely, these issues might cause a major disaster, slowing or stoppage of production SQL Servers causing an enormous headache to everyone involved, and not to mention the cost of having the production environment down for a few hours or, god forbid, days.

June 10, 2020

How to ensure continuous auditing and reporting in SQL Server

ApexSQL Audit scheduled reports summary

Challenge

Organizations are loaded with regulation requirements to audit and report on data access, data changes, login attempts, malicious and inadvertent activity to easier raise awareness on any anomaly behavior, and repeatedly improve controls while eliminating any potential risk-related consequences.

June 1, 2020

How to search for column names in SQL Server

Object search command

Introduction

This article explains the easiest way to search for a column name in SQL Server. This sounds like a simple task on its own and it is, but there are several solutions for this task and the purpose of this write-up is to show the most efficient way of completing it.

June 1, 2020

How to export MySQL data to CSV

ApexSQL Database Power Tools for VS Code Export to CSV option

Many database developers want to export MySQL data that they got as the results of an executed query to a CSV, XML, JSON, or HTML file.

May 21, 2020

How to perform non-database automated tasks in a database continuous integration pipeline

Importing PowerShell script in the Custom step

Challenge

In the DevOps world, it is a common practice to set up database continuous integration solutions for project deliveries, with as many steps included in a single workflow as possible. For the SQL Server project types, most of the database continuous integration and continuous delivery use cases are covered with ApexSQL DevOps toolkit solutions, including both standalone and continuous integration servers plugins. Multiple tasks, including building, testing, and deploying SQL database code, can be done through a single pipeline, but the question remains if there is a possibility to perform some non-database project-related tasks within the same pipeline, i.e., without the need for additional intervention.

May 18, 2020

How to protect sensitive data in SQL Server

ApexSQL Audit auditing report Data access report Data change report

Protecting business data is a relentless task and brings various consequences when mishandled. True costs on security breaches may result in regulatory fines – that are amplified over time, which may result due to non-compliance and also customer confidence. In the process of protecting “something”, the key component is knowing what is that “something” before protecting it, so what is sensitive data after all?

May 15, 2020

How to create a database auditing trail

ApexSQL Audit auditing report preview

Database auditing has become essential as the compliance regulations requirements and fines have amplified. It is important to address the questions not only on who and when accessed the data in your system or how the data has been changed but also how to prevent data breaches from inappropriate actions performed by different users.

May 6, 2020

How to ensure tamper-proof auditing in SQL Server

Tamper proof auditing - internal checks

Inability to ensure tamper-proof auditing is a major flaw of many SQL Server auditing solutions available on the market. While there are many solutions and different tools that utilize different mechanics to provide the information on who saw or changed what, when and how, most of Microsoft’s own, as well as the majority of third-party solutions do not provide any mechanisms or features to protect the audited data or to achieve tamper-proof auditing. This immediately raises a red flag because many internal, as well as external auditors, make it a mandatory requirement for auditing solutions to be able to provide certain levels of data protection for the audited data as well as a self-auditing mechanism for the solution itself, which will ensure that the auditing reports for auditors are 100% genuine and that audited data can be identified as true, untampered and uncorrupt.

April 24, 2020

How to compare SQL Server database schemas

Results of comparing SQL Server database schemas

Scenario

When working in a development environment, developers often use Visual Studio for coding, and it also has an option to connect to SQL Server and open a query window in which they can work on creating new objects or updating existing objects directly on their local Dev database, which further requires a way to compare SQL Server database schemas with the QA database.

April 9, 2020

How to monitor the status of index defragmentation jobs

ApexSQL Defrag Servers actions

SQL Server Index is one of the most important factors in the SQL Server performance tuning field that is used to enhance the queries’ performance by speeding up the data retrieval process. It is a double-edged sword that can enhance the database performance when designing it properly and performing the index defragmentation maintenance periodically, and degrade the database performance when it is not designed or maintained well.

April 7, 2020

Top features you need in a SQL Server transaction log reading solution

Audited DML and DDL operations

Challenge

While a plethora of information on changes made to a database is recorded in transaction log files, SQL Server transaction log reading has always been a big challenge for all SQL Server users since Microsoft never offered a built-in solution which would allow users to immerse themselves and explore the depths of transaction log files. Furthermore, lack of in-house features that would help understand, process, or analyze information within the transaction log files practically made it mandatory for users to turn towards 3rd party solutions.

April 3, 2020