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

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

  PeterDaniels December 16, 2018 14:39

  - 0 +
• 
  I use integrated security for all connections to my local sql server.

  l570 December 17, 2018 12:17

  - 0 +
• 
  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 R December 17, 2018 12:48

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

  l570 December 17, 2018 12:57

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

  l570 December 17, 2018 13:16

  - 0 +
• 
  The target database needs to be pre-created.
  You can do it manually or use the Pre-Deployment script for that

  Sergio R December 17, 2018 13:23

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

  l570 December 17, 2018 14:06

  - 0 +
• 
  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 R December 20, 2018 10:12

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

  samssb82 March 05, 2019 18:24

  - 0 +
• 
  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 R March 05, 2019 20:02

  - 0 +
• 
  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 R April 15, 2019 12:57

  - 0 +

Add comment

Please sign in to leave a comment.