---
tags: CICD, DB
---
# DB Release automation
Retrieve sql scripts by committed ticketed id
Scan and amend DB script (if necessary) and save to output package

{
    "ReleaseTicket": "20220823002",
    "ReleaseEnvironment": "QAT",
    "DBUser": "user",
    "DBPw": "pwd",
    "DeployInfoList": [
        {
            "PartnerId": "P100",
            "Tickets": [
                {
                    "TicketId": "IDP-7606_01",
                    "DBConnection": "DBFQDN\\DBNAME",
                    "SqlFile": ".\\IDP-7606_01\\DBPackageFolderByPartner_P100\\Drop_Index_IX_ProductSetting_ProductID_IncentiveTemplateSlotID.sql"
                },
                {
                    "TicketId": "IDP-7606_02",
                    "DBConnection": "DBFQDN\\DBNAME",
                    "SqlFile": ".\\IDP-7606_02\\DBPackageFolderByPartner_P100\\dbo.IX_ProductSetting_IncentiveTemplateSlotID_ProductID.Index.sql"
                },
                {
                    "TicketId": "IDP-7606_03",
                    "DBConnection": "DBFQDN\\DBNAME",
                    "SqlFile": ".\\IDP-7606_03\\DBPackageFolderByPartner_P100\\dbo.BPE_Product_GetUserSetting.StoredProcedure.sql"
                }
            ]
        }
    ]
}

# DB Deploy automation
1. Get DB release file
`$releaseinfo = Get-Content -Raw -Path $ReleaseInfoFile | ConvertFrom-Json`
2. Verify permission
`& "$($PSScriptRoot)\GetPermissionInfo.ps1"`
3. GetDBScript by ticket ID and deploy
`$msg = sqlcmd -I -S $($ticketinfo.DBConnection) -f 65001 -i $($ticketinfo.SqlFile) | Out-String`
4. Send deploy result to ELK

& "$($PSScriptRoot)\SendDBReleaseInfo2ELK.ps1" -environment -releaseTicket -partner -ticketId -message -seq $counter -total -result -duration -elkUrl $elkUrl -elkindexname -releaseType
Invoke-RestMethod -Uri "http://$($elkUrl)/$($elkindexname)/_doc" -Method 'POST' -Headers $headers -Body $body


{
    "ReleaseTicket": "RLPU-3783",
    "ReleaseEnvironment": "UAT",
    "DBUser": "sa",
    "DBPw": "pass.123",
    "DeployInfoList": [
        {
            "PartnerId": "P100HQ",
            "Tickets": [
                {
                    "TicketId": "RLPU-3733_01",
                    "DBConnection": "RA-UAT-DB248\\RTXUATLIVEDB",
                    "SqlFile": ".\\RLPU-3733_01\\Mark6_OPA_P100HQ\\dbo.UserInfo_Create_Member.StoredProcedure.sql"
                },
                {
                    "TicketId": "RLPU-3733_02B",
                    "DBConnection": "RA-UAT-DB248\\RTXUATLIVEDB",
                    "SqlFile": ".\\RLPU-3733_02B\\Mark6_OPA_P100HQ\\dbo.UserInfo_EditUser_Member.StoredProcedure.sql"
                }
            ]
        },
        {
            "PartnerId": "P100",
            "Tickets": [
                {
                    "TicketId": "RLPU-3733_01",
                    "DBConnection": "RA-UAT-DB248\\RTXUATLIVEDB",
                    "SqlFile": ".\\RLPU-3733_01\\Mark6_OPA_P100\\dbo.UserInfo_Create_Member.StoredProcedure.sql"
                },
                {
                    "TicketId": "RLPU-3733_02B",
                    "DBConnection": "RA-UAT-DB248\\RTXUATLIVEDB",
                    "SqlFile": ".\\RLPU-3733_02B\\Mark6_OPA_P100\\dbo.UserInfo_EditUser_Member.StoredProcedure.sql"
                }
            ]
        },
        {
            "PartnerId": "P105",
            "Tickets": [
                {
                    "TicketId": "RLPU-3733_01",
                    "DBConnection": "RA-UAT-DB248\\RTXUATLIVEDB",
                    "SqlFile": ".\\RLPU-3733_01\\Mark6_OPA_P105\\dbo.UserInfo_Create_Member.StoredProcedure.sql"
                },
                {
                    "TicketId": "RLPU-3733_02B",
                    "DBConnection": "RA-UAT-DB248\\RTXUATLIVEDB",
                    "SqlFile": ".\\RLPU-3733_02B\\Mark6_OPA_P105\\dbo.UserInfo_EditUser_Member.StoredProcedure.sql"
                }
            ]
        },
        {
            "PartnerId": "P115",
            "Tickets": [
                {
                    "TicketId": "RLPU-3733_01",
                    "DBConnection": "RA-UAT-DB248\\RTXUATLIVEDB",
                    "SqlFile": ".\\RLPU-3733_01\\Mark6_OPA_P115\\dbo.UserInfo_Create_Member.StoredProcedure.sql"
                },
                {
                    "TicketId": "RLPU-3733_02B",
                    "DBConnection": "RA-UAT-DB248\\RTXUATLIVEDB",
                    "SqlFile": ".\\RLPU-3733_02B\\Mark6_OPA_P115\\dbo.UserInfo_EditUser_Member.StoredProcedure.sql"
                }
            ]
        }
    ]
}