2

I'm in the middle of a DevOps project automating the deployment of apps of varying code stacks and DB's using a variety of DevOps Tools. I am seeking advice on automating an MS SQL DB deployment and subsequent updates.

Current approach is to build DB VM via a VM blueprint using Terraform or Cloudify. I currently have a VM with MSSQLServer configured and can script sql files against it to instantiate my DB. However once i get to scripting the raw data, size-wise,I often run out of memory. I know, manually i can increase memory etc in properties or use a sql file in CLI. I also know of things like DTS or BCP but what I think I am looking for is advice on best methods in automatically deploying an MS SQL DB via a DevOps pipeline. The intention is to use jenkins and deploy scripts via a power-shell.

Initial thoughts were to force DB owners to provide a bak/mdf file and subsequent updates are scripted. But I'd really appreciate council on best approaches in business especially if you have done this on large scale e.g. 1000's of apps.

If it helps the nature of my pipeline the approach I take needs to work for both small DB's 100MB, and larger ones - say up to 1-2TB.

Nick Gleed
  • 105
  • 1
  • 1
  • 6
  • 1
    Use a database project and build a DACPAC, that's what they're designed to do. For small amounts of data, you can use a BACPAC with the data included. For enormous amounts of data, `BACKUP`/`RESTORE` is absolutely your fastest bet (and really, how often do you deploy 2 TB databases?!) but if you value consistency over performance, the next logical approach is to `bcp` all data to native format and `bcp` it back in after deploying the DACPAC. In all cases, do not expect a free lunch when it comes to upgrades that require data migration -- that will always require some careful scripting. – Jeroen Mostert Dec 22 '16 at 11:26

1 Answers1

0

Another approach (as of early 2018) is to use SQL Server database cloning, which restores a full byte copy of a set of databases into a Windows Virtual Hard Drive (VHD), which the supports delivery of clones (differencing disks) with mountable replicas. Clones can be mounted t conventional SQL Server instances or containers, and you can check out cloning support from Red Gate and Windocks.

paul stanton
  • 936
  • 7
  • 7