We are using gitlab as our devops pipline. I have a Microsoft SQL Server Database Project (.sqlproj), I need to deploy this to a AWS RDS SQL Server instance FROM a Ubutu docker image used in the gitlab pipeline, as per our devops department recommendation. I CANNOT run a windows docker image as the docker environment is linux or os have been told. Here's the docker file to create the ubuntu image for when the database gitlab pipeline runs:
RUN cat /etc/*release
RUN apt-get update
# RUN apt-get upgrade
RUN apt install -y sudo curl wget unzip libunwind8
RUN wget https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/n/netstandard-targeting-pack-2.1/netstandard-targeting-pack-2.1_2.1.0-1_amd64.deb
RUN sudo dpkg -i netstandard-targeting-pack-2.1_2.1.0-1_amd64.deb
#Install sql-package
RUN curl -LO "https://aka.ms/sqlpackage-linux"
RUN set -x \
&& mkdir sqlpackage \
&& unzip sqlpackage-linux -d sqlpackage \
&& echo "export PATH=$PATH:/sqlpackage" >> /etc/profile \
&& wget http://archive.ubuntu.com/ubuntu/pool/main/o/openssl1.0/libssl1.0.0_1.0.2n-1ubuntu5.9_amd64.deb \
&& dpkg -i libssl1.0.0_1.0.2n-1ubuntu5.9_amd64.deb \
&& chmod a+x sqlpackage/sqlpackage \
&& echo "export DOTNET_SYSTEM_GLOBALIZATION_INVARIANT=1" >> /etc/profile
#Install dotnet-sdk-3.1
RUN wget https://packages.microsoft.com/config/ubuntu/20.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb
RUN sudo dpkg -i packages-microsoft-prod.deb ; rm packages-microsoft-prod.deb
RUN sudo apt-get update; \
sudo apt-get install -y apt-transport-https && \
sudo apt-get update && \
sudo apt-get install -y dotnet-sdk-3.1
RUN sudo apt-get install language-pack-en -y
RUN echo locale -a
RUN dotnet new --install MSBuild.Sdk.SqlProj.Templates
RUN dotnet new --install MSBuild.Sdk.SqlProj
My database git repository uses the above docker image in the gitlab-ci.yml and looks like this:
stages:
- post-deploy
db-deploy-job:
stage: post-deploy
environment:
name: Dev
image: sqlpackage-docker-project:latest
script:
- dotnet build "Source/Database/PipelineBuild.sln"
- export DOTNET_SYSTEM_GLOBALIZATION_INVARIANT=1
- export PATH=$PATH:/sqlpackage
- "sqlpackage /Diagnostics:True /Action:Publish /SourceFile:'Source/Database/Database.Build/bin/Release/netstandard2.0/Database.Build.dacpac' /TargetConnectionString:'Server=$DBServer;Database=$DatabaseName;User Id=$DBUserId;Password=$DBPassword;MultipleActiveResultSets=true;'"
I created a dotnet library (targeting netstandard2.0) and pointed to my sql scripts in the SQL project using the MSBuild.Sdk.SqlProj library and making sure this is the only file building in the build configuration. Here is the dotnet library project file contents:
<Project Sdk="MSBuild.Sdk.SqlProj/2.0.0">
<PropertyGroup>
<TargetFramework>netstandard2.0</TargetFramework>
</PropertyGroup>
<ItemGroup>
<Content Include="..\Database\**\*.sql" />
</ItemGroup>
<ItemGroup>
<Content Remove="..\Database\Audit\StoredProcedures\usp_InsertAuditRecord.sql" />
</ItemGroup>
</Project>
After running this I got a error, it did not understand the .sqlproj project file, I then loaded the templates in the docker image above and it started building the SQL project. It build successfully and created a DACPAC!!! It was at this point I though all my troubles have ended...
I then got this error below in my pipeline:
$ sqlpackage /Diagnostics:True /Action:Publish /SourceFile:'Source/ServiceDatabase/Database.Build/bin/Release/netstandard2.0/Database.Build.dacpac' /TargetConnectionString:'Server=$DBServer;Database=$DatabaseName;User Id=$DBUserId;Password=$DBPassword;MultipleActiveResultSets=true;'
Microsoft.Data.Tools.Diagnostics.Tracer Information: 0 : 2022-05-23T14:39:49 : Running 64-bit SqlPackage Version 16.0.5400.1 on .NET Core
Publishing to database '$DatabaseName' on server '$DBServer'.
Microsoft.Data.Tools.Diagnostics.Tracer Information: 0 : 2022-05-23T14:39:50 : Initialized ModelSchema - elements 158
Microsoft.Data.Tools.Diagnostics.Tracer Information: 19 : 2022-05-23T14:39:50 : IsolatedStorageFile probe success
Microsoft.Data.Tools.Diagnostics.Tracer Information: 0 : 2022-05-23T14:39:50 : Initialized ModelSchema - elements 158
Microsoft.Data.Tools.Diagnostics.Tracer Information: 19 : 2022-05-23T14:39:50 : IsolatedStorageFile probe success
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : Logging Ambient Settings...
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : DatabaseLockTimeout: 60000
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : QueryTimeout: 60
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : LongRunningQueryTimeout: 0
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : AlwaysRetryOnTransientFailure: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : ConnectionRetryHandler:
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : MaxDataReaderDegreeOfParallelism: 8
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : TraceRowCountFailure: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : TableProgressUpdateInterval: 300
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : UseOfflineDataReader: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : StreamBackingStoreForOfflineDataReading: File
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : ModelFilePath:
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : MasterReferenceFilePath:
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : DisableIndexesForDataPhase: True
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : DisableParallelismForEnablingIndexes: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : ReliableDdlEnabled: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : ImportModelDatabase: True
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : SupportAlwaysEncrypted: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : AlwaysEncryptedWizardMigration: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : AlwaysEncryptedWizardPreserveTable: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : AlwaysEncryptedEnclaveAttestationUrl:
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : AlwaysEncryptedEnclaveAttestationProtocol: NotSpecified
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : AlwaysEncryptedWizardUseOnlineApproach: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : AlwaysEncryptedWizardDowntimeInSeconds: 300
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : AlwaysEncryptedWizardMaxDivergingIterations: 5
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : AlwaysEncryptedWizardMaxIterations: 100
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : AlwaysEncryptedWizardMaxIterationDurationInDays: 3
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : SkipObjectTypeBlocking: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : DoNotSerializeQueryStoreSettings: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : LargeObjectHeapCompaction: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : LargeObjectHeapCompactionIntervalInSeconds: 30
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : ReferencePaths: System.String[]
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : DeploymentContributorPaths: System.String[]
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : RebuildIndexesOfflineForDataPhase: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : PreserveIdentityLastValues: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : Logging Log Settings...
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-05-23T14:39:50 : HashObjectNamesInLogs: False
Microsoft.Data.Tools.Diagnostics.Tracer Information: 25 : 2022-05-23T14:39:50 : Perf: Operation started (name, details): Initializing deployment,
Initializing deployment (Start)
Microsoft.Data.Tools.Diagnostics.Tracer Error: 19 : 2022-05-23T14:39:50 : Microsoft.SqlServer.Dac.DacServicesException: Could not read schema model header information from package.
---> Microsoft.Data.Tools.Schema.SchemaModel.ModelSerializationException: Collation 1033 is not supported. You must specify one of the supported collations in the Collation attribute.
at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.DataSchemaModelDeserializerState.ProcessStartElement() in F:\B\16835\6200\Sources\Product\Source\SchemaSql\SchemaModel\ModelStore\ModelXmlSerializer.cs:line 2501
at Microsoft.Data.Tools.Schema.SchemaModel.XmlDeserializerEngine.ReadData(DeserializerState initialState, Boolean keepCurrentReaderPosition) in F:\B\16835\6200\Sources\Product\Source\SchemaSql\SchemaModel\ModelStore\XmlSerializerEngine.cs:line 106
at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.ReadDataSchemaModelHeaderFromXml(TextReader input, Boolean readCustomData) in F:\B\16835\6200\Sources\Product\Source\SchemaSql\SchemaModel\ModelStore\ModelXmlSerializer.cs:line 318
at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.ReadDataSchemaModelHeaderFromPackage(SqlPackage package, Boolean readCustomData) in F:\B\16835\6200\Sources\Product\Source\SchemaSql\SchemaModel\ModelStore\ModelXmlSerializer.cs:line 297
at Microsoft.SqlServer.Dac.DacPackage.ReadModelHeader(IPackageSource packageSource) in F:\B\16835\6200\Sources\Product\Source\DeploymentApi\DacPackage.cs:line 954
--- End of inner exception stack trace ---
Microsoft.Data.Tools.Diagnostics.Tracer Information: 25 : 2022-05-23T14:39:50 : Perf: Operation ended (name, details, elapsed in ms): Initializing deployment,,13
Initializing deployment (Failed)
*** Could not read schema model header information from package.
Collation 1033 is not supported. You must specify one of the supported collations in the Collation attribute.
Time elapsed 0:00:00.24
Microsoft.Data.Tools.Diagnostics.Tracer Information: 0 : 2022-05-23T14:39:50 : SqlPackage completed
Cleaning up project directory and file based variables
00:00
ERROR: Job failed: command terminated with exit code 1
This is were I got stuck and need help!!!
Things I have learned:
- The collation 1033 it is referring to, is referenced in the model.xml of the dacpac, if you unpack the dacpac (right click on the dacpac) you will find a model.xml file containing this:
<?xml version="1.0" encoding="utf-8"?>
<DataSchemaModel FileFormatVersion="1.2" SchemaVersion="2.9"
DspName="Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider"
CollationLcid="1033" <---- RIGHT HERE
CollationCaseSensitive="False" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
<Model>
<Element Type="SqlDatabaseOptions" Disambiguator="1">
<Property Name="Collation" Value="SQL_Latin1_General_CP1_CI_AS" />
<Property Name="IsAnsiNullDefaultOn" Value="True" />
...
- Collation 1033 is a Windows language pack: https://learn.microsoft.com/en-us/windows-hardware/manufacture/desktop/available-language-packs-for-windows?view=windows-11
- I can install the ububtu language packs using the command below however they dont link up to the Microsoft language packs:
RUN sudo apt-get install language-pack-en -y
- I learned somewhere that the SSDT libraries was supported in core 3.1. It was at this point I moved down from dotnet core 6.
- This is NOT a database collation issue as I can deploy using my dev laptop, but I stand to be corrected on this?
- Searching in the sqlpacage linux zip for the collation id (1033) revealed the schema the dacpac uses to build, is located in the schema DLL : ..\sqlpackage-linux-x64-en-US-16.0.5400.1 (2)\Microsoft.Data.Tools.Schema.Sql.dll SSQLPackage linux zip found at: https://aka.ms/sqlpackage-linux
Things I have tried:
- Use the same command from my local dev laptop (Windows) and it deployed with no issues.
- Re-creating the Sql project with DACPAC exported from my DB using SQL Management studio.
- Using dotnet core 6, didnt work as it didnt contain the SSDT libraries as per error below.
$ dotnet build "Source/ServiceDatabase/database.sln"
Microsoft (R) Build Engine version 17.2.0+41abc5629 for .NET
Copyright (C) Microsoft Corporation. All rights reserved.
/builds/platform/database/Source/ServiceDatabase/database/database.sqlproj : warning NU1503: Skipping restore for project '/builds/platform/database/Source/ServiceDatabase/database/database.sqlproj'. The project file may be invalid or missing targets required for restore. [/builds/platform/database/Source/ServiceDatabase/database.sln]
Determining projects to restore...
/usr/share/dotnet/sdk/6.0.300/NuGet.targets(130,5): warning : Unable to find a project to restore! [/builds/platform/database/Source/ServiceDatabase/database.sln]
/builds/platform/database/Source/ServiceDatabase/database/database.sqlproj(58,3): error MSB4019: The imported project "/usr/share/dotnet/sdk/6.0.300/Microsoft/VisualStudio/v11.0/SSDT/Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the expression in the Import declaration "/usr/share/dotnet/sdk/6.0.300//Microsoft/VisualStudio/v11.0/SSDT/Microsoft.Data.Tools.Schema.SqlTasks.targets" is correct, and that the file exists on disk.
Build FAILED.
/builds/platform/database/Source/database/database/database.sqlproj : warning NU1503: Skipping restore for project '/builds/platform/database/Source/ServiceDatabase/database/database.sqlproj'. The project file may be invalid or missing targets required for restore. [/builds/platform/database/Source/ServiceDatabase/database.sln]
/usr/share/dotnet/sdk/6.0.300/NuGet.targets(130,5): warning : Unable to find a project to restore! [/builds/platform/database/Source/ServiceDatabase/database.sln]
/builds/platform/database/Source/ServiceDatabase/database/database.sqlproj(58,3): error MSB4019: The imported project "/usr/share/dotnet/sdk/6.0.300/Microsoft/VisualStudio/v11.0/SSDT/Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the expression in the Import declaration "/usr/share/dotnet/sdk/6.0.300//Microsoft/VisualStudio/v11.0/SSDT/Microsoft.Data.Tools.Schema.SqlTasks.targets" is correct, and that the file exists on disk.
2 Warning(s)
1 Error(s)
Time Elapsed 00:00:00.20
The collation 1033 issue, is because the Microsoft language pack doesn't exist on ubuntu? Is there away to load this for Ubuntu?
Thanks for taking the time to read thorough this as I try to be thorough in my explanation.
