November 21, 2007 at 2:58 pm
I was in a meeting today with a number of IT people, when the question of "why should we upgrade to SQL 2005" came up. The major of our systems are performing fine on 2000, so why should a company spend the money on upgrading. Outside of an aging technology, is there definite reasons to upgrade?
November 21, 2007 at 4:51 pm
Wanting to take advantage of new features is probably one of the most popular reasons for upgrading. Some, editor Steve Jones of this site included, have suggested waiting for SQL 2008 if you haven't already upgraded to SQL 2005.
You also need to think about how long Microsoft support will be available for SQL 2000. According to their support list http://support.microsoft.com/lifecycle/?p1=2852, mainstream support will end next April and extended support will end in April, 2013.
Greg
Greg
November 23, 2007 at 9:34 am
It has better better security options, among other things. For instance, DDL and Login triggers, which can prevent schema changes and modifications to critical logins. Also, for those SQL Server based logins that can have their passwords changed, SQL Server 2005 can enforce the password policy on the computer. Also, if the client is running the Native SQL Client to connect, the logon sequence traversing the network is encrypted.
So even if performance is good, there are still security reasons to consider switching. Of course, as has been stated, with SQL Server 2008 coming out, it may be better to wait until that releases rather than upgrade and be stuck a version behind almost immediately.
K. Brian Kelley
@kbriankelley
November 23, 2007 at 11:59 am
Maybe the new security features for parts of the server that point to the outside world and the fact that MS won't support 2k soon are worth it... but most of the new features are not, in my humble opinion. Oh sure, CTE's are nice to have... so are CLR's, Ranking, RowNum, and a couple of other things...
But the fact is, we all got along just fine without those and, as a couple of us have shared through some extreme testing on this forum, some of them can actually hurt performance compared to the work arounds we've been using in 2k for the last 7 years.
The real key is to sit down with the "what's changed" list between 2k and 2k5... get the Developer's Edition of 2k5 and take the time to play with the new functionality to make sure you know what it does and what you'd need it for. Then, try a simple migration and see how much stuff breaks (there will be some...).
So far as all the DDL triggers go... you should be using DBA change controls and restricting devlopers from production changes, not triggers...
Once you're all done with that, then you'll be equipped to make an informed decision... but, even then, don't upgrade to 2k5... wait for 2k8... sp2... 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2007 at 2:24 pm
Jeff Moden (11/23/2007)
Maybe the new security features for parts of the server that point to the outside world and the fact that MS won't support 2k soon are worth it... but most of the new features are not, in my humble opinion. Oh sure, CTE's are nice to have... so are CLR's, Ranking, RowNum, and a couple of other things...But the fact is, we all got along just fine without those and, as a couple of us have shared through some extreme testing on this forum, some of them can actually hurt performance compared to the work arounds we've been using in 2k for the last 7 years.
The real key is to sit down with the "what's changed" list between 2k and 2k5... get the Developer's Edition of 2k5 and take the time to play with the new functionality to make sure you know what it does and what you'd need it for. Then, try a simple migration and see how much stuff breaks (there will be some...).
So far as all the DDL triggers go... you should be using DBA change controls and restricting devlopers from production changes, not triggers...
Once you're all done with that, then you'll be equipped to make an informed decision... but, even then, don't upgrade to 2k5... wait for 2k8... sp2... 😀
Before we get ahead of ourselves, SQL Server 2000 support is out for a while yet. Mainstream support will end next year, but extended support is until 2013.
With respect to the features, service broker can be extremely helpful, but only if you intend to use it. Doing SOA via SQL Server (HTTP endpoints) saves you a server and an IIS install (ask your server and security guys about this one). The DDL triggers are a fail safe. It's really easy to have both connections to production and development and accidentally apply changes to production which are meant for development. In the imperfect world we live in, that's a reality. So even with diligence and proper procedures, DDL triggers can be a lifesaver. Mirroring gives us another high availability option and speaking of high availability, the fact I can use STANDARD edition for a 2 node custer where SQL Server 2000 requires ENTERPRISE edition means a huge cost savings on licensing unless I need all those other enterprise features. Then there's the fact that there's an SMTP mail solution meaning we can get away from the MAPI based SQL Mail that's in SQL Server 7.0 and 2000 which could crash and in some cases the solution was to restart SQL Server and in other cases you had to restart the whole box. And for those with a lot of development effort, the user-schema separation and the granular level of securables is absolutely outstanding (while security related, that's not outward facing)
So there are a lot of good reasons to go the upgrade route (and I didn't even touch on built in encryption or the upgrade to DTS we have in SSIS). However, if your SQL Server is running fine on 2000 and you don't have a compelling performance or security reason to upgrade, then it's probably not worth the time at this point. After SQL Server 2008 RTMs, that may be the time to consider the upgrade.
K. Brian Kelley
@kbriankelley
November 23, 2007 at 3:26 pm
Heh... that's better... thanks, Brian.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2007 at 6:58 am
We're doing all our new development on 2005, but we haven't even started a plan for upgrading from 2000 because, like you, everything is working fine. Our current assumption is that we'll skip a step and upgrade our 2000 machines to 2008 when it comes out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 27, 2007 at 12:26 am
Hi Teekay
10 things that will convince you to upgrade to SQL Server 2005
http://articles.techrepublic.com.com/5100-9592_11-6047958.html
But, in my opinion I will waite for SQL Server 2008 release.
Regards,
Ahmed
November 27, 2007 at 2:37 am
More thoughts on security...
It is very difficult to run SQL 2000 without the service account having Windows local administrator authority. It is easy and recommended to run SQL 2005 without having local administrator authority.
It is impossible in SQL 2000 to allow non-sysadmin staff to view object definitions for things they do not own. This is easy to do in SQL 2005.
Then there is password policy, encryption, etc, etc. For some shops, these security issues are getting important enough to justify moving from SQL 2000.
Going away from security, one of the main percieved blockers to upgrading to SQL 2005 is DTS support. This no longer applies. Microsoft have announced the the DTS designer components available in the SQL 2005 Feature Pack will also be supported in SQL 2008. This means you can keep exactly the same DTS components in SQL 2005 and 2008 as you had in SQL 2000, and maintain them using the same DTS Designer.
If you have not started a move to SQL 2005, then it would be better to move direct to SQL 2008. A migration is going to take the best part of a year to complete, and going direct to SQL 2008 would give you 3 more years at your new platform before you needed to move again. Download SQL 2008 CTP 5 to start getting hands-on experience, but don't expect SQL 2008 RTM to be available much before August 2008.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 27, 2007 at 4:44 am
In addition to Security (and the auditing available with DDL triggers), the main reasons I would add are SSIS, Solution Explorer, Database Mirroring and Snapshots.
DM & Snapshots are wonderful recovery tools. Log Shipping in 2005 is much improved also. Solution Explorer rocks as it gives me a one-stop-shop to keep all my code in one place and the ability to import other people's code also (assuming I have a copy of their projects from a source-control solution).
Integration Services offers a lot more options than DTS, runs a lot better than DTS (fewer errors) and is easier to code in. Sure, there's the whole package conversion/upgrade issue, but SSIS is just a lot more intuitive in a lot of places. Plus you can actually account for "broken files".
In DTS, if there was one line of bad data in a CSV file, you almost had to reject the whole file, find the bad data and manually fix it before you could import it. With SSIS, you can actually send bad data records to a separate queue, import all the good records and then only have to do manual intervention on the records that are actually bad. And coming from a shop that does a lot of importing, this is a MAJOR feature.
However, as several people have pointed out in various articles / blogs (and one person on this thread), if you haven't upgraded already to 2005, I would definitely wait for 2008 to come out. Especially as 2008 is supposed to be out in February (I'll believe it when I see it @=).
Reason 1) There's a lot of work to upgrade to the new SQL Server Engine, especially when you're talking DTS.
Reason 2) Two upgrades in a relatively short time frame is a pain. And that's what you'll have to do if you go for 2005
Reason 3) SQL Server 2008 is supposed to be essentially the same as 2005 with better DBA support features. So if you upgrade once, to SQL Server 2008 instead of 2005, you do all your work at one time and get all the nifty new features and have a support window that will last a lot longer than if you upgrade to 2005 at this late date.
Does that help you out or confuse the issue?
November 27, 2007 at 4:58 am
Just a quick clarification, 2008 isn't supposed to ship until Q2 2008. Personally, I wish they'd wait until Q4, 2009.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 27, 2007 at 5:00 am
I had originally heard February, but that was like 6 months ago, so I'm not surprised my info is out of date. @=)
November 27, 2007 at 5:05 am
Three Simple Reasons
1) Scalability 2) Security and 3)Support
November 27, 2007 at 6:34 am
With respect to the features, service broker can be extremely helpful, but only if you intend to use it. Doing SOA via SQL Server (HTTP endpoints) saves you a server and an IIS install (ask your server and security guys about this one).
Brain,
Are you saying that directly exposing the SQL Server via HTTP endpoints is more secure or less secure than using IIS and web services written in .NET?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 27, 2007 at 6:39 am
HTTP Endpoints are much more secure. You can actually dictate the type of traffic you get and, I believe, the network protocol used to access it.
Not to mention you have granular control over the ports, in addition to what your network routers already give you.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply