%3CLINGO-SUB%20id%3D%22lingo-sub-2118027%22%20slang%3D%22en-US%22%3EAzure%20Synapse%20SQL%20Pools%20Auto%20DR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2118027%22%20slang%3D%22en-US%22%3E%3CP%3EFully%20automate%20DR%20instances%20of%20your%20SQL%20Pools%20and%20reduce%20the%20RPO%20in%20case%20of%20a%20failure.%3CBR%20%2F%3EThis%20article%20will%20show%20how%20to%20use%20Azure%20Data%20Factory%20(or%20Azure%20Synapse%20Pipelines)%20to%20build%20a%20pipeline%20that%20can%20be%20used%20to%20fully%20automate%20DR%20SQL%20Pools%20after%20each%20automatic%20snapshot%2C%20which%20can%20then%20be%20paused%20to%20save%20on%20costs.%20The%20pipeline%20is%20ready%20to%20be%20used%20to%20replicate%20more%20than%20one%20SQL%20Pool%20because%20it%20is%20completely%20generic%20and%20parameterized.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EWhat%20is%20needed%3C%2FSTRONG%3E%3CBR%20%2F%3EAzure%20Key%20Vault%20x1%20-%20new%20or%20existing%3CBR%20%2F%3EAzure%20Data%20Factory%20v2%20(or%20Azure%20Synapse%20Pipelines)%20x1%20-%20new%20or%20existing%3CBR%20%2F%3EAzure%20Synapse%20Analytics%20workspace%20x2%20-%20new%20or%20existing%2C%20to%20be%20used%20as%20source%20and%20target%2FDR%3CBR%20%2F%3ESQL%20Pool%20table%20x1%20%E2%80%93%20metadata%20table%20to%20store%20the%20execution%20runs%3CBR%20%2F%3ESQL%20Pool%20stored%20procedure%20x%201%20%E2%80%93%20to%20register%20each%20full%20run%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%225%22%3E%3CSTRONG%3E1.%20Preparation%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EAzure%20key%20vault%20secret%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3ECreate%20a%20new%20Azure%20key%20vault%2C%20or%20use%20an%20existing%20one%2C%20and%20create%20a%20new%20secret%20using%3A%3C%2FP%3E%0A%3CTABLE%20class%3D%22lia-align-left%22%20style%3D%22width%3A%20100%25%3B%22%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2213.812154696132598%25%22%3EName%3C%2FTD%3E%0A%3CTD%20width%3D%2286.18784530386742%25%22%3E%3CWORKSPACE_NAME%3E-%3CSQL_POOL_NAME%3E%20e.g.%20mySynapseWorkspace-MySQLPool%3C%2FSQL_POOL_NAME%3E%3C%2FWORKSPACE_NAME%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2213.812154696132598%25%22%3EValue%3C%2FTD%3E%0A%3CTD%20width%3D%2286.18784530386742%25%22%3E%3CCONNECTION_STRING%3E%20as%20found%20in%20the%20Azure%20Synapse%20SQL%20Pool%20connection%20string%3C%2FCONNECTION_STRING%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EScreenshots%20of%20creating%20a%20new%20secret%20and%20getting%20the%20connection%20string%20from%20a%20SQL%20Pool%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253217iA5CFFE57A4880B0E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Create%20a%20new%20secret%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ECreate%20a%20new%20secret%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253227i046FD72299AAD14B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Get%20SQL%20Pool%20Connection%20string%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EGet%20SQL%20Pool%20Connection%20string%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENote%3A%20replace%20User%20ID%20and%20Password%20as%20needed.%3CBR%20%2F%3EYou%20need%20to%20grant%20the%20Azure%20Data%20Factory%20Managed%20Identity%20access%20to%20your%20Azure%20Key%20vault.%20Learn%20more%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fstore-credentials-in-key-vault%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EADF%20linked%20service%20to%20Azure%20Key%20vault%3C%2FSTRONG%3E%3CBR%20%2F%3EIn%20ADF%2C%20create%20a%20new%20linked%20service%20to%20the%20Azure%20Key%20vault%20instance%20where%20the%20SQL%20Pool%20connection%20strings%20are%20saved.%20This%20will%20be%20used%20to%20prevent%20having%20credentials%20stored%20in%20the%20ADF%20pipelines%20as%20well%20as%20to%20allow%20us%20to%20build%20a%20generic%20process.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253244i288E31D18A3B2F28%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Create%20a%20new%20linked%20service%20to%20Azure%20Key%20vault%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ECreate%20a%20new%20linked%20service%20to%20Azure%20Key%20vault%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EADF%20linked%20service%20to%20Azure%20Synapse%20Analytics%3C%2FSTRONG%3E%3CBR%20%2F%3EIn%20ADF%2C%20create%20a%20new%20linked%20service%20to%20Azure%20Synapse%20Analytics.%20This%20linked%20service%20will%20use%20the%20secrets%20in%20the%20Azure%20Key%20vault%20we%20referred%20before%2C%20making%20this%20a%20generic%20connector%20to%20Azure%20Synapse%20SQL%20Pools%20that%20we%20can%20use%20to%20connect%20to%20any%20SQL%20Pool%20as%20long%20as%20there%E2%80%99s%20a%20corresponding%20secret%20containing%20a%20valid%20connection%20string.%3C%2FP%3E%0A%3CP%3EHere%20are%20the%20steps%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253250i4A676C04C176CAFF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22New%20Azure%20Synapse%20Analytics%20linked%20service%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ENew%20Azure%20Synapse%20Analytics%20linked%20service%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20327px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253251i462908AD5DAAE58E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22New%20linked%20service%20parameters%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ENew%20linked%20service%20parameters%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20configure%20the%20Secret%20name%20parameter%2C%20select%20Add%20dynamic%20content%20and%20in%20the%20new%20window%20click%20in%20the%20plus%20(%2B)%20sign%20to%20create%20a%20new%20parameter%20for%20the%20SQL%20Pool%20workspace%20name.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253253iEB3FB17CD8717492%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Add%20dynamic%20content%20window%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EAdd%20dynamic%20content%20window%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253257iC6E3370E00AD131C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22New%20parameter%20for%20the%20workspace%20name%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ENew%20parameter%20for%20the%20workspace%20name%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3BClick%20Save%20to%20return%20to%20the%20Add%20dynamic%20content%20window.%20Add%20another%20new%20parameter%20for%20the%20SQL%20Pool%20name%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253258i9A49ED1EDCBDCD61%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22New%20parameter%20for%20the%20SQL%20Pool%20name%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ENew%20parameter%20for%20the%20SQL%20Pool%20name%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%40concat(%0AlinkedService().LS_SYNAPSE_WORKSPACE_NAME%2C%0A'-'%2C%0AlinkedService().LS_SYNAPSE_SQL_POOL_NAME%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20click%20on%20finish%20to%20accept%20the%20dynamic%20content%20and%20then%20on%20Test%20connection%2C%20to%20confirm%20that%20everything%20is%20ok.%20For%20that%20you%20need%20to%20provide%20values%20for%20the%202%20parameters%20of%20this%20linked%20service%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253261i1A9C4E3BF01528B8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Parameter%20for%20the%20generic%20linked%20service%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EParameter%20for%20the%20generic%20linked%20service%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3BThis%20is%20a%20dynamic%20connection%20to%20an%20Azure%20Synapse%20SQL%20Pool%2C%20meaning%20that%20we%20can%20use%20this%20linked%20service%20to%20connect%20to%20SQL%20Pools%20from%20different%20workspaces%2C%20as%20long%20as%20we%20have%20a%20defined%20connection%20string%20in%20the%20form%20of%20a%20secret%20in%20the%20above%20Azure%20Key%20vault.%3CBR%20%2F%3ENote%3A%20don%E2%80%99t%20forget%20to%20grant%20the%20ADF%20Managed%20Identity%20access%20to%20your%20Azure%20Synapse%20Analytics%20workspace.%20Learn%20more%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fconnector-azure-sql-data-warehouse%23using-managed-service-identity-authentication%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ECreate%20metadata%20table%20and%20registration%20stored%20procedure%3C%2FSTRONG%3E%3CBR%20%2F%3EIn%20each%20of%20the%20SQL%20Pools%20you%20want%20to%20automatically%20restore%2C%20create%20a%20new%20table.%20It%20will%20be%20used%20to%20store%20all%20the%20restores%20done%20by%20this%20ADF%20process%20and%20drive%20new%20ones.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20TABLE%20%5Bdbo%5D.%5Brestored_backups%5D%0A(%0A%20%5Brun_id%5D%20%5Bint%5D%20NOT%20NULL%2C%0A%20%5Bname%5D%20%5Bvarchar%5D(100)%20NOT%20NULL%0A)%0AWITH%0A(%0A%20DISTRIBUTION%20%3D%20ROUND_ROBIN%2C%0A%20HEAP%0A)%0AGO%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20addition%2C%20create%20a%20new%20stored%20procedure.%20This%20process%20will%20use%20it%20to%20register%20full%20runs%20and%20drive%20new%20executions.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20PROC%20%5Bdbo%5D.%5Bp_register_restored_backup%5D%20%40p_restore_id%20%5BINT%5D%2C%20%40p_name%20%5Bvarchar%5D(100)%20AS%0ABEGIN%0A%20%20%20%20INSERT%20INTO%20dbo.restored_backups(run_id%2C%20name)%20VALUES(%40p_restore_id%2C%20%40p_name)%0AEND%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EADF%20Dataset%20for%20metadata%3C%2FSTRONG%3E%3CBR%20%2F%3EBefore%20we%20create%20our%20first%20pipeline%2C%20we%20need%20to%20create%20a%20new%20Azure%20Synapse%20Analytics%20ADF%20dataset%20to%20read%20the%20metadata.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253263i4CA5DA98B30778C0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22New%20Azure%20Synapse%20Analytics%20dataset%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ENew%20Azure%20Synapse%20Analytics%20dataset%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3EName%20the%20dataset%20%E2%80%9CLastRestorePoint%E2%80%9D%2C%20select%20the%20previously%20create%20linked%20service%20for%20the%20connection%20(SynapseFullParams)%20and%20click%20Ok%20to%20save%20it.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253271iD8D4D181AE947AC7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Azure%20Synapse%20Analytics%20dataset%20properties%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EAzure%20Synapse%20Analytics%20dataset%20properties%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%20that%20the%20Dataset%20uses%20the%20previously%20configured%20generic%20Azure%20Synapse%20Analytics%20linked%20service%2C%20that%20needs%202%20parameters%20(LS_SYNAPSE_WORKSPACE_NAME%20and%20LS_SYNAPSE_SQL_POOL_NAME).%20We%20now%20also%20need%20to%20create%202%20similar%20parameters%20in%20the%20dataset%20(DS_SYNAPSE_WORKSPACE_NAME%20and%20DS_SYNAPSE_SQL_POOL_NAME)%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253266i7E9F94B658ED2075%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Dataset%20parameters%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EDataset%20parameters%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdditionally%2C%20and%20in%20the%20Connection%20tab%2C%20pass%20them%20to%20the%20linked%20service%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253269iC6FFD2C46C18096F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Passing%20the%20dataset%20parameters%20to%20the%20linked%20service%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EPassing%20the%20dataset%20parameters%20to%20the%20linked%20service%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2237.84530386740332%25%22%3ELS_SYNAPSE_WORKSPACE_NAME%3C%2FTD%3E%0A%3CTD%20width%3D%2262.15469613259668%25%22%3E%40dataset().DS_SYNAPSE_WORKSPACE_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2237.84530386740332%25%22%3ELS_SYNAPSE_SQL_POOL_NAME%3C%2FTD%3E%0A%3CTD%20width%3D%2262.15469613259668%25%22%3E%40dataset().DS_SYNAPSE_SQL_POOL_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%225%22%3E%3CSTRONG%3E2.%20Pipelines%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ECreate%20first%20pipeline%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThe%20first%20pipeline%20will%20check%20if%20a%20SQL%20Pool%20restore%20is%20needed%2C%20by%20matching%20the%20latest%20automatic%20SQL%20Pool%20backup%20record%20(in%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-catalog-views%2Fsys-pdw-loader-backup-runs-transact-sql%3Fview%3Dazure-sqldw-latest%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Esys.pdw_loader_backup_runs%3C%2FA%3E)%20and%20our%20metadata%20table.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3ECreate%20a%20new%20pipeline%2C%20name%20it%20AutoDr%20and%20create%20the%20following%20parameters%3A%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22100%25%22%3EP_SOURCE_WORKSPACE_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_SOURCE_SQL_POOL_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_SOURCE_SUBSCRIPTION_ID%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_SOURCE_RESOURCE_GROUP_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_SUBSCRIPTION_ID%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_RESOURCE_GROUP_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_LOCATION%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_WORKSPACE_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_SQL_POOL_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_DEPLOYMENT_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3EThese%20will%20be%20used%20later%20and%20will%20help%20keep%20this%20process%20as%20generic%20as%20possible.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDuring%20testing%2C%20you%20can%20assign%20some%20default%20values%20to%20the%20parameters%2C%20to%20save%20some%20writing%20when%20you%20manually%20trigger%20the%20pipelines.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253275iAA6098DF1280851F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Pipeline%20parameters%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EPipeline%20parameters%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20add%20a%20Lookup%20activity%20and%20configure%20as%20shown%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253278i8C6DCA5EFE5B9575%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Lookup%20parameters%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ELookup%20parameters%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2235.773480662983424%25%22%20height%3D%2256px%22%3ESource%20dataset%3C%2FTD%3E%0A%3CTD%20width%3D%2264.22651933701657%25%22%20height%3D%2256px%22%3Ethe%20name%20of%20the%20previously%20created%20dataset%20for%20the%20metadata%2C%20in%20our%20case%20%E2%80%9CLastRestorePoint%E2%80%9D%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2235.773480662983424%25%22%20height%3D%2229px%22%3EDS_SYNAPSE_WORKSPACE_NAME%3C%2FTD%3E%0A%3CTD%20width%3D%2264.22651933701657%25%22%20height%3D%2229px%22%3E%40pipeline().parameters.P_SOURCE_WORKSPACE_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2235.773480662983424%25%22%20height%3D%2229px%22%3EDS_SYNAPSE_SQL_POOL_NAME%3C%2FTD%3E%0A%3CTD%20width%3D%2264.22651933701657%25%22%20height%3D%2229px%22%3E%40pipeline().parameters.P_SOURCE_SQL_POOL_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2235.773480662983424%25%22%20height%3D%2229px%22%3EUse%20query%3C%2FTD%3E%0A%3CTD%20width%3D%2264.22651933701657%25%22%20height%3D%2229px%22%3EQuery%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2235.773480662983424%25%22%20height%3D%22236px%22%3EQuery%3C%2FTD%3E%0A%3CTD%20width%3D%2264.22651933701657%25%22%20height%3D%22236px%22%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Eselect%20top%201%0A%20%20%20%20%20%20%20backups.run_id%20%20as%20last_backup_run_id%2C%0A%20%20%20%20%20%20%20backups.name%20%20%20%20as%20last_backup_name%2C%0A%20%20%20%20restores.run_id%20as%20last_restore_run_id%0Afrom%20sys.pdw_loader_backup_runs%20as%20backups%0Aleft%20join%20dbo.restored_backups%20%20as%20restores%0A%20%20%20%20%20%20on%20(restores.run_id%20%3D%20backups.run_id)%0Awhere%20backups.operation_type%20%3D%20'BACKUP'%20and%0A%20%20%20%20%20%20backups.mode%20%3D%20'FULL'%20and%20%0A%20%20%20%20%20%20backups.status%20%3D%20'COMPLETED'%20and%0A%20%20%20backups.progress%20%3D%20100%0Aorder%20by%20backups.run_id%20desc%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2235.773480662983424%25%22%20height%3D%2229px%22%3EFirst%20row%20only%3C%2FTD%3E%0A%3CTD%20width%3D%2264.22651933701657%25%22%20height%3D%2229px%22%3Echecked%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2235.773480662983424%25%22%20height%3D%2229px%22%3EAll%20other%20settings%3C%2FTD%3E%0A%3CTD%20width%3D%2264.22651933701657%25%22%20height%3D%2229px%22%3Eas%20default%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ENow%20publish%20your%20changes%20and%20test%20the%20pipeline%20(Add%20trigger%20%2F%20Trigger%20now).%20If%20you%20didn%E2%80%99t%20add%20default%20values%20to%20the%20pipeline%20parameters%2C%20know%20that%20for%20now%20only%20P_SOURCE_WORKSPACE_NAME%20and%20P_SOURCE_SQL_POOL_NAME%20are%20needed.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENote%3A%20The%20pipeline%20should%20run%20successfully%20no%20matter%20if%20there%E2%80%99s%20a%20restore%20needed%20or%20not.%20For%20now%2C%20it%20is%20only%20executing%20the%20above%20SQL%20query%20against%20a%20generically%20configured%20Azure%20Synapse%20Analytics%20SQL%20Pool.%20We%20will%20return%20to%20this%20pipeline%20later.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ECreate%20second%20pipeline%3C%2FSTRONG%3E%3CBR%20%2F%3EThe%20goal%20for%20this%20pipeline%20is%3A%3CBR%20%2F%3E-%20delete%20the%20target%20DR%20SQL%20Pool%2C%20if%20any%20exists%3CBR%20%2F%3E-%20restore%20a%20running%20SQL%20Pool%20from%20the%20source%20workspace%20into%20the%20target%20DR%20workspace%3CBR%20%2F%3E-%20wait%20for%20the%20new%20SQL%20Pool%20to%20be%20created%20and%20restored%3CBR%20%2F%3E-%20pause%20the%20target%20DR%20SQL%20Pool%2C%20as%20an%20optional%20step%20to%20save%20costs%20if%20you%20don%E2%80%99t%20need%20to%20use%20this%20pool.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreate%20a%20new%20pipeline%20named%20%22AutoRestoreSQLPool%22%20and%20create%20the%20same%20parameters%20as%20for%20the%20first%20pipeline%3A%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22100%25%22%3EP_SOURCE_WORKSPACE_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_SOURCE_SQL_POOL_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_SOURCE_SUBSCRIPTION_ID%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_SOURCE_RESOURCE_GROUP_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_SUBSCRIPTION_ID%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_RESOURCE_GROUP_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_LOCATION%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_WORKSPACE_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_SQL_POOL_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_DEPLOYMENT_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdd%20a%20Web%20activity%2C%20name%20it%20%E2%80%9CDelete%20SQL%20Pool%E2%80%9D%20and%20configure%20as%20follows%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253281iC7C13EA6E2B345BF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Web%20activity%20settings%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EWeb%20activity%20settings%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%3EURL%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%40concat(%0A'https%3A%2F%2Fmanagement.azure.com%2Fsubscriptions%2F'%2C%0Apipeline().parameters.P_DR_SUBSCRIPTION_ID%2C%0A'%2Fresourcegroups%2F'%2C%0Apipeline().parameters.P_DR_RESOURCE_GROUP_NAME%2C%0A'%2Fproviders%2FMicrosoft.Synapse%2Fworkspaces%2F'%2C%0Apipeline().parameters.P_DR_WORKSPACE_NAME%2C%0A'%2FsqlPools%2F'%2C%0Apipeline().parameters.P_DR_SQL_POOL_NAME%2C%0A'%3Fapi-version%3D2019-06-01-preview')%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EMethod%3C%2FTD%3E%0A%3CTD%3EDELETE%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EAuthentication%3C%2FTD%3E%0A%3CTD%3EMSI%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EResource%3C%2FTD%3E%0A%3CTD%3E%3CA%20href%3D%22https%3A%2F%2Fmanagement.azure.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmanagement.azure.com%3C%2FA%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20will%20call%20the%20SQL%20Pool%20REST%20API%20and%20send%20a%20DELETE%20request.%20For%20more%20information%20visit%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Frest%2Fapi%2Fsynapse%2Fsqlpools%2Fdelete%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESql%20Pools%20%E2%80%93%20Delete%3C%2FA%3E.%3CBR%20%2F%3ENote%3A%20the%20Azure%20Data%20Factory%20Managed%20Service%20Identity%20must%20have%20authorization%20to%20perform%20these%20types%20of%20requests%2C%20in%20this%20case%20to%20delete%20SQL%20Pools%2C%20on%20the%20involved%20Azure%20Synapse%20Analytics%20workspaces.%3CBR%20%2F%3ETo%20grant%20the%20ADF%20MSI%20the%20Contributor%20role%20on%20a%20workspace%20using%20the%20Azure%20portal%2C%20open%20the%20%E2%80%9CAccess%20control%20(IAM)%E2%80%9D%20of%20that%20workspace%2C%20click%20on%20%E2%80%9C%2B%20Add%E2%80%9D%20%2F%20Add%20role%20assignment%20and%20populate%20as%20necessary.%20Here%E2%80%99s%20an%20example%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253312iE622F9F601ED44AF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Give%20Contributor%20role%20to%20an%20ADF%20MSI%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EGive%20Contributor%20role%20to%20an%20ADF%20MSI%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3BNote%3A%20don%E2%80%99t%20forget%20to%20select%20the%20MSI%20entry%20and%20click%20Save.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20pipeline%20can%20now%20be%20published%20and%20executed.%3CBR%20%2F%3EThis%20pipeline%20will%20not%20abort%20if%20there%E2%80%99s%20no%20SQL%20Pool%20with%20the%20given%20name%20and%20workspace%20(P_DR_SQL_POOL_NAME%20and%20P_DR_WORKSPACE_NAME).%20Because%20we%20will%20create%20a%20new%20SQL%20Pool%20using%20the%20same%20parameters%2C%20we%20don%E2%80%99t%20need%20to%20care%20for%20this%20fact%20but%2C%20if%20the%20goal%20was%20to%20only%20delete%20the%20target%20SQL%20Pool%2C%20then%20a%20verification%20activity%20should%20be%20added%20after%20the%20delete%20is%20called.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%2C%20add%20a%20second%20Web%20activity%20and%20connect%20the%20output%20of%20the%20above%20created%20Web%20activity%20(Delete%20SQL%20Pool)%20to%20this%20one%20and%20name%20it%20%E2%80%9CRestore%20SQL%20Pool%E2%80%9D.%20To%20learn%20more%20about%20the%20method%20used%20in%20this%20call%20visit%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-resource-manager%2Ftemplates%2Fdeploy-rest%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ethis%20link%3C%2FA%3E.%3CBR%20%2F%3EConfigure%20the%20settings%20as%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253314iC1EFCC156B810874%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Restore%20SQL%20Pool%20settings%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ERestore%20SQL%20Pool%20settings%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2217.127071823204417%25%22%3EURL%3C%2FTD%3E%0A%3CTD%20width%3D%2282.87292817679558%25%22%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%40concat(%0A'https%3A%2F%2Fmanagement.azure.com%2Fsubscriptions%2F'%2C%0Apipeline().parameters.P_DR_SUBSCRIPTION_ID%2C%0A'%2Fresourcegroups%2F'%2C%0Apipeline().parameters.P_DR_RESOURCE_GROUP_NAME%2C%0A'%2Fproviders%2FMicrosoft.Resources%2Fdeployments%2F'%2C%0Apipeline().parameters.P_DR_DEPLOYMENT_NAME%2C%0A'%3Fapi-version%3D2020-06-01')%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2217.127071823204417%25%22%3EMethod%3C%2FTD%3E%0A%3CTD%20width%3D%2282.87292817679558%25%22%3EPUT%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2217.127071823204417%25%22%3EAuthentication%3C%2FTD%3E%0A%3CTD%20width%3D%2282.87292817679558%25%22%3EMSI%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2217.127071823204417%25%22%3EResource%3C%2FTD%3E%0A%3CTD%20width%3D%2282.87292817679558%25%22%3E%3CA%20href%3D%22https%3A%2F%2Fmanagement.azure.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmanagement.azure.com%3C%2FA%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2217.127071823204417%25%22%3EBody%3C%2FTD%3E%0A%3CTD%20width%3D%2282.87292817679558%25%22%3E%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%7B%0A%20%20%20%22properties%22%3A%20%7B%0A%20%20%20%20%20%20%22mode%22%3A%20%22Incremental%22%2C%0A%20%20%20%20%20%20%22template%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%22%24schema%22%3A%20%22http%3A%2F%2Fschema.management.azure.com%2Fschemas%2F2014-04-01-preview%2FdeploymentTemplate.json%23%22%2C%0A%20%20%20%20%20%20%20%20%20%22contentVersion%22%3A%20%221.0.0.0%22%2C%0A%20%20%20%20%20%20%20%20%20%22resources%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22type%22%3A%20%22Microsoft.Synapse%2Fworkspaces%2FsqlPools%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22apiVersion%22%3A%20%222019-06-01-preview%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22location%22%3A%20%22%40%7Bpipeline().parameters.P_DR_LOCATION%7D%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22tags%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22name%22%3A%20%22%40%7Bpipeline().parameters.P_DR_WORKSPACE_NAME%7D%2F%40%7Bpipeline().parameters.P_DR_SQL_POOL_NAME%7D%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22properties%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22createMode%22%3A%20%22Recovery%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22recoverableDatabaseId%22%3A%20%22%2Fsubscriptions%2F%40%7Bpipeline().parameters.P_SOURCE_SUBSCRIPTION_ID%7D%0A%2FresourceGroups%2F%40%7Bpipeline().parameters.P_SOURCE_RESOURCE_GROUP_NAME%7D%0A%2Fproviders%2FMicrosoft.Synapse%2Fworkspaces%2F%40%7Bpipeline().parameters.P_SOURCE_WORKSPACE_NAME%7D%0A%2Frecoverabledatabases%2F%40%7Bpipeline().parameters.P_SOURCE_SQL_POOL_NAME%7D%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22resources%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22condition%22%3A%20false%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22apiVersion%22%3A%20%222019-06-01-preview%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22dependsOn%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22Microsoft.Synapse%2Fworkspaces%2F%40%7Bpipeline().parameters.P_DR_WORKSPACE_NAME%7D%0A%2FsqlPools%2F%40%7Bpipeline().parameters.P_DR_SQL_POOL_NAME%7D%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22location%22%3A%20%22%40%7Bpipeline().parameters.P_DR_LOCATION%7D%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22name%22%3A%20%22config%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22properties%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22Enabled%22%3A%20false%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22type%22%3A%20%22metadataSync%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5D%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%20%5D%0A%20%20%20%20%20%20%7D%0A%20%20%20%7D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ENote%3A%20due%20to%20presentation%20limitations%2C%20some%20lines%20were%20split%20(recoverableDatabaseId%20and%20dependsOn).%20Either%20merge%20them%20or%20use%20the%20file%20attached%20at%20the%20end%20of%20this%20page%20(%22%3CSPAN%3ERestore%20SQL%20Pool%20-%20body%20request.zip%22%3C%2FSPAN%3E)%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20activity%20will%20call%20the%20Azure%20Resource%20Manager%20REST%20API%20and%20request%20the%20incremental%20deployment%20of%20an%20Azure%20resource%2C%20in%20this%20case%20an%20Azure%20Synapse%20Analytics%20SQL%20Pool%20that%20will%20be%20recovered%20from%20another%20existing%20SQL%20Pool.%20More%20options%20can%20be%20found%20in%20the%20online%20documentation%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-resource-%2520REST%20APImanager%2Ftemplates%2Fdeploy-rest%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3CBR%20%2F%3ERunning%20this%20pipeline%20will%20trigger%20the%20deletion%20of%20the%20target%20DR%20SQL%20Pool%20and%20the%20creation%20of%20a%20new%20one%20by%20recovering%20from%20a%20source%20and%20existing%20SQL%20Pool.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAt%20this%20point%2C%20the%20pipeline%20should%20look%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253321i0ECC7B712FE01936%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Current%20design%20of%20the%20second%20pipeline%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ECurrent%20design%20of%20the%20second%20pipeline%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20next%20step%20is%20to%20pause%20the%20newly%20created%20target%20DR%20SQL%20Pool.%20We%20can%E2%80%99t%20do%20that%20immediately%20after%20calling%20the%20restore%20because%20it%E2%80%99s%20an%20asynchronous%20call%20that%20takes%20some%20time%2C%20depending%20on%20the%20size%20of%20the%20source%20SQL%20Pool.%20We%20need%20to%20wait%20and%20that%E2%80%99s%20exactly%20what%20the%20next%20activity%20will%20do.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreate%202%20new%20variables%20that%20will%20be%20used%20to%20check%20if%20the%20target%20DR%20SQL%20Pool%20is%20created%20and%20restored.%20Notice%20the%20Boolean%20type%20for%20both%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253322i4B7ACFDF6D4E6CFB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22New%20pipeline%20variables%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ENew%20pipeline%20variables%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%2C%20add%20a%20new%20Until%20activity%20and%20connect%20the%20output%20of%20the%20%E2%80%9CRestore%20SQL%20Pool%E2%80%9D%20activity%20to%20the%20input%20of%20this%20one.%20Name%20it%20%E2%80%9CWait%20for%20Creation%20and%20Restore%E2%80%9D.%3CBR%20%2F%3EThe%20Expression%20inside%20the%20Settings%20tab%20should%20be%3A%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22100%25%22%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%40and(%0Avariables('V_DR_CREATED')%2C%0Avariables('V_DR_RESTORED')%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EThis%20means%20that%20this%20activity%20will%20loop%20through%20until%20the%20newly%20restored%20target%20DR%20SQL%20Pool%20is%20created%20and%20fully%20restored.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EClick%20on%20the%20pencil%20to%20design%20the%20activities%20for%20the%20loop.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253323i2E95F40F74797C4F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22The%20Until%20activity%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EThe%20Until%20activity%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ETo%20better%20understand%20all%20the%20activities%20and%20how%20they%20are%20connected%2C%20let%E2%80%99s%20have%20a%20look%20at%20how%20it%20will%20look%20like%20at%20the%20end%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253324i3C1E390306B476E3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22The%20complete%20second%20pipeline%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EThe%20complete%20second%20pipeline%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStart%20by%20adding%20a%20new%20Web%20activity%2C%20name%20it%20%E2%80%9CGet%20SQL%20Pool%20Information%E2%80%9D%20and%20configure%20as%20follows%3A%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%3EURL%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%40concat(%0A'https%3A%2F%2Fmanagement.azure.com%2Fsubscriptions%2F'%2C%0Apipeline().parameters.P_DR_SUBSCRIPTION_ID%2C%0A'%2Fresourcegroups%2F'%2C%0Apipeline().parameters.P_DR_RESOURCE_GROUP_NAME%2C%0A'%2Fproviders%2FMicrosoft.Synapse%2Fworkspaces%2F'%2C%0Apipeline().parameters.P_DR_WORKSPACE_NAME%2C%0A'%2FsqlPools%2F'%2C%0Apipeline().parameters.P_DR_SQL_POOL_NAME%2C%0A'%3Fapi-version%3D2019-06-01-preview')%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EMethod%3C%2FTD%3E%0A%3CTD%3EGET%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EAuthentication%3C%2FTD%3E%0A%3CTD%3EMSI%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EResource%3C%2FTD%3E%0A%3CTD%3E%3CA%20href%3D%22https%3A%2F%2Fmanagement.azure.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmanagement.azure.com%3C%2FA%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EAll%20other%20settings%3C%2FTD%3E%0A%3CTD%3Eas%20default%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20activity%20sends%20a%20request%20for%20information%20on%20a%20SQL%20Pool%20to%20the%20Azure%20Synapse%20SQL%20Poll%20REST%20API.%20For%20more%20information%20visit%20this%20page.%3CBR%20%2F%3EIf%20the%20request%20fails%2C%20we%20assume%20the%20target%20DR%20SQL%20Pool%20is%20not%20yet%20created%20(bottom%20part%20of%20the%20flow)%20and%20otherwise%20we%20will%20look%20at%20the%20status%20property%2C%20which%20will%20be%20Online%20when%20the%20SQL%20Pool%20is%20fully%20restored%20and%20ready%20to%20be%20used.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdd%202%20new%20Set%20variable%20activities%2C%20connect%20them%20as%20the%20previous%20picture%20and%20rename%20them%20accordingly%20(%E2%80%9CSet%20V_DR_CREATED%20to%20true%E2%80%9D%20and%20%E2%80%9CSet%20V_DR_CREATED%20to%20false%E2%80%9D).%3CBR%20%2F%3ENote%3A%20to%20change%20a%20connection%20from%20green%20(Success)%20to%20red%20(Failure)%2C%20select%20the%20connection%20you%20want%20to%20change%2C%20right%20click%20it%20and%20change%20its%20%E2%80%9CChange%20To%E2%80%9D%20value%20as%20desired.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20349px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253348i503DC0CE31769B99%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Changing%20connection%20conditions%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EChanging%20connection%20conditions%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20settings%20for%20these%202%20activities%20should%20be%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253358iC45843821C3634F2%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22%E2%80%9CSet%20V_DR_CREATED%20to%20true%E2%80%9D%20settings%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3E%E2%80%9CSet%20V_DR_CREATED%20to%20true%E2%80%9D%20settings%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253359i20480C98615A1E57%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22%E2%80%9CSet%20V_DR_CREATED%20to%20false%E2%80%9D%20settings%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3E%E2%80%9CSet%20V_DR_CREATED%20to%20false%E2%80%9D%20settings%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20finish%20the%20bottom%20part%20of%20the%20flow%20(Failure)%2C%20add%20a%20new%20Wait%20activity%2C%20name%20it%20%E2%80%9CWait%20For%20Creation%20-%2030%20secs%E2%80%9D%2C%20connect%20it%20as%20depicted%20above%20and%20change%20its%20settings%20to%2030%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253354i8151D72D95015A1F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22%E2%80%9CWait%20For%20Creation%20-%2030%20secs%E2%80%9D%20settings%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3E%E2%80%9CWait%20For%20Creation%20-%2030%20secs%E2%80%9D%20settings%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3BThis%20activity%20will%20wait%2030%20seconds%20before%20allowing%20the%20Until%20activity%20to%20continue.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENext%2C%20add%20an%20If%20Condition%20activity%2C%20name%20it%20%E2%80%9CCheck%20If%20Restored%E2%80%9D%2C%20connect%20it%20as%20shown%20before%20and%20change%20its%20Expression%20to%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%40equals(%0Aactivity('Get%20SQL%20Pool%20Information').output.properties.status%2C%0A'Online'%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3BThis%20activity%20will%20read%20the%20value%20of%20the%20status%20property%20of%20the%20newly%20created%20target%20DR%20SQL%20Pool%20and%20check%20if%20it%20is%20%E2%80%9COnline%E2%80%9D.%20If%20not%2C%20we%20need%20to%20wait%20some%20time%20before%20checking%20again%2C%20otherwise%20the%20SQL%20Pool%20is%20ready%20to%20be%20used%20and%20we%20can%20continue%20with%20the%20pipeline.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20achieve%20this%2C%20we%20configure%20the%20If%20Condition%20activity%20as%20follows.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3Ea)%20When%20true%3C%2FSTRONG%3E%2C%20we%20add%20a%20new%20Set%20Variable%20activity%2C%20name%20it%20%E2%80%9CSet%20V_DR_RESTORED%20to%20true%E2%80%9D%20and%20configure%20as%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253357iD44DA55961E3453E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22%E2%80%9CSet%20V_DR_RESTORED%20to%20true%E2%80%9D%20settings%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3E%E2%80%9CSet%20V_DR_RESTORED%20to%20true%E2%80%9D%20settings%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20case%20we%20signal%20that%20the%20SQL%20Pool%20is%20fully%20restored.%20This%20part%20of%20the%20pipeline%20will%20look%20like%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253356i021CD659686A1542%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Flow%20when%20the%20SQL%20Pool%20is%20fully%20restored%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EFlow%20when%20the%20SQL%20Pool%20is%20fully%20restored%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3Ea)%20When%20false%3C%2FSTRONG%3E%2C%20we%20configure%20as%20follows.%3CBR%20%2F%3EAdd%20a%20Set%20Variable%20activity%2C%20name%20it%20%E2%80%9CSet%20V_DR_RESTORED%20to%20false%E2%80%9D%20and%20set%20the%20Variables%20tab%20as%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253361i96C177C5CC3E9463%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22%E2%80%9CSet%20V_DR_RESTORED%20to%20false%E2%80%9D%20settings%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3E%E2%80%9CSet%20V_DR_RESTORED%20to%20false%E2%80%9D%20settings%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%2C%20add%20a%20new%20Wait%20activity%2C%20name%20it%20%E2%80%9CWait%20For%20Restore%20-%2030%20secs%E2%80%9D%2C%20connect%20it%20to%20the%20previous%20activity%20and%20configure%20its%20Wait%20time%20in%20seconds%20to%2030.%3CBR%20%2F%3EThis%20part%20of%20the%20pipeline%20will%20look%20like%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253362i4E3B12105B60F6BB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22When%20the%20SQL%20Pool%20is%20not%20yet%20restored%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EWhen%20the%20SQL%20Pool%20is%20not%20yet%20restored%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CU%3EIf%20there%E2%80%99s%20no%20need%20to%20pause%3C%2FU%3E%20the%20target%20DR%20SQL%20Poll%2C%20then%20this%20second%20pipeline%20is%20finished%20and%20%3CU%3Ewe%20can%20jump%20to%20Complete%20the%20second%20pipeline%3C%2FU%3E.%3CBR%20%2F%3EIf%2C%20instead%2C%20the%20SQL%20Pool%20needs%20to%20be%20paused%2C%20then%20we%20need%20to%20add%20that%20functionality%20as%20described%20in%20this%20next%20step.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAt%20the%20top%20level%20of%20the%20pipeline%20(outside%20the%20IF%E2%80%99s%20and%20Until%E2%80%99s)%2C%20add%20a%20new%20Web%20activity%2C%20name%20it%20%E2%80%9CPause%20SQL%20Pool%E2%80%9D%2C%20connect%20it%20to%20the%20end%20of%20the%20Until%20activity%20and%20configure%20as%20follows%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20379px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253363iDBEF491AFACC6332%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Activity%20to%20pause%20the%20SQL%20Pool%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EActivity%20to%20pause%20the%20SQL%20Pool%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2222.23756906077348%25%22%3EURL%3C%2FTD%3E%0A%3CTD%20width%3D%2277.76243093922652%25%22%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%40concat(%0A'https%3A%2F%2Fmanagement.azure.com%2Fsubscriptions%2F'%2C%0Apipeline().parameters.P_DR_SUBSCRIPTION_ID%2C%0A'%2Fresourcegroups%2F'%2C%0Apipeline().parameters.P_DR_RESOURCE_GROUP_NAME%2C%0A'%2Fproviders%2FMicrosoft.Synapse%2Fworkspaces%2F'%2C%0Apipeline().parameters.P_DR_WORKSPACE_NAME%2C%0A'%2FsqlPools%2F'%2C%0Apipeline().parameters.P_DR_SQL_POOL_NAME%2C%0A'%2Fpause%3Fapi-version%3D2019-06-01-preview')%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2222.23756906077348%25%22%3EMethod%3C%2FTD%3E%0A%3CTD%20width%3D%2277.76243093922652%25%22%3EPOST%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2222.23756906077348%25%22%3EBody%3C%2FTD%3E%0A%3CTD%20width%3D%2277.76243093922652%25%22%3E%3CSPACE%3E%3C%2FSPACE%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2222.23756906077348%25%22%3EAuthentication%3C%2FTD%3E%0A%3CTD%20width%3D%2277.76243093922652%25%22%3EMSI%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2222.23756906077348%25%22%3EResource%3C%2FTD%3E%0A%3CTD%20width%3D%2277.76243093922652%25%22%3E%3CA%20href%3D%22https%3A%2F%2Fmanagement.azure.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmanagement.azure.com%3C%2FA%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3ENote%3A%20when%20the%20Method%20is%20POST%20(or%20PUT)%2C%20the%20Body%20cannot%20be%20empty.%20One%20way%20to%20avoid%20getting%20an%20error%20when%20publishing%20the%20pipeline%20is%20to%20add%20a%20space%20as%20the%20Body%20content.%3C%2FP%3E%0A%3CP%3EThis%20is%20the%20error%20when%20the%20Body%20is%20left%20empty%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253365iE55FF4214D56372B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Error%20when%20method%20is%20PUT%20oor%20Post%20and%20body%20is%20empty%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EError%20when%20method%20is%20PUT%20oor%20Post%20and%20body%20is%20empty%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CDIV%20class%3D%22lia-spoiler-container%22%3E%3CA%20class%3D%22lia-spoiler-link%22%20href%3D%22%23%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%20target%3D%22_blank%22%3ESpoiler%3C%2FA%3E%3CNOSCRIPT%3E(Highlight%20to%20read)%3C%2FNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-border%22%3E%3CDIV%20class%3D%22lia-spoiler-content%22%3E%3CBR%20%2F%3EThis%20Azure%20REST%20API%20call%20is%20also%20useful%20when%20we%20need%20to%20pause%20one%20or%20more%20SQL%20Pools%2C%20for%20example%20outside%20of%20working%20hours%2C%20to%20save%20on%20costs.%20One%20idea%20is%20to%20create%20one%20pipeline%20that%20pause%20a%20list%20of%20SQL%20Pools%20and%20another%20to%20resume%20them%20%E2%80%93%20then%20add%20time%20or%20event%20based%20triggers%20as%20needed.%3CBR%20%2F%3EYou%20can%20read%20more%20about%20these%20API%E2%80%99s%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Frest%2Fapi%2Fsynapse%2Fsqlpools%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FDIV%3E%3CNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-noscript-container%22%3E%3CDIV%20class%3D%22lia-spoiler-noscript-content%22%3EThis%20Azure%20REST%20API%20call%20is%20also%20useful%20when%20we%20need%20to%20pause%20one%20or%20more%20SQL%20Pools%2C%20for%20example%20outside%20of%20working%20hours%2C%20to%20save%20on%20costs.%20One%20idea%20is%20to%20create%20one%20pipeline%20that%20pause%20a%20list%20of%20SQL%20Pools%20and%20another%20to%20resume%20them%20%E2%80%93%20then%20add%20time%20or%20event%20based%20triggers%20as%20needed.You%20can%20read%20more%20about%20these%20API%E2%80%99s%20here.%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FNOSCRIPT%3E%3C%2FDIV%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EComplete%20the%20first%20pipeline%3C%2FSTRONG%3E%3CBR%20%2F%3EAt%20this%20point%2C%20the%20second%20pipeline%20is%20finished.%20What%20is%20left%20is%20to%20invoke%20it%20from%20the%20first%20pipeline%20and%20write%20back%20the%20current%20restore%20id%20in%20the%20metadata%20table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBack%20to%20the%20first%20pipeline%2C%20add%20a%20new%20If%20Condition%20activity%2C%20name%20it%20%E2%80%9CLast%20Backup%20different%20from%20Last%20Restored%E2%80%9D%2C%20connect%20it%20after%20the%20existing%20lookup%20and%20configure%20as%20follows%3A%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2218.370165745856355%25%22%3EExpression%3C%2FTD%3E%0A%3CTD%20width%3D%2281.62983425414365%25%22%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%40not(equals(%0Astring(activity('Check%20For%20New%20Backups').output.firstRow.last_backup_run_id)%2C%0Astring(activity('Check%20For%20New%20Backups').output.firstRow.last_restore_run_id)%0A))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20activity%20will%20compare%20the%20last%20backup%20run%20id%20(from%20the%20source%20SQL%20Pool%20system%20tables)%20with%20the%20last%20restore%20run%20id%20(from%20our%20metadata%20table%20also%20in%20the%20source%20SQL%20Pool).%20Not%20being%20the%20same%20(as%20the%20If%20Condition%20activity%20checks)%2C%20means%20that%20a%20snapshot%20happened%20since%20the%20last%20time%20it%20checked%20and%20a%20restore%20needs%20to%20be%20done.%20If%20they%20are%20the%20same%2C%20it%20means%20that%20the%20last%20snapshot%20taken%20was%20already%20restored%20by%20this%20process.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20%E2%80%9CTrue%E2%80%9D%20definition%20of%20the%20If%20Condition%20add%20a%20new%20Execute%20Pipeline%20activity%2C%20name%20it%20%E2%80%9CExecute%20AutoRestoreSQLPool%E2%80%9D%20and%20configure%20as%20follows%3A%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%3EInvoked%20pipeline%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%3EAutoRestoreSQLPool%2C%20which%20is%20the%20name%20of%20second%20pipeline%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EWait%20on%20completion%3C%2FTD%3E%0A%3CTD%3EChecked%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EParameters%3A%3C%2FTD%3E%0A%3CTD%3E%26nbsp%3B%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_SOURCE_WORKSPACE_NAME%3C%2FTD%3E%0A%3CTD%3E%40pipeline().parameters.P_SOURCE_WORKSPACE_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_SOURCE_SQL_POOL_NAME%3C%2FTD%3E%0A%3CTD%3E%40pipeline().parameters.P_SOURCE_SQL_POOL_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_SOURCE_SUBSCRIPTION_ID%3C%2FTD%3E%0A%3CTD%3E%40pipeline().parameters.P_SOURCE_SUBSCRIPTION_ID%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_SOURCE_RESOURCE_GROUP_NAME%3C%2FTD%3E%0A%3CTD%3E%40pipeline().parameters.P_SOURCE_RESOURCE_GROUP_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_SUBSCRIPTION_ID%3C%2FTD%3E%0A%3CTD%3E%40pipeline().parameters.P_DR_SUBSCRIPTION_ID%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_RESOURCE_GROUP_NAME%3C%2FTD%3E%0A%3CTD%3E%40pipeline().parameters.P_DR_RESOURCE_GROUP_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_LOCATION%3C%2FTD%3E%0A%3CTD%3E%40pipeline().parameters.P_DR_LOCATION%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_WORKSPACE_NAME%3C%2FTD%3E%0A%3CTD%3E%40pipeline().parameters.P_DR_WORKSPACE_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_SQL_POOL_NAME%3C%2FTD%3E%0A%3CTD%3E%40pipeline().parameters.P_DR_SQL_POOL_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EP_DR_DEPLOYMENT_NAME%3C%2FTD%3E%0A%3CTD%3E%40pipeline().parameters.P_DR_DEPLOYMENT_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20add%20a%20new%20Stored%20Procedure%20activity%2C%20name%20it%20%E2%80%9CRegister%20Restore%20ID%E2%80%9D%2C%20connect%20it%20after%20the%20Execute%20Pipeline%20and%20configure%20as%20follows%3A%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%3ELinked%20service%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%3ESynapseFullParams%2C%20which%20is%20the%20fully%20parameterized%20Linked%20Service%20we%20created%20at%20the%20beginning%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%3EThe%26nbsp%3BLinked%20service%20properties%20will%20show%20up%3A%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%3ELS_SYNAPSE_WORKSPACE_NAME%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%3E%40pipeline().parameters.P_SOURCE_WORKSPACE_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%3ELS_SYNAPSE_SQL_POOL_NAME%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%3E%40pipeline().parameters.P_SOURCE_SQL_POOL_NAME%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EStored%20procedure%20name%3C%2FTD%3E%0A%3CTD%3E%5Bdbo%5D.%5B%20p_register_restored_backup%5D%2C%20click%20Refresh%20for%20the%20stored%20procedures%20to%20show%20up%20in%20the%20selection%20box%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EStored%20procedure%20parameters%3A%3C%2FTD%3E%0A%3CTD%3Eclick%20Import%20for%20the%20stored%20procedure%20parameters%20to%20show%20up%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3Ep_name%3C%2FTD%3E%0A%3CTD%3EAutoDR%20Pipeline%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3Ep_restore_id%3C%2FTD%3E%0A%3CTD%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%40string(activity('Check%20For%20New%20Backups').output.firstRow.last_backup_run_id)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20part%20of%20the%20pipeline%20and%20the%20settings%20for%20the%20stored%20procedure%20call%20will%20look%20like%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253371i02D4301F67AADCA0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22Register%20current%20run%20in%20the%20metadata%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ERegister%20current%20run%20in%20the%20metadata%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CU%3EAnd%20this%20completes%20the%20first%20pipeline%20and%20achieves%20the%20goal%20for%20this%20process.%3C%2FU%3E%3C%2FP%3E%0A%3CP%3EAs%20shown%2C%20it's%20very%20easy%20to%20put%20in%20place%20a%20process%20to%20automatically%20restore%20Azure%20Synapse%20Analytics%20SQL%20Pools%20to%20serve%20different%20use-cases%2C%20on%20of%20them%20being%20the%20reduction%20of%20the%20RPO%20in%20case%20of%20a%20failure.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlthough%20this%20is%20a%20fully%20functional%20process%2C%20some%20suggestions%20for%20improvement%20include%3A%3C%2FP%3E%0A%3CDIV%20class%3D%22lia-spoiler-container%22%3E%3CA%20class%3D%22lia-spoiler-link%22%20href%3D%22%23%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%20target%3D%22_blank%22%3ESpoiler%3C%2FA%3E%3CNOSCRIPT%3E(Highlight%20to%20read)%3C%2FNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-border%22%3E%3CDIV%20class%3D%22lia-spoiler-content%22%3E-%20Extend%20the%20metadata%20table%20to%20include%20all%20runs%20of%20this%20process%2C%20even%20the%20ones%20that%20don%E2%80%99t%20restore%20SQL%20Pools%3CBR%20%2F%3E-%20Build%20time-based%20triggers%20to%20enable%20continuous%20execution%20of%20this%20process%2C%20maybe%20start%20by%20intervals%20of%201%20hour%20and%20adjust%20as%20needed%3CBR%20%2F%3E-%20Once%20a%20SQL%20Pool%20is%20restored%2C%20and%20before%20it%20is%20paused%2C%20run%20some%20processes%20on%20the%20target%20DR%20SQL%20Pool.%20Examples%20are%20processes%20that%20may%20affect%20the%20performance%20during%20working%20hours%20and%20can%20be%20executed%20on%20a%20non-active%20SQL%20Pool%2C%20removing%20the%20need%20to%20scale%20up%20the%20active%20SQL%20Pools.%3C%2FDIV%3E%3CNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-noscript-container%22%3E%3CDIV%20class%3D%22lia-spoiler-noscript-content%22%3E-%20Extend%20the%20metadata%20table%20to%20include%20all%20runs%20of%20this%20process%2C%20even%20the%20ones%20that%20don%E2%80%99t%20restore%20SQL%20Pools-%20Build%20time-based%20triggers%20to%20enable%20continuous%20execution%20of%20this%20process%2C%20maybe%20start%20by%20intervals%20of%201%20hour%20and%20adjust%20as%20needed-%20Once%20a%20SQL%20Pool%20is%20restored%2C%20and%20before%20it%20is%20paused%2C%20run%20some%20processes%20on%20the%20target%20DR%20SQL%20Pool.%20Examples%20are%20processes%20that%20may%20affect%20the%20performance%20during%20working%20hours%20and%20can%20be%20executed%20on%20a%20non-active%20SQL%20Pool%2C%20removing%20the%20need%20to%20scale%20up%20the%20active%20SQL%20Pools.%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FNOSCRIPT%3E%3C%2FDIV%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20future%2C%20look%20for%20another%20post%20where%20I%20will%20show%20how%20to%20fully%20export%20SQL%20Pools%20into%20partitioned%20areas%20in%20a%20Data%20Lake%20storage%2C%20that%20can%20be%20used%20to%20cover%20compliance%20regulations%20for%20long%20periods%20of%20backup%20retention%20and%2C%20on%20top%2C%20apply%20some%20backup%20storage%20lifecycle%20rules%20to%20take%20advantage%20of%20different%20types%20of%20storage%20with%20the%20goal%20of%20reducing%20storage%20costs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2118027%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20SQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Fully automate DR instances of your SQL Pools and reduce the RPO in case of a failure.
This article will show how to use Azure Data Factory (or Azure Synapse Pipelines) to build a pipeline that can be used to fully automate DR SQL Pools after each automatic snapshot, which can then be paused to save on costs. The pipeline is ready to be used to replicate more than one SQL Pool because it is completely generic and parameterized.

 

What is needed
Azure Key Vault x1 - new or existing
Azure Data Factory v2 (or Azure Synapse Pipelines) x1 - new or existing
Azure Synapse Analytics workspace x2 - new or existing, to be used as source and target/DR
SQL Pool table x1 – metadata table to store the execution runs
SQL Pool stored procedure x 1 – to register each full run

 

1. Preparation

 

Azure key vault secret

Create a new Azure key vault, or use an existing one, and create a new secret using:

Name <workspace_name>-<sql_pool_name> e.g. mySynapseWorkspace-MySQLPool
Value <Connection_String> as found in the Azure Synapse SQL Pool connection string

 

Screenshots of creating a new secret and getting the connection string from a SQL Pool:

 

Create a new secretCreate a new secret

Get SQL Pool Connection stringGet SQL Pool Connection string

Note: replace User ID and Password as needed.
You need to grant the Azure Data Factory Managed Identity access to your Azure Key vault. Learn more here.

 

ADF linked service to Azure Key vault
In ADF, create a new linked service to the Azure Key vault instance where the SQL Pool connection strings are saved. This will be used to prevent having credentials stored in the ADF pipelines as well as to allow us to build a generic process.

 

Create a new linked service to Azure Key vaultCreate a new linked service to Azure Key vault

 

 

ADF linked service to Azure Synapse Analytics
In ADF, create a new linked service to Azure Synapse Analytics. This linked service will use the secrets in the Azure Key vault we referred before, making this a generic connector to Azure Synapse SQL Pools that we can use to connect to any SQL Pool as long as there’s a corresponding secret containing a valid connection string.

Here are the steps:

 

 

New Azure Synapse Analytics linked serviceNew Azure Synapse Analytics linked service


New linked service parametersNew linked service parameters

 

 

To configure the Secret name parameter, select Add dynamic content and in the new window click in the plus (+) sign to create a new parameter for the SQL Pool workspace name.

 

Add dynamic content windowAdd dynamic content window

 

New parameter for the workspace nameNew parameter for the workspace name

 Click Save to return to the Add dynamic content window. Add another new parameter for the SQL Pool name:New parameter for the SQL Pool nameNew parameter for the SQL Pool name

 

 

@concat(
linkedService().LS_SYNAPSE_WORKSPACE_NAME,
'-',
linkedService().LS_SYNAPSE_SQL_POOL_NAME
)

 

 

Now click on finish to accept the dynamic content and then on Test connection, to confirm that everything is ok. For that you need to provide values for the 2 parameters of this linked service:

Parameter for the generic linked serviceParameter for the generic linked service

 This is a dynamic connection to an Azure Synapse SQL Pool, meaning that we can use this linked service to connect to SQL Pools from different workspaces, as long as we have a defined connection string in the form of a secret in the above Azure Key vault.
Note: don’t forget to grant the ADF Managed Identity access to your Azure Synapse Analytics workspace. Learn more here.

 

Create metadata table and registration stored procedure
In each of the SQL Pools you want to automatically restore, create a new table. It will be used to store all the restores done by this ADF process and drive new ones.

 

 

CREATE TABLE [dbo].[restored_backups]
(
	[run_id] [int] NOT NULL,
	[name] [varchar](100) NOT NULL
)
WITH
(
	DISTRIBUTION = ROUND_ROBIN,
	HEAP
)
GO

 

 

In addition, create a new stored procedure. This process will use it to register full runs and drive new executions.

 

 

CREATE PROC [dbo].[p_register_restored_backup] @p_restore_id [INT], @p_name [varchar](100) AS
BEGIN
    INSERT INTO dbo.restored_backups(run_id, name) VALUES(@p_restore_id, @p_name)
END
GO

 

 

 

ADF Dataset for metadata
Before we create our first pipeline, we need to create a new Azure Synapse Analytics ADF dataset to read the metadata.

New Azure Synapse Analytics datasetNew Azure Synapse Analytics dataset

 

Name the dataset “LastRestorePoint”, select the previously create linked service for the connection (SynapseFullParams) and click Ok to save it.

Azure Synapse Analytics dataset propertiesAzure Synapse Analytics dataset properties

 

 

Note that the Dataset uses the previously configured generic Azure Synapse Analytics linked service, that needs 2 parameters (LS_SYNAPSE_WORKSPACE_NAME and LS_SYNAPSE_SQL_POOL_NAME). We now also need to create 2 similar parameters in the dataset (DS_SYNAPSE_WORKSPACE_NAME and DS_SYNAPSE_SQL_POOL_NAME):

Dataset parametersDataset parameters

 

Additionally, and in the Connection tab, pass them to the linked service:

Passing the dataset parameters to the linked servicePassing the dataset parameters to the linked service

LS_SYNAPSE_WORKSPACE_NAME @dataset().DS_SYNAPSE_WORKSPACE_NAME
LS_SYNAPSE_SQL_POOL_NAME @dataset().DS_SYNAPSE_SQL_POOL_NAME

 

2. Pipelines

 

Create first pipeline

The first pipeline will check if a SQL Pool restore is needed, by matching the latest automatic SQL Pool backup record (in sys.pdw_loader_backup_runs) and our metadata table.


Create a new pipeline, name it AutoDr and create the following parameters:

P_SOURCE_WORKSPACE_NAME
P_SOURCE_SQL_POOL_NAME
P_SOURCE_SUBSCRIPTION_ID
P_SOURCE_RESOURCE_GROUP_NAME
P_DR_SUBSCRIPTION_ID
P_DR_RESOURCE_GROUP_NAME
P_DR_LOCATION
P_DR_WORKSPACE_NAME
P_DR_SQL_POOL_NAME
P_DR_DEPLOYMENT_NAME

These will be used later and will help keep this process as generic as possible.

 

During testing, you can assign some default values to the parameters, to save some writing when you manually trigger the pipelines.

Pipeline parametersPipeline parameters

 

Now add a Lookup activity and configure as shown:Lookup parametersLookup parameters

 

Source dataset the name of the previously created dataset for the metadata, in our case “LastRestorePoint”
DS_SYNAPSE_WORKSPACE_NAME @pipeline().parameters.P_SOURCE_WORKSPACE_NAME
DS_SYNAPSE_SQL_POOL_NAME @pipeline().parameters.P_SOURCE_SQL_POOL_NAME
Use query Query
Query
select top 1
       backups.run_id  as last_backup_run_id,
       backups.name    as last_backup_name,
	   restores.run_id as last_restore_run_id
from sys.pdw_loader_backup_runs as backups
left join dbo.restored_backups  as restores
      on (restores.run_id = backups.run_id)
where backups.operation_type = 'BACKUP' and
      backups.mode = 'FULL' and 
      backups.status = 'COMPLETED' and
	  backups.progress = 100
order by backups.run_id desc
First row only checked
All other settings as default

 

Now publish your changes and test the pipeline (Add trigger / Trigger now). If you didn’t add default values to the pipeline parameters, know that for now only P_SOURCE_WORKSPACE_NAME and P_SOURCE_SQL_POOL_NAME are needed.

Note: The pipeline should run successfully no matter if there’s a restore needed or not. For now, it is only executing the above SQL query against a generically configured Azure Synapse Analytics SQL Pool. We will return to this pipeline later.

 

Create second pipeline
The goal for this pipeline is:
- delete the target DR SQL Pool, if any exists
- restore a running SQL Pool from the source workspace into the target DR workspace
- wait for the new SQL Pool to be created and restored
- pause the target DR SQL Pool, as an optional step to save costs if you don’t need to use this pool.

 

Create a new pipeline named "AutoRestoreSQLPool" and create the same parameters as for the first pipeline:

P_SOURCE_WORKSPACE_NAME
P_SOURCE_SQL_POOL_NAME
P_SOURCE_SUBSCRIPTION_ID
P_SOURCE_RESOURCE_GROUP_NAME
P_DR_SUBSCRIPTION_ID
P_DR_RESOURCE_GROUP_NAME
P_DR_LOCATION
P_DR_WORKSPACE_NAME
P_DR_SQL_POOL_NAME
P_DR_DEPLOYMENT_NAME

 

Add a Web activity, name it “Delete SQL Pool” and configure as follows:

Web activity settingsWeb activity settings

URL
@concat(
'https://management.azure.com/subscriptions/',
pipeline().parameters.P_DR_SUBSCRIPTION_ID,
'/resourcegroups/',
pipeline().parameters.P_DR_RESOURCE_GROUP_NAME,
'/providers/Microsoft.Synapse/workspaces/',
pipeline().parameters.P_DR_WORKSPACE_NAME,
'/sqlPools/',
pipeline().parameters.P_DR_SQL_POOL_NAME,
'?api-version=2019-06-01-preview')
Method DELETE
Authentication MSI
Resource https://management.azure.com

 

This will call the SQL Pool REST API and send a DELETE request. For more information visit Sql Pools – Delete.
Note: the Azure Data Factory Managed Service Identity must have authorization to perform these types of requests, in this case to delete SQL Pools, on the involved Azure Synapse Analytics workspaces.
To grant the ADF MSI the Contributor role on a workspace using the Azure portal, open the “Access control (IAM)” of that workspace, click on “+ Add” / Add role assignment and populate as necessary. Here’s an example:

Give Contributor role to an ADF MSIGive Contributor role to an ADF MSI

 Note: don’t forget to select the MSI entry and click Save.

 

The pipeline can now be published and executed.
This pipeline will not abort if there’s no SQL Pool with the given name and workspace (P_DR_SQL_POOL_NAME and P_DR_WORKSPACE_NAME). Because we will create a new SQL Pool using the same parameters, we don’t need to care for this fact but, if the goal was to only delete the target SQL Pool, then a verification activity should be added after the delete is called.

 

Now, add a second Web activity and connect the output of the above created Web activity (Delete SQL Pool) to this one and name it “Restore SQL Pool”. To learn more about the method used in this call visit this link.
Configure the settings as:

Restore SQL Pool settingsRestore SQL Pool settings

 

URL
@concat(
'https://management.azure.com/subscriptions/',
pipeline().parameters.P_DR_SUBSCRIPTION_ID,
'/resourcegroups/',
pipeline().parameters.P_DR_RESOURCE_GROUP_NAME,
'/providers/Microsoft.Resources/deployments/',
pipeline().parameters.P_DR_DEPLOYMENT_NAME,
'?api-version=2020-06-01')
Method PUT
Authentication MSI
Resource https://management.azure.com
Body
{
   "properties": {
      "mode": "Incremental",
      "template": {
         "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
         "contentVersion": "1.0.0.0",
         "resources": [
            {
               "type": "Microsoft.Synapse/workspaces/sqlPools",
               "apiVersion": "2019-06-01-preview",
               "location": "@{pipeline().parameters.P_DR_LOCATION}",
               "tags": {},
               "name": "@{pipeline().parameters.P_DR_WORKSPACE_NAME}/@{pipeline().parameters.P_DR_SQL_POOL_NAME}",
               "properties": {
                  "createMode": "Recovery",
                  "recoverableDatabaseId": "/subscriptions/@{pipeline().parameters.P_SOURCE_SUBSCRIPTION_ID}
/resourceGroups/@{pipeline().parameters.P_SOURCE_RESOURCE_GROUP_NAME}
/providers/Microsoft.Synapse/workspaces/@{pipeline().parameters.P_SOURCE_WORKSPACE_NAME}
/recoverabledatabases/@{pipeline().parameters.P_SOURCE_SQL_POOL_NAME}"
               },
               "resources": [
                  {
                     "condition": false,
                     "apiVersion": "2019-06-01-preview",
                     "dependsOn": [
                        "Microsoft.Synapse/workspaces/@{pipeline().parameters.P_DR_WORKSPACE_NAME}
/sqlPools/@{pipeline().parameters.P_DR_SQL_POOL_NAME}"
                     ],
                     "location": "@{pipeline().parameters.P_DR_LOCATION}",
                     "name": "config",
                     "properties": {
                        "Enabled": false
                     },
                     "type": "metadataSync"
                  }
               ]
            }
         ]
      }
   }
}

Note: due to presentation limitations, some lines were split (recoverableDatabaseId and dependsOn). Either merge them or use the file attached at the end of this page ("Restore SQL Pool - body request.zip")

 

This activity will call the Azure Resource Manager REST API and request the incremental deployment of an Azure resource, in this case an Azure Synapse Analytics SQL Pool that will be recovered from another existing SQL Pool. More options can be found in the online documentation here.
Running this pipeline will trigger the deletion of the target DR SQL Pool and the creation of a new one by recovering from a source and existing SQL Pool.

 

At this point, the pipeline should look like this:

Current design of the second pipelineCurrent design of the second pipeline

 

The next step is to pause the newly created target DR SQL Pool. We can’t do that immediately after calling the restore because it’s an asynchronous call that takes some time, depending on the size of the source SQL Pool. We need to wait and that’s exactly what the next activity will do.

 

Create 2 new variables that will be used to check if the target DR SQL Pool is created and restored. Notice the Boolean type for both:

New pipeline variablesNew pipeline variables

 

Now, add a new Until activity and connect the output of the “Restore SQL Pool” activity to the input of this one. Name it “Wait for Creation and Restore”.
The Expression inside the Settings tab should be: 

@and(
variables('V_DR_CREATED'),
variables('V_DR_RESTORED')
)

 

This means that this activity will loop through until the newly restored target DR SQL Pool is created and fully restored.

Click on the pencil to design the activities for the loop.

The Until activityThe Until activity

To better understand all the activities and how they are connected, let’s have a look at how it will look like at the end:

The complete second pipelineThe complete second pipeline

 

Start by adding a new Web activity, name it “Get SQL Pool Information” and configure as follows:

URL
@concat(
'https://management.azure.com/subscriptions/',
pipeline().parameters.P_DR_SUBSCRIPTION_ID,
'/resourcegroups/',
pipeline().parameters.P_DR_RESOURCE_GROUP_NAME,
'/providers/Microsoft.Synapse/workspaces/',
pipeline().parameters.P_DR_WORKSPACE_NAME,
'/sqlPools/',
pipeline().parameters.P_DR_SQL_POOL_NAME,
'?api-version=2019-06-01-preview')
Method GET
Authentication MSI
Resource https://management.azure.com
All other settings as default

 

This activity sends a request for information on a SQL Pool to the Azure Synapse SQL Poll REST API. For more information visit this page.
If the request fails, we assume the target DR SQL Pool is not yet created (bottom part of the flow) and otherwise we will look at the status property, which will be Online when the SQL Pool is fully restored and ready to be used.

 

Add 2 new Set variable activities, connect them as the previous picture and rename them accordingly (“Set V_DR_CREATED to true” and “Set V_DR_CREATED to false”).
Note: to change a connection from green (Success) to red (Failure), select the connection you want to change, right click it and change its “Change To” value as desired.

Changing connection conditionsChanging connection conditions

 

The settings for these 2 activities should be:

 

“Set V_DR_CREATED to true” settings“Set V_DR_CREATED to true” settings

 

 

“Set V_DR_CREATED to false” settings“Set V_DR_CREATED to false” settings

 

 

 

To finish the bottom part of the flow (Failure), add a new Wait activity, name it “Wait For Creation - 30 secs”, connect it as depicted above and change its settings to 30:

“Wait For Creation - 30 secs” settings“Wait For Creation - 30 secs” settings

 This activity will wait 30 seconds before allowing the Until activity to continue.

 

Next, add an If Condition activity, name it “Check If Restored”, connect it as shown before and change its Expression to:

@equals(
activity('Get SQL Pool Information').output.properties.status,
'Online'
)

 This activity will read the value of the status property of the newly created target DR SQL Pool and check if it is “Online”. If not, we need to wait some time before checking again, otherwise the SQL Pool is ready to be used and we can continue with the pipeline.

 

To achieve this, we configure the If Condition activity as follows.

 

a) When true, we add a new Set Variable activity, name it “Set V_DR_RESTORED to true” and configure as:

