Using SQL Compare to generate DB in Docker

MrBaas

Hey.

We're creating Docker containers with sql server on them, and we want the docker containers to fetch all databases from GIT and create them. However, we can't find a good solution to this.
We are now looking at running t-sql statements to create empty databases when containers are built, and then sync the databases with sqlcompare tool from host to docker-container. But SqlCompare is not able to sync/deploy databases when "stored procedures" have referances to other databases, e.g:

{
USE [DB1]
CREATE PROCEDURE SomeProcedure
...
From DB2.dbo.SomeTable
..
}

In our case DB1 and DB2 are both referencing each other, so we can't create them in a manual order.

Is there a flag in SQLCompare that can ignore checks on other tables ?
Maybe there's another way of doing this that we're not seeing.. Any help would be great!

Thanks

AlessandroAlpi

Hi @MrBaas,
my reply is related to a certain SQL Server object. I've been in the same trouble years ago, and now the solution is still working. Would you try to use SYNONYMS instead a fully qualified name when calling "external" databases? The compilation of the stored procedures should use the deferred name resolution.
Hope I've got it correctly.

Let us know :-)

MrBaas

We're creating a new test environment for our production databases, and we're talking about a lot of stored procedures. I was hoping for a solution where we didn't need to rewrite all of them
Maybe it's for the best.

Thanks, @AlessandroAlpi

AlessandroAlpi

Got it, typical problem, isn't it? I can understand you :-)
Actually, it's really simple to "rewrite" them all. You could script only the stored procedures in one file, then, replace the full qualified name with the synonym. For creating the synonyms, you can extract with a regexp the fully qualified names and generate the CREATE SYNONYM script.

If you figure out how to match the strings, it will be simple, don't you think?