Integration with SQL CI

StephenMclean

Our environment is Jenkins, DLM Automation Suite & Octopus Deploy

In the Command Line info for SQL CI when referring to the /dlmDashboardHost switch it states:
When you run the build step, SQL CI sends information about the schema to DLM Dashboard. Once you deploy the changes (for example, by running the sync step), DLM Dashboard:
recognizes the deployed schema from the information SQL CI sent during the build step
adds the schema to its list of recognized schemas, with the name <packageId-packageVersion>, for example, WidgetShop1.0
labels the schema with the SQL CI icon
labels the schema as an update , not drift

So we set the BUILD command in Jenkins to:-
"C:Program Files (x86)Red GateDLM Automation Suite 1SQLCISQLCI.exe" Build /scriptsFolder=. /packageId=NewCIPackage /temporaryDatabaseServer=TTS-00TTS2008R2TEST /packageVersion=2.%BUILD_NUMBER% /dlmDashboardHost=localhost /dlmDashboardPort=19528

and the SYNC command to:-
"C:Program Files (x86)Red GateDLM Automation Suite 1SQLCISQLCI.exe" Sync /databaseName=Manzen_CI /package="C:Program Files (x86)JenkinsjobsManzen Database TrunkworkspaceNewCIPackage.2.%BUILD_NUMBER%.nupkg" /databaseServer=TTS-00TTS2008R2TEST /additionalCompareArgs="/scriptFile:"UpgradeTo2.%BUILD_NUMBER%Script.sql""

The issues we have are:
DLM Dashboard does not recognize the schema in the database updated by our SYNC command
We don't see the SQL CI Icon
The Schema is not identified as an update, it is a drift.

We're currently assigning schema numbers in Octopus Deploy but would really like to see the build number coming through from Jenkins

Are we doing something wrong, if so can you identify what it is?

Chris Lambrou

Hi Stephen,

The three issues you've described are all essentially the same problem, that DLM Dashboard is not receiving and/or recognising the schema notifications from SQL CI. We did find an issue with an early release of this functionality that matches the symptoms you've described under some circumstances, so let's do a bit of digging. If would be useful to know the following:

* What version of DLM Dashboard are you using? You can find this at the bottom of each page.
* What version of SQL CI are you using? You can find this in the SQL CI output in the logs of your Jenkins builds
* What version or versions of SQL Server are you targeting?

