Azure SQL Database Tools Part 1: ApexSQL

This is the first article from a series of articles that discusses a list of all of the prominent SQL tools provided by Azure.

20 days ago   •   4 min read

By Felix Schildorfer
Table of contents

Microsoft Azure has a robust set of SQL database tools to cover you for every operation you may need. These include fundamental tasks such as creating and managing databases or allowing you to automate database-related tasks, test data generators, and more.

What truly makes the Azure family user-friendly is its tools for the most intricate and basic operations. This is the first in a series of articles that will discuss a list of all prominent tools that Azure provides.

For example, ApexSQL is an all-encompassing DBA and developer platform with various tools that simplify end-to-end database management operations and it's supported by Microsoft Azure - we will now learn about it in more detail.

ApexSQL Analyze

In simple terms, ApexSQL Analyze helps you view, understand, and derive insights into the dependencies within a database. Rich with robust features, the tool offers dependency graphs, custom visualization, analysis of potential object deletions, dependency diagram exports, and viewing of object scripts.

With these and other features, any user can map down the dependencies to the last column. One can also understand what would happen if certain objects were deleted.

ApexSQL Build

ApexSQL Build allows you to build and deploy databases from multiple data sources in one operation. This saves time for users, who can also automate the entire process according to their needs with CLI support.

You can build databases directly from the source controls like Git or TFS with accurate dependencies and make the complete structure error-free. Besides these, you can create installation packages, understand the impacts of a particular script, group multiple scripts, and even customize their properties.

ApexSQL DevOps Toolkit

The DevOps Toolkit is a dashboard with a web-based user interface that enables CI-CD (Continuous Integration-Continuous Deployment) operations. It helps you create and configure a completely customizable pipeline.

This is possible through the set of steps the toolkit offers in the form of an interactive icon. Each step has a specific function in the pipeline. The series of steps include:

  1. Building
  2. Populating
  3. Documenting
  4. Testing
  5. Reviewing
  6. Packaging
  7. Schema Synchronization
  8. Data Synchronization
  9. Deployment
  10. Notifications
  11. Publishing

ApexSQL Compare

ApexSQL Compare is a standalone tool that can be integrated with the SQL Server Management Studio and Visual Studio. It is primarily used to compare two files, file folders, and database objects.

However, one can also automate and schedule the comparison operations and perform synchronizations and line-by-line comparisons. Other features include ignoring scripts, character-by-character and two-way comparisons, and even a quick load of recent files.

ApexSQL Data Diff

This tool enables you to compare data from two different data sources. While the data can be in tables or views, the sources can be backups, script folders, databases, and source control folders.

The tool also helps you synchronize and extrapolate data changes across environments, repair lost data, and recover row-level data from backups. Like the previous tool, this also can automate and schedule comparisons by leveraging the GUI and CLI.

ApexSQL Diff

This is an extension of the Data Diff tool. Instead of comparing data, it compares schemas, synchronizes them, and even automates and schedules operations. Furthermore, you can migrate databases, extrapolate schema changes, and other operations as you saw in the previous tool.

Besides these, you also have a rollback feature that restores your database to the previous state before the synchronization had begun. The best part about the tool is that you can create detailed reports to understand how the schemas are different and know your next steps.

ApexSQL Doc

As the name suggests, this tool helps you document your SQL Server databases in various formats such as CHM, HTML, Word, MD, and PDF. You can also automate and schedule the documentation process without having any human intervention.

In fact, the tool helps you create documents for a range of items, including SharePoint farms, SSIS packages, schema changes, tableau servers, control flow, and data flow diagrams, multidimensional models, and more.

ApexSQL Generate

This tool allows you generate test data for different columns in your database. Then, based on the name of your column, the system identifies what generator will work best and assign it accordingly. There are numerous generators, including Random, Regular Expression, Foreign Key, and more.

ApexSQL Plan

ApexSQL Plan is perhaps one of the most crucial tools for managing a SQL database server. It helps you analyze your queries and optimize them.

This is done by helping you identify deadlocks, solve query issues, compare the estimated query execution plans with the actual ones, showcase insights into performance statistics, wait for statistics, and more.

Overall, it helps you bridge the gap between your current query plans and the one you had wanted to build.

ApexSQL Propagate

ApexSQL Propagate allows you to execute multiple SQL scripts in one go. These scripts can be across different databases and server instances. The tool also tells you, before the execution begins, whether scripts will pass or fail. This is possible because it parses the various scripts beforehand, thus making the entire process much more time and energy-efficient. Furthermore, once your scripts are executed, you can also export the results in the form of documents of multiple types, including plain text and CSV.

ApexSQL Refactor

ApexSQL Refactor helps you format the database such that it is more time-efficient, energy-efficient, and less tedious to work with. With over 200 formatting options and about 15 code refactors, the tool allows you to improve query performance, clean the database code, consolidate rarely used columns, and more.

Besides these capabilities, you can also change parameters for function without ruining the database integrity, replace one-to-many relationships, and add a surrogate key. All these options can help you optimize the complete code of your database.

ApexSQL Script

ApexSQL Script generates scripts for different parts of your database servers, including files, folders, and deployment packages, down to the last column. It can automate and schedule the process with the help of the CLI.

This tool can script both objects and data, combine numerous scripts into one, and encrypt/decrypt database objects with relative ease.

This tool allows you to search for different texts and data stored in table/view forms and other formats. It also helps you rename objects, schema, tables, views, procedures, columns, etc., and repeat searches from the search history.

ApexSQL Source Control

You can use this tool to integrate source control into your database servers. In fact, only after installing and configuring source control can one perform operations such as building a database directly from the source control.

Conclusion

This blog outlined some of the most important ApexSQL tools used with the Microsoft Azure SQL database. While there are other tools such as Enforce, Mask, and Trigger, the tools outlined above should provide a good starting point. Stay tuned for future articles on more Azure tools. You're on your way to becoming an expert!

Follow Arctype's Development
Programming stories, tutorials, and database tips every 2 weeks

Spread the word

Keep reading