“Set V_DR_RESTORED to true” settings“Set V_DR_RESTORED to true” settings

 

In this case we signal that the SQL Pool is fully restored. This part of the pipeline will look like:

Flow when the SQL Pool is fully restoredFlow when the SQL Pool is fully restored

 

a) When false, we configure as follows.
Add a Set Variable activity, name it “Set V_DR_RESTORED to false” and set the Variables tab as:

“Set V_DR_RESTORED to false” settings“Set V_DR_RESTORED to false” settings

 

Then, add a new Wait activity, name it “Wait For Restore - 30 secs”, connect it to the previous activity and configure its Wait time in seconds to 30.
This part of the pipeline will look like:

When the SQL Pool is not yet restoredWhen the SQL Pool is not yet restored

 

If there’s no need to pause the target DR SQL Poll, then this second pipeline is finished and we can jump to Complete the second pipeline.
If, instead, the SQL Pool needs to be paused, then we need to add that functionality as described in this next step.

 

At the top level of the pipeline (outside the IF’s and Until’s), add a new Web activity, name it “Pause SQL Pool”, connect it to the end of the Until activity and configure as follows:

Activity to pause the SQL PoolActivity to pause the SQL Pool

 

URL
@concat(
'https://management.azure.com/subscriptions/',
pipeline().parameters.P_DR_SUBSCRIPTION_ID,
'/resourcegroups/',
pipeline().parameters.P_DR_RESOURCE_GROUP_NAME,
'/providers/Microsoft.Synapse/workspaces/',
pipeline().parameters.P_DR_WORKSPACE_NAME,
'/sqlPools/',
pipeline().parameters.P_DR_SQL_POOL_NAME,
'/pause?api-version=2019-06-01-preview')
Method POST
Body <space>
Authentication MSI
Resource https://management.azure.com