It would also be useful if you could send me a copy of one of the NuGet packages that is generated by the SQL CI build step, so I can do some testing (provided that you're comfortable with that). You can email this to me at DLMAutomationSupport@red-gate.com

Finally, it's also worth double-checking some of the basics. For example, your SQL CI build command specifies /dlmDashboardHost=localhost. I'm not familiar with the architecture of Jenkins, but most build systems use distributed worker agents, so are you certain that localhost actually refers to the DLM Dashboard instance that you think it does?

Rob C

Hi Stephen,

What output are you getting from the build step? I'd like to know if the build step is actually registering the schema with DLM Dashboard or if it's encountering a problem and not registering it. Another thing you could do to verify that the schema has been registered is to do the following -

1. Visit http://localhost:21060 in your browser
2. In the top menu click on Documents.
3. In the left hand pane click on SqlCompareSnapshots.
4. In the main window, look for a snapshot with the package name that you were trying to register it with.

If you can find it in the list then you've been able to register the schema and the problem is somewhere else.

Another thing I would do is to extract the nuget package to a blank folder and then use SQL Compare to compare it to the database - if there has been some sort of issue with the sync step then there might be a schema difference. That would be a possible explanation for the dashboard not recognising the schema as the one that you registered.

Thanks.

StephenMclean

In answer to Chris Lambrou's questions

* What version of DLM Dashboard are you using? You can find this at the bottom of each page. 1.6.1.440
* What version of SQL CI are you using? You can find this in the SQL CI output in the logs of your Jenkins builds sqlCI.exe -- Red Gate's SQL Continuous Integration v2.1.8.167
* What version or versions of SQL Server are you targeting? SQl Server 2008 R2

"are you certain that localhost actually refers to the DLM Dashboard instance that you think it does?" Jenkins, SQL CI, Octopus Deploy & DLM Dashboard are all running on the same machine. We tried using the IP Address but it made no difference

StephenMclean

Robert Clenshaw wrote:

Hi Stephen,

What output are you getting from the build step? I'd like to know if the build step is actually registering the schema with DLM Dashboard or if it's encountering a problem and not registering it. Another thing you could do to verify that the schema has been registered is to do the following -

1. Visit http://localhost:21060 in your browser
2. In the top menu click on Documents.
3. In the left hand pane click on SqlCompareSnapshots.
4. In the main window, look for a snapshot with the package name that you were trying to register it with.

If you can find it in the list then you've been able to register the schema and the problem is somewhere else.

Another thing I would do is to extract the nuget package to a blank folder and then use SQL Compare to compare it to the database - if there has been some sort of issue with the sync step then there might be a schema difference. That would be a possible explanation for the dashboard not recognising the schema as the one that you registered.

Thanks.

We have looked at the snapshots and the name is blank.

We compared the nugget package to the SYNC database and they were identical.

We're also seeing this in the Jenkins log at the end of the BUILD step:-

COMPLETED SUCCESSFULLY: Validating database state
STARTING: Publishing database package
Writing package to C:Program Files (x86)JenkinsjobsManzen Database TrunkworkspaceNewCIPackage.2.509.nupkg...
Written database package to C:Program Files (x86)JenkinsjobsManzen Database TrunkworkspaceNewCIPackage.2.509.nupkg
COMPLETED SUCCESSFULLY: Publishing database package
Registering schema with DLM Dashboard...
Done

instalectual

I am having a similar issue using SQLCI integration with Teamcity and sending the schema information to DLM Dashboard. Was there ever any resolution on this? The build step in Teamcity just says "Registering schema with DLM Dashboard..." No schemas are recognized and every deployment is listed as drift. I am using the same .scpf filter file in the DLM dashboard as is used throughout our release/deployment process. Is there a way to verify that the DLM Dashboard is in fact recieving the schema updates from SQLCI?

My Config:
DLM Dashboard 1.6.5.639
TeamCity Enterprise 9.1.4 (build 37293)
DLM Automation Suite 1.3.11.393
sqlCI.exe -- Red Gate's SQL Continuous Integration v2.1.9.169
SQL Server 2014

Rob C

Hi instalectual,

We discovered a bug (reference SQLLH-642) in DLM Dashboard where very long file names in the script folder caused a problem that meant the schema didn't get registered. In this case it was down to unit tests with long names, but in theory it could be any object with long names.

Try excluding unit tests from your build step to see if that helps. Make sure you restart the DLM Dashboard processes before doing this to make sure the effects of the bug aren't lingering.

instalectual

I don't see anything that indicates this could be the issue. Perhaps you could clarify what you mean by Scripts folder. I also can't find any information on bug SQLLH-642 in your documentation. My build log only shows the following when executing the DLM Dashboard stage:

[17:08:55][Step 2/4] Registering schema with DLM Dashboard...
[17:08:56][Step 2/4] Done
[17:08:58][Step 2/4] Process exited with code 0

Rob C

By scripts folder, I mean the directory full of script files in your repository that SQL Source Control has created. You'll see a folder structure that represents the database and a creation script for each individual database object.

jmeyerbejdl

Robert Clenshaw wrote:

Hi instalectual,

We discovered a bug (reference SQLLH-642) in DLM Dashboard where very long file names in the script folder caused a problem that meant the schema didn't get registered. In this case it was down to unit tests with long names, but in theory it could be any object with long names.

Try excluding unit tests from your build step to see if that helps. Make sure you restart the DLM Dashboard processes before doing this to make sure the effects of the bug aren't lingering.

What exactly do you mean by 'very long'? We seem to have the same issue, though with Bamboo. As far as I can tell, the longest script name we have is 82 characters, which is quite long but as I don't know what the limit is I've no idea if it's long enough to cause the issue we're seeing.

Thanks