Synonyms in SQL Automation Pack v1.1

Totu

Hi Guys,

I have been testing out the SQL Automation Pack v1.1 and found out that it throws the following error when run through the TeamCity plugin:

[17:20:03][Validating database state] Error: Synchronization of 'Scripts.state' and
[17:20:03][Validating database state] '<CI-SERVER>.sqlCI_4b8d7896-ceb4-4a59-abb6-a7683b2e9e28'
[17:20:03][Validating database state] failed: Synonym 'Geo!Geo.tbGeoData' refers to an invalid object.

The synonym is referring to an object in another database on the same instance. The CI server doesn't have that DB and the validation fails.
The teamcity account is sysadmin on the SQL Server box which is a 2012 Developer Edition (x64).

Reverting back to the pre-1.1 build runner the validation passes.
I am keen to use the v1.1 because it allows us to validate the CLR assemblies in our packages.

This looks like a bug to me. Can you confirm?

Also a related question that I have bumped into recently that also on the CI server if we try to validate a package that contains a linked server reference in one of the SPs, the validation fails as it tries to resolve the full linked server reference. The reference iirc is a select statement (and not an SP call, might be worth trying it that way)
The build runner in this case is a pre-1.1 runner and is more likely to be a SQL Server problem than a RedGate one.


Cheers,
Balint

david.conlin

Hi Balint,

Can I ask which functionality of the old version of SQL CI you are using? It is possible that the validation was just not being run before, and is now turned on by default.

To use SQL CI, you will need to be able to deploy your database to the temporary database server you provide. Unfortunately, we can't deploy it if the database depends on objects in another database which do not exist.

You could fix this by keeping the dependency databases up to date on the CI server, using SQL CI's "sync" step, so that the objects your database depends on will exist.

Alternatively, if you're only using SQL CI to create a package for Deployment Manager, and aren't interested in any of the validation that SQL CI does, you can use RGPublish, a command line utility that comes with Deployment Manager. You can read more about RGPublish here: http://documentation.red-gate.com/display/DM2/Using+RgPublish.exe

Totu

Hi David,

Thanks for the quick answer, sorry for no replies until now.
I was investigating further to try to point out what might be the problem but at this point it looks like the problem might be with certain synonyms only.

I will come back once I have more detailed information about the situation. It may be very well some weird behavior with SQL Server and deferred name resolution and not the SQL Automation pack.

As to answer your suggestions:
I don't think maintaining 30+ databases on the same instance is a healthy thing. Even if the DBs themselves are empty. Our CI server is not designed for this. I will try to resolve the synonym problem instead as I might be able to isolate the conditions.

Cheers,
Balint

Totu

Hi,

I found the problem with the usage of synonyms and as predicted it has nothing to do with the SQL Automation pack. I found another example where it is much clearer why it failed.

For the record: when creating views in SQL Server, all objects must be present at the time of creation even if they are hidden by synonyms.

When I asked my question here on the forum, I was looking at the wrong object and that is why I didn't understand the problem.

Cheers,
Balint