TeamCity and Redgate (DLM Automation) Failure

ZahirYounis

Hi,

I'm using the trial version of your DLM Automation tool and I'm currently running into an issue where by the database is not building due to the following error (TeamCity log):

[16:40:13][Validating database state] Deploying changes (from DB1 to DB2)
[16:40:15][Validating database state] Error: Synchronization of 'Scripts.state' and
[16:40:15][Validating database state] '***.***.***.***.sqlCI_a8b8ea2f-ba60-4bbc-a6a5-e2d9e97c43e2' failed: Synonym
[16:40:15][Validating database state] 'synTest' refers to an invalid object. Error executing the following SQL: -- ============================================= -- Author: **** -- Create date:
[16:40:15][Validating database state] July 2013 -- Description: test Synonym-- ================================...
[16:40:16][Validating database state] FINISHED WITH ERROR: Validating database state
[16:40:21][Step 1/1] Process exited with code 32
[16:40:21][Step 1/1] Step Build DB (Redgate DLM Automation Build) failed

The synonym which is being referred to is accessing an object in another database on the same db server. Now I've tested this locally (TeamCity and DLM Automation) on a dev machine where the database for TeamCity is on the same machine as the DLM Automation is installed and it works fine. I've ensured the TeamCity user has the same permissions as the dev machine but it still doesn't work.

Also a point to note, I build the other database (which is called in the synonym) from TeamCity and that works fine, that database does not have any synonym's.

Can you please help as this is supposed to be the POC to get everything moving forward with Automated Deployment.

Thank you

ZahirYounis

I've done further testing and it seems to be an issue with using synonym's. The database which built perfectly before, now doesn't build with the same error.

NOTE: Both of the databases build fine on a dev machine but not on a server.

Can someone please get back to me with a solution.

Thank you

PeterG

Hi,

I'm guessing the issue is when you build the first database we delete it afterwards, so when you try and build the second database it has disappeared. For this I would suggest creating two databases on the same server - such that the synonyms correctly point at the first database. Then in your build steps in teamcity specify the server and database instead of relying on the defaults. (Database Name is under advanced options).

ZahirYounis

Hi Peter,

The synonyms are pointing to the database correctly.

I've had a look at the build steps but that doesn't have the database name under advanced options.

I select the runner type as 'Redgate DLM Automation Build', and the advanced options are showing 'Additional parameters' and 'Temporary database name'. Also shows 'Execute Steps' but further up underneath 'Step Name'.

One thing I have noticed is that if I follow this blog https://www.red-gate.com/blog/database-lifecycle-management/deploying-cross-database-dependencies and convert the function calls (which are using the synonyms) to stored procedures, the build works.

I have given the TeamCity user the sysadmin role so I can't seem to think it's an issue there.

Your help is much appreciated.

ZahirYounis

Hi,

I've had a look around the forum and I found someone else who was having the same issue as I am (https://forums.red-gate.com/viewtopic.php?t=20791).

The problem is that the CI database is sitting on a different server as to the version controlled databases. The reason why it worked locally was the CI (TeamCity) database was on the dev machine which also had the other databases on there so it could reference them with out any issues.

Now the question is, how do I use synonyms in functions and use DLM Automation to build the database? That is without rewriting 100+ stored procedures (along with numerous front end calls) as mentioned here (https://www.red-gate.com/blog/database-lifecycle-management/deploying-cross-database-dependencies).

PeterG

The problem we had with synonyms was when we called a function that used the synonym it would fail to find the function. The build itself was fine, it was only when we played with the database afterwards that things started failing. If the usage of synonyms is working for you, then there should be no issue.

ZahirYounis

Hi Peter,

I've gone down the route of this article (http://workingwithdevs.com/cross-database-dependencies-and-automated-builds/) which was a solution which I knew about but wasn't entirely keen on.

I spoke to one of your pre-sales engineers who explained the reasoning behind the decision and it makes sense in what Red Gate was trying to achieve.

So for anyone out there who is using synonym's, ensure which ever environment your database is being built in, also has the databases you're referencing e.g. DB1 has a synonym pointing to DB2, when you use DLM Automation (in my case through TeamCity), ensure that the TeamCity build step is using the same 'Temporary Database Server' details where both databases are residing. They can be empty shells of the databases in question, but that is enough for DLM Automation to reference them and build correctly.

Thank you for your help.

saulcruz

Does this mean that if we ever use synonyms in either views or user defined functions we won't be able to use LocalDB as our schema validation database?

for example:

$validatedScriptsFolder = $scriptsFolder | Invoke-DlmDatabaseSchemaValidation -SQLCompareOptions $options

instead of 

$validatedScriptsFolder = $scriptsFolder | Invoke-DlmDatabaseSchemaValidation -SQLCompareOptions $options -TemporaryDatabaseServer $buildDb

Sergio R

Yes, if you have synonyms you should use the TemporaryDatabaseServer or TemporaryServer option rather than LocalDB
@ZahirYounis last post is still 100% relevant in the current version of DLM Automation.