Note: when the Method is POST (or PUT), the Body cannot be empty. One way to avoid getting an error when publishing the pipeline is to add a space as the Body content.

This is the error when the Body is left empty:

Error when method is PUT oor Post and body is emptyError when method is PUT oor Post and body is empty

Spoiler

This Azure REST API call is also useful when we need to pause one or more SQL Pools, for example outside of working hours, to save on costs. One idea is to create one pipeline that pause a list of SQL Pools and another to resume them – then add time or event based triggers as needed.
You can read more about these API’s here.

 

 

Complete the first pipeline
At this point, the second pipeline is finished. What is left is to invoke it from the first pipeline and write back the current restore id in the metadata table.

 

Back to the first pipeline, add a new If Condition activity, name it “Last Backup different from Last Restored”, connect it after the existing lookup and configure as follows:

Expression
@not(equals(
string(activity('Check For New Backups').output.firstRow.last_backup_run_id),
string(activity('Check For New Backups').output.firstRow.last_restore_run_id)
))

 

This activity will compare the last backup run id (from the source SQL Pool system tables) with the last restore run id (from our metadata table also in the source SQL Pool). Not being the same (as the If Condition activity checks), means that a snapshot happened since the last time it checked and a restore needs to be done. If they are the same, it means that the last snapshot taken was already restored by this process.

 

