What is the difference Between Sql-server 2000 & 2005
- 3 Answers
- 9461 Views
- Value Labs, Wipro, I also Faced
- E-Mail Answers
Answer Posted / rvnair
What are the Differences Between SQL Server 2000 and SQL Server 2005?
I've been asked this question every time that there's a new
version and yet I've never been able to give what I think is a
nice, concise, logical answer that satisfies the asker. Probably
it's a lack of my ability to easily form words in my mouth and
get them out in the proper order, so I decided it might make some
sense to do this on paper (metaphorically speaking) and help
others out.
Like many of you, I usually get this question from someone
outside of SQL Server. A windows admin, a network guy, etc.,
someone who has little contact with SQL Server. Or maybe it's
someone who's been stuck with admin'ing a SQL Server instance.
In any case, I wanted to try and explain this concisely for the
non-DBAs. As I began this project, however I soon realized that
it's not easy to just give a good general answer. As with
everything else in SQL Server it seems that "it depends" is the
best general answer, so I broke this up into a few areas. This
part will look at the administrative differences and the next
will cover more of the development differences.
The Administrative Differences
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.
Why Upgrade?
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 Clustering or Log Shipping require Enterprise
Edition. Expensive hardware. Clustering, Database Mirroring or
Log Shipping available in Standard Edition. Database Mirroring
can use cheap hardware.
Scalability Limited to 2GB, 4CPUs in Standard Edition. Limited
64-bit support. 4 CPU, no RAM limit in Standard Edition. More
64-bit options offer chances for consolidation.
Conclusion
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.
| Is This Answer Correct ? | 2 Yes | 2 No |
Post New Answer View All Answers
What are “phantom rows”?
Why do you want to join software field as you have done your BE in Electronics?
Explain how to use linked server?
How do I find the size of a sql server database?
What are policy management terms?
How can sql server instances be hidden?
How to create an index on a view?
What is the advantage of sql server?
What is sql or structured query language?
after migrating the dts packg to ssis by using migrtn wizrd in 2005. iam not able to open ssis pack and getting error. what r those errors? how to resolve?
What are the different normalization forms?
What is collation sensitivity?
What are logical database components? : SQL Server Architecture
What are secondary xml indexes?
Why do we use sql limitations? Which constraints can we use while making a database in sql?
