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

l570

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?

PeterDaniels

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?

l570

I use integrated security for all connections to my local sql server.

Sergio R

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

l570

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

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?  

Sergio R

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

l570

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?

Sergio R

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)

samssb82

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 R

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

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.