Sitecore Bulk Uploading Data Between Identical Databases

 Introduction

In the scenario where there were issues observed within Analytics reporting after upgrading from Sitecore 9.3 to 10.3, the decision to keep the old 9.3 system alive alongside the new 10.3 environment led to the need for migrating data between identical databases. This blog post aims to discuss the process of bulk uploading data from the Sitecore 9.3 compatible database to the Sitecore 10.3 database, addressing the challenges and providing a solution in the form of a C# class for efficient data migration.

 

Understanding the Scenario

After the upgrade, it was identified that data existed in both the Sitecore 9.3 and 10.3 environment shards. To resolve this, the Sitecore 9.3 database had to be made compatible with the Sitecore 10.3 version by following the guidelines outlined in the upgrade guide. This involved taking a backup of the 9.3 database and running the necessary scripts to ensure compatibility with the new version.

 

Challenges Faced

During the initial migration process, it was apparent that some data might have already been migrated. However, as both versions of Sitecore were serving production, it was essential to export the remaining data from the compatible Sitecore 9.3 database to the Sitecore 10.3 database. This posed the challenge of identifying the data that had not been migrated in the first attempt and ensuring a seamless transition without impacting the production environment.

 

Proposed Solution

To address this challenge, a C# Class was created to facilitate the bulk migration of data from the Sitecore 9.3 compatible database to the Sitecore 10.3 database. The approach involved developing a custom C# class, as shown below, to copy the data that was present in the Sitecore 9.3 compatible database but not in the Sitecore 10.3 database. Given the bulk nature of the records, the process required careful handling to ensure data integrity and consistency throughout the migration process.

 

csharp

 public static class ExportIdenticalTableFromDatabase

    {

        private static string sourceConnectionString = "Data Source=(local);Initial Catalog=jss_Xdb.Collection.Shard0;User ID=UserID;Password=password";

        private static string destinationConnectionString = "Data Source=(local);Initial Catalog=sc93_Xdb.Collection.Shard0;User ID=UserID;Password=password";

        private static List<string> TablesToIgnore = new List<string> { "__ShardManagement.ShardMapManagerLocal", "__ShardManagement.ShardMappingsLocal" ,

                                                                 "__ShardManagement.ShardMapsLocal", "__ShardManagement.ShardsLocal" };

        private static int batchSize = 2;

        public static void ExportData()

        {

            using (SqlConnection sourceConnection = new SqlConnection(sourceConnectionString))

            using (SqlConnection destinationConnection = new SqlConnection(destinationConnectionString))

            {

                sourceConnection.Open();

                destinationConnection.Open();

                // Retrieve table names from the source database

                List<string> tableNames = GetTableNamesFromSourceDatabase(sourceConnection);

                // Export data from each table

                foreach (string tableName in tableNames)

                {

                    if (!TablesToIgnore.Contains(tableName)) // Check if the table should be ignored

                    {

                        ExportTableData(sourceConnection, destinationConnection, tableName);

                    }

                }

            }

        }

        private static List<string> GetTableNamesFromSourceDatabase(SqlConnection connection)

        {

            List<string> tableNames = new List<string>();

            using (SqlCommand command = new SqlCommand("SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", connection))

            using (SqlDataReader reader = command.ExecuteReader())

            {

                while (reader.Read())

                {

                    tableNames.Add(reader.GetString(0) + "." + reader.GetString(1));

                }

            }

            return tableNames;

        }

        private static void ExportTableData(SqlConnection sourceConnection, SqlConnection destinationConnection, string tableName)

        {

            using (SqlCommand selectCommand = new SqlCommand($"SELECT * FROM {tableName}", sourceConnection))

            using (SqlDataReader reader = selectCommand.ExecuteReader())

            {

                using (SqlTransaction transaction = destinationConnection.BeginTransaction())

                {

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.Default, transaction))

                    {

                        bulkCopy.DestinationTableName = tableName;

                        bulkCopy.BatchSize = batchSize;

                        reader.Read(); // Read the first row to check if the reader has records

                        if (reader.HasRows && !DataExistsInDestinationTable(transaction, tableName, reader))

                        {

                            bulkCopy.WriteToServer(reader);

                        }

                    }

                    transaction.Commit();

                }

            }

        }

        private static bool DataExistsInDestinationTable(SqlTransaction transaction, string tableName, SqlDataReader reader)

        {

            List<string> columnNames = new List<string>();  // with the actual column names from the destination table

            for (int i = 0; i < reader.FieldCount; i++)

            {

                columnNames.Add(reader.GetName(i));

            }

            // Construct a WHERE clause to compare all columns

            List<string> conditions = new List<string>();

            using (SqlCommand command = transaction.Connection.CreateCommand())

            {

                command.Transaction = transaction;

                for (int i = 0; i < reader.FieldCount; i++)

                {

                    string columnName = reader.GetName(i);

                    string paramName = $"@{columnName}";

                    conditions.Add($"{columnName} = {paramName}");

                    command.Parameters.AddWithValue(paramName, reader.GetValue(i));

                }

                string whereClause = string.Join(" AND ", conditions);

                command.CommandText = $"SELECT COUNT(*) FROM {tableName} WHERE {whereClause}";

                int count = (int)command.ExecuteScalar();

                return count > 0; // Return true if data exists, otherwise false

            }

        }

    }