In the “True” definition of the If Condition add a new Execute Pipeline activity, name it “Execute AutoRestoreSQLPool” and configure as follows:

Invoked pipeline AutoRestoreSQLPool, which is the name of second pipeline
Wait on completion Checked
Parameters:  
P_SOURCE_WORKSPACE_NAME @pipeline().parameters.P_SOURCE_WORKSPACE_NAME
P_SOURCE_SQL_POOL_NAME @pipeline().parameters.P_SOURCE_SQL_POOL_NAME
P_SOURCE_SUBSCRIPTION_ID @pipeline().parameters.P_SOURCE_SUBSCRIPTION_ID
P_SOURCE_RESOURCE_GROUP_NAME @pipeline().parameters.P_SOURCE_RESOURCE_GROUP_NAME
P_DR_SUBSCRIPTION_ID @pipeline().parameters.P_DR_SUBSCRIPTION_ID
P_DR_RESOURCE_GROUP_NAME @pipeline().parameters.P_DR_RESOURCE_GROUP_NAME
P_DR_LOCATION @pipeline().parameters.P_DR_LOCATION
P_DR_WORKSPACE_NAME @pipeline().parameters.P_DR_WORKSPACE_NAME
P_DR_SQL_POOL_NAME @pipeline().parameters.P_DR_SQL_POOL_NAME
P_DR_DEPLOYMENT_NAME @pipeline().parameters.P_DR_DEPLOYMENT_NAME

 

