Exemple #1
0
        private static int FileToDatabaseSync(F2dbOptions opts)
        {
            if (string.IsNullOrEmpty(opts.WorkingDirectory))
            {
                opts.WorkingDirectory = Environment.CurrentDirectory;
            }

            var db   = opts.Database;
            var pk   = BacPackage.Load(opts.InputFile);
            var spec = new DacAzureDatabaseSpecification
            {
                Edition = DacAzureEdition.Basic
            };

            using (var sqlConn = new SqlConnection(opts.OutputConnectionString))
                using (var singleUserCmd = new SqlCommand($"IF db_id('{db}') is not null ALTER DATABASE [{db}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", sqlConn))
                    using (var dropCmd = new SqlCommand($"IF db_id('{db}') is not null DROP DATABASE [{db}]", sqlConn))
                    {
                        sqlConn.Open();

                        singleUserCmd.ExecuteNonQuery();
                        dropCmd.ExecuteNonQuery();
                    }

            var local = new DacServices(opts.OutputConnectionString);

            local.ProgressChanged += (sender, eventArgs) => { Console.WriteLine($"[{db}] {eventArgs.Message}"); };

            local.ImportBacpac(pk, db, spec);

            if (!string.IsNullOrEmpty(opts.LocalUser))
            {
                using (var sqlConn = new SqlConnection(opts.OutputConnectionString))
                    using (var loginCmd = new SqlCommand($"USE [{db}]; CREATE USER [{opts.LocalUser}] FOR LOGIN [{opts.LocalUser}]; USE [{db}]; ALTER ROLE [db_owner] ADD MEMBER [{opts.LocalUser}];", sqlConn))
                    {
                        sqlConn.Open();

                        try
                        {
                            loginCmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"WARNING: Couldn't add user {opts.LocalUser} because: {ex.Message}");
                        }
                    }
            }

            Console.Write("done.");
            Console.WriteLine();

            return(0);
        }
Exemple #2
0
        public static void SetDeployProperties(this DacDeployOptions deployOptions, string[] deployProperties, IConsole console = null)
        {
            foreach (var deployProperty in deployProperties.Where(p => string.IsNullOrWhiteSpace(p) == false))
            {
                var databaseProperty = DatabaseProperty.Create(deployProperty);
                var propertyValue    = deployOptions.SetDeployProperty(databaseProperty.Name, databaseProperty.Value);

                if (console != null)
                {
                    var parsedValue = propertyValue switch
                    {
                        ObjectType[] o => string.Join(',', o),
                        DacAzureDatabaseSpecification s => $"{s.Edition},{s.MaximumSize},{s.ServiceObjective}",
                        _ => propertyValue == null ? "null" : propertyValue.ToString()
                    };

                    console.WriteLine($"Setting property {databaseProperty.Name} to value {parsedValue}");
                }
            }
        }
        private void SQLDBBacPacImportFromFile(DacAzureEdition edition, bool importBlockOnPossibleDataLoss, string importDBConnectionString, string importDBName, string importFileFullPath)
        {
            DacDeployOptions dacOptions = new DacDeployOptions();

            dacOptions.BlockOnPossibleDataLoss = importBlockOnPossibleDataLoss;

            DacServices dacServiceInstance = new DacServices(importDBConnectionString);

            // There are two events to get feed back during import.
            //dacServiceInstance.Message += EventHandlerDACMessage;
            //dacServiceInstance.ProgressChanged += EventHandlerDacServiceInstanceProgressChanged;

            using (BacPackage dacpac = BacPackage.Load(importFileFullPath))
            {
                DacAzureDatabaseSpecification dbSpec = new DacAzureDatabaseSpecification();

                dbSpec.Edition = edition;

                dacServiceInstance.ImportBacpac(dacpac, importDBName);

                dacpac.Dispose();
            }
        }