Code Explanation: Exporting Identical Table Data Between Databases

The provided C# class named ExportIdenticalTableFromDatabase facilitates the export of data from one database to another, specifically handling identical tables. Let's break down the code functionality step by step:

 

1. Initialization of Connection Strings and Variables

    sourceConnectionString & destinationConnectionString: Define the connection strings for the        source and destination databases.

    TablesToIgnore: Lists the tables that should be ignored during the export process.

    batchSize: Determines the number of rows to process in each batch.

2. Method: ExportData()

    Purpose: Manages the export process by connecting to both databases.

    Steps:

        Establish connections to the source and destination databases.

        Retrieve table names from the source database.

        Iterate through each table and export data if the table is not in the ignore list.

3. Method: GetTableNamesFromSourceDatabase()

    Purpose: Fetches the table names from the source database.

    Steps:

         Execute a query to retrieve base table names from the source database.

         Construct a list of table names by combining the schema and table name.

4. Method: ExportTableData()

    Purpose: Handles exporting data for a specific table.

    Steps:

        Create a SELECT query for the table in the source database.

        Begin a transaction in the destination database.

        Use SqlBulkCopy to efficiently copy data to the destination. Only copy if the data does not already exist in the destination table.

5. Method: DataExistsInDestinationTable()

    Purpose: Checks if the data from the source table exists in the destination table.

    Steps:

        Retrieve column names from the destination table.

        Construct a WHERE clause based on column values to compare with the destination.

        Execute a query to check for the existence of the data in the destination table.

6. Method: InsertDataIntoDestinationTable()

    Purpose: Inserts data into the destination table.

    Steps:

        Format column names and values for insertion based on the source row.

        Generate an INSERT query to add the data to the destination table.

        Transaction Entry for Bulk Data Migration

Since the records were in bulk, employing transaction entry was crucial to ensure the reliability and atomicity of the data migration process. Transaction entry allows for a group of operations to be treated as a single unit, ensuring that either all operations are completed successfully, or none of them are executed, thereby maintaining data consistency.

 

Conclusion

The process of bulk uploading data between identical databases, especially in the context of Sitecore version migration, presents a unique set of challenges. By understanding the scenario, leveraging the upgrade guide, and implementing a C# class for data migration, it becomes possible to address these challenges effectively. With the right approach and careful handling, the migration process can be streamlined, ensuring a seamless transition to the new version while maintaining data integrity and minimizing disruption to the production environment.

 

In summary, the utilization of a custom C# class along with transaction entry for bulk data migration proves to be a robust solution for efficiently managing data transfer between identical databases in the context of Sitecore version upgrades.