private static async Task<XDocument> GetDownloadRecords(SqlConnectionStringBuilder source, ReplicationSourceMarker sourceMarker, ReplicationTargetMarker targetMarker, int batchSize) { using (var connection = await source.ConnectTo()) { using (var command = new SqlCommand(@" SELECT TOP(@batchSize) PackageStatistics.[Key] 'originalKey', PackageRegistrations.[Id] 'packageId', Packages.[Version] 'packageVersion', Packages.[Listed] 'packageListed', Packages.[Title] 'packageTitle', Packages.[Description] 'packageDescription', Packages.[IconUrl] 'packageIconUrl', ISNULL(PackageStatistics.[UserAgent], '') 'downloadUserAgent', ISNULL(PackageStatistics.[Operation], '') 'downloadOperation', PackageStatistics.[Timestamp] 'downloadTimestamp', PackageStatistics.[ProjectGuids] 'downloadProjectTypes', PackageStatistics.[DependentPackage] 'downloadDependentPackageId' FROM PackageStatistics INNER JOIN Packages ON PackageStatistics.PackageKey = Packages.[Key] INNER JOIN PackageRegistrations ON PackageRegistrations.[Key] = Packages.PackageRegistrationKey WHERE PackageStatistics.[Key] >= @minSourceKey AND PackageStatistics.[Key] <= @maxSourceKey AND PackageStatistics.[Timestamp] >= @minTimestamp AND PackageStatistics.[Timestamp] < @maxTimestamp AND PackageStatistics.[Key] > @cursor ORDER BY PackageStatistics.[Key] FOR XML RAW('fact'), ELEMENTS, ROOT('facts') ", connection)) { command.Parameters.AddWithValue("@batchSize", batchSize); command.Parameters.AddWithValue("@minSourceKey", sourceMarker.MinKey); command.Parameters.AddWithValue("@maxSourceKey", sourceMarker.MaxKey); command.Parameters.AddWithValue("@cursor", targetMarker.Cursor ?? 0); command.Parameters.AddWithValue("@minTimestamp", targetMarker.MinTimestamp); command.Parameters.AddWithValue("@maxTimestamp", targetMarker.MaxTimestamp); var factsReader = await command.ExecuteXmlReaderAsync(); var nodeType = factsReader.MoveToContent(); if (nodeType != XmlNodeType.None) { var factsDocument = XDocument.Load(factsReader); return factsDocument; } else { // No data returned return null; } } } }
private static async Task<ReplicationTargetMarker> GetReplicationTargetMarker(SqlConnectionStringBuilder target, ReplicationSourceMarker sourceMarker) { using (var connection = await target.ConnectTo()) { using (var command = new SqlCommand("CreateCursor", connection)) { var minTimestamp = new SqlParameter("@minTimestamp", sourceMarker.MinTimestamp) { Direction = ParameterDirection.InputOutput }; var maxTimestamp = new SqlParameter("@maxTimestamp", sourceMarker.MaxTimestamp) { Direction = ParameterDirection.InputOutput }; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(minTimestamp); command.Parameters.Add(maxTimestamp); await command.ExecuteNonQueryAsync(); // If the min/max pair is null then that means there are no records missing // from the target. So we use the MaxTimestamp as the null value for BOTH // as that will result in no records to replicate, but we also set the flag. var minTimestampValue = (minTimestamp.Value as DateTime?) ?? sourceMarker.MaxTimestamp; var maxTimestampValue = (maxTimestamp.Value as DateTime?) ?? sourceMarker.MaxTimestamp; return new ReplicationTargetMarker { MinTimestamp = minTimestampValue, MaxTimestamp = maxTimestampValue, TimeWindowNeedsReplication = (minTimestampValue < maxTimestampValue) }; } } }
private async Task<ReplicationTargetMarker> ReplicateBatch(ReplicationSourceMarker sourceMarker, ReplicationTargetMarker targetMarker, int batchSize) { targetMarker.LastBatchCount = 0; try { JobEventSourceLog.FetchingStatisticsChunk(batchSize); var batch = await GetDownloadRecords(Source, sourceMarker, targetMarker, batchSize); JobEventSourceLog.FetchedStatisticsChunk(); // If there's nothing else to process, then return the specified target marker, // indicating we're done. if (batch == null || !batch.Descendants("fact").Any()) { targetMarker.TimeWindowNeedsReplication = false; return targetMarker; } JobEventSourceLog.VerifyingCursor(targetMarker.MinTimestamp, targetMarker.MaxTimestamp, targetMarker.Cursor.HasValue ? targetMarker.Cursor.ToString() : "<null>"); var cursor = await GetTargetCursor(Destination, targetMarker); if (cursor != targetMarker.Cursor) { throw new InvalidOperationException(string.Format("Expected cursor for {0} to {1} to have the value of {2} but it had the value for {3}. Aborting.", targetMarker.MinTimestamp, targetMarker.MaxTimestamp, targetMarker.Cursor.HasValue ? targetMarker.Cursor.ToString() : "<null>", cursor.HasValue ? cursor.ToString() : "<null>")); } JobEventSourceLog.VerifiedCursor(); // Determine what our new cursor value should be after completing this batch var newCursor = new ReplicationTargetMarker { MinTimestamp = targetMarker.MinTimestamp, MaxTimestamp = targetMarker.MaxTimestamp, TimeWindowNeedsReplication = targetMarker.TimeWindowNeedsReplication, LastBatchCount = batch.Root.Nodes().Count(), Cursor = (from fact in batch.Descendants("fact") let originalKey = (int)fact.Element("originalKey") orderby originalKey descending select originalKey).First() }; var minBatchTime = batch.Descendants("fact").Min(f => DateTime.Parse(f.Element("downloadTimestamp").Value)); var maxBatchTime = batch.Descendants("fact").Max(f => DateTime.Parse(f.Element("downloadTimestamp").Value)); JobEventSourceLog.SavingDownloadFacts(newCursor.LastBatchCount, minBatchTime, maxBatchTime); SqlException potentialException = null; try { await PutDownloadRecords(Destination, batch, targetMarker, newCursor); } catch (SqlException sqlException) { // If we got an exception, it's possible that the batch was still committed. // Capture the exception in case we decide to throw it because the batch failed. potentialException = sqlException; } // See if our new cursor was committed JobEventSourceLog.CheckingCursor(); var committedCursor = await GetTargetCursor(Destination, newCursor); JobEventSourceLog.CheckedCursor(committedCursor.HasValue ? committedCursor.Value.ToString() : "<null>"); if (potentialException != null) { // An exception occurred. It's possible that the batch actually succeeded though. if (committedCursor == newCursor.Cursor) { // Yep, the batch actually succeeded despite the reported exception // A known scenarios for this is when a timeout is reported but the // batch is actually committed JobEventSourceLog.RecoveredFromErrorSavingDownloadFacts(targetMarker.MinTimestamp, targetMarker.MaxTimestamp, targetMarker.Cursor.HasValue ? targetMarker.Cursor.Value.ToString() : "<null>", newCursor.Cursor.HasValue ? newCursor.Cursor.Value.ToString() : "<null>", committedCursor.HasValue ? committedCursor.Value.ToString() : "<null>", potentialException.ToString()); } else if (committedCursor == targetMarker.Cursor) { // Nope, the batch actually failed. Re-throw the exception, and we'll try // to recover by retrying up to the max failure count. throw potentialException; } } if (committedCursor != newCursor.Cursor) { // We didn't get an exception, but our committed cursor doesn't match expectations // Let's abort because we don't know what just happened throw new InvalidOperationException(string.Format("Expected cursor for {0} to {1} to have the value of {2} but it had the value for {3}. Aborting.", newCursor.MinTimestamp, newCursor.MaxTimestamp, newCursor.Cursor.HasValue ? newCursor.Cursor.ToString() : "<null>", committedCursor.HasValue ? committedCursor.Value.ToString() : "<null>")); } JobEventSourceLog.SavedDownloadFacts(newCursor.LastBatchCount); CurrentFailures = 0; return newCursor; } catch (SqlException exception) { // We will ignore failures up to the max failure count, at which time we abort. if (++CurrentFailures == MaxFailures) { throw; } JobEventSourceLog.RecoveredFromFailedBatch(CurrentFailures, MaxFailures, exception.ToString()); return targetMarker; } }
private static async Task <XDocument> GetDownloadRecords(SqlConnectionStringBuilder source, ReplicationSourceMarker sourceMarker, ReplicationTargetMarker targetMarker, int batchSize) { using (var connection = await source.ConnectTo()) { using (var command = new SqlCommand(@" SELECT TOP(@batchSize) PackageStatistics.[Key] 'originalKey', PackageRegistrations.[Id] 'packageId', Packages.[Version] 'packageVersion', Packages.[Listed] 'packageListed', Packages.[Title] 'packageTitle', Packages.[Description] 'packageDescription', Packages.[IconUrl] 'packageIconUrl', ISNULL(PackageStatistics.[UserAgent], '') 'downloadUserAgent', ISNULL(PackageStatistics.[Operation], '') 'downloadOperation', PackageStatistics.[Timestamp] 'downloadTimestamp', PackageStatistics.[ProjectGuids] 'downloadProjectTypes', PackageStatistics.[DependentPackage] 'downloadDependentPackageId' FROM PackageStatistics INNER JOIN Packages ON PackageStatistics.PackageKey = Packages.[Key] INNER JOIN PackageRegistrations ON PackageRegistrations.[Key] = Packages.PackageRegistrationKey WHERE PackageStatistics.[Key] >= @minSourceKey AND PackageStatistics.[Key] <= @maxSourceKey AND PackageStatistics.[Timestamp] >= @minTimestamp AND PackageStatistics.[Timestamp] < @maxTimestamp AND PackageStatistics.[Key] > @cursor ORDER BY PackageStatistics.[Key] FOR XML RAW('fact'), ELEMENTS, ROOT('facts') ", connection)) { command.Parameters.AddWithValue("@batchSize", batchSize); command.Parameters.AddWithValue("@minSourceKey", sourceMarker.MinKey); command.Parameters.AddWithValue("@maxSourceKey", sourceMarker.MaxKey); command.Parameters.AddWithValue("@cursor", targetMarker.Cursor ?? 0); command.Parameters.AddWithValue("@minTimestamp", targetMarker.MinTimestamp); command.Parameters.AddWithValue("@maxTimestamp", targetMarker.MaxTimestamp); var factsReader = await command.ExecuteXmlReaderAsync(); var nodeType = factsReader.MoveToContent(); if (nodeType != XmlNodeType.None) { var factsDocument = XDocument.Load(factsReader); return(factsDocument); } else { // No data returned return(null); } } } }
private static async Task <ReplicationTargetMarker> GetReplicationTargetMarker(SqlConnectionStringBuilder target, ReplicationSourceMarker sourceMarker) { using (var connection = await target.ConnectTo()) { using (var command = new SqlCommand("CreateCursor", connection)) { var minTimestamp = new SqlParameter("@minTimestamp", sourceMarker.MinTimestamp) { Direction = ParameterDirection.InputOutput }; var maxTimestamp = new SqlParameter("@maxTimestamp", sourceMarker.MaxTimestamp) { Direction = ParameterDirection.InputOutput }; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(minTimestamp); command.Parameters.Add(maxTimestamp); await command.ExecuteNonQueryAsync(); // If the min/max pair is null then that means there are no records missing // from the target. So we use the MaxTimestamp as the null value for BOTH // as that will result in no records to replicate, but we also set the flag. var minTimestampValue = (minTimestamp.Value as DateTime?) ?? sourceMarker.MaxTimestamp; var maxTimestampValue = (maxTimestamp.Value as DateTime?) ?? sourceMarker.MaxTimestamp; return(new ReplicationTargetMarker { MinTimestamp = minTimestampValue, MaxTimestamp = maxTimestampValue, TimeWindowNeedsReplication = (minTimestampValue < maxTimestampValue) }); } } }
private async Task <ReplicationTargetMarker> ReplicateBatch(ReplicationSourceMarker sourceMarker, ReplicationTargetMarker targetMarker, int batchSize) { targetMarker.LastBatchCount = 0; try { JobEventSourceLog.FetchingStatisticsChunk(batchSize); var batch = await GetDownloadRecords(Source, sourceMarker, targetMarker, batchSize); JobEventSourceLog.FetchedStatisticsChunk(); // If there's nothing else to process, then return the specified target marker, // indicating we're done. if (batch == null || !batch.Descendants("fact").Any()) { targetMarker.TimeWindowNeedsReplication = false; return(targetMarker); } JobEventSourceLog.VerifyingCursor(targetMarker.MinTimestamp, targetMarker.MaxTimestamp, targetMarker.Cursor.HasValue ? targetMarker.Cursor.ToString() : "<null>"); var cursor = await GetTargetCursor(Destination, targetMarker); if (cursor != targetMarker.Cursor) { throw new InvalidOperationException(string.Format("Expected cursor for {0} to {1} to have the value of {2} but it had the value for {3}. Aborting.", targetMarker.MinTimestamp, targetMarker.MaxTimestamp, targetMarker.Cursor.HasValue ? targetMarker.Cursor.ToString() : "<null>", cursor.HasValue ? cursor.ToString() : "<null>")); } JobEventSourceLog.VerifiedCursor(); // Determine what our new cursor value should be after completing this batch var newCursor = new ReplicationTargetMarker { MinTimestamp = targetMarker.MinTimestamp, MaxTimestamp = targetMarker.MaxTimestamp, TimeWindowNeedsReplication = targetMarker.TimeWindowNeedsReplication, LastBatchCount = batch.Root.Nodes().Count(), Cursor = (from fact in batch.Descendants("fact") let originalKey = (int)fact.Element("originalKey") orderby originalKey descending select originalKey).First() }; var minBatchTime = batch.Descendants("fact").Min(f => DateTime.Parse(f.Element("downloadTimestamp").Value)); var maxBatchTime = batch.Descendants("fact").Max(f => DateTime.Parse(f.Element("downloadTimestamp").Value)); JobEventSourceLog.SavingDownloadFacts(newCursor.LastBatchCount, minBatchTime, maxBatchTime); SqlException potentialException = null; try { await PutDownloadRecords(Destination, batch, targetMarker, newCursor); } catch (SqlException sqlException) { // If we got an exception, it's possible that the batch was still committed. // Capture the exception in case we decide to throw it because the batch failed. potentialException = sqlException; } // See if our new cursor was committed JobEventSourceLog.CheckingCursor(); var committedCursor = await GetTargetCursor(Destination, newCursor); JobEventSourceLog.CheckedCursor(committedCursor.HasValue ? committedCursor.Value.ToString() : "<null>"); if (potentialException != null) { // An exception occurred. It's possible that the batch actually succeeded though. if (committedCursor == newCursor.Cursor) { // Yep, the batch actually succeeded despite the reported exception // A known scenarios for this is when a timeout is reported but the // batch is actually committed JobEventSourceLog.RecoveredFromErrorSavingDownloadFacts(targetMarker.MinTimestamp, targetMarker.MaxTimestamp, targetMarker.Cursor.HasValue ? targetMarker.Cursor.Value.ToString() : "<null>", newCursor.Cursor.HasValue ? newCursor.Cursor.Value.ToString() : "<null>", committedCursor.HasValue ? committedCursor.Value.ToString() : "<null>", potentialException.ToString()); } else if (committedCursor == targetMarker.Cursor) { // Nope, the batch actually failed. Re-throw the exception, and we'll try // to recover by retrying up to the max failure count. throw potentialException; } } if (committedCursor != newCursor.Cursor) { // We didn't get an exception, but our committed cursor doesn't match expectations // Let's abort because we don't know what just happened throw new InvalidOperationException(string.Format("Expected cursor for {0} to {1} to have the value of {2} but it had the value for {3}. Aborting.", newCursor.MinTimestamp, newCursor.MaxTimestamp, newCursor.Cursor.HasValue ? newCursor.Cursor.ToString() : "<null>", committedCursor.HasValue ? committedCursor.Value.ToString() : "<null>")); } JobEventSourceLog.SavedDownloadFacts(newCursor.LastBatchCount); CurrentFailures = 0; return(newCursor); } catch (SqlException exception) { // We will ignore failures up to the max failure count, at which time we abort. if (++CurrentFailures == MaxFailures) { throw; } JobEventSourceLog.RecoveredFromFailedBatch(CurrentFailures, MaxFailures, exception.ToString()); return(targetMarker); } }