处理不同 SQL Server 版本的不同对象 [英] Handling objects being different for different SQL Server Versions

问题描述

我在 Visual Studio 中创建了一个新的数据库项目，并将我预先存在的对象添加到其中.这是一个数据库，我们将使用至少两个不同版本的 sql server 部署到多个不同的服务器.

I have created a new database project in Visual Studio and have been adding my pre-existing objects to it. This is a database that we'll be deploying to multiple different servers with at least two different versions of sql server.

如果服务器是 2016 年，我有一个查看可用性组的视图并拉出 Is_Distributed 列.我需要在这个项目中找到一种方法来基本上保存两个版本，2014 年和 2016 年，并且能够部署正确的版本取决于我要部署到的服务器.

I have a view that looks at availability groups and pulls the Is_Distributed column if the server is 2016. I need a way in this project to essentially save both versions, the 2014 and 2016, and be able to potentially deploy the correct version depending on what server i'm deploying to.

是否可以在 Visual Studios 的数据库项目中执行此操作?

Is this possible to do in a Database Project in Visual Studios?

推荐答案

一种解决方案是创建一个适用于其中任一者的兼容性视图，然后引用该视图而不是 sys.availability_groups.

One solution would be to create a compatibility view that works for either then reference that instead of sys.availability_groups.

在下面的 2016 年 is_distributed 将从 DMV 中提取，但在 2014 年由于 DMV 中没有这样的列，它将从 OptionalColumns 中提取外部作用域并改为 NULL.

In the below on 2016 the is_distributed will be pulled from the DMV but on 2014 as there is no such column in the DMV it will be pulled from OptionalColumns in the outer scope and be NULL instead.

CREATE VIEW availability_groups_compat
AS
  SELECT ca.*
  FROM   (VALUES(CAST(NULL AS BIT))) OptionalColumns(is_distributed)
         CROSS APPLY (SELECT group_id,
                             name,
                             resource_id,
                             resource_group_id,
                             failure_condition_level,
                             health_check_timeout,
                             automated_backup_preference,
                             automated_backup_preference_desc,
                             version,
                             basic_features,
                             dtc_support,
                             db_failover,
                             is_distributed,
                             cluster_type,
                             cluster_type_desc,
                             required_synchronized_secondaries_to_commit,
                             sequence_number
                      FROM   sys.availability_groups) ca 

这篇关于处理不同 SQL Server 版本的不同对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！