Saturday, November 26, 2011

Differences between Oracle to SQL Server

When you thing to migrate database from oracle to SQL Server or some other database then most important is to compare with other databases with Oracle and decide whether will go for migration or Upgrade. If you go for Oracle to some other database then It will be complete migration whereas Oracle to Oracle higher version then it will consider as database upgrade.

Following table will describe the differences between SQL Server 2008 R2 and Oracle 11g R2.  

     Key Features
SQL Server 2008 R2
Oracle 11g R2
ü  Support for 256 logical processors
ü  Improvements in multi-server management
ü  Master Data Services
ü  Geospatial visualizations in Reporting Services
ü  Self-service BI with Project Gemini

ü  Automatic Memory Tuning
ü  SQL Performance Analyzer (Fully Automatic SQL Tuning) 
ü  Automated Storage Load balancing
ü  Automatic Diagnostic Repository

Database Comparison

SQL Server 2008 R2
Oracle 11g R2
Database Change
Migration
Upgrade
Migration Tools
SSMA (SQL Server Migration Assistant 2008) from Microsoft.
Database Upgrade Assistant (DBUA)
License cost
Extra License cost
Existing Oracle License can be used
Offline Version
SQL Server Personal Edition
Express - Free limit 1 processor, 10 gig db Note that SQL Server 2008 had a 4gig limit
Oracle Database Express Edition 11g
Express (XE) Free 1 processor, 4 gb db, 1 gig ram
OS Supports
Windows XP, Windows Vista, Windows 2003, Windows 2008 (runs in both 32-bit and 64-bit)
Linux, Unix, Windows 2003, Windows 2008 (runs in both 32-bit and 64-bit), (R2 does not yet work on Mac apparently,10G seems to be the last to work on Mac. )
.NET connectivity
using  ADO.NET  Data provider
(System.Data.SqlClient.SqlException)
Oracle Data Provider for .NET (ODP.NET)
(Oracle.DataAccess.Client)
Existing Interfaces Compatibility
Need to customize the interfaces to work with SQL server 2008
No customization requires for other interfaces
Testing After Migration
More Testing efforts
Ø  Performance Testing
Ø  Rollback/Failover Testing
Ø  Volume and Load Stress Testing
Ø  Validation of database objects
Ø  Integration Testing

Less Testing efforts
Ø  Upgrade Testing
Ø  Minimal Testing/Smoke testing

               
Different Versions and Licensing Cost
Express - free limit 1 processor, 10 gig db Note that SQL Server 2008 had a 4gig limit
Standard - starts at ~$7500 per processor limit 4 processors
Enterprise ~$29,000 per processor
Datacenter - ~$58,000 per processor
Parallel Data Warehouse - ~$58,000 per processor
Express (XE) Free 1 processor, 4 gb db, 1 gig ram
Personal Edition ~$450 per user
Standard Edition One ~$5800 per proc
Standard Edition ~$17,500 per proc
Enterprise Edition ~47,500 per proc + option to buy Oracle Spatial add on for another $17,500 per proc
Free Object/Relational Mapping
NHibernate.Spatial: Spatial extensions for NHibernate, allows you to connect NHibernate to a spatially enabled database and manipulate geometries in HQL or in .NET code using NetTopologySuite, providing you with a fully integrated GIS programming experience.
Hibernate Spatial: Generic extension to Hibernate for handling geographic data. Hibernate Spatial is open source and licensed, like Hibernate, under the LGPL license.
Hibernate Spatial allows you to deal with geographic data in a standardized way. It abstracts away from the specific way your database supports geographic data, and provides a standardized, cross-database interface to geographic data storage and query functions.
Reports
Microsoft Sql Reporting Services
Crystal Report .NET
Crystal Report .NET
Dialect of SQL supported
T-SQL
PL/SQL
Development Tools available
SQL Server Management Studio,
Visual Studio 2010 etc.
Toad, Oracle Console,
Oracle SQL Developer 3.1 etc.