Hi AWS, I was trying to deploy a CI/CD pipeline for MS SQL 2022 using GitHub Actions on Amazon RDS. Here is the code repo: https://github.com/arjungoel/sql-cicd-poc. Having said that I was trying to create a .dacpac file and then deploying it on the Target Server. However I figured out that only the CREATE TABLE commands were working and apart from that no other DML and DDL commands like INSERT, UPDATE, ALTER are working as I came to know only I got this post from stackoverflow https://stackoverflow.com/questions/19819681/how-to-include-data-in-a-dacpac.
This is the sql code for database table:
CREATE TABLE [dbo].[Bank]
(
[BankName] [nvarchar](50) NOT NULL,
[BankAddress] [nvarchar](200) NOT NULL,
[BankIFSCCode] [nvarchar] (100) NOT NULL
)
GO
Insert into Bank Values ('sbi2', 'naraina2', 'ifsc_code2'), ('sbi3', 'naraina3', 'ifsc_code3'), ('sbi4', 'naraina4', 'ifsc_code4');
GO
But when I ran the Export command to create a .bacpac file as it contains both data and structure and the command is:
SqlPackage /Action:Export /TargetFile:"C:\database-projects\sql-cicd-poc\output.bacpac" /SourceConnectionString:"Server=tcp:EC2AMAZ-DC3KDV1,1433;Initial Catalog=bank;Persist Security Info=False;User ID=sqldemopoc;Password=sqldemo1234@#;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;"
it creates the .bacpac file but it doesn't have the table inside. You can refer the screenshot below:
You can see it is missing _rels
and Data
directory. Is it because the tables have not been created and I need to add one more job just to have that table structure in place (.dacpac) file.
Furthermore I ran these commands:
SqlPackage /Action:Extract /TargetFile:result.dacpac /p:ExtractAllTableData=true /p:VerifyExtraction=true /SourceServerName:EC2AMAZ-DC3KDV1 /SourceDatabaseName:bank /SourceUser:sqldemopoc /SourcePassword:sqldemo1234@# /sec:false --> to create .dacpac file
SqlPackage /Action:Publish /SourceFile:"C:\database-projects\sql-cicd-poc\result.dacpac" /TargetConnectionString:"Server=tcp:EC2AMAZ-DC3KDV1,1433;Initial Catalog=bank;Persist Security Info=False;User ID=sqldemopoc;Password=sqldemo1234@#;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;" --> to deploy the changes on the Target Server. It gave me this message with a warning:
Publishing to database 'bank' on server 'tcp:EC2AMAZ-DC3KDV1,1433'.
Initializing deployment (Start)
*** The object [sqldemopoc] already exists in database with a different definition and will not be altered.
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
Update complete.
Importing data
Disabling indexes. (Start)
Disabling indexes. (Complete)
Processing Import. 0.00% done.
Processing Import. 100.00% done.
Processing Table '[dbo].[Bank]'. 100.00% done.
Enabling indexes. (Start)
Enabling indexes. (Complete)
Importing data
Updating database (Complete)
Successfully published database.
Changes to connection setting default values were incorporated in a recent release. More information is available at https://aka.ms/dacfx-connection
Time elapsed 0:00:09.59
My mind is blown away so I need a way to figure out things in the sequence:
- To create a build (.dacpac or .bacpac) which could have both structure and data in place and for that I need to run two jobs, YES or NO?
- Once done what are the series of commands which I need to run in sequence.
Please guide.
One more question I have why the DDL and DML commands (INSERT, DELETE, UPDATE, ALTER, DROP) etc. are not getting triggered using CI/CD pipeline for both .bacpac and .dacpac. I was expecting the changes would be triggered when exporting the data to .bacpac file but it is not true for the databases which has no tables and records.