VisualStudio SQL 数据库项目 sqlcmdvars TFS 部署覆盖自定义文件路径? [英] VisualStudio SQL database project sqlcmdvars TFS deployment override custom File path?

问题描述

我在 VisualStudio 2010 中有一个 SQL 数据库项目，使用 TFS 2010 进行源代码控制，其中有几个分支，每个分支都根据配置文件部署到不同的服务器.

I have an SQL database project in VisualStudio 2010 in source control with TFS 2010 with a few branches that each deploy to different servers depending on the configuration profile.

使用 sqlcmdvars 文件，在每个配置文件的文件名(Debug.sqlcmdvars 等)中附加配置文件，让我可以通过使用保留名称 Path1 和 Path2 为特定部署环境指定唯一的文件路径mdf 文件和日志文件.

Using a sqlcmdvars file with the configuration profile appended in the filename (Debug.sqlcmdvars, etc..) for each configuration profile lets me specify the unique file path for a particular deployment environment by use of the reserved names Path1 and Path2 for the mdf file and the log file.

<?xml version="1.0" encoding="utf-8"?>
<SqlCommandVariables xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars">
  <Version>1.0</Version>
  <Properties>
    <Property>
      <PropertyName>Path1</PropertyName>
      <PropertyValue>C:\SQLSERVER LOG\$(DatabaseName)\</PropertyValue>
    </Property>
    <Property>
      <PropertyName>Path2</PropertyName>
      <PropertyValue>C:\SQLSERVER DATA\$(DatabaseName)\</PropertyValue>
    </Property>
  </Properties>
</SqlCommandVariables>

现在我正在尝试添加具有关联 FileStream FileGroup 的自定义 FileStream 文件

Now I'm trying to add a custom FileStream file with an associated FileStream FileGroup

我在 sqlcmdvars 文件中添加了一个额外的条目:

I have added an additional entry in sqlcmdvars files:

<Property>
  <PropertyName>PathBlobStream</PropertyName>
  <PropertyValue>C:\SQLSERVER DATA\$(DatabaseName)\BlobStream\</PropertyValue>
</Property>

但我不确定如何告诉数据库在 SchemaObjects\Database Level Objects\Storage\BlobStore.sqlfile.sql 声明的内容上使用它:

But am not sure how to tell the database to use this over what the SchemaObjects\Database Level Objects\Storage\BlobStore.sqlfile.sql has declared:

ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [BlobStore], FILENAME = 'C:\SQLSERVER DATA\####\BlobStream') TO FILEGROUP [BlobStreamFileGroup];

如何使用 sqlcmdvars 中的新条目来覆盖新 FileStream 文件的路径?

How do you use the new entry in the sqlcmdvars to override the path for the new FileStream file?

推荐答案

我发现最好的方法是在 dbproj 文件中添加额外的条目，以根据配置文件使用不同的架构文件:

The best way I found to do this was add additional entries in the dbproj file to use a different schema file depending on the configuration profile:

  <ItemGroup Condition=" '$(Configuration)' == 'Debug' ">
    <Build Include="Schema Objects\Database Level Objects\Storage\Files\BlobStore.sqlfile.sql">
      <SubType>Code</SubType>
    </Build>
  </ItemGroup>
  <ItemGroup Condition=" '$(Configuration)' == 'Internal' ">
    <Build Include="Schema Objects\Database Level Objects\Storage\Files\BlobStore.sqlfile.Internal.sql">
      <SubType>Code</SubType>
    </Build>
  </ItemGroup>

关键是复制/粘贴原始架构文件并在文件名中附加配置文件名称.这将使用原始文件名保留调试变体，以便将来的任何模式比较都不会注意到其他变体.您希望在源代码管理中包含其他架构文件，但如果遵循上述条目，它们将不会出现在项目中.我验证了 MSBuild 正确处理了这个问题.我交叉手指，TFS 也会这样做.

The key was copy/pasting the original schema file and appending the configuration profile name in the file name. This keeps the debug variation with the original file name so that any future schema comparisons will be oblivious to the additional variations. You want to include the additional schema files in source control, but if following suite to the entries above, they won't show up in the project. I verified MSBuild handles this correctly. I'm crossing my fingers that TFS will do the same.

这篇关于VisualStudio SQL 数据库项目 sqlcmdvars TFS 部署覆盖自定义文件路径?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！