locked
Ability to generate deployment script and do the publish at the same time RRS feed

  • Question

  • Currently, I am using sqlpackage.exe in one of our release pipelines to:

    1. Generate the deployment scrips for each targeted environment and store those for audit purposes. I am using the -Action "Script" parameter for this purpose.

    2. Execute the actual deployment for each targeted environment . I am using -Action "Publish" parameter for this purpose.

    Executing both actions in sequence takes a long time and I would like to reduce the overall deployment time.

    Is there any way both actions can be performed in the same run ?


    Friday, February 7, 2020 8:25 PM

Answers

  • You should be able to use the /DeployScriptPath: option of the Publish action

    https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-ver15


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Marked as answer by bxconst19 Monday, February 10, 2020 8:18 PM
    Saturday, February 8, 2020 7:40 AM

All replies

  • No.  There is no way to generate the script and run publish in one command.

    However, you can speed up the process by running the script you generated in step 1 via sqlcmd, instead of running Publish and basically doing all the work again.

    Another option is to setup an audit on the SQL Server to track the changes, instead of keeping the script and eliminate step 1 entirely.

    This is the audit I run on all production servers to monitor changes.

    /*
    	Creates UserAudit and view in {DBNAME}
    
    	SEARCH AND REPLACE {SQLDIR} with actual SQL Directory, ie M:\Apps\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Audit\
    	AND CREATE DIRECTORY BEFORE RUNNING SCRIPT
    	SEARCH AND REPLACE {DBNAME} with existing Database to create audit view
    */
    
    USE [master]
    GO
    CREATE SERVER AUDIT [UserServerAudit]
    TO FILE 
    (	FILEPATH = N'{SQLDIR}'
    	,MAXSIZE = 1024 MB
    	,MAX_ROLLOVER_FILES = 10
    	,RESERVE_DISK_SPACE = OFF
    )
    WITH
    (	QUEUE_DELAY = 5000
    	,ON_FAILURE = CONTINUE
    	,AUDIT_GUID = '5ec3b99f-2336-45d2-846d-fe8ae1891cbc'
    )
    ALTER SERVER AUDIT [UserServerAudit] WITH (STATE = ON)
    GO
    CREATE SERVER AUDIT SPECIFICATION [UserServerAuditSpecification]
    FOR SERVER AUDIT [UserServerAudit]
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (DATABASE_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_CHANGE_GROUP),
    ADD (SCHEMA_OBJECT_CHANGE_GROUP),
    ADD (SERVER_OBJECT_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
    WITH (STATE = ON)
    GO
    USE [msdb]
    GO
    CREATE DATABASE AUDIT SPECIFICATION [SqlAgentObjectAccess_Audit_MSDB]
    FOR SERVER AUDIT [UserServerAudit]
    ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [dbo]),
    ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [SQLAgentUserRole]),
    ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [dbo]),
    ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [SQLAgentUserRole])
    WITH (STATE = ON)
    GO
    
    
    USE {DBNAME}
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE VIEW [dbo].[UserAudit_vw]
    AS
    SELECT
    	[EventDateLocal] = DATEADD(hh,DATEDIFF(hh,GETUTCDATE(), GETDATE()),aud.event_time), 
    	aud.server_instance_name,
    	ActionName = CASE WHEN act.action_id IS NULL THEN act2.name ELSE act.[name] END,
    	cm.class_type_desc,
    	aud.database_name,
    	aud.schema_name,
    	aud.object_name,
    	aud.statement,
    	additional_information = CAST(aud.additional_information AS XML),
    	aud.session_server_principal_name,
    	aud.server_principal_name,
    	aud.database_principal_name,
    	aud.target_server_principal_name,
    	aud.target_database_principal_name,
    	aud.file_name,
    	aud.audit_file_offset,
    	aud.sequence_number,
    	aud.succeeded,
    	aud.session_id
    	--,aud.*
    FROM sys.fn_get_audit_file ('{SQLDIR}\UserServerAudit*',default,default) aud
    	INNER JOIN sys.dm_audit_class_type_map cm
    		ON cm.class_type = aud.class_type
    	LEFT OUTER JOIN sys.dm_audit_actions act
    		ON act.action_id = aud.action_id
    			AND act.class_desc = cm.securable_class_desc
    	LEFT OUTER JOIN sys.dm_audit_actions act2
    		ON act2.action_id = aud.action_id
    			AND act2.class_desc = cm.class_type_desc
    
    WHERE aud.class_type <> 'A'
    	AND aud.server_principal_name NOT IN ('sa','serviceaccountname')  -- Ignore service account and maintenance commands
    	AND NOT (aud.class_type = 'U' AND aud.action_id = 'AL' AND aud.[statement] LIKE 'TRUNCATE TABLE %') -- Ignore TRUNCATE TABLE reported as alter table audits
    	AND NOT (aud.class_type IN ('U','V') AND aud.action_id = 'AL' AND aud.[statement] LIKE 'ALTER INDEX %' AND aud.[statement] LIKE '%REBUILD%') -- Ignore index rebuild reported as alter table audits
    GO
    
    
    


    Friday, February 7, 2020 8:31 PM
  • You should be able to use the /DeployScriptPath: option of the Publish action

    https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-ver15


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Marked as answer by bxconst19 Monday, February 10, 2020 8:18 PM
    Saturday, February 8, 2020 7:40 AM
  • Thank you Erick, somehow I missed this option but this is exactly what I needed and it works great.
    Monday, February 10, 2020 8:20 PM
  • I thought that option deleted the script after running the script?  It was only temp storage.  I don't have time to test it right now.

    Monday, February 10, 2020 8:23 PM