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

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

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
Tagged:

Best Answers

  • Options
    MondayMonday Posts: 77 Silver 3
    @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
  • Options
    PeterDanielsPeterDaniels Posts: 89 Bronze 3
    edited March 10, 2019 12:44PM Answer ✓
    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.

Answers

  • Options
    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:

    Best regards,
    Mac Frierson

    Mac Frierson | Product Support Engineer | Redgate Software
    Have you visited our Help Center?

  • Options
    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
    


  • Options

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

    Best regards,
    Mac Frierson

    Mac Frierson | Product Support Engineer | Redgate Software
    Have you visited our Help Center?

  • Options

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

    Best regards,
    Mac Frierson

    Mac Frierson | Product Support Engineer | Redgate Software
    Have you visited our Help Center?

  • Options
    @Mac_F, thank you the reply. However that does not absans my question. Please see my previous post for clarification.
  • Options
    MondayMonday Posts: 77 Silver 3
    edited February 28, 2019 2:21PM
    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.

  • Options
    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.
  • Options
    MondayMonday Posts: 77 Silver 3
    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
    }
Sign In or Register to comment.