What are the challenges you face when working across database platforms? Take the survey
Options

Using SQL Compare to generate DB in Docker

MrBaasMrBaas Posts: 2 Bronze 1
edited September 29, 2017 8:33AM in SQL Compare
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

Answers

  • Options
    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 :-)
    Alessandro Alpi
    CTO @ Engage IT Services s.r.l.
    Database Administrator and Team Leader
    Microsoft MVP - Data Platform
    Staff member of getlatestversion.it community
    personal website | blog
  • Options
    MrBaasMrBaas Posts: 2 Bronze 1
    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
  • Options
    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?
    Alessandro Alpi
    CTO @ Engage IT Services s.r.l.
    Database Administrator and Team Leader
    Microsoft MVP - Data Platform
    Staff member of getlatestversion.it community
    personal website | blog
Sign In or Register to comment.