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);
