Scopri come confrontare e trasferire strutture di tabelle tra database Azure SQL utilizzando SMO e un completato script C#.
In un recente progetto, è emersa la necessità di confrontare le tabelle esistenti di un database su un Azure SQL Server con quelle di un altro database. L’obiettivo era generare uno script per creare le tabelle mancanti nel database di destinazione. Di seguito, condivido ciò che ho imparato durante questa esperienza.
Ho utilizzato il seguente script C# per lavorare con SQL Server Management Objects (SMO). Tieni presente che lo script è destinato a scopi educativi e dovrebbe essere adattato e testato a fondo prima di utilizzarlo in un ambiente di produzione.
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 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 scripts = new List(); 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)); } }}Lo script esegue i seguenti compiti:
- Stabilisce connessioni ai database di origine e destinazione.
- Itera tra le tabelle nel database di origine, filtrando solo le tabelle che appartengono allo schema dbo.
- Verifica se ogni tabella esiste nel database di destinazione.
- Se una tabella non esiste, crea lo schema e la struttura della tabella nel database di destinazione.
- Registra le azioni eseguite durante il processo.
Un aspetto interessante dell’uso di SMO sono le proprietà che consentono di ottenere dettagli completi sulla struttura delle tabelle, come indici, chiavi primarie, chiavi esterne, indici full-text, trigger e statistiche.
Quando si utilizza lo script in un ambiente Microsoft Azure SQL, è fondamentale adattarlo e testarlo accuratamente per evitare problemi di produzione. Questo processo rappresenta una soluzione efficace per il confronto e il trasferimento di strutture di tabelle tra database.
Avviso
Questo script è destinato solo a scopi educativi. Assicurati di esaminarlo, testarlo e modificarlo a fondo per adattarlo alle tue esigenze specifiche prima di utilizzarlo in produzione. L’autore non è responsabile per eventuali perdite di dati o danni derivanti dall’uso di questo script.