SQL Server 2005 end of life is coming, run to the hills... Microsoft has reminded folk selling and managing IT that another support expiry deadline is looming — SQL Server 2005 goes end of life in roughly twelve months. As of 12 April 2016, Microsoft will no longer provide updates or any form of support unless a business is willing to pay, and those types of arrangements are never …
This post has been deleted by its author
Aye, and other than the security boogyman, I'm unsure what the benefit is for the average SQL user of upgrading. 14x faster? But what if SQL 2005 is already rediculous overkill? Sometimes it's used not because it's the most sensible DB for the use case, but because the developer didn't know how to code for anything else.
All mine run on Server 2008. When the database server runs on Server 2003 and does nothing but run SQL it's real easy to move it from 2003 to 2008 (or newer, if you have licenses).
It's a hell of a lot harder to move from SQL 2005 to SQL 2012. Experience says about 25% of your applications will just flat out stop working. And SQL 2014 is such a dramatic change from SQL 2005 that you can bet most of your applications are going to give up the ghost, unless the devs have been all over it.
Now, in the real world a lot of use use applications where the devs are emphatically not "all over it". Hell, I still have to babysit an application that uses frakking btrieve. That's like bashing two rocks together to make fire. Underwater. While being boiled alive.
Now, SQL 2005 --> SQL 2008 R2 should work for almost everyone and every application, assuming you have licenses.
If you need to go back to your developer and ask them to port the DB, don't get them to port it to Microsoft's latest and greatest. Just get them to port to Postgres. Later this year GPU acceleration for Postgres comes out. From experience, it's pretty fantastic. What's more the licensing costs are a lot more bearable.
If you don't think that licensing can be a bit of a pig, go take a look at the cost of two SQL 2014 enterprise 4 core licenses. (To allow for replication between two 4 core servers.) Tell me your average SMB will afford that.
Hell, for that kind of money, you can probably get your dev to port to Postgres and never worry about the licensing issues again.
Is that proper advice for the enterprise? No. But enterprises are probably not facing the same SQL 2005 issues as SMBs, and it's SMBs that are most likely still clinging to their old databases.
"Move away from SQL 2005" is not a simple, straightforward item with clear cut, universally applicable solutions, or even reasons why companies are facing the problem. It's a tangled mess of a thing and in a lot of ways it far - far - more difficult and problematic in today's datacenters than a "simple" operating system upgrade.
@Trevor_Pott - "Just get them to port to Postgres."
I listened to a podcast interview with one of the principle developers of Postgres a couple of years ago, and he said that the largest number of users porting software from another database to Postgres came from MS SQL Server. This came as a surprise to the interviewer, as Postgres is generally considered to be more similar to Oracle than to MS SQL Server. However, that was their experience.
I take this as meaning that the differences between the details of how different databases work isn't that significant an issue when it comes to porting effort in many cases. I suspect this is because a very large proportion of applications don't really demand that much out of their database and don't use any of the more esoteric features. That is probably especially true for the SMB market that you mention.
Hi Trevor,
From a DB-Migration point-of-view, restoring a DB from SQL Server 2005 on a 2012 server is not at all a painful process. It's what surrounds it is where the pain starts: SSIS packages and the like. What tripped us up was the .NET application that had been written for and ran on Windows Server 2003. That wouldn't run on Windows Server 2013 and the migration was cancelled.
I will echo the sentiment about overkill — if you have a small DB (under 10GB), then the Express Edition can be made to work. It only supports one processor (possibly only one core — I'd have to check that up) and has no SQL Server Agent, but the Scheduler in Windows can be used to run sqlcmd. And if your DB is not that much over 10GB, then a careful refactoring of datatypes (DATE instead of DATETIME for dates, VARCHAR instead of NVARCHAR when you don't need Unicode, SMALLINT instead of INT when your field only has a couple of thousand varying entries etc) and a regular archiving DB policy, so that seldomly-used, older data can be offloaded to the archive DB and thus bringing the main DB down under 10GB.
@deadlockvictim
If you expect an SMB to "refactor data types" when migrating a database, you're completely insane.
Migrating to Server 2012 can indeed be a pain in the ASCII because the servers hve changed enough that 2012 doesn't support everything in exactly the same way that 2005 does. So there absolutely, 100% are databases and applications that, when moved to Server 2012, don't work without the developers changing how the app talks to the database.
That's a huge problem when you developer has not chosen to do so, is out of business, or is charging you a year's turnover for the "new version" that works with the new database.
Did eventually Postgres support LOBs in a decent way? It is usually a real showstopper for any database relying on them for unstructured data (and needing them fully inder transaction control). I also found hard to believe that a properly coded database application running on MS SQL 2005 will break on 2012/2014. You also need to understand what will break moving to a different engine and needs to be rewritten or even re-implemented in a different way.
Also beware when migrating from MS SQL Server to Postgres, Postgres follows very much the Oracle model, and that it means it works in some different ways from SQL Server - you may find some nasty surprises if you're not fully aware of the differences.
GPU acceleration is OK if your DB workload is CPU bound, it will help very little if it's I/O bound or memory bound.
Also, switching to Postgres from the developer viewpoint means 1) spend time to learn how the new db really works, its quirks, and its language for triggers/sp, ecc. 2) learn to use the new tools (and SQL Server has some great tools available) 3) Less integration with your development environment (probably VS) 4) Less integration with your .NET frameworks (if you're using them)
DB and system administrators needs to re-learn deployment, administration, testing, tuning, and backup techniques.
Licensese costs may not be the biggest ones... and you have always the choice of licensing per core or per CAL depending on how large is your SMB and how many users access the datatase. If you're using SQL Enterprise, you're hardly an SMB...
"I also found hard to believe that a properly coded database application running on MS SQL 2005 will break on 2012/2014"
Then you don't actually know much about the changes, especially in 2014. What was a perfectly okay application/database in 2005 will not necessarily work in the newer versions, especially 2014.
And then you go assuming things like "properly coded". Who defines proper? You? Or is "proper" simply "anything that ports seamlessly"? And what about "improper" applications/databases? You just say to folks "oh, sorry, you're fucked, too bad, should have been able to see the future, enjoy being out of business because you can't afford things"?
It's easy to simply write off individuals and companies you don't know with a dismissive wave and a haughty sense of superiority, but way down there past the end of your long nose there are thousands - if not millions - of organizations using applications with databases that absolutely will not migrate smoothly.
Sorry mate, but I've been doing these migrations now for three years, and you just flat out don't know what you're talking about.
I'm sure many companies are running many instances of MSSQL Server 2005, but are there many companies running a business-critical instance of MSSQL Server 2005?
For those installs which do require high-availability, Standard Edition supports a 2-node Cluster. So the comparison with Enterprise Edition licensing (which I agree is really expensive, unless compared to Oracle) is a bit unfair.
Also worth considering that "The PostgreSQL project aims to fully support a major release for five years". Prefer to upgrade the application every 5 years with PostgreSQL, or every 10 years with Microsoft?
"are there many companies running a business-critical instance of MSSQL Server 2005"
107 out of 135 on my list, at the moment.
"For those installs which do require high-availability, Standard Edition supports a 2-node Cluster. So the comparison with Enterprise Edition licensing (which I agree is really expensive, unless compared to Oracle) is a bit unfair."
You'd be surprised how many of the small oil and gas companies I know have run up against the need to be using enterprise. A couple of law firms too.
" Prefer to upgrade the application every 5 years with PostgreSQL, or every 10 years with Microsoft?"
Honestly, having done the math, I believe you pay less to do it the PostgreSQL way. Sure, you migrate more regularly (not a bad thing in and of itself) but you do so by spending yoru money keeping your application developer afoat instead of adding $0.00000000000000000000001 to the shareholder dividend for Microsoft.
That developer relationship will be vital to helping your company grow. Microsoft will probably try to kill your company so it can take over your entire sector.
I'm sure many companies are running many instances of MSSQL Server 2005, but are there many companies running a business-critical instance of MSSQL Server 2005?
For those installs which do require high-availability
I suppose if you work in Big Data, it is easy to lose sight of the fact that high availability isn't the only criterion for business critical.
An extreme case of just this sort of data migration mess is a former employer. They bought a COTS badge printer that came with software that used an MSDE to store the data. This data in turn was exported through a complicated home brew of transformations into an internal employee directory. It also constituted the primary HR database for employees. Oh, and it was purchased when Windows 95SE was still the standard desktop. When we tried to deploy the app on a Windows 2000 machine, it broke. The original printer plus software setup cost around $3K. The replacement for the software alone cost $5K. So the best we could manage was to upgrade it to Windows 98SE and pray we had enough parts to keep it running as they failed. That machine was still running when I was RIFFed and they were debating whether it was time to move the desktops to Windows 7.
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2024
