Applies to:
Introduction
ApexSQL Defrag is a powerful SQL index application used for scheduled analysing and defragmenting of SQL Server database indexes. It consists of three main components:
- User interface – It’s the main part of the application, where user can interact with the application, analyze indexes, create and modify jobs on a schedule, look at reports, monitor the scheduled jobs. The user interface is needed for the creation of scheduled jobs, while the jobs themselves are run from the monitoring agent
- Monitoring agent Service – The monitoring agent is a Windows service which allows the analysis and running jobs. It is not to be confused with SQL Server Agent. One instance of the monitoring agent is required and is sufficient for multiple local or remote server instances
- Central repository database – A database installed on a user specified server with a purpose to store the information needed for communication between the User Interface and the monitoring agent service. Login credentials for connecting to various monitored SQL Servers are also stored in it. One central repository database is required for storing all information
The components of the SQL index application must be installed and configured properly for the application to work. Every component can be installed on a local machine and work in a local environment, but it is also sufficient for remote servers with no additional installation required.
Installation of ApexSQL Defrag User Interface
- Firstly, download the latest version from ApexSQL DBA installer here
-
Run the downloaded exe file to start the installation process
Note: It is recommended to run the installer with a user with full permissions on the ProgramData folder. In most cases, the users have full permissions, but it can vary due to various system security settings.
-
After starting the installer, the welcome screen is displayed and by clicking the Next button setup process is initialized:
-
In the next step, read the Software Transaction Agreement and agree to its terms by choosing the I accept the terms in the license agreement option. To continue with the installation, click the Next button:
-
Previous action will summon the Quest ID setup window with the option to sign in using the existing Quest credentials or sign up for a new account. Click the Skip button to omit this step:
-
In the following window, choose a custom installation folder for the SQL index tool or leave the default selected one. Click the Next button to continue to the next step:
-
In the installation step, select ApexSQL Defrag from the list of offered ApexSQL DBA tools and click the Install button to start the installation process:
-
After the SQL index application is successfully installed, click the Close button to exit the setup window:
-
On the tool’s first start-up, the message states that ApexSQL Defrag Agent service is not installed. Click the Yes button to open the configuration setup:
-
In the ApexSQL Defrag Agent configuration setup, under the Account drop-down list, there are four different accounts to choose from:
- Local System
- Local Service
- Network Service
- User
Every account has a different level of privileges and permissions that varies from system to system and being dependent on system security settings. For this guide, please select the User as it has the most privileges on the system. The current Windows account being used will be automatically inserted into the Username field. In the Password field enter the password used for logging into that Windows account. For more info on tool’s requirements, please consult the following link. Click the OK button to finish the setup:
-
In the following window configure the Central repository database, CRD, for SQL index tool. From the Server drop-down list, choose the server where you will create the CRD. Under the Authentication, there are four different types of authentication:
- Windows
- SQL Server
- Active directory – Password
- Active directory – Integrated
For detailed info on using ApexSQL tools with Azure Active Directory authentication, please consult the Using ApexSQL tools with Azure Active Directory authentication article. For this guide, choose the SQL Server authentication and provide the username and password in the corresponding fields. The action of clicking the OK button will summon the new dialogue window with information that new database will be installed on the selected SQL Server. Click the OK to continue:
In the next step, click the OK button to finish the setup:
Troubleshooting
During the use of ApexSQL Defrag, a couple of problems may occur. In this part of the article we will mention the most common ones.
Problem: “Failed to connect to the central repository database” error is displayed on start-up. This occurs when the SQL Server, on which the Central repository database is created, is not running
Solution 1:
To fix this, go to Services, find the SQL Server entry and start it either by right clicking the service and selecting Start from the context menu or by clicking on Start the service in the top left corner
Note: Starting Services can be done in a couple of ways:
-
by pressing
+ R and typing services.msc,
- going to Start, selecting Run… and typing services.msc or
- going to Start -> Windows Administrative Tools and selecting Services
Solution 2:
Create new CRD by clicking the Repository button and select new SQL Server listed under the Server drop-down list. Click the OK button to finish setup:
Problem: “Communication with agent failed” error message is encountered. This error occurs when user interface of the SQL index application cannot communicate with the monitoring agent.
Solution 1:
The simplest way of fixing this issue is to restart the ApexSQL Defrag application. On new start-up the SQL index tool will detect that ApexSQL Defrag Agent is not running and will automatically start it.
Solution 2:
The other way of dealing with this issue is to head to Services, find the monitoring agent service and start it either by right clicking the service and selecting Start from the context menu or by clicking on Start the service in the top left corner:
Problem: “Wrong username or password” message is shown when configuring the central repository database with SQL Server authentication:
Solution 1:
Double check the username and password entered
Solution 2:
Double check if the SQL Server is allowing SQL Server authenticated connections.
Do the latter by going to properties of the SQL Server in SQL Server Management Studio
In the properties window, select the Security tab, and then, under Server authentication, choose SQL Server and Windows authentication mode and click OK
Frequently asked questions
Q: I have servers on other machines in the network. Do I need to install monitoring agent on every machine?
A: No, monitoring agent needs to be installed only on the machine you are using the SQL index application from.
Q: How do I check if I’m running the latest version of ApexSQL Defrag?
A: You can check if you are running the latest version of ApexSQL Defrag by going to Resources tab of the main User interface and clicking on Get updates
