Lesson Learned #502: Comparing and Transferring Azure SQL Databases Table Structures Using SMO

[Guest Jose_Manuel_Jurado]

This last week, I worked on a service request that we need to compare the existing tables of a database placed on a Azure SQL Server with another hand. If the table doesn't exist in the target database the intention is to have a script of the table and recreate the table in the target. Following, I would like to share my lessons learned here.

 

 

 

I used the following C# script to play with SMO - SQL Server Management Objects (SMO), please note that it is meant for educational purposes and should be adapted and tested thoroughly before using in a production environment.

 

using System;
using System.Data;
using System.Threading;
using Microsoft.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;
using System.Collections.Generic;
using System.IO;

namespace TransferTables
{
   class Program
   {
       static void Main(string[] args)
       {
           string sourceConnectionString = "Server=tcp:sourceserver.database.windows.net;Database=sourcedb;User ID=username1;Password=Password1;";
           string destinationConnectionString = "Server=tcp:targetserver.database.windows.net;Database=targetdb;User ID=username2;Password=Password2;";

           TransferTables(sourceConnectionString, destinationConnectionString);
       }
       static void TransferTables(string sourceConnectionString, string destinationConnectionString)
       {
           string logFilePath = "c:\\temp\\TransferTablesLog.txt";

           using (var sourceConnection = CreateRetryConnection(sourceConnectionString, logFilePath))
           using (var destinationConnection = CreateRetryConnection(destinationConnectionString, logFilePath))
           {
               var sourceServer = new Server(new ServerConnection(sourceConnection));
               var sourceDatabase = sourceServer.Databases["sourcedb"];

               var destinationServer = new Server(new ServerConnection(destinationConnection));
               var destinationDatabase = destinationServer.Databases["targetdb"];

               foreach (Table sourceTable in sourceDatabase.Tables)
               {
                   if (sourceTable.Schema == "dbo")
                   {
                       LogMessage(logFilePath, $"Checking ..{sourceTable.Schema}.{sourceTable.Name}:");

                       if (!destinationDatabase.Tables.Contains(sourceTable.Name, sourceTable.Schema))
                       {
                           if (!destinationDatabase.Schemas.Contains(sourceTable.Schema))
                           {
                               string createSchemaScript = $"CREATE SCHEMA {sourceTable.Schema}";
                               ExecuteWithRetry(destinationDatabase, createSchemaScript, logFilePath);
                               LogMessage(logFilePath, $"Schema created: {sourceTable.Schema}");
                           }

                           var createTableScripts = GetCreateTableScripts(sourceTable);
                           foreach (var script in createTableScripts)
                           {
                               ExecuteWithRetry(destinationDatabase, script, logFilePath);
                           }

                           LogMessage(logFilePath, $"Table created: {sourceTable.Schema}.{sourceTable.Name}");
                       }
                       else
                       {
                           LogMessage(logFilePath, $"Table exists in destination: {sourceTable.Schema}.{sourceTable.Name}");
                       }

                   }
               }
           }
       }

       static List<string> GetCreateTableScripts(Table sourceTable)
       {
           ScriptingOptions options = new ScriptingOptions
           {
               ScriptDrops = false,
               IncludeIfNotExists = false,
               Indexes = true,
               DriAllConstraints = true,
               DriPrimaryKey = true,
               DriForeignKeys = true,
               DriUniqueKeys = true,
               DriClustered = true,
               DriNonClustered = true,
               FullTextIndexes = true,
               Triggers = true,
               Statistics = true
           };

           StringCollection scriptCollection = sourceTable.Script(options);
           List<string> scripts = new List<string>();

           foreach (string script in scriptCollection)
           {
               if (script.StartsWith("CREATE TRIGGER", StringComparison.OrdinalIgnoreCase))
               {
                   scripts.Add(script);
               }
               else if (scripts.Count > 0 && scripts[scripts.Count - 1].StartsWith("CREATE TRIGGER", StringComparison.OrdinalIgnoreCase))
               {
                   scripts[scripts.Count - 1] += Environment.NewLine + script;
               }
               else
               {
                   scripts.Add(script);
               }
           }

           return scripts;
       }


