Generation of new database?

I have a VSTS build (using Redgate DLM Automation: Build) that generates a nuget package and on a later step deploys to our instance of OctopusDeploy (that part is working). In OctopusDeploy, I'm using the 'Redgate - Deploy from Package' step to take that package and deploy to a server.

If the target SQL instance doesn't yet have the database, it'll fail to begin the step (as it cannot set the database/context). If my goal is to deploy all missing deltas to the target server, what step/process should I be using instead?

Comments

  • AlexYatesAlexYates Posts: 264 Rose Gold 2
    I like to stick an IF NOT EXISTS CREATE DATABASE script in source control for each of my databases (for each environment). This way I can source control the way I set up stuff like file groups and possibly security at a per database level. This is useful because some of that stuff is not included by default with the Redgate tools or people tend to filter it out. (Who has the same users on DEV and PROD?)

    Note, add this create script in a parent or sister directory to your SQL Source Control directory. I try to avoid adding my own scripts to the directory you give to Redgate because you can confuse Redgate tools which are designed to parse all the sql scripts in that directory.

    Then, I add a simple step in Octopus Deploy to run my IF NOT EXISTS CREATE DATABASE script before the "Redgate Deploy from Package" step. Then you know your DB will always exist. There is an Octopus Deploy step template to run a SQL script here:
    https://library.octopusdeploy.com/step-templates/73f89638-51d1-4fbb-b68f-b71ba9e86720/actiontemplate-sql-execute-script

    This step template asks you to type the script into the Octopus GUI but it does also accept variable substitution so you could package up your SQL scripts into a different NuGet and reference them from that variable.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • JDenmanJDenman Posts: 9 New member
    edited July 27, 2017 12:53PM
    The first nuget package I created was the database creation & schema script. So ideally I'd want that package (or those changes), as well as my recent change(s) to be deployed to the new server.

    Imagine I have version 1.0, 1.1, and 1.2 committed in my source control, and thus 3 packages in OctopusDeploy. Now I have a SQL server I want to promote these changes to. What's the best way to setup that deploy process so it gets up to date?
  • You could modify your Octopus Deploy deployment process to include a pre-deployment step that creates the database. For instance, you could create a PowerShell script that uses the Invoke-Sqlcmd cmdlet to execute a SQL script on the server. Your SQL script could do something like the following -
    IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = 'myDatabase')
    BEGIN
    	CREATE DATABASE [myDatabase]
    	--You could also create any filegroups, etc here
    END
    

    Does that help?
    Software Engineer
    Redgate Software
  • AlexYatesAlexYates Posts: 264 Rose Gold 2
    Lolz - looks like Rob C and I are on the same page. :-)

    @JDenham: Assuming you created your package from scripts maintained by SQL Source Control the package contains the desired state/model and the deployment, under the hood, will use SQL Compare to get the DB into the desired state. Hence, the NuGet packages do not contain your updates/migrations. These are worked out for you by the software.

    Hence, as long as the database exists with the correct name you should not need any old packages.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • JDenmanJDenman Posts: 9 New member
    Okay that's what I expected. So there's currently no step to actually create the database itself - that's something we'd have to create a custom step for and use before the RedGate deployment step?

    Easy enough, just figured it had been done before.
  • JDenman wrote: »
    Okay that's what I expected. So there's currently no step to actually create the database itself - that's something we'd have to create a custom step for and use before the RedGate deployment step?

    Yes, that's correct.

    Software Engineer
    Redgate Software
  • JDenmanJDenman Posts: 9 New member
    Excellent. Thanks guys!
  • yughenyughen Posts: 1 New member
    Instead of creating a new database you could just use a ready solution. Anyways, that provides more choice and you don't have to spend your time on integrating another database after that step. Excellent idea, in my opinion.
    Database management - https://www.enteros.com/.
Sign In or Register to comment.