How can we help you today? How can we help you today?

Rebuilding from the nuget package for deployment purposes

We're currently working in a development & build environment that is strictly segmented from our production environment.   None of our development machines can reach into production, and neither can our build server.  We're currently building with TFS 2015 with the SCA extensions for Build and Release and doing basic run-through with some development servers that are reachable from the build server.  Additionally, we're working with a large number of existing databases that were not developed in any Redgate product (SCA, ReadyRoll, SQL Source Control).  Finally, there are occasional communication lapses between operations and development in situations where emergency patches have to be made, so drift is of course a concern. 

We're working on getting approval to poke a hole strictly from our build server to our production system to generate accurate reports and update scripts, but that could be a lengthy process.   In the meantime, I'm trying to essentially take the built nuget package and rebuild it to reflect a different target database, simulating the handover of our package from Development to Production.  

Working with the powershell commandlets, I've come up with the following approach:

$package = '<MYDB>.nupkg'$production = New-DatabaseConnection -ServerInstance "localhost" -Database "<MYDB>"$build = Import-DatabaseBuildArtifact $package $release = New-DatabaseReleaseArtifact -Source $build -Target $production$release | Export-DatabaseReleaseArtifact -Path "C:\temp\Release"$path = "C:\temp\Release\Project\<MYDB>.sqlproj"$validatedProject = $path| Invoke-DatabaseBuild -TemporaryDatabase 'Data Source=localhost'$buildArtifact = New-DatabaseBuildArtifact -PackageId DoubleCheck -PackageVersion 1.0 -InputObject $validatedProject$release = New-DatabaseReleaseArtifact -Source $buildArtifact -Target $production$release |Export-DatabaseReleaseArtifact -Path "C:\temp\DoubleCheck" -Format Folder

But looking at the output in DoubleCheck, I'm getting a Changes and Drift report that tells me 

No schema snapshot could be retrieved from the target database; no report will be generated until your next deployment. If you continue to see this message, then it is possible that a schema snapshot file is not being created during build. To resolve, specify the ShadowServer property in your build configuration. See https://www.red-gate.com/sca/continuous-integration

So that leaves me with a few questions (besides the obvious one "Why am I doing this to myself?"):
  1. Is the thing I'm attempting to do even possible?
  2. If so, what am I missing?  What am I doing wrong?
  3. Is there an easier way to overcome the challenges posed by our environment (besides allowing the build server to use production as a target for comparison purposes.  We're working on that)?

l570
0

Comments

1 comment

Sort by
  • way0utwest
    A couple things, first, rather than try to decode your PoSh, can you explain what and where you're trying to do something? We can't see your environment, so I have no idea where dev, test, production are or what the machines are.

    Second, there is no issue with a database developed in any way. The first time you start using SCA to deploy, it will square things up.

    Third, are you using SCA in VS as the client or SQL Source Control in SSMS for development? SCA biuld/release is the same, but it helps to guide you to know.

    Next, the purpose of build is to validate your SQL. the purpose of seeing a target is to ensure we build a valid package. If you can't see production, you will have issues here, especially with drift. Drift isn't an issue overall, but you want to account for it, and so any issues need to be fed back to development (recommended) to clear the drift issues. If you use the SkipDriftCheck (?) flag, then you risk the package not deploying, which defeats the purpose of using a DevOps style flow for development.

    If you can't connect to production, what I'd recommend is that you get a SQL Compare snapshot of prod and pull that back, load that to a staging database and use that as the target. You don't need data (really), just schema. Of course, data changes in your release aren't tested, but this is a lightweight way of getting schema deploys out. You can schedule this, make it part of the pipeline, or something else, but you need a good view of production schema to do the release packaging.

    If that doesn't make sense, you have other questions, or I've misrepresented what you're doing, please let me know.
    way0utwest
    0

Add comment

Please sign in to leave a comment.