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

• 
  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

  
  

  Mac_F February 25, 2019 18:45

  - 0 +
• 
  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
  

  
  

  
  

  PeterDaniels February 25, 2019 21:49

  - 0 +
• 
  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 February 26, 2019 10:54

  - 0 +
• 
  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 February 26, 2019 10:54

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

  PeterDaniels February 28, 2019 13:40

  - 0 +
• 
  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.
  
  

  Monday February 28, 2019 14:20

  - 0 +
• 
  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.

  PeterDaniels February 28, 2019 15:30

  - 0 +
• 
  @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

  Monday February 28, 2019 15:45

  - 0 +
• 
  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

  }

  Monday February 28, 2019 15:50

  - 0 +
• 
  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.

  PeterDaniels March 09, 2019 15:46

  - 0 +

Add comment

Please sign in to leave a comment.