Is there a project file setting to remove the server name check?

PeterDaniels

At my company, there is a large wall between dev and ops. Ops DBAs want a single SQL script to use in all of their environments (anything above dev and qa). So, I have been taking the TargetedDeploymentScript.sql and removing the check that requires a specific sever name, so it will execute on multiple severs. Yes,I realize this is non-ideal.  I have a powershell script that does this.

Wondering if there is a property that I can set in the sqlproj file, or a cmdlet option that will remove it automatically?

TIA,

-Peter

Monday

@PeterDaniels  I don't get a TargetedDeploymentScript.sql at all. I get a PackageScript.sql in \db\output\

Here is how I am doing my build that produces a nuget package where PackageScript.sql is located.

$temporaryDatabase = New-DatabaseConnection -ServerInstance $EAMDBServer -Database $PQTempDB

$ProjectObject = $ProjectFile | Invoke-DatabaseBuild -TemporaryDatabase $temporaryDatabase

$DBDeployFile = $ProjectObject | New-DatabaseBuildArtifact -PackageId EAMDatabase -PackageVersion $PackageVersion

$DBDeployFile | Export-DatabaseBuildArtifact -Path $CopyLocation

PeterDaniels

Thank you, @Monday.  It took me a while to find time to dig into this.  I had never looked into the actual files within the nupkg file.  Now, thanks to you, I do see the db\output\PackageScript.sql.  I also see how it has ALL of the migrations including baseline and will intelligently apply them to whatever DB I set in the sqlcmd var DatabaseName.
There are a couple of issues with this file (in its raw form) that I will need to resolve to be able to pass it to the ops DBAs so they can execute in SSMS via SQLCMD mode:
1) I'll need to uncomment the sqlcmd vars section
2) I'll need to manually set the ReleaseVersion and PackageVersion.  Not sure why the PackageVersion isn't set by New-DatabaseBuildArtifact cmdlet since we are passing that in.  

Probably a lil powershell will take care of the above concerns, but it would be nice to have options during build to extract this file with these taken care of.

UPDATE: I am also able to get the PackageScript.sq content from the PackageScript property of the BuildArtifact object returned from Invoke-DatabaseBuild.  This way I can skip the Export-DatabaseBuild step, and just save this to a file and process it.

Mac_F

Hi Peter,

Since you are targeting multiple servers to update, we would recommend using Package deployment scripts. Here is a link to the documentation page:

https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/generating-deployment-scripts/deployment-scripts

I hope this helps.

Best regards,

Mac Frierson

PeterDaniels

Thank, Mac.  I'm using package deployment.  I'm using New-DatabaseReleaseArtifact + Export-DatabaseReleaseArtifact.  One of the outputs of Export-DatabaseReleaseArtifact is a "TargetedDeploymentScript.sql".  This script works for me, but I have to edit it to remove the code that requires a specific target server name.

I'm asking if there is a setting in the project file that I can use to have it not include that check so I don't have to edit it each time?

Example of the generated code I remove:

-- As this script has been generated for a specific server instance/database combination, stop execution if there is a mismatch
IF (@@SERVERNAME != 'ATL2100PC0GQ1DZ' OR '$(DatabaseName)' != 'TestSCA_DEV_INT')
BEGIN
	RAISERROR(N'This script should only be executed on the following server/instance: [ATL2100PC0GQ1DZ] (Database: [TestSCA_DEV_INT]). Halting deployment.', 16, 127, N'UNKNOWN') WITH NOWAIT;
	RETURN;
END
GO

My PoSh function to remove it:

function Remove-LinesFromFileAfterMatch {
    [cmdletbinding()]

    param (
        [parameter(Mandatory=$true)]
        [string]
        $FilePath

        ,[parameter(Mandatory=$true)]
        [string]
        $StringToMatch

        ,[parameter(Mandatory=$false)]
        [int]
        $NumLinesToRemove = 1
    )

    begin {
        $TempFile = New-TemporaryFile
        $LineNum = 0
    }

    process {
        switch -Wildcard -File $FilePath {
            $StringToMatch {
                # We don't want to output this line or the next $NumLinesToRemove
                $LineNum++
                continue
            }

            {$LineNum -gt 0 -and $LineNum -le $NumLinesToRemove} {
                $LineNum++
                continue
            }

            # Send rest of teh orig file to the new file
            default {$_ >> $TempFile}
        }
    }

    end {
        # and now swap the files (tmp -> orig)
        Copy-Item -Path $TempFile -Destination $FilePath -Force
        $FilePath
    }
}

And using it with the file:

$FilePath = "C:\users\peter\tmp\TargetedDeploymentScript.sql"

$Args = @{
    FilePath = "$FilePath"
    StringToMatch = "-- As this script has been generated for a specific server instance/database combination, stop execution if there is a mismatch*"
    NumLinesToRemove = 8
}

Remove-LinesFromFileAfterMatch @Args

Mac_F

Hi @PeterDaniels ,

Since you are targeting multiple servers to update, we would recommend using Package deployment scripts. Here is a link to the documentation page:

https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/generating-deployment-scripts/deployment-scripts

I hope this helps.

Best regards,

Mac Frierson

Mac_F

Hi @PeterDaniels ,

Since you are targeting multiple servers to update, we would recommend using Package deployment scripts. Here is a link to the documentation page:

https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/generating-deployment-scripts/deployment-scripts

I hope this helps.

Best regards,

Mac Frierson

PeterDaniels

@Mac_F, thank you the reply. However that does not absans my question. Please see my previous post for clarification.

Monday

Are you using the Schema drift options?  https://documentation.red-gate.com/sca3/automating-database-changes/automated-deployments/handling-schema-drift   I am not and I have never seen this TargetedDeploymentScript.sql. I would think you would not want to be using drift when you are not targeting a specific server / DB. Maybe not using drift will solve your problem.

PeterDaniels

Thanks, @Monday.  So, you're not getting the server name check code section in your TargetedDeploymentScript.sql?  I always get it, and I don't have any special drift settings set (all default).  Is there a different way to run the New-DataBaseReleaseArtifact that will generate a TargetedDeploymentScript.sql without the check?  I still need to pass a single SQL script (can be SQLCMD) to the ops DBAs that will deploy the latest changes (to multiple target DBs/servers).  They are not yet willing to run a PS script.  I'm trying to avoid using the MSBuild generated scripts, too, but willing to consider if that seems like the right approach.

Monday

I should also note though that when it comes to the deployment it uses a power-shell script that uses variables for which server / db to use.

If ($DoDBDeploy -eq 1) {

Write-Host "Making database changes...."

$SqlCmdVariables = @{ReleaseVersion = $IterationNumber;}

$DBConnection = New-DatabaseConnection -ServerInstance $EAMDBServer -Database $PQDBName

Test-DatabaseConnection $DBConnection -ErrorAction SilentlyContinue -ErrorVariable ConnectionErrors

if ($ConnectionErrors.count -ne 0) #if we couldn't connect

{

write-warning $ConnectionErrors[0]

exit

}

if (!(Test-Path $DBDeployFile)) {

Write-Warning " $DBDeployFile does not exist"

exit

}

$DBUpdate = New-DatabaseReleaseArtifact -Source $DBDeployFile -Target $DBConnection -SqlCmdVariables $SqlCmdVariables

Use-DatabaseReleaseArtifact $DBUpdate -DeployTo $DBConnection

}