10 most compelling reasons why you should upgrade to SQL Server 2005.

csharpwithdj.blogspot.com

Find out the 10 most compelling reasons why you should upgrade to SQL Server 2005. Several enhancements to the latest edition of SQL Server include Management Studio, SSIS, and built-in business intelligence.

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, I 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.

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.

Difference between SQL SERVER 2000 and 2005

csharpwithdj.blogspot.com

What is the Difference between SQL SERVER 2000 and 2005?

Following are some major differences between the two versions:

• (PG) The most significant change is the .NET integration with SQL SERVER 2005. Stored procedures, user-defined functions, triggers, aggregates, and user-defined types can now be written using your own favorite .NET language (VB.NET, C#, J# etc.). This support was not there in SQL SERVER 2000 where the only language was T-SQL. In SQL 2005, you have support for two languages T-SQL and .NET.

• (PG) SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000. It was a separate installation for SQL Server 2000.

• (PG) SQL SERVER 2005 has introduced two new data types varbinary (max) and XML. If you remember in SQL SERVER 2000, we had image and text data types. Problem with image and text data types is that they assign the same amount of storage irrespective of what the actual data size is. This problem is solved using varbinary (max) which acts depending on amount of data. One more new data type is included XML which enables you to store XML documents and does schema verification. In SQL SERVER 2000, developers used varchar or text data type and all validation had to be done programmatically.

• (PG) SQL SERVER 2005 can now process direct incoming HTTP request without IIS Web server. In addition, stored procedure invocation is enabled using the SOAP protocol.

• (PG) Asynchronous mechanism is introduced using server events. In Server event model the server posts an event to the SQL Broker service, later the client can come and retrieve the status by querying the broker.

• For huge databases, SQLSERVER has provided a cool feature called “Data partitioning”. In data partitioning, you break a single database object such as a table or an index into multiple pieces. But for the client application accessing the single database object, “partitioning” is transparent.

• In SQL SERVER 2000, if you rebuilt clustered indexes even the non-clustered indexes where rebuilt. But in SQL SERVER 2005 building the clustered indexes does not build the non-clustered indexes.

• Bulk data uploading in SQL SERVER 2000 was done using BCP (Bulk copy program’s) format files. Now in SQL SERVER 2005 bulk, data uploading uses XML file format.

• In SQL SERVER 2000 there were maximum 16 instances, but in 2005 you can have up to 50 instances.

• SERVER 2005 has support of “Multiple Active Result Sets” also called as “MARS”. In previous versions of SQL SERVER 2000 in one connection, you could only have one result set. Now in one SQL connection, you can query and have multiple results set.

• In previous versions of SQL SERVER 2000, system catalog was stored in the master database. In SQL SERVER 2005, it’s stored in a resource database which is stored as sys object. You cannot access the sys object directly as in the older version we were accessing the master database.

• This is one of the hardware benefits which SQL SERVER 2005 has over SQSERVER 2000 – support of hyper threading. WINDOWS 2003 supports hyper threading; SQL SERVER 2005 can take advantage of the feature unlike SQL SERVER 2000 which did not support hyper threading.
• Note: Hyper threading is a technology developed by INTEL which creates two logical processors on a single physical hardware processor.
• SMO will be used for SQL Server Management.
• AMO (Analysis Management Objects) to manage Analysis Services servers, data sources, cubes, dimensions, measures, and data mining models. You can mapm AMO in old SQL SERVER with DSO (Decision Support Objects).
• Replication is now managed by RMO (Replication Management Objects).
• Note: SMO, AMO and RMO are all using .NET Framework.
• SQL SERVER 2005 uses current user execution context to check rights rather than ownership link chain, which was done in SQL SERVER 2000.
  Note: There is a question on this later see for execution context questions.
• In previous versions of SQL SERVER the schema and the user name was same, but in current, the schema is separated from the user. Now the user owns schema.
  Note: There are questions on this, refer “Schema” later.
  Note: Ok below are some GUI changes.

• Query analyzer is now replaced by query editor.
• Business Intelligence development studio will be used to create Business intelligence solutions.
• OSQL and ISQL command line utility is replaced by SQLCMD utility.
• SQL SERVER Enterprise manager is now replaced by SQL SERVER Management studio.
• SERVER Manager which was running in system tray is now replaced by SQL Computer manager.
• Database mirror concept is supported in SQL SERVER 2005, which was not present in SQL SERVER 2000.
• In SQL SERVER 2005 Indexes can be rebuilt online when the database is in actual production. If you look back in SQL SERVER 2000, you cannot do insert, update, and delete operations when you are building indexes.
• (PG) Other than Serializable, Repeatable Read, Read Committed, and Read Uncommitted isolation levels, there is one more new isolation level “Snapshot Isolation level”.
  Note: We will see “Snapshot Isolation level” in detail in the coming questions.

Summarizing: The major significant difference between SQL SERVER 2000 and SQL SERVER 2005 is in terms of support of .NET Integration, Snap shot isolation level, Native XML support, handling HTTP request, Web service support and Data partitioning. You do not have to really say all the above points during an interview. A sweet summary and you will rock.