Geek Answers Handbook

What are the key benefits of SQL Server 2005/2008 compared to SQL Server 2000?

One of our customers is updating their servers because older machines can no longer handle the database load. They have been using sql 2000 for the last 6 years, and db has grown to store several GB of data.

Should I upgrade to 2005 or 2008? What are the main advantages of the new versions compared to the year 2000?

+3
source share
9 answers

I plan to upgrade from SqlServer 2000, I would skip 2005 and upgrade directly to SqlServer 2008

It has all the features of 2005 and some additional functions (for example, the option of transferring a variable table as a parameter of a stored procedure, new types of dates, processing of spatial data, etc.)

You can refer to the Benefits of MS SQL Server 2008 over MS SQL Server 2005 for a complete list of features

EDIT

I see that the question has been updated, and now the SqlServer 2008 question is included in the question.

+3
source

In addition to the CLR integration mentioned by Galwegian, the main advantages for me are:

Another difference is that instead of the DTS packages you would use in Sql 2005, Integration Services is used , which while similar is a completely different ball game.

+4
source

Depending on which version of SQL Server you are using, SQL 2005 has less restrictive hardware / software limitations than the corresponding releases in SQL2k.

For example, SQL 2000 Standard Edition will not use more than 2 GB (in practice 1.7 GB), while SQL 2005 Standard Edition is not limited (comes to OS max).

See: http://msdn.microsoft.com/en-us/library/aa933149(SQL.80).aspx

... and ...

http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx

So: if you use the standard version +, your instance of SQL Server 2000 currently uses RAM ~ 1.6Gb +, your server has 3 GB or more physical memory, then it is probably worth upgrading just to increase memory usage .. . (more cached tabular data, indexes, plans, etc.)

+4
source

MS SQL 2005 and 2008 have many inflated technologies, one of which is the ability to enter CLR code in stored procedures. DON'T DO IT!

Another “feature” is the ability to display your database as WebServices, but again; DON'T DO IT!

The third feature is the ability to use “notifications” from your database and to your application layer, but again; DO NOT DO IT ...!

Your database is a bucket, and it should “store data,” period. Many of the Microsoft features delivered in 2005 and 2008, I'm sure they did this because they wanted to complicate the use of O / RM libraries, which abstract from the actual database provider so that people can modify the databases as they see fit. Then, adding a lot of “dumb functions” that run counter to every best practice that we learned about databases from the 70s, they managed to create a new lock in which vendor locks were removed, forcing people to use stuff that they really don't need use one way or another ...

Partly from the fact that in 2005 and 2008 there can be many interesting functions (for example, the support mentioned here!) And such things as optimization, corrections, etc. But be careful not to start using material that holds your application, and does not allow you to use best practices and blocks you ... :(

+3
source

The main advantage of CLR integration to be honest is that it gives you much more flexibility in how you code your database, giving you the option to include procedural C # or VB.NET in your procedures instead of a set-based T-SQL.

+2
source

There are some new features that are useful, for example, as a service broker, but in terms of performance, you will not see big improvements in the transition from 2000 to 2005. You would be much better off: a) setting up your database and b) investing in new equipment.

+1
source

Separation of users and schemes is another advantage. In SQL 2005, if you want to split a schema into a logical / functional area or similar, and not into a user in your database, you can create schemas such as "hr", "sales", "accounting", "production", and then create custom tables in accordance with the corresponding schemes.

In SQL 2000, the schema name was identical to the owner / creator of the table.

+1
source

I think SQL Server 2000 is no longer supported by Microsoft. If I'm wrong, it's coming soon ...

+1
source

Index index rebuilds are a good feature. I think this may be the only option in the Enterprise Edition.

0
source

More articles:


All Articles