You guys still use SQL Server 2000? Really?

Yes, really. So what?

Several times in the last week I've heard of people denigrating anyone who is still running SQL Server 2000 (paraphrasing):

How can they possibly still be running on a version that old? Why on Earth haven't they upgraded? *Everyone's* on something more recent. 2000 is old and sucky, so you guys must be too.

And the like.

To me this attitude demonstrates a naiveté about the industry and the financial, regulatory, and practical realities that many SQL Server users face.

Let me give you three examples:

  • A client of ours builds software that runs casinos. If you run a casino in the state of Washington, you have no choice except to run SQL Server 2000 as that's the only version of SQL Server that the gaming board has certified.
  • A level-1 trauma center whose internal pharmacies run pick-n-place robots that are controlled from SQL Server 2000. Replacing them would cost $millions and would introduce dangerous risk into a very stable pharmacy system.
  • A major retailer that runs SQL Server 2000 in several thousand stores across the country on a wide variety of hardware. Upgrading will introduce a huge amount of risk and complexity so it's being put off until absolutely necessary. 

And then there are all the people who rely on a 3rd-party product that isn't certified on more recent versions. If it works, why introduce risk by upgrading just for the sake of it?

SQL Server 2000 was a really solid release, and with SP4, it's a really stable database platform. Yes, it's old and support ends next year, but we'll see people using it for many years to come.

