SQL Server数据库更改工作流最佳实践 [英] SQL Server database change workflow best practices

问题描述

背景

我的群组有4个SQL Server数据库：

• 生产


• UAT


• 测试


• 开发
$ b

我在Dev环境中工作。当时间到来促进我一直在工作的对象（表，视图，函数，存储过程），我提出一个请求我的经理，谁促进测试。测试后，她向向UAT宣传的管理员提交请求。成功的用户测试后，同一个管理员会提升到生产。

问题

由于几个原因，整个过程很尴尬。 / p>

1. 每个人必须手动跟踪他们的更改。如果我更新，添加，删除我需要跟踪的任何对象，以便我的促销请求包含我做的所有。在理论上，如果我错过了测试或UAT应该抓住它，但这是不确定的，这是浪费了测试者的时间，无论如何。


2. 我做的很多变化是迭代的，在GUI中完成，这意味着没有记录我做什么变化，只有最终结果（至少就我所知）。


3. 我们处于早期阶段构建一个数据集市，所以大部分的更改，至少在计数方面，是微小的事情：改变列的数据类型，改变表的名称，因为我们把它们用于什么，调整功能
   
   
   
   
   
   
   
   

   几十年来，人们一直在做这种工作，所以我想，必须有一个更好的方法来管理这个过程。我喜欢的是，如果我可以在两个数据库之间运行一个差异，看看结构是如何不同，使用diff生成一个更改脚本，使用更改脚本作为我的促销请求。这可能吗？如果没有，有没有其他方法来组织这个过程？

   
   
   

   为了记录，我们是一个100％的微软商店，刚刚更新一切到SQL Server 2008，所以在这个包中的任何工具都是公平的游戏。 / p>
   
   

   ---

   
   
   

   我应该澄清一下，我不一定在寻找差异工具。如果这是同步我们的环境的最好的方法，那么很好，但如果有一个更好的方式，我正在寻找。

   
   
   

   一个例子做我想要的真正好在Ruby on Rails中的迁移。死的简单语法，所有的更改都有自动记录，默认情况下，确定哪些迁移需要运行几乎容易。我喜欢如果有类似的东西SQL Server。

   
   
   

   我的理想解决方案是1）容易和2）难以搞砸。 Rails迁移都是;到目前为止，我在SQL Server上所做的一切都不是。

   解决方案

   版本控制和您的数据库

   
   
   

   邪恶的根源在UI中做出改变。 SSMS是一个DBA工具，而不是开发人员。开发人员必须使用脚本对数据库模型/模式进行任何更改。对元数据进行版本控制，并且将升级脚本从每个版本N升级到版本N + 1都是经过验证可靠地工作的唯一方式。这是SQL Server本身部署以跟踪元数据更改（资源db更改）的解决方案。

   
   
   

   比较工具，如SQL比较或来自VS数据库项目的vsdbcmd 和.dbschema文件只是没有做正确版本化方法的商店的最后一个度假村。他们在简单的情况下工作，但我看到他们都严重失败在严重的部署。如果工具尝试对数据进行复制，则只是不信任某个工具对+ 5TB表进行更改...

   
   

   The Background

   My group has 4 SQL Server Databases:

   • Production
   • UAT
   • Test
   • Dev

   I work in the Dev environment. When the time comes to promote the objects I've been working on (tables, views, functions, stored procs) I make a request of my manager, who promotes to Test. After testing, she submits a request to an Admin who promotes to UAT. After successful user testing, the same Admin promotes to Production.

   The Problem

   The entire process is awkward for a few reasons.

   1. Each person must manually track their changes. If I update, add, remove any objects I need to track them so that my promotion request contains everything I've done. In theory, if I miss something testing or UAT should catch it, but this isn't certain and it's a waste of the tester's time, anyway.
   2. Lots of changes I make are iterative and done in a GUI, which means there's no record of what changes I made, only the end result (at least as far as I know).
   3. We're in the fairly early stages of building out a data mart, so the majority of the changes made, at least count-wise, are minor things: changing the data type for a column, altering the names of tables as we crystallize what they'll be used for, tweaking functions and stored procs, etc.

   The Question

   People have been doing this kind of work for decades, so I imagine there have got to be a much better way to manage the process. What I would love is if I could run a diff between two databases to see how the structure was different, use that diff to generate a change script, use that change script as my promotion request. Is this possible? If not, are there any other ways to organize this process?

   For the record, we're a 100% Microsoft shop, just now updating everything to SQL Server 2008, so any tools available in that package would be fair game.

   ---

   I should clarify I'm not necessarily looking for diff tools. If that's the best way to sync our environments then it's fine, but if there's a better way I'm looking for that.

   An example doing what I want really well are migrations in Ruby on Rails. Dead simple syntax, all changes are well documented automatically and by default, determining what migrations need to run is almost trivially easy. I'd love if there was something similar to this for SQL Server.

   My ideal solution is 1) easy and 2) hard to mess up. Rails Migrations are both; everything I've done so far on SQL Server is neither.

   解决方案

   Version Control and your Database

   The root of all things evil is making changes in the UI. SSMS is a DBA tool, not a developer one. Developers must use scripts to do any sort of changes to the database model/schema. Versioning your metadata and having upgrade script from every version N to version N+1 is the only way that is proven to work reliably. It is the solution SQL Server itself deploys to keep track of metadata changes (resource db changes).

   Comparison tools like SQL Compare or vsdbcmd and .dbschema files from VS Database projects are just last resorts for shops that fail to do a proper versioned approach. They work in simple scenarios, but I see them all fail spectacularly in serious deployments. One just does not trust a tool to do a change to +5TB table if the tools tries to copy the data...

   这篇关于SQL Server数据库更改工作流最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！