Exemple #4
0
        private static async Task PublishDatabases(string[] args, CancellationToken cancellation)
        {
            #region Arguments

            PublisherOptions           opt;
            SqlConnectionStringBuilder adminConnBldr = null;
            {
                // Build the configuration root based on project user secrets
                IConfiguration config = new ConfigurationBuilder()
                                        .AddUserSecrets(typeof(Program).Assembly)
                                        .AddCommandLine(args) // Higher precedence
                                        .Build();

                opt = config.Get <PublisherOptions>();

                // DACPAC File (Required)
                if (!opt.SkipPublish)
                {
                    while (string.IsNullOrWhiteSpace(opt.DacpacFile))
                    {
                        Write("Enter path to DACPAC file: ");
                        opt.DacpacFile = ReadLine();

                        if (opt.DacpacFile == null)
                        {
                            throw new OperationCanceledException();
                        }
                    }

                    if (!opt.DacpacFile.EndsWith(".dacpac"))
                    {
                        throw new ArgumentException($"DACPAC file must have the \".dacpac\" extension");
                    }
                    else if (!File.Exists(opt.DacpacFile))
                    {
                        throw new ArgumentException($"No DACPAC found at \"{opt.DacpacFile}\"");
                    }
                }

                // Admin Connection (Required)
                while (string.IsNullOrWhiteSpace(opt.AdminConnection))
                {
                    Write("Enter the admin DB connection string: ");
                    opt.AdminConnection = ReadLine();

                    if (opt.AdminConnection == null)
                    {
                        throw new OperationCanceledException();
                    }
                }

                try
                {
                    adminConnBldr = new SqlConnectionStringBuilder(opt.AdminConnection);
                }
                catch
                {
                    throw new ArgumentException($"Invalid connection string \"{opt.AdminConnection}\"");
                }

                // Pre-Publish Script
                if (!string.IsNullOrWhiteSpace(opt.PrePublishScript) && !File.Exists(opt.PrePublishScript))
                {
                    throw new ArgumentException($"No Pre-Publish script found at \"{opt.PrePublishScript}\"");
                }

                // Backup Folder
                string workingDirectory = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
                opt.BackupFolder ??= Path.Combine(workingDirectory, "Backups");

                // To avoid negative batch size
                opt.BatchSize = Math.Max(opt.BatchSize, 1);
            }

            #endregion

            #region Tenants

            List <PublishWorkspace> workspaces;
            {
                var adminOpt = Options.Create(new AdminRepositoryOptions {
                    ConnectionString = adminConnBldr.ConnectionString
                });
                var logger = new NullLogger <AdminRepository>();
                var repo   = new AdminRepository(adminOpt, logger);
                var ctx    = new QueryContext(0);

                WriteLine($"Loading tenants info from server \"{adminConnBldr.DataSource}\"...");
                var databases = await repo.SqlDatabases
                                .Expand(nameof(SqlDatabase.Server))
                                .OrderBy(nameof(SqlDatabase.Id))
                                .ToListAsync(ctx, cancellation);

                workspaces = databases.Select(db =>
                {
                    var connInfo = AdminRepositoryConnectionResolver.ToConnectionInfo(
                        serverName: db.Server.ServerName,
                        dbName: db.DatabaseName,
                        userName: db.Server.UserName,
                        _: db.Server.PasswordKey,
                        adminConnBuilder: adminConnBldr);

                    var dbConnBldr = new SqlConnectionStringBuilder
                    {
                        DataSource          = connInfo.ServerName,
                        InitialCatalog      = connInfo.DatabaseName,
                        UserID              = connInfo.UserName,
                        Password            = connInfo.Password,
                        IntegratedSecurity  = connInfo.IsWindowsAuth,
                        PersistSecurityInfo = false,
                    };

                    return(new PublishWorkspace
                    {
                        DbName = db.DatabaseName,
                        ConnectionString = dbConnBldr.ConnectionString
                    });
                })
                             .ToList();

                Write($"\u2713 ", ConsoleColor.Green);
                Write($"Found DBs:");
                WriteLine($" [{string.Join("], [", workspaces.Select(w => w.DbName))}]", ConsoleColor.Cyan);
            }

            #endregion

            #region Confirmation

            if (!opt.SkipConfirmation)
            {
                string confirmed = "Confirmed";

                WriteLine();
                Write($"Confirm going ahead with all tenant DBs by typing ");
                Write($"\"{confirmed}\"");
                Write($": ");

                var answer = ReadLine();
                if (answer == null)
                {
                    throw new OperationCanceledException();
                }
                else if (answer.ToLower() != confirmed.ToLower())
                {
                    Write($"X", ConsoleColor.Red);
                    Write($" Did not confirm");
                    WriteLine();

                    return;
                }
                else
                {
                    Write($"\u2713 ", ConsoleColor.Green);
                    Write("Confirmation acquired.");
                    WriteLine();
                }
            }

            #endregion

            #region Backup Dir

            DateTime now         = DateTime.Now;
            string   backupsPath = null;

            if (!opt.SkipBackup)
            {
                string nowString = DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss");
                backupsPath = Path.Combine(opt.BackupFolder, nowString);

                Directory.CreateDirectory(backupsPath);

                Write($"\u2713 ", ConsoleColor.Green);
                Write($"Backup directory created at ");
                Write(backupsPath, ConsoleColor.Cyan);
                Write($".");
                WriteLine();
            }

            #endregion

            #region PrePublish Script

            string prePublishScript = null;
            if (!string.IsNullOrWhiteSpace(opt.PrePublishScript))
            {
                prePublishScript = await File.ReadAllTextAsync(opt.PrePublishScript, cancellation);
            }

            #endregion

            DacPackage dacPackage = null;
            try
            {
                #region DACPAC

                // Get the DACPAC package
                if (!opt.SkipPublish)
                {
                    dacPackage = DacPackage.Load(opt.DacpacFile);
                    {
                        // Sanity check just in case
                        string expectedName = "Tellma.Database.Application";
                        if (dacPackage.Name != expectedName)
                        {
                            throw new ArgumentException($"The DACPAC file \"{opt.DacpacFile}\" does not have the name {expectedName}.");
                        }

                        Write($"\u2713 ", ConsoleColor.Green);
                        WriteLine($"DACPAC version {dacPackage.Version} loaded.");
                    }
                }

                #endregion

                #region Backup and Publish

                WriteLine();
                WriteLine($"Operation started at {now:hh:mm:ss tt}...");

                foreach (var workspace in workspaces)
                {
                    workspace.Top = Console.CursorTop;
                    workspace.UpdateStatus("Getting ready");
                    WriteLine();
                }

                int skip = 0;
                while (!cancellation.IsCancellationRequested)
                {
                    var batch = workspaces.Skip(skip).Take(opt.BatchSize);
                    if (batch.Any())
                    {
                        await Task.WhenAll(batch.Select(async ws =>
                        {
                            try
                            {
                                #region DacService

                                var service      = new DacServices(ws.ConnectionString);
                                service.Message += (object s, DacMessageEventArgs e) =>
                                {
                                    ws.UpdateStatus(e.Message);
                                };

                                #endregion

                                #region Backup

                                if (!opt.SkipBackup)
                                {
                                    // Export Package
                                    string bacpacPath = Path.Combine(backupsPath, $"{ws.DbName}.bacpac");
                                    await Task.Run(() => service.ExportBacpac(bacpacPath, ws.DbName, null, cancellation), cancellation);
                                }

                                #endregion

                                #region Pre-Publish Script

                                if (!string.IsNullOrWhiteSpace(opt.PrePublishScript))
                                {
                                    ws.UpdateStatus("Executing Pre-Publish Script (Started)");

                                    using var conn = new Microsoft.Data.SqlClient.SqlConnection(ws.ConnectionString);

                                    await Task.Run(() =>
                                    {
                                        Server server = new(new ServerConnection(conn));
                                        server.ConnectionContext.ExecuteNonQuery(prePublishScript);
                                    });

                                    ws.UpdateStatus("Executing Pre-Publish Script (Completed)");
                                }

                                #endregion

                                if (!opt.SkipPublish)
                                {
                                    #region DB Specs

                                    DacAzureDatabaseSpecification specs = null;
                                    {
                                        ws.UpdateStatus("Retrieving DB Specs (Started)");

                                        using var conn  = new SqlConnection(ws.ConnectionString);
                                        using var cmd   = conn.CreateCommand();
                                        cmd.CommandText = @$ "
IF OBJECT_ID(N'sys.database_service_objectives') IS NOT NULL
SELECT 
	[edition] AS [Edition], 
Exemple #5
0
        public void SetProperty(string key, string value)
        {
            try
            {
                // Convert value into the appropriate type depending on the key
                object propertyValue = key switch
                {
                    "AdditionalDeploymentContributorArguments" => value,
                    "AdditionalDeploymentContributorPaths" => value,
                    "AdditionalDeploymentContributors" => value,
                    "AllowDropBlockingAssemblies" => bool.Parse(value),
                    "AllowIncompatiblePlatform" => bool.Parse(value),
                    "AllowUnsafeRowLevelSecurityDataMovement" => bool.Parse(value),
                    "BackupDatabaseBeforeChanges" => bool.Parse(value),
                    "BlockOnPossibleDataLoss" => bool.Parse(value),
                    "BlockWhenDriftDetected" => bool.Parse(value),
                    "CommandTimeout" => int.Parse(value),
                    "CommentOutSetVarDeclarations" => bool.Parse(value),
                    "CompareUsingTargetCollation" => bool.Parse(value),
                    "CreateNewDatabase" => bool.Parse(value),
                    "DatabaseLockTimeout" => int.Parse(value),
                    "DatabaseSpecification" => ParseDatabaseSpecification(value),
                    "DeployDatabaseInSingleUserMode" => bool.Parse(value),
                    "DisableAndReenableDdlTriggers" => bool.Parse(value),
                    "DoNotAlterChangeDataCaptureObjects" => bool.Parse(value),
                    "DoNotAlterReplicatedObjects" => bool.Parse(value),
                    "DoNotDropObjectTypes" => ParseObjectTypes(value),
                    "DropConstraintsNotInSource" => bool.Parse(value),
                    "DropDmlTriggersNotInSource" => bool.Parse(value),
                    "DropExtendedPropertiesNotInSource" => bool.Parse(value),
                    "DropIndexesNotInSource" => bool.Parse(value),
                    "DropObjectsNotInSource" => bool.Parse(value),
                    "DropPermissionsNotInSource" => bool.Parse(value),
                    "DropRoleMembersNotInSource" => bool.Parse(value),
                    "DropStatisticsNotInSource" => bool.Parse(value),
                    "ExcludeObjectTypes" => ParseObjectTypes(value),
                    "GenerateSmartDefaults" => bool.Parse(value),
                    "IgnoreAnsiNulls" => bool.Parse(value),
                    "IgnoreAuthorizer" => bool.Parse(value),
                    "IgnoreColumnCollation" => bool.Parse(value),
                    "IgnoreColumnOrder" => bool.Parse(value),
                    "IgnoreComments" => bool.Parse(value),
                    "IgnoreCryptographicProviderFilePath" => bool.Parse(value),
                    "IgnoreDdlTriggerOrder" => bool.Parse(value),
                    "IgnoreDdlTriggerState" => bool.Parse(value),
                    "IgnoreDefaultSchema" => bool.Parse(value),
                    "IgnoreDmlTriggerOrder" => bool.Parse(value),
                    "IgnoreDmlTriggerState" => bool.Parse(value),
                    "IgnoreExtendedProperties" => bool.Parse(value),
                    "IgnoreFileAndLogFilePath" => bool.Parse(value),
                    "IgnoreFilegroupPlacement" => bool.Parse(value),
                    "IgnoreFileSize" => bool.Parse(value),
                    "IgnoreFillFactor" => bool.Parse(value),
                    "IgnoreFullTextCatalogFilePath" => bool.Parse(value),
                    "IgnoreIdentitySeed" => bool.Parse(value),
                    "IgnoreIncrement" => bool.Parse(value),
                    "IgnoreIndexOptions" => bool.Parse(value),
                    "IgnoreIndexPadding" => bool.Parse(value),
                    "IgnoreKeywordCasing" => bool.Parse(value),
                    "IgnoreLockHintsOnIndexes" => bool.Parse(value),
                    "IgnoreLoginSids" => bool.Parse(value),
                    "IgnoreNotForReplication" => bool.Parse(value),
                    "IgnoreObjectPlacementOnPartitionScheme" => bool.Parse(value),
                    "IgnorePartitionSchemes" => bool.Parse(value),
                    "IgnorePermissions" => bool.Parse(value),
                    "IgnoreQuotedIdentifiers" => bool.Parse(value),
                    "IgnoreRoleMembership" => bool.Parse(value),
                    "IgnoreRouteLifetime" => bool.Parse(value),
                    "IgnoreSemicolonBetweenStatements" => bool.Parse(value),
                    "IgnoreTableOptions" => bool.Parse(value),
                    "IgnoreTablePartitionOptions" => bool.Parse(value),
                    "IgnoreUserSettingsObjects" => bool.Parse(value),
                    "IgnoreWhitespace" => bool.Parse(value),
                    "IgnoreWithNocheckOnCheckConstraints" => bool.Parse(value),
                    "IgnoreWithNocheckOnForeignKeys" => bool.Parse(value),
                    "IncludeCompositeObjects" => bool.Parse(value),
                    "IncludeTransactionalScripts" => bool.Parse(value),
                    "LongRunningCommandTimeout" => int.Parse(value),
                    "NoAlterStatementsToChangeClrTypes" => bool.Parse(value),
                    "PopulateFilesOnFileGroups" => bool.Parse(value),
                    "RegisterDataTierApplication" => bool.Parse(value),
                    "RunDeploymentPlanExecutors" => bool.Parse(value),
                    "ScriptDatabaseCollation" => bool.Parse(value),
                    "ScriptDatabaseCompatibility" => bool.Parse(value),
                    "ScriptDatabaseOptions" => bool.Parse(value),
                    "ScriptDeployStateChecks" => bool.Parse(value),
                    "ScriptFileSize" => bool.Parse(value),
                    "ScriptNewConstraintValidation" => bool.Parse(value),
                    "ScriptRefreshModule" => bool.Parse(value),
                    "SqlCommandVariableValues" => throw new ArgumentException("SQLCMD variables should be set using the --sqlcmdvar command line argument and not as a property."),
                          "TreatVerificationErrorsAsWarnings" => bool.Parse(value),
                          "UnmodifiableObjectWarnings" => bool.Parse(value),
                          "VerifyCollationCompatibility" => bool.Parse(value),
                          "VerifyDeployment" => bool.Parse(value),
                          _ => throw new ArgumentException($"Unknown property with name {key}", nameof(key))
                };

                PropertyInfo property = typeof(DacDeployOptions).GetProperty(key, BindingFlags.Public | BindingFlags.Instance);
                property.SetValue(DeployOptions, propertyValue);

                var parsedValue = propertyValue switch
                {
                    ObjectType[] o => string.Join(',', o),
                    DacAzureDatabaseSpecification s => $"{s.Edition},{s.MaximumSize},{s.ServiceObjective}",
                    _ => propertyValue.ToString()
                };

                _console.WriteLine($"Setting property {key} to value {parsedValue}");
            }
            catch (FormatException)
            {
                throw new ArgumentException($"Unable to parse value for property with name {key}: {value}", nameof(value));
            }
        }