QBO3 Database Deployments

posted Jul 25, 2014, 10:38 AM by Eric Patrick   [ updated Jul 27, 2014, 1:26 PM ]

Background

A Visual Studio Database project 'qbo.Db' has been created in Source\qbo.3\qbo.Db. This project contains all database components required for a 'fresh' QBO 3 installation, and can be used to upgrade QBO 2 and QBO 3 systems to a current data structure.

Publication

To create a 'fresh' database:
  • Open the qbo.Db solution
  • Right-click on the Standard project, and choose Publish
  • From the Publish screen, create a connection to the SQL Server that you wish to install the database to
    • you need to pre-select an existing database; don't worry, you'll be able to choose the name of your new database before you publish
    • test your connection
  • From the Publish screen, enter the name of your database (e.g. uatclient.quandis.net)
  • Click Publish
To update an existing database, follow the instructions above, but choose an existing database instead of enter a new database name.

Notes

The Visual Studio database projects are very powerful; they allow definition of file groups, extended properties, and installation of CLR modules.

When publishing a database, the project will generate a schema from source code, and if you are updating an existing database, it will also generate a schema from the target database. Publication to an existing database is done by comparing the schemes, generating a change script, and running the change script against the target db.  If you prefer, when you publish, you can just generate the change script and inspect that prior to running it.

Creating the 'Standard' DB on TAURUS2UAT over the VPN took a little more than 7 minutes (7:12).

Snapshots

Project "snapshots" create .dacpac files that can be deployed to create new or updated existing databases.  A .datpac is the database equivalent to a web publication package (.zip file with all the web components).  When deploying a .datpac, a tool called SqlPackage.exe will compare the .datpac schema to the target database schema, build an upgrade script, and deploy.

To deploy a .dacpac directly, open a CMD or Powershell window in a folder containing SqlPackage.exe:

PS C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120> .\sqlpackage.exe
  /action:publish
  /sourcefile:E:\Source\qbo.3\qbo.Db\Standard\Snapshots\Standard_20140727_15-00-49.dacpac
  /targetServerName:minbar
  /targetdatabaseName:testdb.quandis.net
  /variables:FileDataPath=e:\data\
  /variables:FileBackupPath=e:\data\
  /variables:FileBlobPath=e:\data\
  /variables:FileHistoryPath=e:\data\
  /variables:FileLogPath=e:\data\
  /variables:FileIndexPath=e:\data\

To view prospective changes and their impact, use action:deployReport and specify an outputPath:

PS C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120> .\sqlpackage.exe 
  /action:deployReport
  /outputPath:.\qboTest.Deploy.log
  /sourcefile:E:\Source\qbo.3\qbo.Db\Standard\Snapshots\Standard_20140727_15-00-49.dacpac 
  /targetServerName:minbar 
  /targetdatabaseName:testdb.quandis.net 
  /variables:FileDataPath=e:\data\ 
  /variables:FileBackupPath=e:\data\ 
  /variables:FileBlobPath=e:\data\ 
  /variables:FileHistoryPath=e:\data\ 
  /variables:FileLogPath=e:\data\ 
  /variables:FileIndexPath=e:\data\

Items to note:
  • the Standard.sqlproj file what was used to build this .datpac includes several required project variables; each is specified with a /variables:{variable}={value} switch
  • you can specify a sql username and password; in this example, NTLM authentication was used, so no explicit credentials are required
  • carriage returns were added for readability
Comments