private bool ReplicateDeletionsToDestination(SqlReplicationConfig cfg, IEnumerable <ListItem> deletedDocs) { var identifiers = deletedDocs.Select(x => x.Key).ToList(); if (identifiers.Count == 0) { return(true); } var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name)); using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats)) { foreach (var sqlReplicationTable in cfg.SqlReplicationTables) { writer.DeleteItems(sqlReplicationTable.TableName, sqlReplicationTable.DocumentKeyColumn, cfg.ParameterizeDeletesDisabled, identifiers); } writer.Commit(); if (Log.IsDebugEnabled) { Log.Debug("Replicated deletes of {0} for config {1}", string.Join(", ", identifiers), cfg.Name); } } return(true); }
public RelationalDatabaseWriter.TableQuerySummary[] SimulateSqlReplicationSQLQueries(string strDocumentId, SqlReplicationConfig sqlReplication, bool performRolledbackTransaction, out Alert alert) { alert = null; RelationalDatabaseWriter.TableQuerySummary[] resutls = null; try { var stats = new SqlReplicationStatistics(sqlReplication.Name); var docs = new List <JsonDocument>() { Database.Documents.Get(strDocumentId, null) }; var scriptResult = ApplyConversionScript(sqlReplication, docs); var connectionsDoc = Database.Documents.Get(connectionsDocumentName, null); var sqlReplicationConnections = connectionsDoc.DataAsJson.JsonDeserialization <SqlReplicationConnections>(); if (PrepareSqlReplicationConfig(sqlReplication, sqlReplication.Name, stats, sqlReplicationConnections, false, false)) { if (performRolledbackTransaction) { using (var writer = new RelationalDatabaseWriter(Database, sqlReplication, stats)) { resutls = writer.RolledBackExecute(scriptResult).ToArray(); } } else { var simulatedwriter = new RelationalDatabaseWriterSimulator(Database, sqlReplication, stats); resutls = new List <RelationalDatabaseWriter.TableQuerySummary>() { new RelationalDatabaseWriter.TableQuerySummary() { Commands = simulatedwriter.SimulateExecuteCommandText(scriptResult) .Select(x => new RelationalDatabaseWriter.TableQuerySummary.CommandData() { CommandText = x }).ToArray() } }.ToArray(); } } alert = stats.LastAlert; } catch (Exception e) { alert = new Alert() { AlertLevel = AlertLevel.Error, CreatedAt = SystemTime.UtcNow, Message = "Last SQL replication operation for " + sqlReplication.Name + " was failed", Title = "SQL replication error", Exception = e.ToString(), UniqueKey = "Sql Replication Error: " + sqlReplication.Name }; } return(resutls); }
private bool ReplicateChangesToDestination(SqlReplicationConfig cfg, ICollection <ReplicatedDoc> docs, out int countOfReplicatedItems) { countOfReplicatedItems = 0; var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name)); var scriptResult = ApplyConversionScript(cfg, docs, replicationStats); if (scriptResult.Ids.Count == 0) { return(true); } countOfReplicatedItems = scriptResult.Data.Sum(x => x.Value.Count); try { using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats)) { if (writer.Execute(scriptResult)) { Log.Debug("Replicated changes of {0} for replication {1}", string.Join(", ", docs.Select(d => d.Key)), cfg.Name); replicationStats.CompleteSuccess(countOfReplicatedItems); } else { Log.Debug("Replicated changes (with some errors) of {0} for replication {1}", string.Join(", ", docs.Select(d => d.Key)), cfg.Name); replicationStats.Success(countOfReplicatedItems); } } return(true); } catch (Exception e) { Log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name, e); SqlReplicationStatistics replicationStatistics; DateTime newTime; if (statistics.TryGetValue(cfg.Name, out replicationStatistics) == false) { newTime = SystemTime.UtcNow.AddSeconds(5); } else { if (replicationStatistics.LastErrorTime == DateTime.MinValue) { newTime = SystemTime.UtcNow.AddSeconds(5); } else { // double the fallback time (but don't cross 15 minutes) var totalSeconds = (SystemTime.UtcNow - replicationStatistics.LastErrorTime).TotalSeconds; newTime = SystemTime.UtcNow.AddSeconds(Math.Min(60 * 15, Math.Max(5, totalSeconds * 2))); } } replicationStats.RecordWriteError(e, Database, countOfReplicatedItems, newTime); return(false); } }
private IEnumerable <string> GenerteInsertItemCommandText(string tableName, string pkName, List <ItemToReplicate> dataForTable) { foreach (var itemToReplicate in dataForTable) { var sb = new StringBuilder("INSERT INTO ") .Append(GetTableNameString(tableName)) .Append(" (") .Append(commandBuilder.QuoteIdentifier(pkName)) .Append(", "); foreach (var column in itemToReplicate.Columns) { if (column.Key == pkName) { continue; } sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", "); } sb.Length = sb.Length - 2; sb.Append(") VALUES (") .Append(itemToReplicate.DocumentId) .Append(", "); foreach (var column in itemToReplicate.Columns) { if (column.Key == pkName) { continue; } DbParameter param = new OdbcParameter(); RelationalDatabaseWriter.SetParamValue(param, column.Value, null); sb.Append("'").Append(param.Value).Append("'").Append(", "); } sb.Length = sb.Length - 2; sb.Append(")"); if (IsSqlServerFactoryType && cfg.ForceSqlServerQueryRecompile) { sb.Append(" OPTION(RECOMPILE)"); } sb.Append(";"); yield return(sb.ToString()); } }
private bool ReplicateChangesToDesintation(SqlReplicationConfig cfg, IEnumerable <JsonDocument> docs) { var scriptResult = ApplyConversionScript(cfg, docs); if (scriptResult.Data.Count == 0) { return(true); } var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name)); var countOfItems = scriptResult.Data.Sum(x => x.Value.Count); try { using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats)) { if (writer.Execute(scriptResult)) { replicationStats.CompleteSuccess(countOfItems); } else { replicationStats.Success(countOfItems); } } return(true); } catch (Exception e) { log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name, e); SqlReplicationStatistics replicationStatistics; DateTime newTime; if (statistics.TryGetValue(cfg.Name, out replicationStatistics) == false) { newTime = SystemTime.UtcNow.AddSeconds(5); } else { var totalSeconds = (SystemTime.UtcNow - replicationStatistics.LastErrorTime).TotalSeconds; newTime = SystemTime.UtcNow.AddSeconds(Math.Max(60 * 15, Math.Min(5, totalSeconds + 5))); } replicationStats.RecordWriteError(e, Database, countOfItems, newTime); return(false); } }
private bool ReplicateDeletionsToDestination(SqlReplicationConfig cfg, IEnumerable <ListItem> deletedDocs) { var identifiers = deletedDocs.Select(x => x.Key).ToList(); if (identifiers.Count == 0) { return(true); } var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name)); using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats)) { foreach (var sqlReplicationTable in cfg.SqlReplicationTables) { writer.DeleteItems(sqlReplicationTable.TableName, sqlReplicationTable.DocumentKeyColumn, identifiers); } writer.Commit(); } return(true); }
private IEnumerable <string> GenerateDeleteItemsCommandText(string tableName, string pkName, bool doNotParameterize, List <string> identifiers) { const int maxParams = 1000; database.WorkContext.CancellationToken.ThrowIfCancellationRequested(); for (int i = 0; i < identifiers.Count; i += maxParams) { var sb = new StringBuilder("DELETE FROM ") .Append(GetTableNameString(tableName)) .Append(" WHERE ") .Append(commandBuilder.QuoteIdentifier(pkName)) .Append(" IN ("); for (int j = i; j < Math.Min(i + maxParams, identifiers.Count); j++) { if (i != j) { sb.Append(", "); } if (doNotParameterize == false) { sb.Append(identifiers[j]); } else { sb.Append("'").Append(RelationalDatabaseWriter.SanitizeSqlValue(identifiers[j])).Append("'"); } } sb.Append(")"); if (IsSqlServerFactoryType && cfg.ForceSqlServerQueryRecompile) { sb.Append(" OPTION(RECOMPILE)"); } sb.Append(";"); yield return(sb.ToString()); } }
private bool ReplicateChangesToDesintation(SqlReplicationConfig cfg, IEnumerable<JsonDocument> docs) { var scriptResult = ApplyConversionScript(cfg, docs); if (scriptResult.Data.Count == 0) return true; var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name)); var countOfItems = scriptResult.Data.Sum(x => x.Value.Count); try { using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats)) { if (writer.Execute(scriptResult)) replicationStats.CompleteSuccess(countOfItems); else replicationStats.Success(countOfItems); } return true; } catch (Exception e) { log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name, e); SqlReplicationStatistics replicationStatistics; DateTime newTime; if (statistics.TryGetValue(cfg.Name, out replicationStatistics) == false) { newTime = SystemTime.UtcNow.AddSeconds(5); } else { var totalSeconds = (SystemTime.UtcNow - replicationStatistics.LastErrorTime).TotalSeconds; newTime = SystemTime.UtcNow.AddSeconds(Math.Max(60 * 15, Math.Min(5, totalSeconds + 5))); } replicationStats.RecordWriteError(e, Database, countOfItems, newTime); return false; } }
private bool ReplicateDeletionsToDestination(SqlReplicationConfig cfg, IEnumerable<ListItem> deletedDocs) { var identifiers = deletedDocs.Select(x => x.Key).ToList(); if (identifiers.Count == 0) return true; var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name)); using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats)) { foreach (var sqlReplicationTable in cfg.SqlReplicationTables) { writer.DeleteItems(sqlReplicationTable.TableName, sqlReplicationTable.DocumentKeyColumn, identifiers); } writer.Commit(); } return true; }
public RelationalDatabaseWriter.TableQuerySummary[] SimulateSqlReplicationSqlQueries(string strDocumentId, SqlReplicationConfig sqlReplication, bool performRolledbackTransaction, out Alert alert) { RelationalDatabaseWriter.TableQuerySummary[] resutls = null; try { var stats = new SqlReplicationStatistics(sqlReplication.Name, false); var jsonDocument = Database.Documents.Get(strDocumentId, null); JsonDocument.EnsureIdInMetadata(jsonDocument); var doc = jsonDocument.ToJson(); doc[Constants.DocumentIdFieldName] = jsonDocument.Key; var docs = new List <ReplicatedDoc> { new ReplicatedDoc { Document = doc, Etag = jsonDocument.Etag, Key = jsonDocument.Key, SerializedSizeOnDisk = jsonDocument.SerializedSizeOnDisk } }; var scriptResult = ApplyConversionScript(sqlReplication, docs, stats); var sqlReplicationConnections = Database.ConfigurationRetriever.GetConfigurationDocument <SqlReplicationConnections <SqlReplicationConnections.PredefinedSqlConnectionWithConfigurationOrigin> >(Constants.SqlReplication.SqlReplicationConnectionsDocumentName); if (PrepareSqlReplicationConfig(sqlReplication, sqlReplication.Name, stats, sqlReplicationConnections.MergedDocument, false, false)) { if (performRolledbackTransaction) { using (var writer = new RelationalDatabaseWriter(Database, sqlReplication, stats)) { resutls = writer.RolledBackExecute(scriptResult).ToArray(); } } else { var simulatedwriter = new RelationalDatabaseWriterSimulator(Database, sqlReplication, stats); resutls = new List <RelationalDatabaseWriter.TableQuerySummary> { new RelationalDatabaseWriter.TableQuerySummary { Commands = simulatedwriter.SimulateExecuteCommandText(scriptResult) .Select(x => new RelationalDatabaseWriter.TableQuerySummary.CommandData { CommandText = x }).ToArray() } }.ToArray(); } } alert = stats.LastAlert; } catch (Exception e) { alert = new Alert { AlertLevel = AlertLevel.Error, CreatedAt = SystemTime.UtcNow, Message = "Last SQL replication operation for " + sqlReplication.Name + " was failed", Title = "SQL replication error", Exception = e.ToString(), UniqueKey = "Sql Replication Error: " + sqlReplication.Name }; } return(resutls); }
public RelationalDatabaseWriter.TableQuerySummary[] SimulateSqlReplicationSqlQueries(string strDocumentId, SqlReplicationConfig sqlReplication, bool performRolledbackTransaction, out Alert alert) { RelationalDatabaseWriter.TableQuerySummary[] resutls = null; try { var stats = new SqlReplicationStatistics(sqlReplication.Name, false); var jsonDocument = Database.Documents.Get(strDocumentId, null); JsonDocument.EnsureIdInMetadata(jsonDocument); var doc = jsonDocument.ToJson(); doc[Constants.DocumentIdFieldName] = jsonDocument.Key; var docs = new List<ReplicatedDoc> { new ReplicatedDoc { Document = doc, Etag = jsonDocument.Etag, Key = jsonDocument.Key, SerializedSizeOnDisk = jsonDocument.SerializedSizeOnDisk } }; var scriptResult = ApplyConversionScript(sqlReplication, docs, stats); var connectionsDoc = Database.Documents.Get(Constants.RavenSqlReplicationConnectionsDocumentName, null); var sqlReplicationConnections = connectionsDoc != null ? connectionsDoc.DataAsJson.JsonDeserialization<SqlReplicationConnections>() : new SqlReplicationConnections(); if (PrepareSqlReplicationConfig(sqlReplication, sqlReplication.Name, stats, sqlReplicationConnections, false, false)) { if (performRolledbackTransaction) { using (var writer = new RelationalDatabaseWriter(Database, sqlReplication, stats)) { resutls = writer.RolledBackExecute(scriptResult).ToArray(); } } else { var simulatedwriter = new RelationalDatabaseWriterSimulator(Database, sqlReplication, stats); resutls = new List<RelationalDatabaseWriter.TableQuerySummary> { new RelationalDatabaseWriter.TableQuerySummary { Commands = simulatedwriter.SimulateExecuteCommandText(scriptResult) .Select(x => new RelationalDatabaseWriter.TableQuerySummary.CommandData { CommandText = x }).ToArray() } }.ToArray(); } } alert = stats.LastAlert; } catch (Exception e) { alert = new Alert { AlertLevel = AlertLevel.Error, CreatedAt = SystemTime.UtcNow, Message = "Last SQL replication operation for " + sqlReplication.Name + " was failed", Title = "SQL replication error", Exception = e.ToString(), UniqueKey = "Sql Replication Error: " + sqlReplication.Name }; } return resutls; }
private bool ReplicateChangesToDestination(SqlReplicationConfig cfg, ICollection<ReplicatedDoc> docs, out int countOfReplicatedItems) { countOfReplicatedItems = 0; var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name)); var scriptResult = ApplyConversionScript(cfg, docs, replicationStats); if (scriptResult.Ids.Count == 0) return true; countOfReplicatedItems = scriptResult.Data.Sum(x => x.Value.Count); try { using (var writer = new RelationalDatabaseWriter(Database, cfg, replicationStats)) { if (writer.Execute(scriptResult)) { Log.Debug("Replicated changes of {0} for replication {1}", string.Join(", ", docs.Select(d => d.Key)), cfg.Name); replicationStats.CompleteSuccess(countOfReplicatedItems); } else { Log.Debug("Replicated changes (with some errors) of {0} for replication {1}", string.Join(", ", docs.Select(d => d.Key)), cfg.Name); replicationStats.Success(countOfReplicatedItems); } } return true; } catch (Exception e) { Log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name, e); SqlReplicationStatistics replicationStatistics; DateTime newTime; if (statistics.TryGetValue(cfg.Name, out replicationStatistics) == false) { newTime = SystemTime.UtcNow.AddSeconds(5); } else { if (replicationStatistics.LastErrorTime == DateTime.MinValue) { newTime = SystemTime.UtcNow.AddSeconds(5); } else { // double the fallback time (but don't cross 15 minutes) var totalSeconds = (SystemTime.UtcNow - replicationStatistics.LastErrorTime).TotalSeconds; newTime = SystemTime.UtcNow.AddSeconds(Math.Min(60 * 15, Math.Max(5, totalSeconds * 2))); } } replicationStats.RecordWriteError(e, Database, countOfReplicatedItems, newTime); return false; } }
public RelationalDatabaseWriter.TableQuerySummary[] SimulateSqlReplicationSQLQueries(string strDocumentId, SqlReplicationConfig sqlReplication, bool performRolledbackTransaction, out Alert alert) { alert = null; RelationalDatabaseWriter.TableQuerySummary[] resutls = null; try { var stats = new SqlReplicationStatistics(sqlReplication.Name); var docs = new List<JsonDocument>() { Database.Documents.Get(strDocumentId, null) }; var scriptResult = ApplyConversionScript(sqlReplication, docs); var connectionsDoc = Database.Documents.Get(connectionsDocumentName, null); var sqlReplicationConnections = connectionsDoc.DataAsJson.JsonDeserialization<SqlReplicationConnections>(); if (PrepareSqlReplicationConfig( sqlReplication, sqlReplication.Name, stats, sqlReplicationConnections, false,false)) { if (performRolledbackTransaction) { using (var writer = new RelationalDatabaseWriter(Database, sqlReplication, stats)) { resutls = writer.RolledBackExecute(scriptResult).ToArray(); } } else { var simulatedwriter = new RelationalDatabaseWriterSimulator(Database, sqlReplication, stats); resutls = new List<RelationalDatabaseWriter.TableQuerySummary>() { new RelationalDatabaseWriter.TableQuerySummary() { Commands = simulatedwriter.SimulateExecuteCommandText(scriptResult) .Select(x => new RelationalDatabaseWriter.TableQuerySummary.CommandData() { CommandText = x }).ToArray() } }.ToArray(); } } alert = stats.LastAlert; } catch (Exception e) { alert = new Alert() { AlertLevel = AlertLevel.Error, CreatedAt = SystemTime.UtcNow, Message = "Last SQL replication operation for " + sqlReplication.Name + " was failed", Title = "SQL replication error", Exception = e.ToString(), UniqueKey = "Sql Replication Error: " + sqlReplication.Name }; } return resutls; }