{"id":410,"date":"2014-04-12T11:48:27","date_gmt":"2014-04-12T11:48:27","guid":{"rendered":"http:\/\/coding.moris.org\/?p=410"},"modified":"2025-10-17T23:29:25","modified_gmt":"2025-10-17T22:29:25","slug":"how-we-use-sql-server-data-tools","status":"publish","type":"post","link":"https:\/\/priscimon.net\/coding\/2014\/04\/12\/how-we-use-sql-server-data-tools\/","title":{"rendered":"How we use SQL Server Data Tools"},"content":{"rendered":"\n<p>This post describes\u00a0our process for developing databases with\u00a0<a href=\"http:\/\/msdn.microsoft.com\/en-us\/data\/tools.aspx\">SQL Server Data Tools<\/a>\u00a0(SSDT) in Visual Studio.<\/p>\n\n\n\n<p>For it to work, the conventions below must be respected.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use the live database as the gold standard for schema objects (and data).<\/li>\n\n\n\n<li>Deploy only database projects that have been built successfully.<\/li>\n\n\n\n<li>Deploy to a database that matches the schema of the live database.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">At the beginning of a development iteration<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Restore a copy of the live database onto the development computer.<\/li>\n\n\n\n<li>Synchronise database project schema objects with the schema objects in the restored database.<\/li>\n\n\n\n<li>Remove pre-deployment and post-deployment scripts from the database project.<\/li>\n\n\n\n<li>Update the database project version number.<\/li>\n\n\n\n<li>Build the database project.<\/li>\n\n\n\n<li>If the build fails, fix the errors and rebuild.<\/li>\n\n\n\n<li>If the build completes, check in the changes.<\/li>\n<\/ol>\n\n\n\n<h4 class=\"wp-block-heading\">During a development iteration<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Make changes to script files in the database project.<\/li>\n\n\n\n<li>If the changes might result in data loss, write pre-deployment and post-deployment scripts to migrate the data.<\/li>\n\n\n\n<li>Build the database project.<\/li>\n\n\n\n<li>If the build fails, fix the errors and rebuild.<\/li>\n\n\n\n<li>If the build succeeds, publish the changes onto the database on the development computer and test.<\/li>\n<\/ol>\n\n\n\n<h4 class=\"wp-block-heading\">Interim releases to the test environment<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Restore a copy of the live database from backup.<\/li>\n\n\n\n<li>Build the database project.<\/li>\n\n\n\n<li>Publish the database project onto the test server.<\/li>\n<\/ol>\n\n\n\n<h4 class=\"wp-block-heading\">Deployment to the live environment<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Back up the live database.<\/li>\n\n\n\n<li>Build the database project.<\/li>\n\n\n\n<li>Publish the database onto the live server.<\/li>\n<\/ol>\n\n\n\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This post describes\u00a0our process for developing databases with\u00a0SQL Server Data Tools\u00a0(SSDT) in Visual Studio. For it to work, the conventions below must be respected. At the beginning of a development iteration During a development iteration Interim releases to the test environment Deployment to the live environment &nbsp;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-410","post","type-post","status-publish","format-standard","hentry","category-general"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3I4g9-6C","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/priscimon.net\/coding\/wp-json\/wp\/v2\/posts\/410","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/priscimon.net\/coding\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/priscimon.net\/coding\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/priscimon.net\/coding\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/priscimon.net\/coding\/wp-json\/wp\/v2\/comments?post=410"}],"version-history":[{"count":23,"href":"https:\/\/priscimon.net\/coding\/wp-json\/wp\/v2\/posts\/410\/revisions"}],"predecessor-version":[{"id":1973,"href":"https:\/\/priscimon.net\/coding\/wp-json\/wp\/v2\/posts\/410\/revisions\/1973"}],"wp:attachment":[{"href":"https:\/\/priscimon.net\/coding\/wp-json\/wp\/v2\/media?parent=410"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/priscimon.net\/coding\/wp-json\/wp\/v2\/categories?post=410"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/priscimon.net\/coding\/wp-json\/wp\/v2\/tags?post=410"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}