将SQL Server数据库对象公开为文件系统中的文件 [英] Exposing SQL Server database objects as files in a file system

问题描述

文件系统不止一个

大多数版本控制工具在本地磁盘文件系统上运行.

大多数关系数据库系统 do 的数据库对象都存在于文件系统中，因为存在一个文本名称标识该对象，并且可以使用该名称检索或至少生成该创建脚本./p>

但这不是本地磁盘文件系统，因此对于严格在本地磁盘文件系统上运行的CVS或SVN之类的工具而言，它们是不可见的.

为了将SVN应用于数据库对象，必须将它们复制到本地磁盘文件系统中，并且必须将对本地磁盘文件系统的更改复制到数据库中.

不同的使用方式

与每个开发人员都维护一个私有工作副本的源代码不同，开发人员倾向于在网络上某个服务器上的共享数据库上工作.虽然Visual Studio为按需安装的项目本地数据库副本提供直接支持，但开发人员避开了此功能，因为没有方便可靠的合并更改的方法.

但是，一旦通过复制合并版本控制系统(例如CVS或SVN)管理了数据库结构的更改，则传播和合并几乎都是自动的(栏冲突)，并且 不再有任何理由共享数据库.

排除SCC作为选择

Microsoft SQL Management Studio支持任何实现SCC规范的版本控制.微软只列出了VSS(漏洞)，但Google透露了很多选择. 但是，SCC完全是关于锁定-双重故障.

在文件系统之间复制

整个问题现在涉及到文件系统之间的复制之一. CodePlex包含VS2005/SQL2005的实现，但不适用于VS2008/SQL2008.

在这一点上，尽管我不确定如何奖励积分，我认为已经令人满意地解决了我应该怎么做"这一基本问题.

感谢所有关注您的意见.

确实出现了一些具体的问题，主要与如何编写各种类型的模式对象有关.

• 如何按依赖关系顺序提取create和alter脚本
  • 表
  • 查看
  • 存储过程
  • 功能
  • 触发
  • 索引
  • 外键
• 如何按依赖关系顺序提取表填充脚本
• 如何有效地检测模式更改(在sys.objects上没有触发器的情况下，有必要进行轮询；这最好既快速又便宜)

检测更改

引起我注意的是，可以使用策略将操作绑定到架构更改中.仍然存在依存关系排序和如何编写表创建语句的问题

解决方案

我们使用Red Gate将当前模式与SVN中存储的脚本文件进行基线，版本控制等进行比较

但是，我们的主要参考实际上是生产的还原副本.这是我们的基准，应该对应于SVN.将主脚本提交到SVN是部署过程的一部分，Red Gate会这样做:它仅更改已更改对象的文件.

我们进一步分离了工作脚本和发布脚本(仅更改)，因此我们在SVN中始终有一个主数据库和一个基准.不过，我们只使用脚本进行开发.

数据库源代码管理是好东西，但是由于SQL Server对象的本质，它很难实现:在某些表中一行或3 ...

There's more than one file system

Most version control tools operate on the local disk file system.

Database objects for most relational database systems do exist in a file system, inasmuch as there is a textual name identifying the object and the creation script can be retrieved or at least generated using this name.

But it isn't the local disk file system, and as a result they are invisible to tools like CVS or SVN, which operate strictly on the local disk file system.

In order for SVN to be applied to database objects, they must be replicated into the local disk file system, and changes to the local disk file system must be replicated to the database.

Different mode of use

Unlike source code, of which each developer maintains a private working copy, developers tend to work on a shared database on a server somewhere on the network. While Visual Studio provides direct support for mount-on-demand project-local copies of the database, developers have shunned this facility because there is no convenient and reliable way to merge changes.

However, once changes to the database structure are managed by a copy-merge version control system like CVS or SVN, propagation and merging will be mostly automatic (bar conflicts) and there is no longer any reason to share a database.

Ruling out SCC as an option

Microsoft SQL Management Studio supports version control for anything that implements the SCC spec. Microsoft only lists VSS (blech) but Google reveals a plethora of options. However, SCC is all about locking - double blech.

Replicating between file systems

The whole question now devolves to one of replicating between file systems. CodePlex contains an implementation for VS2005/SQL2005 but it doesn't work with VS2008/SQL2008.

At this point I think the underpinning question of "how should I go about this" has been satisfactorily addressed, although I'm not sure how to award points.

Thank you to all concerned for your input.

Some concrete questions do arise, mostly to do with how to script out various types of schema object.

• How to extract create and alter scripts in dependency order for
  • table
  • view
  • stored procedure
  • function
  • trigger
  • index
  • foreign key
• How to extract table population scripts in dependency order
• How to efficiently detect changes to the schema (in the absence of triggers on sys.objects it will be necessary to poll; this had better be fast and cheap)

Detecting changes

It has come to my attention that it is possible to bind actions to changes in schema using policies. There remain the questions of dependency ordering and of how to script a table creation statement

解决方案

We use Red Gate to compare current schema against the scripted files stored in SVN for baselines, versioning etc

However, our master reference is actually a restored copy of production. This is our baseline and should correspond to SVN. It's part of deployment process to commit the master scripts to SVN, which Red Gate does usefully: it only changing the files for changed objects.

We further separate our working scripts and release scripts (changes only), so we always have a master DB and a baseline in SVN. We only use scripts for development though.

Database source control is good stuff, but it's challenging to implement because of the nature of what a SQL Server object is: a row or 3 in some tables...

这篇关于将SQL Server数据库对象公开为文件系统中的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！