Executing custom SQL Command against the Episerver DB

After upgrading the DbLocalizationProvider to 6.5.2 we noticed issues in our preproduction environment in Optimizely DXP (see bug report). 

Somehow we had gotten duplicates which casued an issue in the upgrade. 

To be able to proceed we needed to first remove the duplicates from the database, we did so by creating a schedueled job that would execute the SQL commands needed to clean the entries from the Optimizely (formerly Episerver) database. 

These are the steps we went through to solve the issues:

1. Create a new release branch based on the release that was out in production
2. Add the schedueled job that would clean out the entries to the site 
3. Run the schedueled job against a local copy of the production database
4. Verified the entries where removed
5. Deployed the new release branch to preproduction
6. Ran the job in preproduction to clean out the duplicate entries
7. Deployed the code from preproduction to production
8. Ran the job on production

We actually created two jobs, one to clean and one to remove the entries so we didn't need to fetch a extra copy of the database to verify the fix.

Example of schedueled job to execute SQL Commands

This job will only count the amount of AspNetUsers that exists in the database.

using EPiServer.PlugIn;
using EPiServer.Scheduler;
using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;

namespace Site.Infrastructure.Migration
{
    /// <summary>
    /// Scheduled job run a custom SQL Command against EPiServerDB
    /// </summary>
    [ScheduledPlugIn(DisplayName = "Execute SQL Command",
    Description = "This job will run a custom command against the EPiServerDB",
    GUID = "eb850d57-1291-4639-a6d9-0d6c5af88aa7")]
    public class ExecuteSQLCommandSchedueledJob : ScheduledJobBase
    {
        public ExecuteSQLCommandSchedueledJob()
        {
            IsStoppable = false;
        }

        /// <summary>
        /// Called when a scheduled job executes
        /// </summary>
        /// <returns>A status message to be stored in the database log and visible from admin mode</returns>
        public override string Execute()
        {
            return ExecuteSQLCommands();
        }

        private readonly string customSQLCommand = $"SELECT COUNT(*) FROM [dbo].[AspNetUsers]";

        public string ExecuteSQLCommands()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["EPiServerDB"].ConnectionString;
            int foundEntriesCount = 0;

            var stopWatch = new Stopwatch();
            stopWatch.Start();

            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand(customSQLCommand, connection))
                {
                    connection.Open();
                    command.CommandTimeout = 600;
                    foundEntriesCount = Convert.ToInt32(command.ExecuteScalar());
                }
                connection.Close();
            }

            return $"Found {foundEntriesCount} users in {stopWatch.ElapsedMilliseconds / 1000} seconds.";
        }
    }
}

I would not recommend this to be used all the time, it's best to work with the Optimizely providers instead making custom SQL commands against a database, especially in production environments. 

But sometimes you have little to no choice and this could get you our of a pinch.

The SQL Command we actually used to remove the duplicate translation entries where:

WITH cte AS (SELECT *, row_number() OVER(PARTITION BY[Language], [ResourceId] ORDER BY[ResourceId] desc) AS[rn] FROM [dbo].[LocalizationResourceTranslations])
DELETE from cte WHERE [rn] > 1