9

I am currently working on a project that needs to operate in different SQL environments. Most of my developers have SQL Server 2017. The application runs in SQL Azure, and the client has recently started running some environments using SQL Server 2016. The project is using SSDT to generate Dacpac files which works fine for development purposes, however we recently ran into a case where a developer used a system function that was introduced in 2017. The stored procedure scripts that referenced this function worked fine in our dev and Azure environments and failed in the 2016 environment.

Is there an easy way to validate a script against some arbitrary SQL Server version, without requiring us to create multiple test environments for each SQL Server version we wish to support? The method does not need to be 100% foolproof, but should catch the vast majority of potential compatibility issues.

|improve this question
  • 2
    You could set the project properties to the target version of SQL Server you plan to deploy to and that may help solve your problem. – Antoine Hernandez Nov 11 '19 at 18:09
  • We actually do change the target version during the CI builds for each environment as the Dacpac has to match the target DB. Unfortunately, the target version will raise schema issues, but it does not raise system sproc or system function issues. – Joe Brinkman Nov 12 '19 at 13:03
9

There is not a simple tool or existing script for this that I am aware of. In terms of scanning for the use of functions added in a specific version (or enabled by a specific compatibility level) something could be written using the documentation from MS though that might not catch calls via ad-hoc SQL if your stored procedure and other use that pattern for anything, and you'll need to do further research to cover changes to undocumented functions.

without requiring us to create multiple test environments for each SQL Server version we wish to support?

Would it not be acceptable to test against the minimum version you need to support, rather than testing against every version from that onwards? That would only be one extra instance you need to add to your CI/Test/other processes. If 2016sp1 is the earliest version you need to support then almost everything is available in all editions so you can use Express and not even have any licensing costs for that one instance. You might get away with that with prior versions too, if you don't use any features that were standard-/enterprise- only prior to 2016sp1.

|improve this answer
  • That currently looks like the most viable option. It is complicated because we are a consulting shop and have many different customer projects. I don't want to keep having one off servers for each different client/project. Even this one customer had some environments in SQL 2008 R2, SQL 2016 and SQL Azure. – Joe Brinkman Nov 11 '19 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.