Transcript Database Projects

Database Projects
An introduction and technical overview
The Case for Database Projects
 Get db code into Source Control
 Link application code to database code
 CI (Continuous Integration) Builds
 Db developed in dev is db going to prod
 Simplified (automated) Deployment Model
 Refactoring Tools
 Code within Visual Studio
Build and Deploy (publish)
Process
 Build
 Pre-build script
 Build creates DACPAC
 Post-build script
 Deploy (publish)
 Db compare DACPAC to target database
 Generate script to make target database structurally similar to
the DACPAC
 Execute PreDeploy script
 Execute generated script
 Execute PostDeploy script
Demo
 Properties of a database project
 Database settings
 Build and deploy settings
 Navigating thru the database project
 Solution Explorer
 Development Tools
 Refactoring
 Building and deploying a DACPAC
 Data Scripts
 Unit Tests
Following slides are more
detailed…
What is a database project?
 A code based database definition in Visual Studio
 Provides a common application developer platform
 Uses Sql Server Data Tools (SSDT)

SSDT requires Visual Studio Professional Edition (VS2010+)

VS2008 doesn’t use SSDT directly

VS2010 and VS2012 require SSDT to be upgraded outside of VS

VS2013 and VS2015 notify user of changes to SSDT
 VS uses a SqlExpress (localdb) database behind the scene
Benefits of Database Projects
 Automation: Deployment of DACPACs can be automated
through SSDT’s publishing tool “SQLPackage.exe”
 DACPACs can be applied to an environment rapidly and
easily.
 Deployment of DACPACs can be rolled back when a failure
occurs. (or so I’m told!)
 Unit Tests: Unit tests can be created and executed as
part of Continuous Integration process.
 Can include data deployment
Benefits of Database Projects
 Places database code in source control
 Links database changes to application code changes
 Shelve database changes to synchronize changes with app dev
push.
 Refactoring Tools
 Rename a table/column propagates change thru all objects.
 Rename generates sp_rename instead of table drop/create
 Automates SELECT * removal
 Applies alias to appropriate columns in query
 Project oriented features more in line with traditional dev
tools: “Find All References” and “Goto Definition”.
 Builds similar to code: Schema is validated and built into a
data-tier application package (DACPAC) prior to
deployment.
Database Project Gotchas
 Multiple databases in a db solution – DON’T DO IT!!!
 Reference other databases using DACPAC references
 Replication and CDC can be tricky. Be careful!
 Forces the schema to be the same. Does not do well
with client specific code.
 Client specific data is fine, code is more difficult
 Warning if column name casing is incorrect
 Some warnings are errors
 Deferred name resolution
Build vs. Publish
 Build creates DACPAC (database model)
 Compiles (Validates) db code
 (optional) pre-build script
 (optional) post-build script
 Publish Deploys the code
 Create migration script based upon db/DACPAC differences
 Run pre-deployment script
 Run migration script
 Run post-deployment script
Database Project Build
 The database build process creates the DACPAC based
upon the build parameters
 Build parameters can be saved in publish config file
 Build/Publish Options in publish.config file
 Refactoring is your friend!
 Add to CI builds using…
 Microsoft.SqlServer.Dac.dll
 DacServices.Deploy
 SqlPackage.exe
 msbuild.exe
 Serializes the database project into .dbmdl file
Database Project Suggestions
 Integrate with TFS (Source Control) at the beginning!
 Change project settings to name objects using schema
 When importing from existing database, change the
folder structure to “object type”
 Can further breakdown by schema afterwards (manually)
 Refactor/Rename whenever possible
 Generates sp_rename instead of “happy table dance”
 <project>_refactorlog table
 refactor.xml inside DACPAC
Random Thoughts…
 All scripts are SqlCmd scripts
 Use SqlCmd variables to data specific to an environment
or client.
 Must use CREATE statements
 Database projects don’t like ALTER statements.
 Can get warnings/errors if you’re not using Dev edition
locally. (partitioning + other enterprise features)
 Add unit tests using the wizard
 Yes, a useful wizard!!!
More random stuff
 Scripts
 begin/end statements can be collapsed like C#
#region/#endregion
 :r for “included” files (SqlCmd)
 F12 go to definition
Pre- and Post- Deployment
Scripts
 Only 1 pre- and 1 post- script
 Use SqlCmd attributes to include multiple files in a script
 Disabling unique indices and foreign keys may be
needed
 Example: new NOT NULL foreign key on existing table
 Maintaining data when structure upgrades requires data
migration planning; and maybe a prebuild script.
 Identify indices and tables in production that are not in
pre-prod databases
DACPAC Deployments
 DACPAC is created during database build
 Within VS or from msbuild.exe
 If using msbuild.exe as part of CI, be sure to get latest from
TFS
 When publishing, DACPACs do the following
1. Compare DACPAC to target database
2. Generate upgrade script
3. Execute PRE-deployment script
4. Execute the generated upgrade script (from step 2.)
5. Execute POST-deployment script
SqlPackage.EXE
(1)
sqlpackage.exe option DACPAC configFile
 option
 DeployReport: creates an XML document that shows the
database changes that will be deployed
 Script: creates a SqlCmd script that can be used to deploy
changes. It can also be maintained as an audit trail.
 Publish: determines database changes and applies them to
the target database
 DACPAC
 Data tier application created using msbuild.exe
SqlPackage.EXE
(2)
sqlpackage.exe option DACPAC configFile
 configFile
 Multiple configs but one DACPAC!
 TargetDatabaseName
 TargetConnection
 GenerateSmartDefaults
 DropObjectsNotInSource
 ScriptDatabaseOptions
 more…
Key Takeaways
 Gets database code into source control
 Simplifies deployments
 Links sql code to app code
 Guarantees that what was tested in lower environments
is what gets to production
 Build creates DACPAC (database model)
 Publish/Deploy order of operations
 Creates migration script based upon db/DACPAC differences
 Run pre-deployment scripts
 Run migration script
 Run post-deployment scripts