How do you actually deploy a new database from the nuget package

I'm trying to recreate a problem in production.   I've dropped my own version of the database and trying to deploy the item from our built nuget package.  But since I don't have the database anymore, the connection object fails. So how do you actually deploy a new database from the nuget package.

My steps are as follows:
  1. $production = New-DatabaseConnection -ServerInstance "SERVERINSTANCE" -Database "DATABASENAME"
  2. $package ='PACKAGETITLE.nupkg'
  3. $build = Import-DatabaseBuildArtifact $package
  4. $release = New-DatabaseReleaseArtifact -Source $build -Target $production
  5. Use-DatabaseReleaseArtifact $release -DeployTo $production
My error is:

New-DatabaseReleaseArtifact : Couldn't connect to the database specified by the Target
parameter: Cannot open database "DATABASENAME" requested by the login. The login failed.
Login failed for user 'ME'.
Local parameters [connectionString = Data Source=SERVERINSTANCE;Initial 
Catalog=DATABASENAME;Integrated Security=True;Pooling=False;Application Name="Redgate
Software - SQL Change Automation"]

Am I missing a flag or something?
Tagged:

Answers

  • How do you normally connect to this database?  Are you able to connect to it using other tools (e.g. SSMS, sqlcmd) using integrated security from this host?  Or do you need to use a SQL login?
  • l570l570 Posts: 16 Bronze 1
    I use integrated security for all connections to my local sql server.
  • You can connect using both Integrated Windows authentication or a SQL user.
    Your script appears to be ok, as hinted already by PeterDaniels, I would suggest testing the same connection settings in SSMS on that system
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • l570l570 Posts: 16 Bronze 1
    So....I can connect to my local SQL server on my own machine with Windows Auth just fine.  The database doesn't exist as I'm trying to create it via the Nuget Package.  I can try creating a sql user and setting up the $production variable that way, but I'd expect the same results.   
  • l570l570 Posts: 16 Bronze 1
    Right, so changing the authentication method doesn't make a difference.  New-DatabaseReleaseArtifact always prompts me for a -Target value, and as the target database doesn't exist yet (as I'm trying to create it), it fails.

    So what am I doing wrong?  
  • The target database needs to be pre-created.
    You can do it manually or use the Pre-Deployment script for that
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • l570l570 Posts: 16 Bronze 1
    That's surprising and disappointing.   Are there plans to move this functionality into the SCA powershell scripts?    A flag to indicate that the database doesn't exist yet and will be created by the scripts inside of the package?
  • Currently we are not planning to change this, in these cases we advise using Pre-Deployment scripts. If you are using SQL Change Automation projects, you might notice that we already provide a template for this when you create a project (you will find a script named 01_Create_Database.sql, inside the Pre-Deployment folder in the project)

    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • samssb82samssb82 Posts: 12 Bronze 2
    Hi @Sergio R,

    The problem is that the release does not even get to the Pre-Deployment script because it is trying to validate a connection to the database being released.  Since the database does not exist, the connection fails.  This all happens prior to the Pre-Deployment script.

    The connection string specifies the target database, which does not exist.  Obviously there must be a way to make this work since you have the concept of a Pre-Deployment script that creates the database.

    Any help is much appreciated.
    Sam
  • Sergio RSergio R Posts: 600 Rose Gold 5
    edited March 5, 2019 8:05PM
    Hi Sam,

    I have just tested this with a SCA project and it is working, you should only get a warning:

    WARNING: C:\Users\Sergio\AppData\Local\Temp\2\DLM Automation\kyzd3cdb.sl4\Database37.sqlproj: Drift report and drift correction script cannot be generated because the target database [132576_temp] on [W2K16-1\SQL2012] does not exist.

    I have created a support ticket and will contact you there to get more details regarding your particular issue.
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • Sam was using the Azure DevOps/TFS add-on, and, unlike Powershell, up until recently, it didn't support using variables to create a database. The Azure DevOps/TFS add-on version 3.0.3 and newer now support this.
    Please note that if you are using Azure Devops you will use the latest version of the add-on by default, but if you are on-premises TFS you need to update it manually from the marketplace.
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
Sign In or Register to comment.