[Edit: And yes, it's hard to support – we're even reluctant to take on clients with it – but it's out there – and for many good reasons.]

As an aside, when I left DEC/Compaq in early 1999 we'd just finished working on V7.2 and I remember Intel was still running chip fab lines on V5.5-2, the latest maintenance release for V5 that was released in 1988. Why? Because it was incredibly stable and switching things out on the fab lines was going to be wicked expensive. Ah – the good old days of DEC and VMS… (see here for more of my nostalgia for that…)

Next time you hear of someone on a 2000 system, before berating them and looking like a fool, ask them *why* they're still on 2000.

If you're still on 2000 (or earlier) for some systems, I'd love to hear about what's holding you there…

58 thoughts on “You guys still use SQL Server 2000? Really?

  1. I write commercially available software that is installed at customer sites. We recently (August 2011) switched our minimum version of SQL from 2000 to 2005. As a software vendor, we cannot control the environments that our software is installed in. If we required the latest and greatest, we risk alienating our current customers and potential future customers.

    Our software is currently compatible with SQL2005, SQL2008 and SQL2008R2. We need to be backward compatible with our database engine to keep the customers happy. They’re the ones that pay the bills.

  2. We have a one system likely to remain in SQL 2000 due to the Important message here; "Expressions that appear in a search condition, select list, or any other location within a query may be broken down and reorganized into one or more independent expressions. SQL Server may evaluate these independent expressions in any order with respect to each other. Filtering operations, including joins, are not necessarily applied before the result columns are computed." http://msdn.microsoft.com/en-us/library/ms188295(SQL.90).aspx Being in this situation could be a result of poor database design; we have staging tables with columns of mixed logical data types – dates, numbers, text. In 2000, Where clauses and Joins worked together to isolate records we want as data moved through staging tables. 2005 doesn’t handle the queries the same and we get errors. Regardless, to code around this change would take months and is not worth the effort for an otherwise stable legacy system.

  3. I agree completely with the old mantra: if it ain’t broke, don’t fix it. It is true that there are many reasons why the latest and greatest doesn’t get put to use right away. Working for a large medical software company – making products in a regulated market, we must test and validate everything – soup to nuts. This includes new features that we develop, as well as the platform it sits on. Customers tend to want new application features more than features in the db platform.

    We look at SQL Server for the features that it has, and do a bang for the buck calculation. In our case the performance boost of SQL Server 2005 was incredible – so moving to it made financial sense for our many customers in form of smaller servers and lower heating/power costs. Seriously, our system could handle the same load on 1/8 of the hardware and giving both lower and more predictable response times. We’re considering looking at the page compression features of SS2012 at a MS Lab engagement later this year. Moving our customers to SS2005 made them happier – and they’ve been using it for awhile. There was big reason to move to SS2005, but 2008 & now 2012 aren’t quite as compelling.

    As geeks I think many of us just want to use the newest shiniest thing. Twin turbos, direct shift transmissions, and chrome tail pipes.. right?!

    Most customers don’t care (except for Windows server, viruses and the like). However there are those that want the latest for a variety of reasons:
    1) all products in enterprise on same version allows for having a single set of skills across their IT – lowers TCO.
    2) feature X helps them move some larger corp IT effort forward (usually in the DR space)
    3) Those with more formal DBA roles see the value in ease of maintenance available in newer versions of SS.

    We look at the customer feature need of the db platform the same way we look at features in our product.

    Although I give warning… the longer you wait, the more expensive it becomes. The app itself may be on some "old" technology too. At some point all of the cruft in your app, coupled with the platform testing/compatibility will paint you into a corner. The need or want to rewrite the whole app starts to look better and better.

    I believe in continued investment. It always ends with, It depends. Make the right choice, and have a plan for the future. Do the best with what you have.

  4. Our "skeleton in the closet" is an old data warehouse, 90% of which has been migrated to Oracle. There is one client whose data remains on the old warehouse, due to heavily customized ETL processes and reports. This system is slated for retirement (three years ago), so there’s no incentive to upgrade it. It just keeps humming along, and when there’s a problem with it, we draw straws to see who has to deal with it.

  5. Well put Paul! It all comes down to risk/rewards, and its not limited to SQL server. I’d never start a greenfield project with SQL Server 2000, just like I’d never write a vbscript, or an application in Visual Basic 6. However, I’d think twice about rewriting a large codebase of vbScripts calling ActiveX dlls written in VB6 in a mission critical setup. If the scripts were buggy and in need of constant change, I’d rewrite them. However, if everything "just worked" I’d need a good reason to go rewriting everything.

  6. I actually just out of a meeting with a vendor that’s just starting to support anything newer than SQL 2000 (they skipped 2005 and the projected GA date for the release supporting SQL 2008 is in 3-6 months.) We’re pushing hard for all of our vendors to upgrade but I don’t anticipate being able to get off SQL 2000 before it goes out of support. Right now we still have 14 servers running SQL 2000 some of which are in areas that are much, much slower to move.

  7. Paul,

    SQL 2000 is over 12 years old. It is now 3 versions behind. My experience in the finance industry stated that we were not allowed to ever be more than one major version behind otherwise we risked failing an audit. And if you fail an audit you might as well close your doors.

    What I find frustrating is the ISVs that don’t certify their products for later versions. So when we tell the vendor we need to upgrade in order to remain compliant and we are told "sorry, we aren’t certified for later versions" it sucks. And that would be my biggest complaint on this topic. If you are an ISV and you haven’t certified your product for a version later than SQL 2000 then you are, IMO, "doing it wrong".

    The points you list above are fine points, but they don’t address what was my biggest frustration by far: reluctance by ISVs to certify their products on later versions. Your examples are about consumers of the software, not the makers of the software. If I want to run on a current version of SQL Server, I should at least have my ISV give me that option.

  8. Amen to the retailer scenario. 3600 stores are very expensive to do a massive upgrade to. Also, we could only communicate with them over satellite. Some data deployments (new product catalog) would take 4-5 days to roll out chain-wide due to reliability issues with communications. Even then some stores would require a visit instead. Upgrading all those servers to a new version isn’t going to happen without a site visit to each one. That’s why the upgrade from ISAM to SQL Server took 10 years. It’s all about cost, benefit and risk.

    I use a specialized application that isn’t supported on higher SQL Server versions. In order to upgrade, we’d have to replace the software. That takes real money and adds risk.

    The main reason why infrastructure components remain un-upgraded is that there’s no direct quantifiable ROI on upgrading just so a DBA is happier. There’s only cost and risk and some vague benefits that may or may not exceed the costs, depending on the situation.

    We only have to look to all the Windows XP installs out there. My clients who are still on XP enterprise wide are there because they have applications that aren’t supported on new OSs. Or they haven’t paid for those upgrades. And they want to avoid training costs for 10k users.

    Oh, and I have one vendor app that I support that runs SQL Server 7x. I’m lost every time I log into that box. Thank goodness I don’t have to do that very often.

    The one thing we have to go on is eventually something will fail hard enough that the ROI will suddenly change and we can upgrade.

  9. Oh yes, all of that is true. And what makes the "no more than one version behind" rule tougher is when you have shorter release cycles. Most businesses are not going to upgrade every 36 months on general principle.

    There are many valid reasons for being on older versions of software. Where I struggled was having managers ask "when are you going to get done with migrating us off of SQL 2000" and I had to deal (1) ISVs not certifying because they essentially didn’t want to and (2) our business units not wanting to touch something that was working fine anyway.

    At this point when I see a version of SQL 2000 in the wild I just shrug it off and give my customer a shoulder to cry on. We both share that understanding look – that knowing look – that we can’t do anything about it.

  10. I completely agree with you. There are good reasons to stay on SQL 2000, most of which are financial. If it’s still running now, chances are it’s very, very stable. In 2002, I had a client that had a SQL 65 box running a key card system. It was a pain working on the system at times, but overall, it just worked. It would have cost $75k to upgrade to newer SQL 2000 software + the SQL licenses. Not worth it. We eventually virtualized the server to avoid hardware issues and let it run.

    I think we will have SQL 2000 for a lot of years as well.

  11. My employer processes over a million automated phone calls a day for medical and other industries. Our base platform runs on Visual FoxPro, VB6, VB.Net/C# and SQL Server. The transition of code pieces from VFP to SQL 2000 was enough of a pain point, so when I insisted we move to 2008 just a few years later, I had to sell it as performance and scalability improvements, along with the fact that we have a corporate policy to always have third-party support on speed dial. For us, 15 minutes of downtime at the wrong hour can cost a lot of money, or worse. While migrating to 2008, since I had 20 years experience as a developer, I encouraged the developers to remove some hardcoding and other bad practices, so I was able to keep it presented as win/win. But in my case, we own all the problem code and have the ability to fix all the problems. We’re just growing so fast that it’s hard to repour the foundation while someone’s adding floors to the building. So, I understand staying on SQL 2000, even SQL 7 – but anyone without an upgrade plan is putting themselves at exponentially increasing risk.

    A previous employer had client-installed software on 2,500 sites (about 50,000 PCs), and when MS released an update (In 2005-6 I think) that broke our installs on NT Server 4, we literally had to choose to say goodbye to the client so that the others could move ahead. One day, my current employer could be faced with the receiving side of that scenario (see VFP and VB6 above), so I’m on several committees to ensure we get ahead of it with a major effort. Keeping up with tech changes is not always something you can patch.

  12. In few words, Microsoft CMS 2002. ‘nuf said? Sure there may be a few workarounds for fudging it onto 2005, but what if you’d already made the jump to 2008 and there is no more money for that 2005 license to run just CMS? 2000 it is…

    Even Microsoft dishes issues to us and we just have to live with the old clunker until $ grows on a few nearby trees :-)

  13. My current job has a lot of SQL 2000 instances. The 2 biggest reasons that we have not upgraded is because the business does not want to invest the money to license the 20 or so servers still running SQL 2000 and doesn’t want to invest the time converting/supporting the hundreds of DTS packages attached to those servers.

    The people in charge of the funds don’t see any gain from upgrading if the system still currently "works".

    I have to admit that it’s a pain at times. I try to use 2012 SSMS on my local machine but end up going back to 2008 SSMS to view DTS packages.

  14. Excellent post. It’s way too easy for people to put the IT blinders on and miss what’s up with the business. I’ve said it before, Microsoft did too good a job on SQL Server 2000. It’s stable. It’s functional. It scales well enough for most people. I was supporting hundreds of 2000 databases prior to leaving my last position.

    The one point I made about upgrading was that there is always the possibility that an OS comes out that won’t support SQL Server 2000. I think that’s a bigger worry than anything else. That can mean you’re not just locked into SQL Server, but you’re locked into whatever OS it runs on. From what I’ve seen, that’s more of a concern for shops than that they’re not on the latest & greatest cycle of SQL Server.

    All that said, oh how I hate not having DMOs to access information in 2000 systems. I have a hard time getting my brain reconfigured to DBCC this and sp_ that.

  15. Thank you Paul! You are absolutely right. We support over 400 different application spanning more than 250 production SQL Servers. About 20% of our systems are SQL 2000. Most of these are very small databases. In most cases we are looking at a 1-2 year sunset date for many of these application so why bother to upgrade?

    Also, keep in mind these systems have been virtualized onto faster hardware. Many upgrades were needed due more to aging hardware rather then aging software so the incentive to upgrade has been reduced as a direct result of the virtual environment.

    Having older systems does present management issues. Policy management, no DMV’s, DTS, and some SSMS issues. Overall though the systems run well and will, most likely, fade away without a official upgrade project.

  16. We have multiple clients still running SQL 2000 and in many cases the issue is not that SQL 2000 is old/hard to support/etc. but rather that they are running it on old hardware that is difficult to support or get replacement components for (or that is simply too slow for their current needs).

    In these cases they frequently end up upgrading SQL (and Windows) simply because of their hardware refresh.

    1. Hello Andy
      We run a CRM built on sequel 2000. According to my IT Manager, we have to get off it in order
      to run the newwer windows applications.
      I need to find a sequel 2000 expert to look at our system and see if they can recommend any course of action other than sopeniding 250K because I cant affordf that
      I hope you can help me
      Sincerely
      Benjmain Arnold
      AED Inc

  17. While I completely agree with you and I still see a lot of folks running on 2000, I do end up spending more time working on these systems and troubleshooting problems (lack of DMVs is especially painful). So, while some companies *think* they’re losing money by not upgrading (mostly because of the very noticeable charge for the hardware/software upgrade itself) they’re actually also losing money by not upgrading.

    Again, I’m not saying that people should upgrade solely for these reasons but staying with an older release does cost most than some folks thinks especially because of the additional tools added in 2005. And, with the per core pricing of 2012… And, from what I’ve heard quite a few companies are already planning to stay with 2008 (or R2) quite a bit longer than they were going to. So, "skiiping a release" might become even more common.

    Definitely a great discussion and there are LOTS of good reasons to stay with a particular version. I agree that people don’t really think about their reactions all that well sometimes… to lots of things. ;-)

    Cheers,
    kt

    1. Although this is an old and cold thread, the reality in Banking is that it is very hard to get banks to move off of old technology.

  18. Hey Tom – Yes, I know how old SQL 2000 is, having written a bunch of it myself :-) But what if the ISV had killed the product line – and so there’s no financial downside for them to say ‘no, we will not certify on a later version’. Or if they’ve gone out of business. Of if they just can’t be bothered. Then there’s no option at all – except to move to another vendor, with all the inherent risk and cost involved.

    My post was more about people not understanding the many valid reasons for 2000 still being out there. Intransigent vendors is just one of those reasons.

  19. I will admit that I have been guilty of expressing sorrow or sympathy towards DBAs that that were forced to still support SQL Server 2000 instances in production, but I know there are a lot of people in that situation, for a lot of different reasons. It is so much more difficult to troubleshoot and manage SQL Server 2000 after you have been spoiled by later releases that have DMVs, Extended Events, etc.

    At least going P2V can help get people off of extremely ancient hardware that is long out of warranty, and very slow by today’s standards.

  20. We still have a few systems running on top of the 2000 version. There are many valid reasons.
    –The system is performing its job and stable.
    –There is no budget and sponsor to start a new project.
    –The people developed the 2000 applications have left and the new guys don’t want to touch it until it is really necessory.
    –Nobody has really truely understood how those spaghetti of dts packages working together and not an easy job to upgrade them as a whole to the new ssis packages.
    –a good backup/restore strategy is good enough for DR for those applications and don’t need fancy Mirrorring and AlwaysOn etc.

    But I really want to get rid of them after all.

  21. What I like about my SQL 2000 instances I still have to support is that they never go down or have problems and I never have to touch them. Everyone wins.

  22. A client of mine still have one SQL 6.5 server!

    The ability to upgrade the app was constrained by a very tight support contract with the vendor for many many years & the vendor wanted a crazy amount of money to upgrade it. The app is only used very occasionally by a couple of users & every year it is on the list to have its functionality rewritten into another database, but always gets decoped as few users & very sporadically used…

    I did hear a rumour it was going to be replaced this year, but I’ve heard that before!

  23. Yep, we still use it in a number of areas. One is a very old application that no one knows if it would work with a newer version. The original coder retired 8 years ago and everyone is afraid to touch it as it means an entire full rewrite testing, documentation, and man hours out the wazzoo. Old crappy code, in line dynamic SQL generated on the fly within the code, poorly written procedures, yadda yadda yadda, but right now it WORKS. So let sleeping dogs lie. The other is there are alot of small 3rd party apps built to run on MSDE. MSDE came with SQL agent, and you could do some maintenence at least easily. When the first versions of express came out, no SQL agent, the gyrations you had to run through to get a backup with a bat file and windows scheduler were obnoxious. Why is it still there? The same reason, it WORKS. Would I like to upgrade, sure, but do I have to, nope.

  24. Hi,

    I still have two sql 6.5 running on nt 4.0, hardware is long gone as they now are VM’s, running some old legacy systems that there never have been money nor interest to migrate. I put them on the priority list every year, and still see the migration slashed compared to other buisness projects…..

    Regards
    SUN

  25. My production systems are split across SQL Server 2000 & SQL Server 2005 because the versions of 3rd party software we are using don’t support anything later. These systems have been heavily customised, documentation is scant, and the systems have a limited lifespan anyway, as data is being migrated out and systems are being shut down.

    Why should I risk my business on an upgrade? There is *no* business case. There is massive risk.

    Yes, it’s a pain from the professional development perspective – I don’t get to play with the latest shiny in production environments, but, y’know, I can and am using them on my machine, and I am using SQL2008R2 on internal / departmental level systems. And I reckon that that shows that I can work with a variety of systems which will help when it comes to getting the next job.

    Oh, and SQL2000 SP4? I wish – I still have some SP3a systems running. Because the supplier won’t support anything later.

  26. I’m currently working for a consultancy that supports the NHS (National Health Service) in the UK. There are hundreds of SQL Server 2000 instances running critical systems in our hospitals. It will be a long time before they (and the hardware) are replaced, mainly due to the expense. It is very frustrating.

    Some Trusts run SQL 2005, 2008 and even 2008 R2, but together they make up merely 20%, 80% being SQL 2000.

  27. At my current company I support SQL 7 on NT 4 through SQL 2008 R\2 on Server 2008 R\2. We use both in-house developed and third-party applications.
    What tends to happen is when a new solution is implemented it is done so on the latest version of SQL Server but then is not upgraded in the future as clients are happier with a stable solution than paying to upgrade and not seeing much difference from their perspective as they have the application functionality they require. The position is also that if we were to upgrade the SQL Server version that an application runs on (even if the application version remains the same) then this would require expensive regression testing; again clients when given the option do not want to pay for this when they see no difference. Another factor is that our clients pay for support costs on fixed length contracts (normally 5 years) and so any benefits in upgrading of cost-saving on support has to be weighed up over a shorter time period as we go through the contract period

    Although sometimes I wish I could do some of the stuff with the earlier versions of SQL that I can do with the later ones I also quite like the fact that I have not ‘lost’ skills and can troubleshoot in different ways depending on the version I am dealing with at the time (having said that I’m very impressed with some of 2012!)

  28. I’ll echo the tie to hardware update cycles, as most of our upgrades have been due to the infrastructure team declaring the hardware so far out of warranty and OEM replacement parts becoming difficult to find (w/o resorting to Ebay, etc.) For the servers I deal with regularly the versions about equally distributed from 2000 through 2008R2, which seems to correlate a 3-5 year hardware cycle with the past several SQL release dates. However, with the explosion of virtualization I see those update cycles slowing down even more as people rush to run a p2v and then focus their attention somewhere else. Now the infrastructure team is going after which OS versions are falling out of even extended support, but the various business managers often resists doing it for a variety of reasons. At least I’m not running any NT4/SQL7 (or earlier) boxes anymore…

  29. Hi Paul – I see plenty of large global companies that have compliance requirements still using old hardware, operating systems and software. Usually it’s a combination of cost and stability or lack of vendor support for new versions from both ISV’s and internal dev teams.

    I have always tried to support a max of two versions of any one product however there are generally always exceptions and the best we can do is make sure the relevant stakeholders are aware of the risks and limitations of not moving forward.

    More than once I have come into an organisation and found a server or system somewhere that has the "ancient" tagline and most of the time it requires very little support as it is so stable which often leads to the business wanting to stick with what they know rather than risk an upgrade.

  30. I have several customers still running SQL Server 2000 because the version of the major application they are running was installed with SQL Server 2000, the application is running just fine and an upgrade project would cost hundreds of thousands of dollars, if not break the 7 figure barrier and the justification for that just isn’t there at this time.

    It’s not just a SQL Server thing either.

    I also have 2 customers still running production applications using Oracle 8 (which was released in 1999). Why ? The app was custom written to exact specifications that met several key business requirements by developers who are no longer there. It does exactly what they need it to do and it "just runs" without constant need for code tweaking. Admittedly it probably is getting to a stage where running on an unsupported OS and RDBMS version may mean more risk than reward but running old database software / operating systems can have very legitimate business reasons that may not be immediately apparent to a casual bystander.

  31. Thanks Paul – completely agree – although (like I guess most devs) I do prefer ’05/ ’08/ R2 and am looking forward to getting stuck into 2012, one can’t ignore the reality of the business environment. Hence really interesting to hear some real-world business cases for sticking with 2000.

  32. When Microsoft came out with their shiny new 2005 SQL server, I decided to implement it in a few installations. Then I went to do a backup and restore at a client site one day because they had a problem with their server. The restore had a message something to the tune of this database was backed up with version 2005.10231020132 not 2005.02323232. That was it for me… I never once had a problem backing up or restoring or attaching/detaching any instance of an SQL 2000 database or file.

    I laugh every time I hear statements like “you can’t be more than *n* releases behind”. That is rhetoric from people who don’t have to worry about things working in the real world. Just because some company (Microsoft) releases some version of their software, doesn’t mean it in any way meets your needs. Microsoft’s biggest concern for SQL server 2005 was integrating .net for code based stored procedures, a feature I still to this day think is a waste…

    If a release tries to be all things to all people, and doesn’t do the work that your enterprise requires, then it doesn’t matter one bit that it is *n* releases out. Just because a software company releases something, doesn’t make it worthwhile… Windows 8 is a perfect example of that.

  33. Our small internet based retail business has around 15 to 20 workstations depending on the season. We are running SBS 2003 which I guess means SQL server 2000 and also SharePoint services 2.0. We recently switched to Office 365 for Exchange only. Our hardware is from 2005 and still working great but we are concerned about the server going down, needing to get new hardware, and then being forced into the latest server software anyway. We can’t afford any real down time.

    I understand the points made on both sides of this topic. Could I get some brief opinions on a date that would probably be taking things too far? My guess is that everyone would agree that by 2025, SBS 2003 users would have to move on. If you like, you can all just respond with simple dates just to keep it tidy. My boss is quite frugal as you might have guessed and we are trying to decide what to do. Thanks in advance if anyone is still out there!

  34. Thank you Paul.

    Any suggestions as to an upgrade path? Do you think that a new server and the latest software makes sense? I heard that soon Microsoft is going to shift everything to the cloud and not even sell server software that you can install on your own hardware. I hope that’s just a rumor.

    1. Yes – you’re going to want new hardware at the same time as new software. Windows/SQL 2012 would be the way to go right now. If you need some help figuring out what hardware to get for your workload when the time comes, let us know as that’s one of the services we offer. A couple of hours of our time can save you thousands in licensing costs, and avoid over-provisioning hardware that you can’t actually use.

  35. Hi Paul,

    Now that SQL 2000 is out of mainstram support, will Microsoft support it as far as surrounding systems are concerned? I’m talking about Server patches, ODBC, Networking, .Net Framework and so on, being tested as working with SQL 2000.

    Or will one day a Windows server patch will be released that will kill any SQL 2000 instance installed on that server because Microsoft has depreciated a DLL or function that only SQL 2000 uses.

  36. Well, we run sql 2k and a lot of vb6 aps on top of it. We also have .net applications using 2K.
    It runs fine, works well and it is refreshing to see our developers just get on with it rather than saying I need sql 2008 to make this app work …… so many times have we been told we need to upgrade, and not once have we not been able to program an app that would not work on 2k regardless of the language

  37. We have 1 production system running in 2000 compatibility mode because some legacy Cold Fusion code has syntax not supported in 2005 and up. So it will take some work to identify all the code not supported, then look in the old code and modify it. A risk to production without a lot of reward, especially for an understaffed IT group. I am new here, but it is now on my “to do” list. Sice we’re on the subject, is there a list of code somewhere that will not work in 2005+ ?

  38. Steven Wang says:

    March 8, 2012 at 11:55 pm

    “… –Nobody has really truely understood how those spaghetti of dts packages working together and not an easy job to upgrade them as a whole to the new ssis packages….”

    You can import the DTS packages and leave them in “DTS Mode” so you don’t have to make any changes to them.

  39. Installing SQL 2008 not as easy as SQL 2000.

    Some query that works in SQL 2000 might become very slow (or broken) in SQL 2008. We must re-check and optimizing code and table index using sys.dm_db_missing_index_details.

    Nobody tell us about this. Luckily we found out this issue several days before go live.

    Pain in the ***

    1. Actually it’s a well-known must-do to test your workload extensively on any new version you’re upgrading to as there can be behavior changes that can cause perf issues. Sounds like you found it through your testing, so your process worked as it should.

  40. We are running Windows Server 2000 and SQL Server 2000. To get to SQL 2008, can we do one upgrade? Or do we have to upgrade to SQL 2005, and then upgrade to 2008?

    I am hoping that we get a big performance boost from SQL 2005 or 2008. These will be 64-bit systems, and with newer hardware, we will benefit from more memory and faster processors and faster storage (maybe even flash).

  41. I have a query regarding migration of SQL 2000 STD and SQL 2005 Express edition both running on same Windows 2003 R2 Std OS server. The client wants to upgrade to SQL 2012 or SQL 2014. The challenge is to upgrade the front end web MIS portal on the newer version of SQL database.
    My question is if we migrate SQL 2000 database instance to SQL 2012 or 2014 we obviously have to perform backup/restore to SQL 2005, and then to SQL 2008 and 2012 respectively. So when we start the activity do we need a MS SQL STD licensed version of 2005 and 2008 and then SQL 2012 license at every step or we we can do the migration on express edition onto to the license 2012 SQL instance. Same query for 2005 express edition as well?keeping in mind both the database’s have to be consolidated and moved to a single instance.

    1. Adil, if you still haven’t upgraded, I might be able to answer this question, although it’s not exactly what was asked:
      “if we migrate SQL 2000 database instance to SQL 2012 or 2014 we obviously have to perform backup/restore to SQL 2005, and then to SQL 2008 and 2012 respectively.”

      You actually won’t have to upgrade the Sql Server 2000 database instance to Sql Server 2005, then Sql Server 2008 before finally upgrading to Sql Server 2012. I recently had to plan a process for (and am in the middle of) migrating almost 100 Sql Server 2000 servers, and you can actually go from Sql Server 2000 directly to Sql Server 2008, skipping the Sql Server 2005 upgrade, and then upgrade from 2008 to 2012.

      One of our biggest issues with moving to Sql Server 2012 was that I had to correct quite a few code objects (the finalized alter scripts totaled around 50000 lines of code) that used T-SQL syntax that wasn’t compliant with Sql Server 2005/2008/2012/2014, and there were several DTS packages that had to be converted.

      There’s been resistance from some of the programmers, but other than that, once I had the scripts written and the process developed and documented it’s been going pretty well.

  42. Hello Paul,
    As you’d love to hear about what’s holding people on SQL Server 2000, I’m going to share my case.
    All our DWH system is based on SQL Server 2000 DTS.
    The reason to hold on SQL 2000 is that we must upgrade all our DTS to SSIS, if we want to leave SQL 2000.
    Automated migration from DTS to SSIS is not a real option. We must rewrite the functionality on SSIS from scratch.
    That’s risky, and will consume a lot of time.
    But I think the moment to change is comming fast.
    For example, now we are installing our new servers with Windows Server 2012 R2.
    My surprise is that we can still installing SQL Server 2000 on Server 2012. That’s great.
    But the problem we found recently is when opening Enterprise Manager (SQL Server 2000 Client Tool) and try to do any of the next commands, Enterprise Manager craches:
    – Return all rows of any table
    – Edit or create a view
    – Edit or create a DTS package
    The problem is related to MMC (Microsoft Management Console):
    – Problem Event Name: BEX
    – Application Name: mmc.exe
    – Application Versión: 6.3.9600.17415
    – Fault Module Name: ntdll.dll
    At the moment, we don’t know how to solve this problem, if possible.
    Perhaps Enterprise Manager has stopped working on new versions of Windows or MMC…
    Of course, any help on this mmc craches on Server 2012 would be great!
    Thanks.
    Regards.

    1. Jortx,
      We are having the same issue, have you figured this out? sql 2000 enterprise manager on windows 2012?
      Thanks,

  43. I’ve been supporting SQL 2000 for 20 years across many industries in Scotland with no end in sight! Worst offenders are government bodies where the prevailing thinking remains if it aint broke…

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.