140 likes | 160 Views
Learn about Database Projects in Visual Studio for managing database schemas, deployment, and future enhancements. Explore core DNN module deployment, programmatic deployment options, and benefits like version control and easy integration. Discover the future of database project management with NuGet packages, improved script quality, and better developer integration with DNN. Additional resources for further reading provided.
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/