1

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.

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.

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

DForck42
  • 19,789
  • 13
  • 59
  • 84

1 Answers1

1

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

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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • interesting. could you explain the OptionalColumns(is_distributed) part a bit more, I haven't seen this syntax before. i understand that optionalColumns is an alias – DForck42 May 02 '19 at 17:55
  • @DForck42 - that creates a derived table with a single column and single row. You can see this with `SELECT * FROM (VALUES(CAST(NULL AS BIT))) OptionalColumns(is_distributed)` – Martin Smith May 02 '19 at 17:56
  • huh, cool. i've never used derived tables like this, thank you! – DForck42 May 02 '19 at 17:57
  • 1
    @DForck42 - the falling back to a different scope if column doesn't exist I got from here https://dba.stackexchange.com/questions/66741/why-cant-i-use-a-case-statement-to-see-if-a-column-exists-and-not-select-from-i/66755#66755 – Martin Smith May 02 '19 at 17:59