Page Comparison

Datical's Deployment Packager for SQL Server requires elevated permissions as documented here: /wiki/spaces/DDOC59/pages/795738600SQL Server Roles and Permissions for Datical DB. These roles inclue:

• dbcreator - server-level permission
• db_backupoperator - database-level permission

...

This approach leverages Datical's baseline to capture the state of the database (establish baseline before you do anything else - this will populate changelog.xml with initial change sets). Then use custom backup/restore script that doesn't use the native SQL Server backup/restore mechanism (and require the associated privileges).

This script, as part of restoring the database, performs drop of all objects to clean out the database. By configuring to use "on_demand" backup mode, Deployment Packger Packager will use the changelog instead of the backup file to restore the database.

...

• The database must be baselined so that all objects in the database have a corresponding change set in Datical's changelog. The baseline (change sets) serve as backup of the database from which databases will be rebuilt in case restore operation needs to be performed.
• Deployment Packager must be configured with databaseBackupMode=on_demand
• Use the attached groovy script for custom backup/restore method. 
  • Groovy script: BackupRestoreSqlServerNoBackupFile-v5.4.groovy. 
    • This script would need to be added to "extensions" directory, which requires additional property to be set in Deployment Packager configuration: extensionsPath=C://apps//DaticalDB//repl//scripts//extensions
  • As such, Deployment Packager must be configured with databaseBackupRestoreMethod=BackupRestoreSqlServerNoBackupFile

Step-by-Step

DBA:

1. Create a REF DB. This could be a blank database or generated from PROD (with no data). F
2. Setup permissions on REF database as follows:

   1. 
      

      Code Block
      language sql title Non-elevated permisssions linenumbers true
      USE [EPOD_CMS_REF] GO EXEC sp_addrolemember N'db_ddladmin', N'datical_user' GO EXEC sp_addrolemember N'db_datareader', N'datical_user' GO EXEC sp_addrolemember N'db_datawriter', N'datical_user' GO EXEC sp_addrolemember N'db_securityadmin', N'datical_user' GO GRANT VIEW DATABASE STATE to [datical_user] GO

      
      

Datical SME:

...

:

...

1. Save the groovy script (BackupRestoreSqlServerNoBackupFile-v5.4.groovy) to a path within Datical install directory, e.g., C://apps//DaticalDB//repl//scripts//extensions.
   1. Rename the file to BackupRestoreSqlServerNoBackupFile.groovy
   2. The script performs the following operations:
      1. Backup: No backup is taken. This is configured in Step 3 where Datical project is configured for "on_demand" backup mode.
      2. Restore: Performs a drop of all objects by executing SQL code (built into the groovy script) and then restores/rebuilds the database from change sets in the Datical project. 
2. Provide a mechanism to build REF DB using one of the following two approaches:
   1. If the REF DB is already populated, then create a baseline of your REF database using the command: hammer groovy project_baseline.groovy
   2. If the REF DB is a blank database, then DBAs will need to provide a DDL script of all objects from production instance. This script will need to be added to sql_direct/ref_only folder containing custom metadata.properties file that mentions contexts=REF_ONLY
3. Update deployPackager.properties with these new lines:
   1. databaseBackupMode=on_demand
   2. databaseBackupRestoreMethod=BackupRestoreSqlServerNoBackupFile
   3. extensionsPath=C://apps//DaticalDB//repl//scripts//extensions
4. Ensure that REF DB has context set to REF_ONLY. This will allow load script from sql_direct/REF_ONLY 

You are now configured with non-elevated use that can run Deployment Packager. Start packager your SQL scripts.

Expected Behavior

For packaging scripts using CONVERT packaging method (ddl): Deployment Packager will always perform a restore operation.  This will manifest as following intermediate deploy operation during packaging job:

Image Added

For packaging scripts using STOREDLOGIC, DDL_DIRECT, DATA_DML, SQLFILE, or DIRECT packaging methods: Deployment Packager may not always perform a restore operation. The restore operation will only be invoked when a failure is encountered during the packaging job. 

...