private Dictionary <string, List <ItemToReplicate> > ApplyConversionScript(SqlReplicationConfig cfg, IEnumerable <JsonDocument> docs)
{
var dictionary = new Dictionary <string, List <ItemToReplicate> >();
foreach (var jsonDocument in docs)
{
if (string.IsNullOrEmpty(cfg.RavenEntityName) == false)
{
var entityName = jsonDocument.Metadata.Value <string>(Constants.RavenEntityName);
if (string.Equals(cfg.RavenEntityName, entityName, StringComparison.InvariantCultureIgnoreCase) == false)
{
continue;
}
}
var patcher = new SqlReplicationScriptedJsonPatcher(Database, dictionary, jsonDocument.Key);
try
{
DocumentRetriever.EnsureIdInMetadata(jsonDocument);
jsonDocument.Metadata[Constants.DocumentIdFieldName] = jsonDocument.Key;
var document = jsonDocument.ToJson();
patcher.Apply(document, new ScriptedPatchRequest
{
Script = cfg.Script
});
}
catch (Exception e)
{
log.WarnException("Could not process SQL Replication script for " + cfg.Name + ", skipping this document", e);
}
}
return(dictionary);
}
private DbProviderFactory GetDbProviderFactory(SqlReplicationConfig cfg)
{
DbProviderFactory providerFactory;
try
{
providerFactory = DbProviderFactories.GetFactory(cfg.FactoryName);
}
catch (Exception e)
{
log.WarnException(
string.Format("Could not find provider factory {0} to replicate to sql for {1}, ignoring", cfg.FactoryName,
cfg.Name), e);
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not find factory provider",
Message = string.Format("Could not find factory provider {0} to replicate to sql for {1}, ignoring", cfg.FactoryName,
cfg.Name),
UniqueKey = string.Format("Sql Replication Provider Not Found: {0}, {1}", cfg.Name, cfg.FactoryName)
});
throw;
}
return(providerFactory);
}
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 SqlReplicationMetricsCountersManager(Metrics dbMetrics, SqlReplicationConfig sqlReplicationConfig)
{
this.dbMetrics = dbMetrics;
this.sqlReplicationConfig = sqlReplicationConfig;
SqlReplicationBatchSizeMeter = dbMetrics.Meter("metrics", "SqlReplication Batch docs/min for " + sqlReplicationConfig.Name, "SQLReplication docs/min Counter", TimeUnit.Minutes);
SqlReplicationBatchSizeHistogram = dbMetrics.Histogram("metrics", "SqlReplication Batch histogram for " + sqlReplicationConfig.Name);
SqlReplicationDurationHistogram = dbMetrics.Histogram("metrics", "SQLReplication duration Histogram for " + sqlReplicationConfig.Name);
TablesMetrics = new ConcurrentDictionary<string, SqlReplicationTableMetrics>();
}
public SqlReplicationMetricsCountersManager(Metrics dbMetrics, SqlReplicationConfig sqlReplicationConfig)
{
this.dbMetrics = dbMetrics;
this.sqlReplicationConfig = sqlReplicationConfig;
SqlReplicationBatchSizeMeter = dbMetrics.Meter("metrics", "SqlReplication Batch docs/min for " + sqlReplicationConfig.Name, "SQLReplication docs/min Counter", TimeUnit.Minutes);
SqlReplicationBatchSizeHistogram = dbMetrics.Histogram("metrics", "SqlReplication Batch histogram for " + sqlReplicationConfig.Name);
SqlReplicationDurationHistogram = dbMetrics.Histogram("metrics", "SQLReplication duration Histogram for " + sqlReplicationConfig.Name);
TablesMetrics = new ConcurrentDictionary <string, SqlReplicationTableMetrics>();
}
public SqlReplicationTableMetrics(string tableName, SqlReplicationConfig cfg, Metrics dbMetrics)
{
this.dbMetrics = dbMetrics;
Config = cfg;
TableName = tableName;
deleteMeterName = "SqlReplication Deletes/min for table :" + TableName + " in replication: " + Config.Name;
insertMeterName = "SqlReplication Inserts/min for table :" + TableName + " in replication: " + Config.Name;
}
public SqlReplicationTableMetrics(string tableName, SqlReplicationConfig cfg, Metrics dbMetrics)
{
this.dbMetrics = dbMetrics;
Config = cfg;
TableName = tableName;
deleteMeterName = "SqlReplication Deletes/min for table :" + TableName + " in replication: " + Config.Name;
insertMeterName = "SqlReplication Inserts/min for table :" + TableName + " in replication: " + Config.Name;
}
public SqlReplicationScriptedJsonPatcher(DocumentDatabase database,
ConversionScriptResult scriptResult,
SqlReplicationConfig config,
string docId)
: base(database)
{
this.scriptResult = scriptResult;
this.config = config;
this.docId = docId;
}
public SqlReplicationScriptedJsonPatcher(DocumentDatabase database,
ConversionScriptResult scriptResult,
SqlReplicationConfig config,
string docId)
: base(database)
{
this.scriptResult = scriptResult;
this.config = config;
this.docId = docId;
}
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 ConversionScriptResult ApplyConversionScript(SqlReplicationConfig cfg, IEnumerable <JsonDocument> docs)
{
var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name));
var result = new ConversionScriptResult();
foreach (var jsonDocument in docs)
{
Database.WorkContext.CancellationToken.ThrowIfCancellationRequested();
if (string.IsNullOrEmpty(cfg.RavenEntityName) == false)
{
var entityName = jsonDocument.Metadata.Value <string>(Constants.RavenEntityName);
if (string.Equals(cfg.RavenEntityName, entityName, StringComparison.InvariantCultureIgnoreCase) == false)
{
continue;
}
}
var patcher = new SqlReplicationScriptedJsonPatcher(Database, result, cfg, jsonDocument.Key);
using (var scope = new SqlReplicationScriptedJsonPatcherOperationScope(Database))
{
try
{
DocumentRetriever.EnsureIdInMetadata(jsonDocument);
var document = jsonDocument.ToJson();
document[Constants.DocumentIdFieldName] = jsonDocument.Key;
patcher.Apply(scope, document, new ScriptedPatchRequest {
Script = cfg.Script
}, jsonDocument.SerializedSizeOnDisk);
if (log.IsDebugEnabled && patcher.Debug.Count > 0)
{
log.Debug("Debug output for doc: {0} for script {1}:\r\n.{2}", jsonDocument.Key, cfg.Name, string.Join("\r\n", patcher.Debug));
patcher.Debug.Clear();
}
replicationStats.ScriptSuccess();
}
catch (ParseException e)
{
replicationStats.MarkScriptAsInvalid(Database, cfg.Script);
log.WarnException("Could not parse SQL Replication script for " + cfg.Name, e);
return(result);
}
catch (Exception e)
{
replicationStats.RecordScriptError(Database);
log.WarnException("Could not process SQL Replication script for " + cfg.Name + ", skipping document: " + jsonDocument.Key, e);
}
}
}
return(result);
}
public ElasticsearchReplicationConfig(SqlReplicationConfig other)
{
this.ConnectionString = other.ConnectionString;
this.FactoryName = other.FactoryName;
this.Script = other.Script;
this.Disabled = other.Disabled;
this.Id = other.Id;
this.Name = other.Name;
this.RavenEntityName = other.RavenEntityName;
this.SqlReplicationTables = other.SqlReplicationTables;
}
public RelationalDatabaseWriterSimulator( DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = DbProviderFactories.GetFactory(cfg.FactoryName);
commandBuilder = providerFactory.CreateCommandBuilder();
if (SqlServerFactoryNames.Contains(cfg.FactoryName))
{
IsSqlServerFactoryType = true;
}
}
private Etag GetLastEtagFor(SqlReplicationStatus replicationStatus, SqlReplicationConfig sqlReplicationConfig)
{
var lastEtag = Etag.Empty;
var lastEtagHolder = replicationStatus.LastReplicatedEtags.FirstOrDefault(
x => string.Equals(sqlReplicationConfig.Name, x.Name, StringComparison.InvariantCultureIgnoreCase));
if (lastEtagHolder != null)
{
lastEtag = lastEtagHolder.LastDocEtag;
}
return(lastEtag);
}
public RelationalDatabaseWriterSimulator(DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = DbProviderFactories.GetFactory(cfg.FactoryName);
commandBuilder = providerFactory.CreateCommandBuilder();
if (SqlServerFactoryNames.Contains(cfg.FactoryName))
{
IsSqlServerFactoryType = true;
}
}
public SqlReplicationMetricsCountersManager(Metrics dbMetrics, SqlReplicationConfig sqlReplicationConfig)
{
this.dbMetrics = dbMetrics;
this.sqlReplicationConfig = sqlReplicationConfig;
meterName = "SqlReplication Batch docs/min for " + sqlReplicationConfig.Name;
SqlReplicationBatchSizeMeter = dbMetrics.Meter(MeterContext, meterName, "SQLReplication docs/min Counter", TimeUnit.Minutes);
MetricsTicker.Instance.AddMeterMetric(SqlReplicationBatchSizeMeter);
SqlReplicationBatchSizeHistogram = dbMetrics.Histogram("metrics", "SqlReplication Batch histogram for " + sqlReplicationConfig.Name);
SqlReplicationDurationHistogram = dbMetrics.Histogram("metrics", "SQLReplication duration Histogram for " + sqlReplicationConfig.Name);
TablesMetrics = new ConcurrentDictionary<string, SqlReplicationTableMetrics>();
ReplicationPerformanceStats = new ConcurrentQueue<SqlReplicationPerformanceStats>();
}
public SqlReplicationMetricsCountersManager(Metrics dbMetrics, SqlReplicationConfig sqlReplicationConfig)
{
this.dbMetrics = dbMetrics;
this.sqlReplicationConfig = sqlReplicationConfig;
meterName = "SqlReplication Batch docs/min for " + sqlReplicationConfig.Name;
SqlReplicationBatchSizeMeter = dbMetrics.Meter(MeterContext, meterName, "SQLReplication docs/min Counter", TimeUnit.Minutes);
MetricsTicker.Instance.AddMeterMetric(SqlReplicationBatchSizeMeter);
SqlReplicationBatchSizeHistogram = dbMetrics.Histogram("metrics", "SqlReplication Batch histogram for " + sqlReplicationConfig.Name);
SqlReplicationDurationHistogram = dbMetrics.Histogram("metrics", "SQLReplication duration Histogram for " + sqlReplicationConfig.Name);
TablesMetrics = new ConcurrentDictionary<string, SqlReplicationTableMetrics>();
ReplicationPerformanceStats = new ConcurrentQueue<SqlReplicationPerformanceStats>();
}
public RelationalDatabaseWriter(DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = GetDbProviderFactory(cfg);
commandBuilder = providerFactory.CreateCommandBuilder();
connection = providerFactory.CreateConnection();
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
if (SqlServerFactoryNames.Contains(cfg.FactoryName))
{
IsSqlServerFactoryType = true;
}
try
{
connection.Open();
}
catch (Exception e)
{
var message = "Sql Replication could not open connection to " + connection.ConnectionString;
log.Error(message);
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = message,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
tx = connection.BeginTransaction();
stringParserList = GenerateStringParsers();
sqlReplicationMetrics = database.StartupTasks.OfType <SqlReplicationTask>().FirstOrDefault().GetSqlReplicationMetricsManager(cfg);
}
private void UpdateReplicationPerformance(SqlReplicationConfig replicationConfig, DateTime startTime, TimeSpan elapsed, int batchSize)
{
var performance = new SqlReplicationPerformanceStats
{
BatchSize = batchSize,
Duration = elapsed,
Started = startTime
};
var sqlReplicationMetricsCounters = GetSqlReplicationMetricsManager(replicationConfig);
sqlReplicationMetricsCounters.ReplicationPerformanceStats.Enqueue(performance);
while (sqlReplicationMetricsCounters.ReplicationPerformanceStats.Count() > 25)
{
SqlReplicationPerformanceStats _;
sqlReplicationMetricsCounters.ReplicationPerformanceStats.TryDequeue(out _);
}
}
private ConversionScriptResult ApplyConversionScript(SqlReplicationConfig cfg, IEnumerable <JsonDocument> docs)
{
var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name));
var result = new ConversionScriptResult();
foreach (var jsonDocument in docs)
{
if (string.IsNullOrEmpty(cfg.RavenEntityName) == false)
{
var entityName = jsonDocument.Metadata.Value <string>(Constants.RavenEntityName);
if (string.Equals(cfg.RavenEntityName, entityName, StringComparison.InvariantCultureIgnoreCase) == false)
{
continue;
}
}
var patcher = new SqlReplicationScriptedJsonPatcher(Database, result, cfg, jsonDocument.Key);
try
{
DocumentRetriever.EnsureIdInMetadata(jsonDocument);
jsonDocument.Metadata[Constants.DocumentIdFieldName] = jsonDocument.Key;
var document = jsonDocument.ToJson();
patcher.Apply(document, new ScriptedPatchRequest
{
Script = cfg.Script
});
replicationStats.ScriptSuccess();
}
catch (ParseException e)
{
replicationStats.MarkScriptAsInvalid(Database, cfg.Script);
log.WarnException("Could parse SQL Replication script for " + cfg.Name, e);
return(result);
}
catch (Exception e)
{
replicationStats.RecordScriptError(Database);
log.WarnException("Could not process SQL Replication script for " + cfg.Name + ", skipping this document", e);
}
}
return(result);
}
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);
}
}
public RelationalDatabaseWriter( DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = GetDbProviderFactory(cfg);
commandBuilder = providerFactory.CreateCommandBuilder();
connection = providerFactory.CreateConnection();
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
if (SqlServerFactoryNames.Contains(cfg.FactoryName))
{
IsSqlServerFactoryType = true;
}
try
{
connection.Open();
}
catch (Exception e)
{
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
tx = connection.BeginTransaction();
stringParserList = GenerateStringParsers();
sqlReplicationMetrics = database.StartupTasks.OfType<SqlReplicationTask>().FirstOrDefault().GetSqlReplicationMetricsManager(cfg);
}
public SqlReplicationConfigWithLastReplicatedEtag(SqlReplicationConfig config, Etag lastReplicatedEtag)
{
LastReplicatedEtag = lastReplicatedEtag;
ConnectionString = config.ConnectionString;
ConnectionStringName = config.ConnectionStringName;
ConnectionStringSettingName = config.ConnectionStringSettingName;
Disabled = config.Disabled;
FactoryName = config.FactoryName;
ForceSqlServerQueryRecompile = config.ForceSqlServerQueryRecompile;
Id = config.Id;
Name = config.Name;
ParameterizeDeletesDisabled = config.ParameterizeDeletesDisabled;
PredefinedConnectionStringSettingName = config.PredefinedConnectionStringSettingName;
QuoteTables = config.QuoteTables;
RavenEntityName = config.RavenEntityName;
Script = config.Script;
SqlReplicationTables = config.SqlReplicationTables;
}
public SqlReplicationConfigWithLastReplicatedEtag(SqlReplicationConfig config, Etag lastReplicatedEtag)
{
LastReplicatedEtag = lastReplicatedEtag;
ConnectionString = config.ConnectionString;
ConnectionStringName = config.ConnectionStringName;
ConnectionStringSettingName = config.ConnectionStringSettingName;
Disabled = config.Disabled;
FactoryName = config.FactoryName;
ForceSqlServerQueryRecompile = config.ForceSqlServerQueryRecompile;
Id = config.Id;
Name = config.Name;
ParameterizeDeletesDisabled = config.ParameterizeDeletesDisabled;
PredefinedConnectionStringSettingName = config.PredefinedConnectionStringSettingName;
QuoteTables = config.QuoteTables;
RavenEntityName = config.RavenEntityName;
Script = config.Script;
SqlReplicationTables = config.SqlReplicationTables;
}
private DbProviderFactory TryGetDbProviderFactory(SqlReplicationConfig cfg)
{
DbProviderFactory providerFactory;
try
{
providerFactory = DbProviderFactories.GetFactory(cfg.FactoryName);
}
catch (Exception e)
{
log.WarnException(
string.Format("Could not find provider factory {0} to replicate to sql for {1}, ignoring", cfg.FactoryName,
cfg.Name), e);
lastError[cfg.Name] = DateTime.MaxValue; // always error
return(null);
}
return(providerFactory);
}
public ElasticsearchDestinationWriter(DocumentDatabase database, SqlReplicationConfig _cfg, SqlReplicationStatistics replicationStatistics)
{
var cfg = new ElasticsearchReplicationConfig(_cfg);
this.database = database;
this.cfg = cfg;
this.targetIndexName = cfg.FactoryName.ToLowerInvariant(); // Elasticsearch requires all index names to be lowercased
this.replicationStatistics = replicationStatistics;
try
{
elasticsearchClient = cfg.GetElasticClient();
}
catch (UriFormatException e)
{
if (database != null)
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Invalid Elasticsearch URL provided",
Message = "Elasticsearch Replication could not parse one of the provided node URLs",
UniqueKey = "Elasticsearch Replication Connection Error: " + cfg.ConnectionString
});
}
catch (Exception e)
{
if (database != null)
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Elasticsearch Replication could not open connection",
Message = "Elasticsearch Replication could not open connection to " + cfg.ConnectionString,
UniqueKey = "Elasticsearch Replication Connection Error: " + cfg.ConnectionString
});
throw;
}
}
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 bool ReplicateToDesintation(SqlReplicationConfig cfg, IEnumerable <JsonDocument> docs)
{
var providerFactory = TryGetDbProviderFactory(cfg);
if (providerFactory == null)
{
return(false);
}
var dictionary = ApplyConversionScript(cfg, docs);
if (dictionary.Count == 0)
{
return(true);
}
try
{
WriteToRelationalDatabase(cfg, providerFactory, dictionary);
return(true);
}
catch (Exception e)
{
log.WarnException("Failure to replicate changes to relational database for " + cfg.Name + ", updates", e);
DateTime time, newTime;
if (lastError.TryGetValue(cfg.Name, out time) == false)
{
newTime = SystemTime.UtcNow.AddMinutes(1);
}
else
{
var totalMinutes = (SystemTime.UtcNow - time).TotalMinutes;
newTime = SystemTime.UtcNow.AddMinutes(Math.Max(10, Math.Min(1, totalMinutes + 1)));
}
lastError[cfg.Name] = newTime;
return(false);
}
}
public RelationalDatabaseWriter(DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = GetDbProviderFactory(cfg);
commandBuilder = providerFactory.CreateCommandBuilder();
connection = providerFactory.CreateConnection();
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
tx = connection.BeginTransaction();
}
public RelationalDatabaseWriter( DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = GetDbProviderFactory(cfg);
commandBuilder = providerFactory.CreateCommandBuilder();
connection = providerFactory.CreateConnection();
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
tx = connection.BeginTransaction();
}
private bool ReplicateToDesintation(SqlReplicationConfig cfg, IEnumerable<JsonDocument> docs)
{
var providerFactory = TryGetDbProviderFactory(cfg);
if (providerFactory == null)
return false;
var dictionary = ApplyConversionScript(cfg, docs);
if (dictionary.Count == 0)
return true;
var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name));
var countOfItems = dictionary.Sum(x => x.Value.Count);
try
{
if (WriteToRelationalDatabase(cfg, providerFactory, dictionary, replicationStats))
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;
}
}
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);
}
public SqlReplicationTableMetrics(string tableName, SqlReplicationConfig cfg, Metrics dbMetrics)
{
this.dbMetrics = dbMetrics;
Config = cfg;
TableName = tableName;
}
public SqlReplicationMetricsCountersManager GetSqlReplicationMetricsManager(SqlReplicationConfig cfg)
{
return(SqlReplicationMetricsCounters.GetOrAdd(cfg.Name,
s => new SqlReplicationMetricsCountersManager(sqlReplicationMetrics, cfg)
));
}
private Dictionary<string, List<ItemToReplicate>> ApplyConversionScript(SqlReplicationConfig cfg, IEnumerable<JsonDocument> docs)
{
var dictionary = new Dictionary<string, List<ItemToReplicate>>();
foreach (var jsonDocument in docs)
{
if (string.IsNullOrEmpty(cfg.RavenEntityName) == false)
{
var entityName = jsonDocument.Metadata.Value<string>(Constants.RavenEntityName);
if (string.Equals(cfg.RavenEntityName, entityName, StringComparison.InvariantCultureIgnoreCase) == false)
continue;
}
var patcher = new SqlReplicationScriptedJsonPatcher(Database, dictionary, jsonDocument.Key);
try
{
DocumentRetriever.EnsureIdInMetadata(jsonDocument);
jsonDocument.Metadata[Constants.DocumentIdFieldName] = jsonDocument.Key;
var document = jsonDocument.ToJson();
patcher.Apply(document, new ScriptedPatchRequest
{
Script = cfg.Script
});
}
catch (Exception e)
{
log.WarnException("Could not process SQL Replication script for " + cfg.Name + ", skipping this document", e);
}
}
return dictionary;
}
private DbProviderFactory TryGetDbProviderFactory(SqlReplicationConfig cfg)
{
DbProviderFactory providerFactory;
try
{
providerFactory = DbProviderFactories.GetFactory(cfg.FactoryName);
}
catch (Exception e)
{
log.WarnException(
string.Format("Could not find provider factory {0} to replicate to sql for {1}, ignoring", cfg.FactoryName,
cfg.Name), e);
lastError[cfg.Name] = DateTime.MaxValue; // always error
return null;
}
return providerFactory;
}
private bool PrepareSqlReplicationConfig(SqlReplicationConfig cfg, string sqlReplicationConfigDocumentKey, SqlReplicationStatistics replicationStats, SqlReplicationConnections <SqlReplicationConnections.PredefinedSqlConnectionWithConfigurationOrigin> sqlReplicationConnections, bool writeToLog = true, bool validateSqlReplicationName = true)
{
if (validateSqlReplicationName && string.IsNullOrWhiteSpace(cfg.Name))
{
if (writeToLog)
{
Log.Warn("Could not find name for sql replication document {0}, ignoring", sqlReplicationConfigDocumentKey);
}
replicationStats.LastAlert = new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = DateTime.UtcNow,
Title = "Could not start replication",
Message = string.Format("Could not find name for sql replication document {0}, ignoring", sqlReplicationConfigDocumentKey)
};
return(false);
}
if (string.IsNullOrWhiteSpace(cfg.PredefinedConnectionStringSettingName) == false)
{
var matchingConnection = sqlReplicationConnections.PredefinedConnections.FirstOrDefault(x => string.Compare(x.Name, cfg.PredefinedConnectionStringSettingName, StringComparison.InvariantCultureIgnoreCase) == 0);
if (matchingConnection != null)
{
cfg.ConnectionString = matchingConnection.ConnectionString;
cfg.FactoryName = matchingConnection.FactoryName;
}
else
{
if (writeToLog)
{
Log.Warn("Could not find predefined connection string named '{0}' for sql replication config: {1}, ignoring sql replication setting.",
cfg.PredefinedConnectionStringSettingName,
sqlReplicationConfigDocumentKey);
}
replicationStats.LastAlert = new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = DateTime.UtcNow,
Title = "Could not start replication",
Message = string.Format("Could not find predefined connection string named '{0}' for sql replication config: {1}, ignoring sql replication setting.",
cfg.PredefinedConnectionStringSettingName,
sqlReplicationConfigDocumentKey)
};
return(false);
}
}
else if (string.IsNullOrWhiteSpace(cfg.ConnectionStringName) == false)
{
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[cfg.ConnectionStringName];
if (connectionString == null)
{
if (writeToLog)
{
Log.Warn("Could not find connection string named '{0}' for sql replication config: {1}, ignoring sql replication setting.",
cfg.ConnectionStringName, sqlReplicationConfigDocumentKey);
}
replicationStats.LastAlert = new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = DateTime.UtcNow,
Title = "Could not start replication",
Message = string.Format("Could not find connection string named '{0}' for sql replication config: {1}, ignoring sql replication setting.",
cfg.ConnectionStringName,
sqlReplicationConfigDocumentKey)
};
return(false);
}
cfg.ConnectionString = connectionString.ConnectionString;
}
else if (string.IsNullOrWhiteSpace(cfg.ConnectionStringSettingName) == false)
{
var setting = Database.Configuration.Settings[cfg.ConnectionStringSettingName];
if (string.IsNullOrWhiteSpace(setting))
{
if (writeToLog)
{
Log.Warn("Could not find setting named '{0}' for sql replication config: {1}, ignoring sql replication setting.",
cfg.ConnectionStringSettingName,
sqlReplicationConfigDocumentKey);
}
replicationStats.LastAlert = new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = DateTime.UtcNow,
Title = "Could not start replication",
Message = string.Format("Could not find setting named '{0}' for sql replication config: {1}, ignoring sql replication setting.",
cfg.ConnectionStringSettingName,
sqlReplicationConfigDocumentKey)
};
return(false);
}
}
return(true);
}
private bool WriteToRelationalDatabase(SqlReplicationConfig cfg, DbProviderFactory providerFactory, Dictionary<string, List<ItemToReplicate>> dictionary, SqlReplicationStatistics replicationStatistics)
{
using (var commandBuilder = providerFactory.CreateCommandBuilder())
using (var connection = providerFactory.CreateConnection())
{
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
Database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
bool hadErrors = false;
using (var tx = connection.BeginTransaction())
{
const string cmdText = "DELETE FROM {0} WHERE {1} IN ({2})";
foreach (var kvp in dictionary)
{
// array of ids to delete
List<string> ids = kvp.Value.Select(x => x.DocumentId).ToList();
foreach (var partitionOfIds in ids.Partition(1000))
{
using (var deleteCmd = connection.CreateCommand())
{
// create an array of param names to store our ids (@id1, @id2 etc)
string[] paramNames = partitionOfIds.Select(
(s, i) => "@id" + i.ToString()
).ToArray();
// add a parameter for each id to our command
string inClause = string.Join(",", paramNames);
for (int i = 0; i < paramNames.Length; i++)
{
var param = deleteCmd.CreateParameter();
param.ParameterName = paramNames[i];
param.Value = partitionOfIds[i];
deleteCmd.Parameters.Add(param);
}
deleteCmd.Transaction = tx;
deleteCmd.CommandText = string.Format(cmdText,
commandBuilder.QuoteIdentifier(kvp.Key),
commandBuilder.QuoteIdentifier(
kvp.Value[0].PkName),
inClause);
try
{
deleteCmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.WarnException(
"Failure to replicate changes to relational database for: " + cfg.Name +
" (delete), will continue trying." + Environment.NewLine +
deleteCmd.CommandText, e);
replicationStatistics.RecordWriteError(e, Database);
hadErrors = true;
}
}
}
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var sb = new StringBuilder("INSERT INTO ")
.Append(commandBuilder.QuoteIdentifier(kvp.Key))
.Append(" (")
.Append(commandBuilder.QuoteIdentifier(itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
pkParam.Value = itemToReplicate.DocumentId;
cmd.Parameters.Add(pkParam);
sb.Append(") \r\nVALUES (")
.Append(GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
var colParam = cmd.CreateParameter();
colParam.ParameterName = column.Key;
SetParamValue(colParam, column.Value);
cmd.Parameters.Add(colParam);
sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
cmd.CommandText = sb.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name + " (insert), will continue trying." + Environment.NewLine + cmd.CommandText, e);
replicationStatistics.RecordWriteError(e, Database);
hadErrors = true;
}
}
}
}
tx.Commit();
}
return hadErrors == false;
}
}
private void WriteToRelationalDatabase(SqlReplicationConfig cfg, DbProviderFactory providerFactory,
Dictionary <string, List <ItemToReplicate> > dictionary)
{
using (var commandBuilder = providerFactory.CreateCommandBuilder())
using (var connection = providerFactory.CreateConnection())
{
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
connection.Open();
using (var tx = connection.BeginTransaction())
{
foreach (var kvp in dictionary)
{
// first, delete all the rows that might already exist there
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
cmd.Parameters.Add(dbParameter);
dbParameter.Value = itemToReplicate.DocumentId;
cmd.CommandText = string.Format("DELETE FROM {0} WHERE {1} = {2}",
commandBuilder.QuoteIdentifier(kvp.Key),
commandBuilder.QuoteIdentifier(itemToReplicate.PkName),
dbParameter.ParameterName
);
cmd.ExecuteNonQuery();
}
}
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var sb = new StringBuilder("INSERT INTO ")
.Append(commandBuilder.QuoteIdentifier(kvp.Key))
.Append(" (")
.Append(commandBuilder.QuoteIdentifier(itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
{
continue;
}
sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
pkParam.Value = itemToReplicate.DocumentId;
cmd.Parameters.Add(pkParam);
sb.Append(") \r\nVALUES (")
.Append(GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
{
continue;
}
var colParam = cmd.CreateParameter();
colParam.ParameterName = column.Key;
SetParamValue(colParam, column.Value);
cmd.Parameters.Add(colParam);
sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
}
}
tx.Commit();
}
}
}
private bool HasChanges(SqlReplicationConfigModel local, SqlReplicationConfig remote)
{
if (remote == null)
return false;
if (local.RavenEntityName != remote.RavenEntityName)
return true;
if (local.Script != remote.Script)
return true;
if (local.Disabled != remote.Disabled)
return true;
if (local.ConnectionString != remote.ConnectionString)
return true;
if (local.ConnectionStringName != remote.ConnectionStringName)
return true;
if (local.ConnectionStringSettingName != remote.ConnectionStringSettingName)
return true;
if (local.FactoryName != remote.FactoryName)
return true;
return false;
}
private void WriteToRelationalDatabase(SqlReplicationConfig cfg, DbProviderFactory providerFactory,
Dictionary<string, List<ItemToReplicate>> dictionary)
{
using (var commandBuilder = providerFactory.CreateCommandBuilder())
using (var connection = providerFactory.CreateConnection())
{
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
connection.Open();
using (var tx = connection.BeginTransaction())
{
foreach (var kvp in dictionary)
{
// first, delete all the rows that might already exist there
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
cmd.Parameters.Add(dbParameter);
dbParameter.Value = itemToReplicate.DocumentId;
cmd.CommandText = string.Format("DELETE FROM {0} WHERE {1} = {2}",
commandBuilder.QuoteIdentifier(kvp.Key),
commandBuilder.QuoteIdentifier(itemToReplicate.PkName),
dbParameter.ParameterName
);
cmd.ExecuteNonQuery();
}
}
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var sb = new StringBuilder("INSERT INTO ")
.Append(commandBuilder.QuoteIdentifier(kvp.Key))
.Append(" (")
.Append(commandBuilder.QuoteIdentifier(itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
pkParam.Value = itemToReplicate.DocumentId;
cmd.Parameters.Add(pkParam);
sb.Append(") \r\nVALUES (")
.Append(GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
var colParam = cmd.CreateParameter();
colParam.ParameterName = column.Key;
SetParamValue(colParam, column.Value);
cmd.Parameters.Add(colParam);
sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
}
}
tx.Commit();
}
}
}
private static string GetSqlReplicationDeletionName(SqlReplicationConfig replicationConfig)
{
return "SqlReplication/Deleteions/" + replicationConfig.Name;
}
private DbProviderFactory GetDbProviderFactory(SqlReplicationConfig cfg)
{
DbProviderFactory providerFactory;
try
{
providerFactory = DbProviderFactories.GetFactory(cfg.FactoryName);
}
catch (Exception e)
{
log.WarnException(
string.Format("Could not find provider factory {0} to replicate to sql for {1}, ignoring", cfg.FactoryName,
cfg.Name), e);
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not find factory provider",
Message = string.Format("Could not find factory provider {0} to replicate to sql for {1}, ignoring", cfg.FactoryName,
cfg.Name),
UniqueKey = string.Format("Sql Replication Provider Not Found: {0}, {1}", cfg.Name, cfg.FactoryName)
});
throw;
}
return providerFactory;
}
private ConversionScriptResult ApplyConversionScript(SqlReplicationConfig cfg, IEnumerable<JsonDocument> docs)
{
var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name));
var result = new ConversionScriptResult();
foreach (var jsonDocument in docs)
{
if (string.IsNullOrEmpty(cfg.RavenEntityName) == false)
{
var entityName = jsonDocument.Metadata.Value<string>(Constants.RavenEntityName);
if (string.Equals(cfg.RavenEntityName, entityName, StringComparison.InvariantCultureIgnoreCase) == false)
continue;
}
var patcher = new SqlReplicationScriptedJsonPatcher(Database, result, cfg, jsonDocument.Key);
try
{
DocumentRetriever.EnsureIdInMetadata(jsonDocument);
jsonDocument.Metadata[Constants.DocumentIdFieldName] = jsonDocument.Key;
var document = jsonDocument.ToJson();
patcher.Apply(document, new ScriptedPatchRequest
{
Script = cfg.Script
});
replicationStats.ScriptSuccess();
}
catch (ParseException e)
{
replicationStats.MarkScriptAsInvalid(Database, cfg.Script);
log.WarnException("Could parse SQL Replication script for " + cfg.Name, e);
return result;
}
catch (Exception e)
{
replicationStats.RecordScriptError(Database);
log.WarnException("Could not process SQL Replication script for " + cfg.Name + ", skipping this document", e);
}
}
return result;
}
private ConversionScriptResult ApplyConversionScript(SqlReplicationConfig cfg, IEnumerable<JsonDocument> docs)
{
var replicationStats = statistics.GetOrAdd(cfg.Name, name => new SqlReplicationStatistics(name));
var result = new ConversionScriptResult();
foreach (var jsonDocument in docs)
{
Database.WorkContext.CancellationToken.ThrowIfCancellationRequested();
if (string.IsNullOrEmpty(cfg.RavenEntityName) == false)
{
var entityName = jsonDocument.Metadata.Value<string>(Constants.RavenEntityName);
if (string.Equals(cfg.RavenEntityName, entityName, StringComparison.InvariantCultureIgnoreCase) == false)
continue;
}
var patcher = new SqlReplicationScriptedJsonPatcher(Database, result, cfg, jsonDocument.Key);
try
{
DocumentRetriever.EnsureIdInMetadata(jsonDocument);
var document = jsonDocument.ToJson();
document[Constants.DocumentIdFieldName] = jsonDocument.Key;
patcher.Apply(document, new ScriptedPatchRequest
{
Script = cfg.Script
}, jsonDocument.SerializedSizeOnDisk);
if (log.IsDebugEnabled && patcher.Debug.Count > 0)
{
log.Debug("Debug output for doc: {0} for script {1}:\r\n.{2}", jsonDocument.Key, cfg.Name, string.Join("\r\n", patcher.Debug));
patcher.Debug.Clear();
}
replicationStats.ScriptSuccess();
}
catch (ParseException e)
{
replicationStats.MarkScriptAsInvalid(Database, cfg.Script);
log.WarnException("Could not parse SQL Replication script for " + cfg.Name, e);
return result;
}
catch (Exception e)
{
replicationStats.RecordScriptError(Database);
log.WarnException("Could not process SQL Replication script for " + cfg.Name + ", skipping document: " + jsonDocument.Key, e);
}
}
return result;
}
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 ReplicateToDesintation(SqlReplicationConfig cfg, IEnumerable<JsonDocument> docs)
{
var providerFactory = TryGetDbProviderFactory(cfg);
if (providerFactory == null)
return false;
var dictionary = ApplyConversionScript(cfg, docs);
if (dictionary.Count == 0)
return true;
try
{
WriteToRelationalDatabase(cfg, providerFactory, dictionary);
return true;
}
catch (Exception e)
{
log.WarnException("Failure to replicate changes to relational database for " + cfg.Name +", updates", e);
DateTime time, newTime;
if (lastError.TryGetValue(cfg.Name, out time) == false)
{
newTime = SystemTime.UtcNow.AddMinutes(1);
}
else
{
var totalMinutes = (SystemTime.UtcNow - time).TotalMinutes;
newTime = SystemTime.UtcNow.AddMinutes(Math.Max(10, Math.Min(1, totalMinutes + 1)));
}
lastError[cfg.Name] = newTime;
return false;
}
}
private bool PrepareSqlReplicationConfig(SqlReplicationConfig cfg, string sqlReplicationConfigDocumentKey, SqlReplicationStatistics replicationStats, SqlReplicationConnections sqlReplicationConnections, bool writeToLog = true, bool validateSqlReplicationName = true)
{
if (validateSqlReplicationName && string.IsNullOrWhiteSpace(cfg.Name))
{
if (writeToLog)
Log.Warn("Could not find name for sql replication document {0}, ignoring", sqlReplicationConfigDocumentKey);
replicationStats.LastAlert = new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = DateTime.UtcNow,
Title = "Could not start replication",
Message = string.Format("Could not find name for sql replication document {0}, ignoring", sqlReplicationConfigDocumentKey)
};
return false;
}
if (string.IsNullOrWhiteSpace(cfg.PredefinedConnectionStringSettingName) == false)
{
var matchingConnection = sqlReplicationConnections.PredefinedConnections.FirstOrDefault(x => string.Compare(x.Name, cfg.PredefinedConnectionStringSettingName, StringComparison.InvariantCultureIgnoreCase) == 0);
if (matchingConnection != null)
{
cfg.ConnectionString = matchingConnection.ConnectionString;
cfg.FactoryName = matchingConnection.FactoryName;
}
else
{
if (writeToLog)
Log.Warn("Could not find predefined connection string named '{0}' for sql replication config: {1}, ignoring sql replication setting.",
cfg.PredefinedConnectionStringSettingName,
sqlReplicationConfigDocumentKey);
replicationStats.LastAlert = new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = DateTime.UtcNow,
Title = "Could not start replication",
Message = string.Format("Could not find predefined connection string named '{0}' for sql replication config: {1}, ignoring sql replication setting.",
cfg.PredefinedConnectionStringSettingName,
sqlReplicationConfigDocumentKey)
};
return false;
}
}
else if (string.IsNullOrWhiteSpace(cfg.ConnectionStringName) == false)
{
var connectionString = ConfigurationManager.ConnectionStrings[cfg.ConnectionStringName];
if (connectionString == null)
{
if (writeToLog)
Log.Warn("Could not find connection string named '{0}' for sql replication config: {1}, ignoring sql replication setting.",
cfg.ConnectionStringName, sqlReplicationConfigDocumentKey);
replicationStats.LastAlert = new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = DateTime.UtcNow,
Title = "Could not start replication",
Message = string.Format("Could not find connection string named '{0}' for sql replication config: {1}, ignoring sql replication setting.",
cfg.ConnectionStringName,
sqlReplicationConfigDocumentKey)
};
return false;
}
cfg.ConnectionString = connectionString.ConnectionString;
}
else if (string.IsNullOrWhiteSpace(cfg.ConnectionStringSettingName) == false)
{
var setting = Database.Configuration.Settings[cfg.ConnectionStringSettingName];
if (string.IsNullOrWhiteSpace(setting))
{
if (writeToLog)
Log.Warn("Could not find setting named '{0}' for sql replication config: {1}, ignoring sql replication setting.",
cfg.ConnectionStringSettingName,
sqlReplicationConfigDocumentKey);
replicationStats.LastAlert = new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = DateTime.UtcNow,
Title = "Could not start replication",
Message = string.Format("Could not find setting named '{0}' for sql replication config: {1}, ignoring sql replication setting.",
cfg.ConnectionStringSettingName,
sqlReplicationConfigDocumentKey)
};
return false;
}
}
return true;
}
private static string GetSqlReplicationDeletionName(SqlReplicationConfig replicationConfig)
{
return("SqlReplication/Deletions/" + replicationConfig.Name);
}
public RelationalDatabaseWriter(DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = GetDbProviderFactory(cfg);
commandBuilder = providerFactory.CreateCommandBuilder();
connection = providerFactory.CreateConnection();
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
tx = connection.BeginTransaction();
stringParserList = new List <Func <DbParameter, string, bool> > {
(colParam, value) => {
if (char.IsDigit(value[0]))
{
DateTime dateTime;
if (DateTime.TryParseExact(value, Default.OnlyDateTimeFormat, CultureInfo.InvariantCulture,
DateTimeStyles.RoundtripKind, out dateTime))
{
switch (providerFactory.GetType( ).Name)
{
case "MySqlClientFactory":
colParam.Value = dateTime.ToString("yyyy-MM-dd HH:mm:ss.ffffff");
break;
default:
colParam.Value = dateTime;
break;
}
return(true);
}
}
return(false);
},
(colParam, value) => {
if (char.IsDigit(value[0]))
{
DateTimeOffset dateTimeOffset;
if (DateTimeOffset.TryParseExact(value, Default.DateTimeFormatsToRead, CultureInfo.InvariantCulture,
DateTimeStyles.RoundtripKind, out dateTimeOffset))
{
switch (providerFactory.GetType( ).Name)
{
case "MySqlClientFactory":
colParam.Value = dateTimeOffset.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.ffffff");
break;
default:
colParam.Value = dateTimeOffset;
break;
}
return(true);
}
}
return(false);
}
};
}
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 SqlReplicationMetricsCountersManager GetSqlReplicationMetricsManager(SqlReplicationConfig cfg)
{
return SqlReplicationMetricsCounters.GetOrAdd(cfg.Name,
s => new SqlReplicationMetricsCountersManager(sqlReplicationMetrics, cfg)
);
}
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 void UpdateReplicationPerformance(SqlReplicationConfig replicationConfig, DateTime startTime, TimeSpan elapsed, int batchSize)
{
var performance = new SqlReplicationPerformanceStats
{
BatchSize = batchSize,
Duration = elapsed,
Started = startTime
};
var sqlReplicationMetricsCounters = GetSqlReplicationMetricsManager(replicationConfig);
sqlReplicationMetricsCounters.ReplicationPerformanceStats.Enqueue(performance);
while (sqlReplicationMetricsCounters.ReplicationPerformanceStats.Count() > 25)
{
SqlReplicationPerformanceStats _;
sqlReplicationMetricsCounters.ReplicationPerformanceStats.TryDequeue(out _);
}
}
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 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 Guid GetLastEtagFor(SqlReplicationStatus replicationStatus, SqlReplicationConfig sqlReplicationConfig)
{
var lastEtag = Guid.Empty;
var lastEtagHolder = replicationStatus.LastReplicatedEtags.FirstOrDefault(
x => string.Equals(sqlReplicationConfig.Name, x.Name, StringComparison.InvariantCultureIgnoreCase));
if (lastEtagHolder != null)
lastEtag = lastEtagHolder.LastDocEtag;
return lastEtag;
}
private ConversionScriptResult ApplyConversionScript(SqlReplicationConfig cfg, IEnumerable<ReplicatedDoc> docs, SqlReplicationStatistics replicationStats)
{
var result = new ConversionScriptResult();
foreach (var replicatedDoc in docs)
{
Database.WorkContext.CancellationToken.ThrowIfCancellationRequested();
if (string.IsNullOrEmpty(cfg.RavenEntityName) == false)
{
var entityName = replicatedDoc.Document[Constants.Metadata].Value<string>(Constants.RavenEntityName);
if (string.Equals(cfg.RavenEntityName, entityName, StringComparison.InvariantCultureIgnoreCase) == false)
continue;
}
var patcher = new SqlReplicationScriptedJsonPatcher(Database, result, cfg, replicatedDoc.Key);
using (var scope = new SqlReplicationScriptedJsonPatcherOperationScope(Database))
{
try
{
patcher.Apply(scope, replicatedDoc.Document, new ScriptedPatchRequest { Script = cfg.Script }, replicatedDoc.SerializedSizeOnDisk);
if (Log.IsDebugEnabled && patcher.Debug.Count > 0)
{
Log.Debug("Debug output for doc: {0} for script {1}:\r\n.{2}", replicatedDoc.Key, cfg.Name, string.Join("\r\n", patcher.Debug));
patcher.Debug.Clear();
}
replicationStats.ScriptSuccess();
}
catch (ParseException e)
{
replicationStats.MarkScriptAsInvalid(Database, cfg.Script);
Log.WarnException("Could not parse SQL Replication script for " + cfg.Name, e);
return result;
}
catch (Exception diffExceptionName)
{
replicationStats.RecordScriptError(Database, diffExceptionName);
Log.WarnException("Could not process SQL Replication script for " + cfg.Name + ", skipping document: " + replicatedDoc.Key, diffExceptionName);
}
}
}
return result;
}
