SQL CI integration with DLM dashboard not working

jmeyerbejdl

Hi,

I'm trying to get the DLM dashboard set up for one of our databases which we've just started building and deploying using SQL CI. We're using Bamboo and are building the database using the command line rather than the Bamboo add on. Looking at the Bamboo build logs, it appears as though the schema was successfully registered with the DLM dashboard but once it's deployment (deployment step is automatic on a successful build in this case as it's deploying to our dev integration environment so it's happening only a few minutes later) the DLM dashboard is showing it as Drift rather than the expected Update. We've been really impressed with the DLM dashboard up to now and SQL CI is proving to be invaluable, but we really need to get this working so that we don't have to manually approve known schemas - if everything looks like drift it's pretty hard to monitor what's going on.

I'm reasonably sure that the command is good but here it is, just in case:

"C:Program Files (x86)Red GateDLM Automation Suite 1SQLCIsqlCI.exe" Build /scriptsFolder=E:amboo-homexml-datauild-dirDB-TITAN-JOB1DB /packageId=Titan /packageVersion=0.46 /temporaryDatabaseServer=. /temporaryDatabaseName=Titan /outputFolder=E:amboo-homexml-datauild-dirDB-TITAN-JOB1 /dlmDashboardHost=lon-sqlmon01 /dlmDashboardPort=19528

And here's the section from our Bamboo logs that makes me think that the schema should have been successfully registered with DLM dashboard:

build 21-Jan-2016 10:53:40

---

build 21-Jan-2016 10:53:44 COMPLETED SUCCESSFULLY: Validating database state
build 21-Jan-2016 10:53:45 STARTING: Publishing database package
build 21-Jan-2016 10:53:45 Writing package to E:amboo-homexml-datauild-dirDB-TITAN-JOB1Titan.0.46.nupkg...
build 21-Jan-2016 10:53:56 Written database package to E:amboo-homexml-datauild-dirDB-TITAN-JOB1Titan.0.46.nupkg
build 21-Jan-2016 10:53:56 COMPLETED SUCCESSFULLY: Publishing database package
build 21-Jan-2016 10:53:56 Registering schema with DLM Dashboard...
build 21-Jan-2016 10:54:01 Done
simple 21-Jan-2016 10:54:02 Finished task 'Build DEV DB (command line)' with result: Success

Any help troubleshooting this would be greatly appreciated. I saw it working correctly (though with Team City) on your training course, so I know it can be done.

Thanks

James Meyer-Bejdl

Rob C

Hi James,

We need 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. To verify that the schema has been registered, please perform the following -

1. Visit http://localhost:21060 in Internet Explorer.
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.

jmeyerbejdl

Robert Clenshaw wrote:

Hi James,

We need 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. To verify that the schema has been registered, please perform the following -

1. Visit http://localhost:21060 in Internet Explorer.
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.

Robert,

Thanks for the reply. I can see that several snapshots exist with names of packages built in Bamboo and yet I've never seen anything that wasn't identified as drift. It appears that registering the schema with the dashboard is working, exactly as the Bamboo log had suggested, but that something else is going wrong. I'll try comparing the nuget package as you suggested. Witha a bit of luck that might shed some light on the issue.

jmeyerbejdl

Robert,

I've now tried comparing the nuget package as suggested and it seems as though perhaps there is an issue with static data. If I compare the extracted nuget package from the root I get a whole load of errors saying 'Non-schema statement ignored', followed by one of the inserts of static data rows to our tables. I'm not sure if this is significant or not, but if do the comparison in the dbstate subfolder of the package rather than at root, that works correctly - the schema in the package and the database schema are identical. Is this down to the static data we have linked and if so is there some way that I can fix this?

Thanks

jmeyerbejdl

Robert,

In an attempt to prove/disprove the notion that it was down to reference data I've added another database to our CI builds. This one is an archive database that has no reference data at all and it fails in exactly the same way, just without any errors being displayed in SQL Compare - the snapshot is visible in the SqlCompareSnapshots and comparing the extracted nuget package with database using SQL compare shows them to be identical, but the dashboard still reports drift rather than an update. Any idea where we can go from here? I'd really like to get this working.

Thanks

Rob C

Hi James,

I've created a support ticket for you and you'll receive an email shortly.

crimdon

Hi Rob,

Was this ever resolved as I'm getting the same thing.


Regards

Andrew