Please refer to the following URL to know the differences between SQL Server 2000 and SQL Server 2005,http://onlydifferencefaqs.blogspot.in/2012/07/sql-server-difference-faqs-5.html
These seem to be the major highlights from my perspective as an administrator. While there are other improvements, such as the schema changes flowing through replication, I'm not sure that they represent compelling changes for the non-DBA.
In the next article, I'll examine some of the changes from a developer perspective and see if any of those give you a reason to upgrade.
And I welcome your comments and thoughts on this as well. Perhaps there are some features I've missed in my short summary.
Ref Article From http://www.sqlservercentral.com/articles/Administration/2988/
Administering a SQL Server instance to me means making sure the server service runs efficiently and is stable and allows clients to access the data. The instance should keep data intact and function according to the rules of the code implemented while being well maintained.
Or for the non-DBAs, it means that you are the sysadmin and it just works.
The overall differences are few. Sure we use Management Studio instead of Enterprise Manager, but that's not really a big deal. Really many of the changes, like being able to change connections for a query, are superficial improvements that don't really present a substantial change. If you think they do, you might be in the wrong job.
Security is one area that is a very nice improvement. The separation of the schema from the owner makes administrative changes easier, but that is a big deal because it greatly increases the chances you won't keep an old account active because it's a pain to change owners on objects. There's also more granularity and ease of administration using the schema as another level of assigning permissions.
Another big security change is the ability to secure your web services using certificates instead of requiring authentication using a name and password. Add to that the capability to encrypt data, and manage the keys, can make a big difference in the overall security of your data. You have to carefully ensure your application and access is properly secured, but just the marketing value of encryption when you have credit card, financial, or medical data is huge. SQL Server 2000 had no real security features for data, allowing an administrator to see all data. You could purchase a third party add-on, but it was expensive and required staff training. Not that you don't need to learn about SQL Server 2005, but it should be a skill that most DBAs will learn and be able to bring to your organization over time.
High availability is becoming more and more important to all sizes of businesses. In the past, clustering or log shipping were your main choices, but both were expensive and required the Enterprise Edition. This put these features out of the reach of many companies, or at least, out of many DBAs' budgets. With SQL Server 2005, you can now implement clustering, log shipping, or the new Database Mirroring with the Standard edition. With the ability of Database Mirroring to use commodity hardware, even disparate hardware between the primary and mirror databases, this is a very reasonable cost solution for almost any enterprise.
There are also online indexes, online restores, and fast recovery in the Enterprise Edition that can help ensure that you take less downtime. Fast recovery especially can be an important feature, allowing the database to be accessed as the undo operations start. With a log of open transactions when a database is restarted, this can really add up to significant amounts of time. In SQL Server 2000, you had to have a complete, intact database before anyone could access it. With redo/undo operations sometimes taking a significant amount of time, this could delay the time from Windows startup to database availability by minutes.
Data sizes always grow and for most companies, performance is always an issue on some server. With SQL Server 2000, you were limited to using 2GB of RAM and 4 CPUs on the Standard Edition. The number of CPUs hasn't changed, but you can now use as much RAM as the OS allows. There also is no limit to the database size, not that the 1,048,516 TB in SQL Server 2000. Since RAM is usually a limiting factor in the performance of many databases, upgrading to SQL Server 2005 could be something you can take advantage of. SQL Server 2005 also has more options and capabilities on the 64-bit platform than SQL Server 2000.
This is an interesting question and one I've been asked quite a bit over the last 18 months since SQL Server 2005 has been released. The short answer is that if SQL Server 2000 meets your needs, then there's no reason to upgrade. SQL Server 2000 is a strong, stable platform that has worked well for millions of installations. If it meets your needs, you are not running up against the limits of the platform, and you are happy with your system, then don't upgrade.
However, there is a caveat to this. First the support timeline for SQL Server 2000 shows mainstream support ending next year, in April 2008. I can't imagine that Microsoft wouldn't extend that given the large number of installations of SQL Server 2000, but with the next version of SQL Server likely to come out next year, I can see this being the point at which you cannot call for regular support. The extended support timeline continues through 2013, but that's an expensive option.
The other consideration is that with a new version coming out next year, you might want to just start making plans to upgrade to that version even if you're happy with SQL Server 2000. If the plan is to release a new version every 2-3 years, you'll need to upgrade at least every 5-6 years to maintain support options.
Be sure that in any case you are sure the application you are upgrading, if it's a third party, is supported on SQL Server 2005.
Lastly, if you have multiple servers and are considering new hardware for more than 1 of them, it might make some sense to be sure to look at buying one large 64-bit server and performing some consolidations. I might recommend that you wait for the next version of SQL Server if you are worried about conflicts as I have heard rumors of switches to help govern the resource usage in Katmai (SQL Server 2008).
A quick summary of the differences:
Feature
SQL Server 2000
SQL Server 2005
Security
Owner = Schema, hard to remove old users at times
Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates.
Encryption
No options built in, expensive third party options with proprietary skills required to implement properly.
Encryption and key management build in.
High Availability
hshs s shs