140 likes | 157 Views
Visual Studio Database Projects. Erik van Ballegoij Arrow Digital Erik.vanballegoij@arrowdigital.com. Please support our valuable sponsors. Agenda. What is this about? Development Deployment Looking ahead. What is a Database Project.
E N D
Visual Studio Database Projects Erik van Ballegoij Arrow Digital Erik.vanballegoij@arrowdigital.com
Agenda • What is this about? • Development • Deployment • Looking ahead
What is a Database Project A database project is the offline representation of a database schema. This representation contains the object definitions and deployment scripts that you would need to create a separate instance of that database or to update an existing instance. By creating a database project, you can put your database under version control.
What is a Database Project Core DNN Module Deploy multiple database projects to the same target database
What is a Database Project Auth (Users, Roles, Permissions) DNN Feature (feature specific tables) Module Deploy multiple database projects to the same target database
Development • Start from existing database or start from scratch • Full intellisense • Compilation support - build fails on errors • Full support for tools like RedGate
Deployment • From Visual Studio • Using SQLPackage.exe (C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin) • Using DacServices (Microsoft.SqlServer.Dac namespace)
Programmatic Deployment – DacServices.Deploy using (DacPackagedacpac = DacPackage.Load(FilePath)) { dacServices.Deploy( package: dacpac, targetDatabaseName: targetDatabaseName, upgradeExisting: true, options: dacDeployOptions); }
Programmatic Deployment - DacDeployOptions DacDeployOptionsdacDeployOptions = new DacDeployOptions { CreateNewDatabase = false, ExcludeObjectTypes = new[] { ObjectType.Users, ObjectType.Permissions, ObjectType.StoredProcedures, ObjectType.RoleMembership, ObjectType.Aggregates, ObjectType.ExtendedProperties }, BlockOnPossibleDataLoss = true, IgnoreFileAndLogFilePath = true };
Looking Ahead • Remove {ObjectQualifier} • DNN Database project(s) – use different schemas for different areas of application • Create separate database projects – each schema should have its own database project • Publish all database projects as separate NuGet packages (.dacpac files) • Add NuGet package support to database projects • Add deployment support in DNN Installer, using DacServices • Module developers reference dacpac files they need
Looking Ahead • No more manual versioning of db scripts • (deployment tooling can generate diff scripts on the fly) • Better quality of db scripts – compile time reference and syntax checking • Easier for module developers to integrate with DNN database • Separation of concerns in Core database
Further reading • https://docs.microsoft.com/en-us/visualstudio/data-tools/creating-and-managing-databases-and-data-tier-applications-in-visual-studio • https://msdn.microsoft.com/en-us/library/aa833292(v=vs.80).aspx • https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.dacservices(v=sql.120).aspx • https://blog.prowareness.com/2015/12/deploying-database-programmatically-using-data-tier-applications-dac-2/