SQL Server Environments - How far back do you test?
-
Regarding remote customers using MS SQL Server in-house, how far back do you go version-wise for compliancy in your code? I've just spent the morning chasing down query issues with a client...one of those 'well, it works fine here' type of issues. I was finally able to replicate the issue by setting my test database compatibility setting to 2K! :omg: :wtf: (customer's sql environment is 2008r2) Yeah, I fixed it, but also left a message for their dba to discuss moving to a new server and/or changing the compatibility options to max setting to avoid further issues. It's a reminder that I've been doing this for 20 years and this customer was one of our first. What a long, strange trip it's been. :laugh: btw, the problems involved 'ambiguous' fields in the Order By clause of a union query...something that seems to have been fixed with 2005.
"Go forth into the source" - Neal Morse
-
Regarding remote customers using MS SQL Server in-house, how far back do you go version-wise for compliancy in your code? I've just spent the morning chasing down query issues with a client...one of those 'well, it works fine here' type of issues. I was finally able to replicate the issue by setting my test database compatibility setting to 2K! :omg: :wtf: (customer's sql environment is 2008r2) Yeah, I fixed it, but also left a message for their dba to discuss moving to a new server and/or changing the compatibility options to max setting to avoid further issues. It's a reminder that I've been doing this for 20 years and this customer was one of our first. What a long, strange trip it's been. :laugh: btw, the problems involved 'ambiguous' fields in the Order By clause of a union query...something that seems to have been fixed with 2005.
"Go forth into the source" - Neal Morse
kmoorevs wrote:
btw, the problems involved 'ambiguous' fields in the Order By clause of a union query...something that seems to have been fixed with 2005.
well, there are people that program with bugs interesting features in mind (- moreso in the past.) ... jus sayin. ok, gulty once long ago... with the way mysql (vs almost all other dbs) handle re-referenced fields in update queries - and btw: the "standard" is unclear.
Message Signature (Click to edit ->)
-
Regarding remote customers using MS SQL Server in-house, how far back do you go version-wise for compliancy in your code? I've just spent the morning chasing down query issues with a client...one of those 'well, it works fine here' type of issues. I was finally able to replicate the issue by setting my test database compatibility setting to 2K! :omg: :wtf: (customer's sql environment is 2008r2) Yeah, I fixed it, but also left a message for their dba to discuss moving to a new server and/or changing the compatibility options to max setting to avoid further issues. It's a reminder that I've been doing this for 20 years and this customer was one of our first. What a long, strange trip it's been. :laugh: btw, the problems involved 'ambiguous' fields in the Order By clause of a union query...something that seems to have been fixed with 2005.
"Go forth into the source" - Neal Morse
The fix is really simple. Simply specify that your app requires SQL Server 20xx or higher. Problem solved.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
The fix is really simple. Simply specify that your app requires SQL Server 20xx or higher. Problem solved.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013John Simmons / outlaw programmer wrote:
Simply specify that your app requires SQL Server 20xx or higher. Problem solved.
I can document (and have) all I want, but the end users don't read that stuff, nor do they care. Any time the product fails, it's my fault. Given the choice between just fixing it myself or sending a request and waiting for someone else to do their job, I've always found it easier to choose the former. It was hard to find, but really easy to fix. Besides, in this case, there may be other longtime customers whose part-time/faux dbas weren't diligent when migrating off older servers. With that in mind, I've put in the extra effort to warn the end users (unobtrusively or course) (and us) when that setting is below the supported setting and how to resolve it. There's always room for improvement. :)
"Go forth into the source" - Neal Morse
-
Regarding remote customers using MS SQL Server in-house, how far back do you go version-wise for compliancy in your code? I've just spent the morning chasing down query issues with a client...one of those 'well, it works fine here' type of issues. I was finally able to replicate the issue by setting my test database compatibility setting to 2K! :omg: :wtf: (customer's sql environment is 2008r2) Yeah, I fixed it, but also left a message for their dba to discuss moving to a new server and/or changing the compatibility options to max setting to avoid further issues. It's a reminder that I've been doing this for 20 years and this customer was one of our first. What a long, strange trip it's been. :laugh: btw, the problems involved 'ambiguous' fields in the Order By clause of a union query...something that seems to have been fixed with 2005.
"Go forth into the source" - Neal Morse
SQL92 ought to be enough for anybody.
-
Regarding remote customers using MS SQL Server in-house, how far back do you go version-wise for compliancy in your code? I've just spent the morning chasing down query issues with a client...one of those 'well, it works fine here' type of issues. I was finally able to replicate the issue by setting my test database compatibility setting to 2K! :omg: :wtf: (customer's sql environment is 2008r2) Yeah, I fixed it, but also left a message for their dba to discuss moving to a new server and/or changing the compatibility options to max setting to avoid further issues. It's a reminder that I've been doing this for 20 years and this customer was one of our first. What a long, strange trip it's been. :laugh: btw, the problems involved 'ambiguous' fields in the Order By clause of a union query...something that seems to have been fixed with 2005.
"Go forth into the source" - Neal Morse
Upto the version that it was developed on, or the "current" last supported version. Not indefinetly :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
SQL92 ought to be enough for anybody.
-
Upto the version that it was developed on, or the "current" last supported version. Not indefinetly :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Regarding remote customers using MS SQL Server in-house, how far back do you go version-wise for compliancy in your code? I've just spent the morning chasing down query issues with a client...one of those 'well, it works fine here' type of issues. I was finally able to replicate the issue by setting my test database compatibility setting to 2K! :omg: :wtf: (customer's sql environment is 2008r2) Yeah, I fixed it, but also left a message for their dba to discuss moving to a new server and/or changing the compatibility options to max setting to avoid further issues. It's a reminder that I've been doing this for 20 years and this customer was one of our first. What a long, strange trip it's been. :laugh: btw, the problems involved 'ambiguous' fields in the Order By clause of a union query...something that seems to have been fixed with 2005.
"Go forth into the source" - Neal Morse
Why not adhere to Microsoft's support lifecycle for SQL Server, encouraging your customers to stay secure by running a version that's still supported and receiving security updates? In that case, the oldest version that should be allowed is SQL Server 2016 Service Pack 1, and even then only until extended support ends in July of this year. Why support a version of your own product that uses underlying technologies no longer supported by the vendor and with (possibly, depending on version) known security vulnerabilities? I'm not a developer, I ask as a MSFT field engineer supporting other technologies what the challenges are for you to consider taking such a position with your customers. I'm fascinated when I run across conversations like this and wish to learn the thinking behind it in the hopes I can better support the developers I work with. Thanks! Jon
-
Regarding remote customers using MS SQL Server in-house, how far back do you go version-wise for compliancy in your code? I've just spent the morning chasing down query issues with a client...one of those 'well, it works fine here' type of issues. I was finally able to replicate the issue by setting my test database compatibility setting to 2K! :omg: :wtf: (customer's sql environment is 2008r2) Yeah, I fixed it, but also left a message for their dba to discuss moving to a new server and/or changing the compatibility options to max setting to avoid further issues. It's a reminder that I've been doing this for 20 years and this customer was one of our first. What a long, strange trip it's been. :laugh: btw, the problems involved 'ambiguous' fields in the Order By clause of a union query...something that seems to have been fixed with 2005.
"Go forth into the source" - Neal Morse
We lay out a upgrade schedule our clients must adhere to. Our code verifies server versions and will only run it the version is at a certain level or better. The client can opt not to upgrade to the current level our our software to continue using an old version but at some point they'll lose support for our code if it gets too far out of date. It's the only way we found to make things reasonable. We need to develop against the current (and up coming versions) of Windows and the Database, we can't also maintain 10 versions back, things have changed too much over that period. We allow support of our current code and one major version back. When they upgrade our code it forces the upgrade on its dependencies.
-
Why not adhere to Microsoft's support lifecycle for SQL Server, encouraging your customers to stay secure by running a version that's still supported and receiving security updates? In that case, the oldest version that should be allowed is SQL Server 2016 Service Pack 1, and even then only until extended support ends in July of this year. Why support a version of your own product that uses underlying technologies no longer supported by the vendor and with (possibly, depending on version) known security vulnerabilities? I'm not a developer, I ask as a MSFT field engineer supporting other technologies what the challenges are for you to consider taking such a position with your customers. I'm fascinated when I run across conversations like this and wish to learn the thinking behind it in the hopes I can better support the developers I work with. Thanks! Jon
Hi Jon and thanks for the reply. It may surprise you to learn that many businesses are still using sql server 2008r2, which is the minimum that we officially support. We have nothing to do with a client's upgrade schedule, but basically have always just used whatever they had available, and back in the beginning that was sql 2000/msde. IME, my customer's IT departments value stability and familiarity over support lifecycle schedules. They've probably never needed to open a support ticket for sql server anyway, and not many are concerned with security issues in older versions of a product that still gets the job done. The history of this particular application may be of interest in this case. Originally, this application was developed to use an Access 2K database. A few years later, the decision was made to allow customer choice of either Access or SQL Server based on customer needs. Almost 20 years later, and 95% of the same queries work under both environments. IMO, that's a testament to the longevity of SQL standards and backward compatibility. I really can't recall when a newer version of SQL Server broke anything. :) Personally, I favor sql server 2014 for development and for in-house or self-hosted applications. I have 2016 installed and available, but despise that version's SSMS as half the time it flakes out when starting. 2017 seems better but I only use it when importing from Azure.
"Go forth into the source" - Neal Morse
-
We lay out a upgrade schedule our clients must adhere to. Our code verifies server versions and will only run it the version is at a certain level or better. The client can opt not to upgrade to the current level our our software to continue using an old version but at some point they'll lose support for our code if it gets too far out of date. It's the only way we found to make things reasonable. We need to develop against the current (and up coming versions) of Windows and the Database, we can't also maintain 10 versions back, things have changed too much over that period. We allow support of our current code and one major version back. When they upgrade our code it forces the upgrade on its dependencies.
jeb1217 wrote:
Our code verifies server versions
Yep, this is something I implemented after this incident, but with a nice warning message box that they can screen capture and send to the appropriate people. Previously, we were only checking server versions and not the database compatibility option. It's hard to think of everything the first time! :laugh:
"Go forth into the source" - Neal Morse
-
Hi Jon and thanks for the reply. It may surprise you to learn that many businesses are still using sql server 2008r2, which is the minimum that we officially support. We have nothing to do with a client's upgrade schedule, but basically have always just used whatever they had available, and back in the beginning that was sql 2000/msde. IME, my customer's IT departments value stability and familiarity over support lifecycle schedules. They've probably never needed to open a support ticket for sql server anyway, and not many are concerned with security issues in older versions of a product that still gets the job done. The history of this particular application may be of interest in this case. Originally, this application was developed to use an Access 2K database. A few years later, the decision was made to allow customer choice of either Access or SQL Server based on customer needs. Almost 20 years later, and 95% of the same queries work under both environments. IMO, that's a testament to the longevity of SQL standards and backward compatibility. I really can't recall when a newer version of SQL Server broke anything. :) Personally, I favor sql server 2014 for development and for in-house or self-hosted applications. I have 2016 installed and available, but despise that version's SSMS as half the time it flakes out when starting. 2017 seems better but I only use it when importing from Azure.
"Go forth into the source" - Neal Morse
kmoorevs wrote:
They've probably never needed to open a support ticket for sql server anyway, and not many are concerned with security issues in older versions of a product that still gets the job done.
That reasoning is precisely why I have a job. Running old, out-of-support software puts an organization at a high level of risk, period, end of story. Especially when we're talking about something like SQL with a laundry list of vulnerabilities over the years ... hopefully they're at least at a patch level that supports TLS 1.2. I'm guessing their endpoint security is also incredibly weak, which increases the risk of keeping old server software on the network. I can't tell you how many times I've walked into an organization recovering from a horrific intrusion because someone felt it wasn't worth the money or effort to get things to a proper level. "Support" means more than just "a need to open a support ticket", and dealing with the aftermath of an attack is almost always more expensive -- if not in real dollars, than in company reputation. I'm not so lost in la-la land that I don't appreciate what I'm saying just doesn't compute for a lot of orgs. I get it. Doesn't mean it doesn't still amaze me beyond belief at times. ;P
-
Regarding remote customers using MS SQL Server in-house, how far back do you go version-wise for compliancy in your code? I've just spent the morning chasing down query issues with a client...one of those 'well, it works fine here' type of issues. I was finally able to replicate the issue by setting my test database compatibility setting to 2K! :omg: :wtf: (customer's sql environment is 2008r2) Yeah, I fixed it, but also left a message for their dba to discuss moving to a new server and/or changing the compatibility options to max setting to avoid further issues. It's a reminder that I've been doing this for 20 years and this customer was one of our first. What a long, strange trip it's been. :laugh: btw, the problems involved 'ambiguous' fields in the Order By clause of a union query...something that seems to have been fixed with 2005.
"Go forth into the source" - Neal Morse
This is the code I found ages ago to check the compatability level for the databases (good old 80 compatability messed a few things up for some reports I was doing) and if your using stored procedures for a login/access check this could be part of the result where your front end checks the data and goes 100 is the minimum compatability required for the successful running of this application, so if it breaks it's your fault (technically 100 is SQL 2008 but at least it's not SQL 2000). Unfortunately this doesn't seem to be widely know (or checked for) since where I work we use a few different programs that I'm involved in and once I had set the compatability level to 80 on the wrong database (on my machine for testing) otherwise another program would behave really weirdly. The front end software suddenly broke the next day but nothing was coming up that there was anything incorrect with the system and configuration, which took about 30 minutes to track down all the stuff I had changed in the last few days.
SELECT compatibility_level
FROM sys.databases
WHERE name = DB_NAME() -
This is the code I found ages ago to check the compatability level for the databases (good old 80 compatability messed a few things up for some reports I was doing) and if your using stored procedures for a login/access check this could be part of the result where your front end checks the data and goes 100 is the minimum compatability required for the successful running of this application, so if it breaks it's your fault (technically 100 is SQL 2008 but at least it's not SQL 2000). Unfortunately this doesn't seem to be widely know (or checked for) since where I work we use a few different programs that I'm involved in and once I had set the compatability level to 80 on the wrong database (on my machine for testing) otherwise another program would behave really weirdly. The front end software suddenly broke the next day but nothing was coming up that there was anything incorrect with the system and configuration, which took about 30 minutes to track down all the stuff I had changed in the last few days.
SELECT compatibility_level
FROM sys.databases
WHERE name = DB_NAME()Thanks Allan, I implemented that same code last week to identify those users stuck on 80 compatibility. I suppose it's one of the hazards of having an application that has been in use for almost 18 years now, first rolled out when sql 2000 was in vogue. My guess is that through the years, as those customers migrated to newer sql server versions, the dbas simply forgot to check that comp. setting. Actually, this issue will resolve itself in the near future as more orgs migrate from sql 2008 as the 80 and 90 settings aren't even available under newer versions.
"Go forth into the source" - Neal Morse