async Task CreateDatabase(SqlDeploymentExecuteContext context, SqlConnection cnn, CancellationToken cancellationToken) { var defaultDataFilePath = DefaultDataFilePath ?? Path.Combine(await cnn.GetServerPropertyAsync("InstanceDefaultDataPath", cancellationToken), Name + ".mdf"); var dataFileExistsResult = await cnn.ExecuteXpFileExist(defaultDataFilePath, cancellationToken); if (dataFileExistsResult.FileExists == 1 && Overwrite) { await cnn.ExecuteXpDeleteFiles(new[] { defaultDataFilePath }, cancellationToken); dataFileExistsResult = await cnn.ExecuteXpFileExist(defaultDataFilePath, cancellationToken); } if (dataFileExistsResult.FileExists == 1) { throw new SqlDeploymentException($"Data file '{defaultDataFilePath}' already exists."); } var defaultLogFilePath = DefaultLogFilePath ?? Path.Combine(await cnn.GetServerPropertyAsync("InstanceDefaultLogPath", cancellationToken), Name + "_log.ldf"); var logFileExistsResult = await cnn.ExecuteXpFileExist(defaultLogFilePath, cancellationToken); if (logFileExistsResult.FileExists == 1 && Overwrite) { await cnn.ExecuteXpDeleteFiles(new[] { defaultLogFilePath }, cancellationToken); logFileExistsResult = await cnn.ExecuteXpFileExist(defaultLogFilePath, cancellationToken); } if (logFileExistsResult.FileExists == 1) { throw new SqlDeploymentException($"Log file '{defaultLogFilePath}' already exists."); } context.Logger.LogInformation("Creating database {DatabaseName}.", Name); await cnn.ExecuteNonQueryAsync((string)$"CREATE DATABASE [{Name}]", cancellationToken : cancellationToken); }
/// <summary> /// Returns a task that is completed when the specified action is complete. /// </summary> /// <param name="context"></param> /// <param name="action"></param> /// <param name="cancellationToken"></param> /// <returns></returns> async Task ExecuteActionAsync(SqlDeploymentExecuteContext context, SqlDeploymentAction action, CancellationToken cancellationToken) { context.Logger.LogDebug("Starting action {Action} against {InstanceName}.", action.GetType().Name, action.InstanceName); await action.ExecuteAsync(context, cancellationToken); context.Logger.LogDebug("Finished action {Action} against {InstanceName}.", action.GetType().Name, action.InstanceName); }
/// <summary> /// Applies the configuration value. /// </summary> /// <param name="context"></param> /// <param name="cancellationToken"></param> /// <returns></returns> public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken = default) { using var cnn = await OpenConnectionAsync(cancellationToken); // load existing information about value var config = await cnn.ExecuteSpConfigure(Name, cancellationToken); if (config == null) { throw new SqlDeploymentException("Unknown configuration name."); } // check for range if (Value < config.Minimum || Value > config.Maximum) { throw new SqlDeploymentException("Configuration value out of range."); } // has the value changed? if (config.ConfigValue != Value || config.RunValue != Value) { context.Logger.LogInformation("Setting server configuration '{Name}' to {Value}.", Name, Value); await cnn.ExecuteSpConfigure(Name, Value, cancellationToken); await cnn.ExecuteNonQueryAsync($"RECONFIGURE", cancellationToken : cancellationToken); } }
/// <summary> /// Executes each of the given actions in order. /// </summary> /// <param name="actions"></param> /// <returns></returns> async Task ExecuteAsync(SqlDeploymentExecuteContext context, SqlDeploymentAction[] actions, CancellationToken cancellationToken) { foreach (var action in actions) { cancellationToken.ThrowIfCancellationRequested(); await ExecuteAsync(context, action, cancellationToken); } }
public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken = default) { using var publish = await OpenConnectionAsync(cancellationToken); // switch to publisher database publish.ChangeDatabase(DatabaseName); // ensure replication is enabled on the database await publish.ExecuteSpSetReplicationDbOptionAsync(DatabaseName, "publish", "true"); // configure log reader agent var logReaderAgent = await publish.ExecuteSpHelpLogReaderAgentAsync(cancellationToken); if (logReaderAgent?.JobId == null) { await publish.ExecuteSpAddLogReaderAgentAsync( LogReaderAgent?.ProcessCredentials?.UserName, LogReaderAgent?.ProcessCredentials?.Password, LogReaderAgent?.ConnectCredentials == null? 1 : 0, LogReaderAgent?.ConnectCredentials?.UserId, LogReaderAgent?.ConnectCredentials?.Password != null?new NetworkCredential("", LogReaderAgent.ConnectCredentials.Password).Password : null, cancellationToken); } // add publication if it does not exist var existingPublication1 = await publish.LoadDataTableAsync($"SELECT * from syspublications WHERE name = {Name}", cancellationToken : cancellationToken); if (existingPublication1.Rows.Count == 0) { await publish.ExecuteSpAddPublicationAsync(Name, "active", true, true, true, cancellationToken); } // add publication snapshot if it does not exist var existingPublication2 = await publish.LoadDataTableAsync($"SELECT * from syspublications WHERE name = {Name} AND snapshot_jobid IS NOT NULL", cancellationToken : cancellationToken); if (existingPublication2.Rows.Count == 0) { await publish.ExecuteSpAddPublicationSnapshotAsync( Name, SnapshotAgent?.ProcessCredentials?.UserName, SnapshotAgent?.ProcessCredentials?.Password, SnapshotAgent?.ConnectCredentials == null? 1 : 0, SnapshotAgent?.ConnectCredentials?.UserId, SnapshotAgent?.ConnectCredentials?.Password != null?new NetworkCredential("", SnapshotAgent.ConnectCredentials.Password).Password : null, cancellationToken); } // update the snapshot directory ACLs await UpdateSnapshotDirectoryAcl(context, publish, Name, cancellationToken); // change publication options await publish.ExecuteSpChangePublicationAsync(Name, "allow_anonymous", "false", 1, cancellationToken); await publish.ExecuteSpChangePublicationAsync(Name, "immediate_sync", "false", 1, cancellationToken); await publish.ExecuteSpChangePublicationAsync(Name, "sync_method", "database snapshot", 1, cancellationToken); }
/// <summary> /// Executes the given target by name. /// </summary> /// <param name="context"></param> /// <param name="targetName"></param> /// <param name="cancellationToken"></param> /// <returns></returns> Task ExecuteAsync(SqlDeploymentExecuteContext context, string targetName, CancellationToken cancellationToken) { if (plan.Targets.TryGetValue(targetName, out var target) == false) { throw new SqlDeploymentException($"Could not resolve target '{targetName}'."); } return(ExecuteAsync(context, target, cancellationToken)); }
/// <summary> /// Creates the database. /// </summary> /// <param name="context"></param> /// <param name="cancellationToken"></param> /// <returns></returns> public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken = default) { using var cnn = await OpenConnectionAsync(cancellationToken); if ((string)await cnn.ExecuteScalarAsync($"SELECT name FROM sys.databases WHERE name = {Name}") == null) { await CreateDatabase(context, cnn, cancellationToken); } }
/// <summary> /// Does the work of installing SQL server. /// </summary> /// <param name="context"></param> /// <param name="cancellationToken"></param> /// <returns></returns> async Task ExecuteAsyncInternal(SqlDeploymentExecuteContext context, CancellationToken cancellationToken) { // acquire breakdown of instance information var serverName = await TryGetServerName(cancellationToken) ?? InstanceName; var m = serverName.Split(new[] { '\\' }, 2); var host = m.Length >= 1 ? m[0].TrimOrNull() : null; var name = m.Length >= 2 ? m[1].TrimOrNull() : null; // instance requires host name if (host == null) { throw new InvalidOperationException("Missing host name for instance."); } // fallback to default if (name == null) { name = DEFAULT_INSTANCE_NAME; } // target machine is localhost if (GetLocalServerNames().Contains(host, StringComparer.OrdinalIgnoreCase)) { // but not yet installed if (GetLocalInstanceNames().Contains(name, StringComparer.OrdinalIgnoreCase) == false) { await InstallSqlServer(name, cancellationToken); } } else { throw new NotImplementedException("Remote installation of SQL Server is not yet implemented."); } // test connection now that installation has completed if (await TryGetServerName(cancellationToken) is string s) { // required for deployment if (await IsSysAdmin(cancellationToken) != true) { throw new InvalidOperationException($"Unable to verify membership in sysadmin role on '{InstanceName}'."); } // ensure agent is setup properly await ConfigureSqlAgent(cancellationToken); return; } throw new InvalidOperationException($"Could not establish connection SQL Server '{InstanceName}'."); }
/// <summary> /// Executes the step. /// </summary> /// <param name="context"></param> /// <param name="cancellationToken"></param> /// <returns></returns> public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken = default) { using var cnn = await OpenConnectionAsync(cancellationToken); cnn.ChangeDatabase(DatabaseName); var owner = (string)await cnn.ExecuteScalarAsync($"SELECT SUSER_SNAME(owner_sid) owner_name FROM sys.databases WHERE name = {DatabaseName}", cancellationToken : cancellationToken); if (owner != Login) { await SetDatabaseOwner(context, cnn, cancellationToken); } }
public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken = default) { using var publisher = await OpenConnectionAsync(cancellationToken); using var distributor = DistributorInstanceName != null ? await OpenConnectionAsync(DistributorInstanceName, cancellationToken) : publisher; // load name of publisher var publisherName = await publisher.GetServerPropertyAsync("SERVERNAME", cancellationToken); if (publisherName == null) { throw new InvalidOperationException(); } var knownPublisherName = (string)await distributor.ExecuteScalarAsync($@" SELECT name FROM msdb.dbo.MSdistpublishers WHERE name = {publisherName}", cancellationToken : cancellationToken); if (knownPublisherName == null) { await distributor.ExecuteSpAddDistPublisherAsync(publisherName, DistributorDatabaseName, 1, "false", 0, "MSSQLSERVER", cancellationToken : cancellationToken); } // grant distributor permissions to server if not already var distributorInfo = await distributor.ExecuteSpHelpDistributorAsync(cancellationToken); if (distributorInfo?.Account != null) { await publisher.ExecuteSpAddSrvRoleMemberAsync(distributorInfo.Account, "sysadmin"); } // ensure server is enabled with the distributor await publisher.ExecuteNonQueryAsync($@" IF NOT EXISTS ( SELECT * from sys.servers WHERE is_distributor = 1 ) BEGIN EXEC sp_adddistributor @distributor = {await distributor.GetServerPropertyAsync("SERVERNAME", cancellationToken)}, @password = {DistributorAdminPassword} END", cancellationToken : cancellationToken); }
public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken = default) { using var connection = await OpenConnectionAsync(cancellationToken); connection.ChangeDatabase(DatabaseName); var table = await LoadTableArticle(connection, cancellationToken); if (table == null) { throw new InvalidOperationException($"Missing table '{Name}'."); } if (table.ArticleId == null) { await connection.ExecuteSpAddArticleAsync( publication : table.PublicationName, article : table.ObjectName, sourceOwner : table.SchemaName, sourceObject : table.ObjectName, destinationTable : table.ObjectName, destinationOwner : table.SchemaName, status : 24, forceInvalidateSnapshot : true, cancellationToken : cancellationToken); // start publication try { await connection.ExecuteSpStartPublicationSnapshotAsync(PublicationName, cancellationToken); } catch (SqlException) { // ignore } } }
public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken = default) { using (var cnn = await OpenConnectionAsync(cancellationToken)) { // check that server already exists if (await ShouldExecute(cnn, cancellationToken) == false) { return; } await cnn.ExecuteNonQueryAsync($@" IF EXISTS ( SELECT * FROM sys.servers WHERE name = {Name} ) BEGIN EXEC sp_dropserver @server = {Name}, @droplogins = 'droplogins' END"); using (var cmd = cnn.CreateCommand()) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "sp_addlinkedserver"; var p0 = cmd.CreateParameter(); p0.ParameterName = "@server"; p0.Value = Name; cmd.Parameters.Add(p0); var p1 = cmd.CreateParameter(); p1.ParameterName = "@srvproduct"; p1.Value = Product ?? ""; cmd.Parameters.Add(p1); var p2 = cmd.CreateParameter(); p2.ParameterName = "@datasrc"; p2.Value = DataSource; cmd.Parameters.Add(p2); if (Provider != null) { var p3 = cmd.CreateParameter(); p3.ParameterName = "@provider"; p3.Value = Provider; cmd.Parameters.Add(p3); } if (ProviderString != null) { var p4 = cmd.CreateParameter(); p4.ParameterName = "@provstr"; p4.Value = ProviderString; cmd.Parameters.Add(p4); } if (Location != null) { var p5 = cmd.CreateParameter(); p5.ParameterName = "@location"; p5.Value = Location; cmd.Parameters.Add(p5); } if (Catalog != null) { var p6 = cmd.CreateParameter(); p6.ParameterName = "@catalog"; p6.Value = Catalog; cmd.Parameters.Add(p6); } await cmd.ExecuteNonQueryAsync(); } await cnn.ExecuteNonQueryAsync($@" EXEC sp_addlinkedsrvlogin @rmtsrvname = {Name}, @locallogin = NULL, @useself = N'True'"); // ensure the linked server is configured correctly and accessible await cnn.ExecuteNonQueryAsync($"EXEC sp_testlinkedserver @servername = {Name}"); } }
/// <summary> /// Deploys the database. /// </summary> /// <param name="context"></param> /// <param name="cancellationToken"></param> /// <returns></returns> public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken) { using (var cnn = await OpenConnectionAsync(cancellationToken)) await new SqlDacPacDeploy(Source, context.Logger).DeployAsync(cnn, Name, Profile, cancellationToken); }
public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken = default) { using var cnn = await OpenConnectionAsync(cancellationToken); await cnn.ExecuteNonQueryAsync($@"EXEC sys.sp_addextendedproperty @name = {Name}, @value = {Value}", cancellationToken : cancellationToken); }
/// <summary> /// Deploys a new instance of SQL Server. /// </summary> /// <param name="context"></param> /// <param name="cancellationToken"></param> /// <returns></returns> public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken) { using (await Mutex.WaitOneAsync(cancellationToken)) await ExecuteAsyncInternal(context, cancellationToken); }
/// <summary> /// Sets the database owner. /// </summary> /// <param name="context"></param> /// <param name="connection"></param> /// <param name="cancellationToken"></param> /// <returns></returns> async Task SetDatabaseOwner(SqlDeploymentExecuteContext context, SqlConnection connection, CancellationToken cancellationToken) { context.Logger.LogInformation("Changing database owner of {DatabaseName} to {Owner}.", DatabaseName, Login); await connection.ExecuteNonQueryAsync((string)$@"ALTER AUTHORIZATION ON DATABASE::[{DatabaseName}] TO [{Login}]", cancellationToken : cancellationToken); }
/// <summary> /// Returns a task that is completed when the specified action is complete. /// </summary> /// <param name="context"></param> /// <param name="action"></param> /// <param name="cancellationToken"></param> /// <returns></returns> Task ExecuteAsync(SqlDeploymentExecuteContext context, SqlDeploymentAction action, CancellationToken cancellationToken) { return(tasks.GetOrAdd(action, _ => new Lazy <AsyncJob <bool> >(() => new AsyncJob <bool>(async ct => { await ExecuteActionAsync(context, _, ct); return true; }), true)).Value.WaitAsync(cancellationToken)); }
/// <summary> /// Gets the task that executes the actions of a target. /// </summary> /// <param name="context"></param> /// <param name="target"></param> /// <param name="cancellationToken"></param> /// <returns></returns> Task GetExecuteTaskAsync(SqlDeploymentExecuteContext context, SqlDeploymentPlanTarget target, CancellationToken cancellationToken) { return(ExecuteAsync(context, target.Actions, cancellationToken)); }
public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken = default) { var instanceName = InstanceName.Replace(@"(localdb)\", ""); var instance = await Task.Run(() => GetOrCreateLocalDbInstance(Api, instanceName)); }
/// <summary> /// Updates the ACLs of the snapshot directory. /// </summary> /// <param name="context"></param> /// <param name="connection"></param> /// <param name="publication"></param> /// <param name="cancellationToken"></param> /// <returns></returns> async Task UpdateSnapshotDirectoryAcl(SqlDeploymentExecuteContext context, SqlConnection connection, string publication, CancellationToken cancellationToken) { if (connection == null) { throw new ArgumentNullException(nameof(connection)); } if (publication == null) { throw new ArgumentNullException(nameof(publication)); } // find login of publiation var publicationInfo = await connection.ExecuteSpHelpPublicationSnapshotAsync(publication, cancellationToken); var logReaderInfo = await connection.ExecuteSpHelpLogReaderAgentAsync(cancellationToken); // nothing to update if (publicationInfo?.JobLogin == null && logReaderInfo?.JobLogin == null) { return; } // file access security only functions on Windows if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows)) { var d = new DirectoryInfo(await GetSnapshotFolder(connection, publication, cancellationToken)); if (d.Exists == false) { // attempt to determine domain name of SQL instance and use to append to path var u = new Uri(d.FullName); if (u.IsUnc && u.Host.Contains(".") == false) { var n = await connection.GetServerDomainName(cancellationToken); if (string.IsNullOrWhiteSpace(n) == false) { var b = new UriBuilder(u); b.Host += "." + n; d = new DirectoryInfo(b.Uri.LocalPath); } } if (d.Exists == false) { return; } } await Task.Run(() => { try { // grant permissions to directory to snapshot agent account var acl = d.GetAccessControl(); if (publicationInfo?.JobLogin != null) { acl.AddAccessRule(new FileSystemAccessRule( publicationInfo.JobLogin, FileSystemRights.ReadAndExecute | FileSystemRights.Write, InheritanceFlags.ContainerInherit | InheritanceFlags.ObjectInherit, PropagationFlags.InheritOnly, AccessControlType.Allow)); } if (logReaderInfo?.JobLogin != null) { acl.AddAccessRule(new FileSystemAccessRule( logReaderInfo.JobLogin, FileSystemRights.ReadAndExecute, InheritanceFlags.ContainerInherit | InheritanceFlags.ObjectInherit, PropagationFlags.InheritOnly, AccessControlType.Allow)); } d.SetAccessControl(acl); } catch (Exception e) { context.Logger.LogError(e, "Unexpected exception updating snapshot directory permissions."); } }); } }
/// <summary> /// Configures the distributor. /// </summary> /// <param name="context"></param> /// <param name="cancellationToken"></param> /// <returns></returns> public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken = default) { using var cnn = await OpenConnectionAsync(cancellationToken); cnn.ChangeDatabase("master"); // find proper name of server var distributorName = await cnn.GetServerNameAsync(); // configure as distributor if required var currentDistributorName = (string)await cnn.ExecuteScalarAsync($"SELECT name FROM sys.servers WHERE is_distributor = 1"); if (currentDistributorName != "repl_distributor") { if (AdminPassword == null) { throw new SqlDeploymentException("Cannot configure distributor: missing AdminPassword."); } context.Logger?.LogInformation("Creating distributor on {InstanceName}.", InstanceName); await cnn.ExecuteNonQueryAsync($@" EXEC sp_adddistributor @distributor = {distributorName}, @password = {AdminPassword}"); } else if (AdminPassword != null) { context.Logger?.LogInformation("Changing distributor password on {InstanceName}.", InstanceName); await cnn.ExecuteNonQueryAsync($@" EXEC sp_changedistributor_password @password = {AdminPassword}"); } // configure distribution database if required var databaseName = DatabaseName ?? "distribution"; var currentDistributionDbs = await cnn.ExecuteSpHelpDistributionDbAsync(cancellationToken); var currentDistributionDb = currentDistributionDbs?.FirstOrDefault(i => i.Name == databaseName); if (currentDistributionDb == null) { context.Logger?.LogInformation("Adding distribution database {DatabaseName} on {InstanceName}.", databaseName, InstanceName); using var cmd = cnn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_adddistributiondb"; cmd.Parameters.AddWithValue("@database", databaseName); cmd.Parameters.AddWithValue("@security_mode", 1); if (MinimumRetention != null) { cmd.Parameters.AddWithValue("@min_distretention", MinimumRetention); } if (MaximumRetention != null) { cmd.Parameters.AddWithValue("@max_distretention", MaximumRetention); } if (HistoryRetention != null) { cmd.Parameters.AddWithValue("@history_retention", HistoryRetention); } if (await cmd.ExecuteScalarAsync(cancellationToken) is int i && i != 0) { throw new SqlDeploymentException("Error code returned executing sp_adddistributiondb."); } } else { if (MinimumRetention != null && currentDistributionDb.MinDistRetention != MinimumRetention) { using var cmd = cnn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_changedistributiondb"; cmd.Parameters.AddWithValue("@database", databaseName); cmd.Parameters.AddWithValue("@property", "min_distretention"); cmd.Parameters.AddWithValue("@value", MinimumRetention); if (await cmd.ExecuteScalarAsync(cancellationToken) is int i && i != 0) { throw new SqlDeploymentException("Error code returned executing sp_changedistributiondb."); } } if (MaximumRetention != null && currentDistributionDb.MaxDistRetention != MaximumRetention) { using var cmd = cnn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_changedistributiondb"; cmd.Parameters.AddWithValue("@database", databaseName); cmd.Parameters.AddWithValue("@property", "max_distretention"); cmd.Parameters.AddWithValue("@value", MaximumRetention); if (await cmd.ExecuteScalarAsync(cancellationToken) is int i && i != 0) { throw new SqlDeploymentException("Error code returned executing sp_changedistributiondb."); } } if (HistoryRetention != null && currentDistributionDb.HistoryRetention != HistoryRetention) { using var cmd = cnn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_changedistributiondb"; cmd.Parameters.AddWithValue("@database", databaseName); cmd.Parameters.AddWithValue("@property", "history_retention"); cmd.Parameters.AddWithValue("@value", HistoryRetention); if (await cmd.ExecuteScalarAsync(cancellationToken) is int i && i != 0) { throw new SqlDeploymentException("Error code returned executing sp_changedistributiondb."); } } } // should be derived from information on server var defaultDataRootTable = await cnn.LoadDataTableAsync(@"EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\Setup', N'SQLDataRoot'"); var defaultDataRootMap = defaultDataRootTable.Rows.Cast <DataRow>().ToDictionary(i => (string)i["Value"], i => i["Data"]); var defaultDataRoot = (string)defaultDataRootMap.GetOrDefault("SQLDataRoot"); var defaultReplData = defaultDataRoot != null?Path.Combine(defaultDataRoot, "ReplData") : null; // update snapshot folder if determined var snapshotPath = SnapshotPath ?? defaultReplData; if (snapshotPath != null) { await cnn.ExecuteNonQueryAsync($@" IF NOT EXISTS (SELECT * from sysobjects where name = 'UIProperties' and type = 'U') CREATE TABLE UIProperties(id int) IF EXISTS (SELECT * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)) EXEC sp_updateextendedproperty N'SnapshotFolder', {snapshotPath}, 'user', dbo, 'table', 'UIProperties' ELSE EXEC sp_addextendedproperty N'SnapshotFolder', {snapshotPath}, 'user', dbo, 'table', 'UIProperties'"); } }
public override Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken = default) { throw new NotImplementedException(); }
public override async Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken = default) { using var sub = await OpenConnectionAsync(cancellationToken); sub.ChangeDatabase(DatabaseName); var subServerName = await sub.GetServerNameAsync(cancellationToken); using var pub = await OpenConnectionAsync(PublisherInstanceName, cancellationToken); pub.ChangeDatabase(PublicationDatabaseName); var pubServerName = await pub.GetServerNameAsync(cancellationToken); await pub.ExecuteNonQueryAsync($@" IF NOT EXISTS ( SELECT * FROM syssubscriptions WHERE srvname = {subServerName} AND dest_db = {DatabaseName} ) EXEC sp_addsubscription @publication = {PublicationName}, @subscriber = {subServerName}, @destination_db = {DatabaseName}, @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0", cancellationToken : cancellationToken); var articles = (await pub.LoadDataTableAsync($@" SELECT DISTINCT NULLIF(a.artid, '') as article_id, NULLIF(a.name, '') as article_name, COALESCE(u.srvname, '') as subscriber_name FROM syspublications p INNER JOIN sysarticles a ON a.pubid = p.pubid INNER JOIN sys.tables t ON t.object_id = a.objid INNER JOIN sys.schemas s ON s.schema_id = t.schema_id LEFT JOIN syssubscriptions u ON u.artid = a.artid AND u.srvname = {subServerName} WHERE p.name = {PublicationName}", cancellationToken: cancellationToken)) .Rows.Cast <DataRow>() .Select(i => new { ArticleId = (int)i["article_id"], ArticleName = (string)i["article_name"], SubscriberName = (string)i["subscriber_name"] }); // add missing articles to the subscription foreach (var article in articles) { if (!string.IsNullOrEmpty(article.ArticleName) && string.IsNullOrEmpty(article.SubscriberName)) { await pub.ExecuteSpAddSubscriptionAsync( publication : PublicationName, subscriber : subServerName, subscriberType : 0, subscriptionType : "Push", destinationDb : DatabaseName, article : article.ArticleName, syncType : "automatic", updateMode : "read only", cancellationToken : cancellationToken); } } try { await pub.ExecuteSpAddPushSubscriptionAgentAsync( publication : PublicationName, subscriber : subServerName, subscriberDb : DatabaseName, subscriberSecurityMode : 1, cancellationToken : cancellationToken); } catch (Exception e) { } // start publication try { await pub.ExecuteSpStartPublicationSnapshotAsync(PublicationName, cancellationToken); } catch (Exception) { // ignore } }
/// <summary> /// Executes the given targets in parallel. /// </summary> /// <param name="context"></param> /// <param name="targets"></param> /// <param name="cancellationToken"></param> /// <returns></returns> Task ExecuteAsync(SqlDeploymentExecuteContext context, IEnumerable <SqlDeploymentPlanTarget> targets, CancellationToken cancellationToken) { return(Task.WhenAll(targets.Select(i => ExecuteAsync(context, i, cancellationToken)))); }
/// <summary> /// Executes the given target. /// </summary> /// <param name="context"></param> /// <param name="target"></param> /// <param name="cancellationToken"></param> /// <returns></returns> async Task ExecuteAsync(SqlDeploymentExecuteContext context, SqlDeploymentPlanTarget target, CancellationToken cancellationToken) { await Task.WhenAll(target.DependsOn.Select(i => ExecuteAsync(context, i, cancellationToken))); await GetExecuteTaskAsync(context, target, cancellationToken); }
/// <summary> /// Applies the step to the instance. /// </summary> /// <param name="context"></param> /// <param name="cancellationToken"></param> public abstract Task ExecuteAsync(SqlDeploymentExecuteContext context, CancellationToken cancellationToken);