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 | 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 | HibernateSpatial NHibernateSpatial (this is a .NET object relational spatial mapper) (just geometry) 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 - this is a java object relational mapper, NHibernate Spatial 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. | |
