Options

VSTS Azure deploy error

StephanBisStephanBis Posts: 5 New member
edited April 23, 2018 4:08PM in ReadyRoll
Hello,

We've followed the documentation on how to deploy the database via VSTS CI/CD. We are trying to deploy to SQL Azure, the release deploy step is configured and points towards the correct database.
However when we want to deploy our database the deploy step fails with the following error message:
Azure SQL Database does not support switching between databases. Connect to [{dbname}] and then re-run the script.

Thank you in advance,
Stephan
Tagged:
· Share on Twitter

Best Answer

  • Options
    dnlnlndnlnln Posts: 234 Gold 2
    Answer ✓
    Hi Stephan,

    I'm sorry to hear you're continuing to run into issues with your deployment. At this stage you may have to try and debug the deployment script against the Azure environment line-by-line to find out where the problem is arising. For information on how to execute a package script in SQL Server Management Studio, please see this article.
    Daniel Nolan
    Product Manager
    Redgate Software
    · Share on Twitter

Answers

  • Options
    dnlnlndnlnln Posts: 234 Gold 2
    edited April 24, 2018 6:58AM
    Hi Stephan,

    Sorry to hear you've run into a problem with deploying your database in VSTS. Would you mind confirming which  Target platform is selected in your project file? This can be done by opening the project designer (right-click Project in the Solution Explorer and select Properties).


    Either of the Azure options should work. If you needed to change the selection, you may need to close and re-open the project in order for the setting to take effect within IntelliSense, but the project should then deploy successfully.
    Daniel Nolan
    Product Manager
    Redgate Software
    · Share on Twitter
  • Options
    StephanBisStephanBis Posts: 5 New member
    Hello Daniel,

    I've tried both Azure platforms (normal and V12) but both give me the exact same error on deploy. The weird part is that the generated SQL files do not even contain any USE statements, is this something that gets generated on the background while deploying?

    Best regards,
    Stephan
    · Share on Twitter
  • Options
    dnlnlndnlnln Posts: 234 Gold 2
    Hi Stephan,

    That's very unusual, I don't believe that any code is inserted at deployment time. Would you mind sending me your full deployment log as a private message (with any important details redacted) so I can investigate further?

    Thanks!
    Daniel Nolan
    Product Manager
    Redgate Software
    · Share on Twitter
  • Options
    StephanBisStephanBis Posts: 5 New member
    Hello Daniel,

    We re-added the ReadyRoll project and built the solution again. It seems that the error described above has been solved!

    We're now receiving this error: There is already an object named '{name}' in the database. We had some trial and error and think we know where the problem lies: the generated script contains a couple of create schema lines, we think these are the culprit. As there are some create table statements afterwards which also use the same schemas.
    However when we delete these, the build fails because the script cannot be validated (    The specified schema name "{name}" either does not exist or you do not have permission to use it.).

    Best regards,
    Stephan
    · Share on Twitter
  • Options
    dnlnlndnlnln Posts: 234 Gold 2
    edited April 26, 2018 4:01AM
    Hi Stephan,

    Great to hear that you managed to get the build working.

    In terms of why the script is failing, this may be difficult to determine without knowledge of the design of your target database and setup of your project. However may I offer a couple of thoughts on how you might go about diagnosing the problem:

    • To make it easier to debug the validation step, include the shadow db deployment as part of your local build. To do so, in Visual Studio, open the project designer. On the Debug tab, under "Shadow database", uncheck "Skip shadow database deployment when building inside Visual Studio". When you Build the solution, the shadow db will deploy and display any errors with validating the project. To ensure you're using a fresh copy of the shadow with each build, Clean the solution or perform a Rebuild.
    • To determine why the deployment itself is failing, take a backup of the schema from the database from Azure and restore it onto your machine (e.g. using via a DACPAC). If you deploy the solution and there are errors, then it's possible that the project is not in-sync with the target database. If this is the case, the baseline scripts may need tweaking so that they correctly reflect the current state of the target.

    As a last resort, I'd suggest starting with a new ReadyRoll project and going through the import wizard again. Note that, to reset your target database on Azure, you may need to drop the [__MigrationLog] table manually to allow ReadyRoll to reset the baseline.

    For more information, please see Deploying to an existing database.
    Daniel Nolan
    Product Manager
    Redgate Software
    · Share on Twitter
  • Options
    StephanBisStephanBis Posts: 5 New member
    Hello Daniel,

    We've tried your suggestions and found out that deploying the shadow database locally works as expected, it deploys and then says the target (development) database is in sync with the shadow.
    However when we deploy the to the target Azure database we still get the exact same error.

    2018-05-02T06:21:41.3135389Z ***** EXECUTING MIGRATION "Migrations\1.0.0-Baseline\001_20180502-0810_username.sql", ID: {06d2371d-562c-4c24-9251-8c3830090786} *****
    2018-05-02T06:21:41.3198498Z Creating schemas
    2018-05-02T06:21:41.4371392Z Msg 2714, Level 16, State 6, Server db, Line 2
    2018-05-02T06:21:41.4371671Z There is already an object named 'proc' in the database.
    2018-05-02T06:21:41.4371878Z Msg 2759, Level 16, State 1, Server db, Line 2
    2018-05-02T06:21:41.4372047Z CREATE SCHEMA failed due to previous errors.
    2018-05-02T06:21:42.3988336Z ##[error]A deployment error occurred: sqlcmd.exe exited with a non-zero exit code.
    2018-05-02T06:21:42.4983187Z ##[section]Finishing: ReadyRoll Deploy Database

    The target Azure database is completely empty and does not contain any tables so it's strange to us that it "complains" about the object already being there. We re-created the ReadyRoll project and tried again, still the same error message.

    Best regards,
    Stephan
    · Share on Twitter
  • Options
    StephanBisStephanBis Posts: 5 New member
    Hello Daniel,

    We've re-created the Azure database and now everything is working as expected... our guess is that something went wrong with the first few deployments and caused our problems. 
    Thank you for your help!
    · Share on Twitter
  • Options
    dnlnlndnlnln Posts: 234 Gold 2
    Hi Stephan,

    I'm very glad to hear that! (Incidentally, I did post an additional suggestion of debugging the package script in SSMS, but unfortunately it seems to have been lost).

    If you encounter any further issues please let me know.
    Daniel Nolan
    Product Manager
    Redgate Software
    · Share on Twitter
Sign In or Register to comment.