Then add a new Stored Procedure activity, name it “Register Restore ID”, connect it after the Execute Pipeline and configure as follows:

Linked service SynapseFullParams, which is the fully parameterized Linked Service we created at the beginning
The Linked service properties will show up:  
LS_SYNAPSE_WORKSPACE_NAME @pipeline().parameters.P_SOURCE_WORKSPACE_NAME
LS_SYNAPSE_SQL_POOL_NAME @pipeline().parameters.P_SOURCE_SQL_POOL_NAME
Stored procedure name [dbo].[ p_register_restored_backup], click Refresh for the stored procedures to show up in the selection box
Stored procedure parameters: click Import for the stored procedure parameters to show up
p_name AutoDR Pipeline
p_restore_id
@string(activity('Check For New Backups').output.firstRow.last_backup_run_id)

 

This part of the pipeline and the settings for the stored procedure call will look like:

Register current run in the metadataRegister current run in the metadata

 

And this completes the first pipeline and achieves the goal for this process.

As shown, it's very easy to put in place a process to automatically restore Azure Synapse Analytics SQL Pools to serve different use-cases, on of them being the reduction of the RPO in case of a failure.

 

Although this is a fully functional process, some suggestions for improvement include:

Spoiler
- Extend the metadata table to include all runs of this process, even the ones that don’t restore SQL Pools
- Build time-based triggers to enable continuous execution of this process, maybe start by intervals of 1 hour and adjust as needed
- Once a SQL Pool is restored, and before it is paused, run some processes on the target DR SQL Pool. Examples are processes that may affect the performance during working hours and can be executed on a non-active SQL Pool, removing the need to scale up the active SQL Pools.

 

In the future, look for another post where I will show how to fully export SQL Pools into partitioned areas in a Data Lake storage, that can be used to cover compliance regulations for long periods of backup retention and, on top, apply some backup storage lifecycle rules to take advantage of different types of storage with the goal of reducing storage costs.