By Arthur Fuller and Stephen Giles

Most IT pros are working in SQL Server 2000 and still
supporting SQL Server 7 databases (a few folks are even supporting SQL Server
6.5 databases). With the arrival of SQL
Server 2005, we’re often asked: Should I upgrade? In this article, we offer the 10 most compelling reasons why
you should upgrade to SQL Server 2005.

1. Everything that works now will
continue to work (sort of).
SQL Server 2005 Management
Studio will let you control SQL Server 2000 and SQL Server 2005 databases. Management
Studio will not work for SQL Server 6.5 and 7.0, but it’s simple to port to
the acceptable versions.

Due to compatibility issues, some things will not port
easily to Management Studio. For example, if your SQL Server 2000 database
contains diagrams, you won’t be able to touch them from SQL Server 2005 without
upgrading the database.

2. SQL Server 2005
comes with more components.
Earlier versions of SQL Server packaged the various
components in several different ways. For instance, the all-inclusive
Enterprise edition bundled everything, but you may not have been lucky enough
to access that edition. The Analysis Services component was not included on SQL
Server 2000 Standard—you had to buy that component separately.

Microsoft has changed its marketing strategy and bundled all
the components into a single package. SQL Server 2005 does cost more than
previous editions, but it is still an incredible deal. And, if you attend a SQL
Server 2005 launch presentation, Microsoft will give you a free version—no
strings attached.

Weekly SQL tips in your inbox

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

3. Disparate user
interfaces have been rolled into one UI.
In the old days of Enterprise Manager and Query Analyzer,
Query Profiler, Reporting Services, and Data Transformation Services (DTS) were
isolated apps whose interfaces were anything but consistent. SQL Server 2005
Management Studio gives you one clean UI that accesses all of the components,
even online analytical processing (OLAP) and SQL Server Integration
Services (SSIS), without regard to the server upon which the component of interest
resides. This translates into more productivity and cheaper training costs.
Even if you choose to keep your databases in the SQL Server 2000 format, you
can still use the wonderful new interface to perform your tasks.

4. Let .NET languages
do the heavy lifting.
T-SQL is still the tool for doing 90% of your work, but some
tasks require special precision; in these situations, T-SQL is ungainly at
best. Examples include row-by-row control, particularly when you must compare
the current row to a previous row and write to multiple tables within one
procedure. You can do this in T-SQL. Therefore, you might find it simpler to let
.NET rowsets handle very complex logic. We aren’t suggesting that you run off
and rewrite all of your stored procedures in .NET; instead, think of this as an
added luxury and not as a replacement of T-SQL.

5. You can take
advantage of Reporting Services.
We operate on the general principle that everything the back
end can do, the back end should do. For
example, we think that constructing dynamic SQL Server queries in the
application is most often silly. It is necessary from time to time, but a
better and more secure option is to have the front-end application assemble the
parameters and then call a stored procedure.

SQL Server 2005 Reporting Services
extends this concept to another level. In SQL Server 2000 and earlier versions,
reports were delivered by various front-end applications (C++, VB, Delphi,
Access, Crystal Reports, etc.). You can roll this into Reporting Services with
enormous advantages. First, you take all of the logic out of the given front end.
Then, you can use Reporting Services from virtually any front end, which means
that your application
developers can remove a lot of code from the applications. There’s one
report for all possible UIs, which means that if there’s a bug in the report,
you fix it once and all the UIs inherit the fix.

6. Business
Intelligence is built in.
Business
Intelligence (OLAP) was not built in previous versions of SQL Server,
unless you purchased the Enterprise edition of SQL Server 2000. Even if you
could afford this, you had to master a whole new interface. Using SQL Server
2005, you can do everything you could before, using a clean and integrated UI.

7. Bid adieu to DTS
and say hello to SSIS.
SQL Server 2005 completely replaces DTS with a new
technology called SSIS, which is a quantum leap forward. We think the coolest
part of SSIS is that the data transformation is now an SSIS object. Finally,
you can really do flow control and error handling by building tasks outside of
the extraction, transformation, and load (ETL) operation.

8. Upgrade wisely using
the Upgrade Advisor.
SQL Server 2005 has introduced new features and changed
existing features to help improve performance, security, and manageability.
These changes may affect your existing applications. This is why Microsoft’s
SQL Server team developed the Upgrade Advisor, which intelligently guides you through
the upgrade process and points out any compatibility issues that might arise.

9. You now have granular
security at your fingertips.
With the addition of the proper database schema, and the
ability to assign some administrative tasks without making every developer and
junior DBA a full Senior Architect, you can greatly increase the security of
SQL Server 2005 by giving users only the rights they need to do their jobs. (Admittedly, some developers might
not see this as a good reason to upgrade.)

10. Benefit from enterprise
scalability.
SQL Server 2000 has issues when it comes to enterprise
scalability, but this is now a thing of the past with SQL Server 2005. It
looks like SQL Server 2005 is ready to hold its own against Oracle and DB/2 for
the enterprise. It is also significantly cheaper than either Oracle or DB/2 no
matter how you measure the cost (whether it’s per processor or per seat).

Summary

Even if you have no immediate need to migrate from SQL Server
7 or 2000, you should install SQL Server 2005 because the benefits are huge.
You can continue to administer SQL Server 2000 databases without converting
them, and also enjoy the cool new extensions in SQL Server 2005. An hour into
the new SQL Server Management Studio will persuade you to forget all about
Enterprise Manager and Query Analyzer. They will seem as old as CP/M.