User profile for alanna

I'd like to thank SQL Release Support for helping me figure this out. Just in case anyone else encounters this issue, I'll explain what went wrong and how it was resolved. A quick summary of what the issue was: The first issue we encountered was due to the fill factor bug that they say will be fixed in an upcoming release - in the meantime, the workaround is to skip the pre-update check (see step 2 below). Once that was in place, it was discovered that there ended up being an issue with the foreign keys on two of our static tables - these foreign keys reference the static table that I'd updated as part of my test. When data was added into that table, constraints had to be dropped and re-added. Re-adding the constraints had the side effect of causing those same constraints to be untrustworthy (WITH NOCHECK). This caused the post-update check to fail. It is possible to skip this check (add -SkipPostUpdateSchemaCheck to the script mentioned in step 2 below), but this is not recommended by the SQL Release support team. Instead, here's what I had to do to fix it: 1. In Octopus Deploy, open the Step Template for Redgate - Deploy from Package 2. At the end of the script, change: $release | Use-DatabaseRelease -DeployTo $targetDB -Verbose to: $release | Use-DatabaseRelease -DeployTo $targetDB -SkipPreUpdateSchemaCheck -Verbose 3. Click Save and be sure to update the process step in your project to use the updated step template 4. Go into the Octopus Deploy project, click on Process and select the process step associated to the Redgate - Deploy from Package template (for me, this was process step 2, the 1st one being the one that downloads the package) 5. In the SQL Compare options field, enter the following then click Save: IgnoreWithNoCheck 6. Add a new step, also using the Redgate - Deploy from Package template 7. Enter in all the same information as in step 2, but this time leave the SQL Compare options field blank. Instead of doing steps 6 & 7, you could also achieve the same thing by either manually running SQL Compare after the deployment completes (not really an option if you're looking to fully automate the whole process), or use Octopus Deploy to run a SQL script that would drop and re-add the constraints for the tables in question. This wasn't an option for us as we'd like to remove all manual intervention/customization from our deployments and this would require us to create a custom script every time depending on what static tables were updated in the deployment. NOTE: They did say that they'd look into the issue surrounding the constraints - hopefully at some point in the future it won't be an issue anymore and I can remove that last step that I'd added to Octopus Deploy. / comments

I'd like to thank SQL Release Support for helping me figure this out. Just in case anyone else encounters this issue, I'll explain what went wrong and how it was resolved.A quick summary of what t...

0 votes

Thanks Peter. In TeamCity, the deploy step is configured as follows: Runner Type: Command Line Step Name: Auto deploy to Testing Run: Executable with parameters Command Executable: C:OctopusOctopusTools.2.6.1.52Octo.exe Command parameters: create-release --server="http://<OurInternalServerName>/Octopus/" --apiKey=<OurAPIKey> "--project=Compas" --version=0.%build.number% --deployTo=Testing --waitfordeployment --enableServiceMessages --progress In Octopus Deploy, there is an environment configured called Testing that points to my local machine. Clicking on "check health" reports that it's able to connect to the machine ok. The Compas project is configured with 2 process steps: 1. Download the package for the database, this step completes successfully. 2. Deploy the package to the database - this is the step that fails. In the Octopus Deploy step responsible for deploying the nuget package to the database in question, I'm using the Redgate template for the "Redgate - Deploy from Package" step type. The template is set up as follows: Database package step: The previous step for downloading the package for the database is selected. Target SQL Server instance is set to the variable #{DATABASE_SERVER}, which is assigned the value of the name of my local machine/SQL Server instance: DEV-LAPTOPMSI Target database name is set to the variable #{DATABASE_NAME}, which is assigned the value of Test_B Username & Password are blank (NOTE: Every time I check this process step, it pre-populates my username & password. If I make changes, I have to remember to clear these fields again before hitting save) Filter path: empty. I have tried setting it to C:SQLCompareFiltersForTentaclemainDatabaseExclusions.scpf NOTE: This is the same filter that TeamCity is set to use when it builds the database nuget package initially. I have tried it both with/without the filter at this point and the outcome is the same. Presumably this configuration will properly set the target and source databases. I'm unsure of how to get my hands on the script that this process is using - I did spend some time last week trying to find it because I figured if I could just try running it myself, it may help point me in the right direction. However, I wasn't successful - it seems as though all the scripts/files that are created get deleted as soon as it fails (which makes sense) and I haven't been fast enough to copy them before they're removed. I can, however, email you a copy of the log files from both Octopus Deploy and Team City. Hopefully those will help. / comments

Thanks Peter.In TeamCity, the deploy step is configured as follows: Runner Type: Command Line Step Name: Auto deploy to Testing Run: Executable with parameters Command Executable: C:OctopusOctopu...

0 votes

Auto Build/Deploy fails: SchemasAndStaticDataAreNotIdentical

I have configured an automatic build/deployment in Team City using SQL CI and Octopus Deploy. It is set up to build the database from source control and deploy to database Test_B. There is an err...

2 followers 3 comments 0 votes

Just in case anyone else has the same issue in the future, the issue was that the git config file requires that the path to git.exe is contained within the Windows path variable. It wasn't for me, so I had to create a custom git.xml config file and specifying the full path to git.exe for each of the command lines. I knew I was missing something simple [image] / comments

Just in case anyone else has the same issue in the future, the issue was that the git config file requires that the path to git.exe is contained within the Windows path variable. It wasn't for me,...

0 votes

Error linking database to Git

I'm in the process of evaluating a bunch of the Red Gate tools - SQL Source Control being one of them. Currently, none of our databases are in source control and this getting to be a big issue for...

2 followers 2 comments 0 votes

How can we help you today?

alanna

Activity overview