Differences Between SQL Server 2000 and SQL Server 2005

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

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.