Different Between SQL Server 2000 and 2005.

Feature

SQL Server 2000

Security:Owner = Schema, hard to remove old users at times

Encryption:No options built in, expensive third party options with proprietary skills required to implement properly.

High Availability Clustering or Log Shipping require Enterprise Edition. Expensive hardware.

Scalability:Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support.

SQL Server 2005

Security Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates.

Encryption Encryption and key management build in.

High Availability Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware.

Scalability 4 CPU, no RAM limit in Standard Edition. More 64-bit options offer chances for consolidation.

Differences :

1. In SQL Server 2000 the Query Analyzer and Enterprise Manager are seperate,whereas in SQL Server 2005 both were combined as Management Studio.

2. In Sql 2005,the new datatype XML is used,whereas in Sql 2000 there is no such datatype

3. In Sql server2000 we can create 65,535 databases,whereas in Sql server2005 2(pow(20))-1 databases can be created.

1 UI Differences -Tools Analysis Services Manager (ASM)
Query Analyzer (QA)
Enterprise Manager (EM)
Report Manager
Business Intelligence Development Studio (BIDS)
SQL Server Management Studio (SSMS)

2 UI Differences -Organizing Grain
ASM "Database" BIDS "Solution"and "Project"

3 UI Differences - Data Modeling Must be connected to do anything
Single Data Source, Single Fact Table per Cube
Cannot directly rename entities -must resort to database views to rename entities and for logical abstraction
Must resort to virtual cubes for mixed fact granularity
No support for arbitrary SQL as a Data Source
Data Source Views (DSVs) provide an abstraction layer
Between Database and Cubes, DSV can contain tables drawn from multiple heterogeneous
In data source , once DSV established, need not be connected to work with cube models
Can rename entities, provide annotations
Named Queries -arbitrary SQL as Data Source
DSVs can be shared between cubes and DTS for improved consistency and administration

4 UI Differences -Cube Construction Manually intensive, even with Wizard
Must know the underlying schema well
Intellicube

5 UI Differences -Deployment Archive/Restore Database
CAB files
No configuration variables for Data Source connectivity, etc.
Elaborate Configuration and Deployment Model

6 UI Differences - Metadata Storage
There isa repository
Defaults to Access -can upgrade to SQL Server
Cannot really be source-controlled
There is not a repository
Metadata stored in XML documents, can easily be source-controlled
Team development is facilitated

7 Architecture -Unified Dimensional Model
Distinct difference between flat file, relational, cube and web-service data sources
Unified Dimensional Model (UDM) integrates all types of data sources -data consumer sees single, multi-dimensional interface
Allows the user model to be greatly enriched
Provides high performance queries supporting interactive analysis, even over huge data volumes
Allows business rules to be captured in the model to support richer analysis
Supports writeback and 'closing the loop'

8 Architecture –Dimensions
Role-playing requires multiple Dimensions
Role-playing is a new Dimension type
Reference Dimension
Many-to-many Dimension

9 Architecture –Hierarchies One and only one Hierarchy per Dimension
Attributes are either part of Hierarchy or are Properties
Zero to multiple Hierarchies in same Dimension