Database Maintenance - Microsoft SQL Server / Azure SQL (Connect 2024)
Reorganize Database Indexes
The automatic clean-up functionality of Connect removes a significant number of records resulting in fragmentation of the corresponding database indexes. To address this issue, the following SQL script should be scheduled for daily database maintenance:
To reorganize all Connect application specific indexes, use th following SQL script:
Delete obsolete data
If there is a need to delete all Contexts and Performance Records created before a specific date, the following script can be employed. Be aware that fragmented indexes may cause poor performance during deletion. If required, defragment these indexes prior to deleting data. Ensure to adjust the date in the first line of the script as needed:
In case you want to delete all Contexts and Performance Records, the process be sped-up by using TRUNCATE:
Reducing the size of the database
Deleting a significant volume of data may not always result in a decrease in the overall size of the database. In such scenarios, it is recommended to direct the database to free up the unnecessary space. This can be accomplished by executing the following SQL script:
The value 10 represents the amount of unused storage capacity that the database can retain, enabling the addition of new data without requiring extra memory allocation. Following a size reduction of the database, it might be beneficial to restructure the indices.
Schedule index reorganization when using Azure SQL Database
Step 1: Setup Azure Automation account
If you do not have an existing Azure Automation account you need to setup a new one. Proceed as follows:
Navigate to the Azure portal.
Click on “Create a resource” and search for “Automation”.
Select “Automation” and click “Create”.
Enter a unique name for your Automation account.
Select an existing resource group or create a new one.
Choose the appropriate Azure region.
Click “Review + create” and then “Create”.
Step 2: Add credentials for database access
In the Azure portal, navigate to your newly created Automation account.
Under Shared Resources, select Credentials.
Click “Add credentials”.
Enter a Name (e.g., “ConnectDatabaseCredentials”).
Enter the database administrator user name and passwort and re-enter the password to confirm it.
Click “Create”.
Step 3: Create a Runbook
In the Azure portal, navigate to your newly created Automation account.
Under Process Automation, select Runbooks.
Click “Create runbook”.
Enter a Name (e.g., “Optimize-ConnectDatabase”).
Select Runbook type “PowerShell”.
Select Runtime Version “7.2”.
Click “Review + create” and then “Create”.
Paste the code below into the Runbook editor.
Click “Save”.
Step 4: Test and publish the Runbook
Select tab “Test”.
On the left side enter the parameters:
SQLSERVER: The host name of the SQL Server
DATABASE: The name of the database
CREDENTIALNAME: The name of the SQL Server credentials (e.g., “ConnectDatabaseCredentials”)Click “Start” to test the Runbook manually.
Once the test is completed successfully, click the “X” in the upper left corner to return to the editor. The browser shows a dialog box asking you to confirm this action.
Click “Publish” and then “Yes”.
Step 5: Create a Schedule
In the Azure portal, navigate to your Runbook.
Under Resources, select Schedules.
Click “Schedule”.
Select a Schedule or create a new one.
Click “Parameters”.
Enter the parameters:
SQLSERVER: The host name of the SQL Server
DATABASE: The name of the database
CREDENTIALNAME: The name of the SQL Server credentials (e.g., “ConnectDatabaseCredentials”)Click “OK”
Click “OK”