       static SqlConnection CreateRetryConnection(string connectionString, string logFilePath)
       {
           SqlConnection connection = new SqlConnection(connectionString);
           RetryPolicy retryPolicy = new RetryPolicy();

           for (int retry = 0; retry < retryPolicy.MaxRetryCount; retry++)
           {
               try
               {
                   connection.Open();
                   return connection;
               }
               catch (SqlException ex)
               {
                   LogMessage(logFilePath, $"Error opening connection: {ex.Message}");
                   if (retry == retryPolicy.MaxRetryCount - 1)
                   {
                       throw;
                   }
                   Thread.Sleep(retryPolicy.GetRetryDelay(retry));
               }
           }

           return connection;
       }

       static void ExecuteWithRetry(Database database, string query, string logFilePath)
       {
           RetryPolicy retryPolicy = new RetryPolicy();

           for (int retry = 0; retry < retryPolicy.MaxRetryCount; retry++)
           {
               try
               {
                   database.ExecuteNonQuery(query);
                   LogMessage(logFilePath, $"Successfully executed query: {query}");
                   return;
               }
               catch (Exception ex)
               {
                   LogMessage(logFilePath, $"Error executing query: {ex.Message} - Query: {query}");
                   if (retry == retryPolicy.MaxRetryCount - 1)
                   {
                       LogMessage(logFilePath, $"Imposible after retries");
                   }
                   Thread.Sleep(retryPolicy.GetRetryDelay(retry));
               }
           }
       }

       static SqlDataReader ExecuteReaderWithRetry(SqlCommand command, string logFilePath)
       {
           RetryPolicy retryPolicy = new RetryPolicy();

           for (int retry = 0; retry < retryPolicy.MaxRetryCount; retry++)
           {
               try
               {
                   return command.ExecuteReader();
               }
               catch (SqlException ex)
               {
                   LogMessage(logFilePath, $"Error executing reader: {ex.Message}");
                   if (retry == retryPolicy.MaxRetryCount - 1)
                   {
                       throw;
                   }
                   Thread.Sleep(retryPolicy.GetRetryDelay(retry));
               }
           }

           return null;
       }

       static void LogMessage(string logFilePath, string message)
       {
           using (StreamWriter writer = new StreamWriter(logFilePath, true))
           {
               writer.WriteLine($"{DateTime.Now}: {message}");
               Console.WriteLine($"{DateTime.Now}: {message}");
           }
       }
   }

   public class RetryPolicy
   {
       public int MaxRetryCount { get; set; } = 5;
       public int RetryDelayBase { get; set; } = 2000; // 2 seconds

       public TimeSpan GetRetryDelay(int retryAttempt)
       {
           return TimeSpan.FromMilliseconds(RetryDelayBase * (retryAttempt + 1));
       }
   }
}

 

 

 

The script performs the following tasks:

 

1. Establishes connections to the source and destination databases.
   
2. Iterates through the tables in the source database. In this case, I also included to filter just only the tables that belongs to dbo.
   
3. Checks if each table exists in the destination database.
   
4. If a table does not exist, it creates the schema and table structure in the destination database.
   
5. Logs the actions performed during the process.
   

 

 

 

One thing that pay my attention and why we used SMO is for the properties that you could specify to obtain the details of the table structure, for example, looking at the GetCreateTableScript Method we could see all the options that the script of the table structure will return, indexes, primary keys, indexes, statistics, etc..

 

 

 

Example:

 

 

 

 

 

 

[HEADING=2] [/HEADING]

[HEADING=2]Disclaimer[/HEADING]

 

This script is for educational purposes only. Ensure you thoroughly review, test, and modify the script to suit your specific requirements and environment before using it in production. The author is not responsible for any data loss or damage resulting from the use of this script.

 

Continue reading...