This is regarding new redesigning project of custom critical reporting application. The new proposed new design will be using MS SQL Server 2005 as database and web reporting tool. In turn SQL Server will be getting data from Oracle 10G server.
Overall Project Scope: Develop new reporting application which has scalability, availability and high performance system over existing reporting application.
A) Current design:
MS Access Reports ßàMS Access Databaseß----Oracle Link from Access DB--àOracle 10 G Database
Limitations of current design:
1. Reports can’t be published over the intranet
2. Database size limitation, can’t grow more than 2GB
3. Concurrent multi user access of the reports
4. Data security is also an issue with MS Access database
Tools/Software: MS Access 2000, MS Access Forms, Oracle 10G, (Oracle 7.3 has been installed on my desktop).
Do I need to uninstall Oracle 7.3 and then install Oracle 10g on my desktop? As when Oracle 10g client was installed on my system and I tried to run the existing queries in MS-Access I get ODBC Connection has failed.
B) New Design: SQL Server 2005 database and it’s tools were selected to overcome limitations with MS Access Database.
SQL Server Reporting Services ßàSQL Server 2005 Databaseß Linked ServersàOracle 10 G Database
New Design Status:
1. The SQL Server 2005 including SSRS and SSIS has been installed on the server (Windows 2003) with IIS and ASP.NET on the server.
New Design Pending Issues:
1. MS Access Migration to MS SQL Server 2005: Now the migration needs to take place from MS-Access but, in MS -Access we are using Oracle 10g tables as linked tables. I would still have to continue use the Oracle tables as linked tables.
Question is what is the best approach to be followed to migrate tables and also the code which is in VBA to SQL Server including using BIDS?
2. Linked Servers: SQL Server 2005 is capable to get data from different data sources like Oracle 10g, DB2 etc., provided SQL Server is configured with appropriate drivers and tools to access the new data sources like oracle.
Question is how I can link Oracle data source from SQL Server in order to run some distributed queries to fetch data feed for the reports?
C) What is happening now in this process?
I have the following questions in regards to Oracle server access from SQL Server 2005.
1. Oracle 10g client needs to be installed on my local machine initially and also the on the server. What are the steps for configuring Oracle 10g client on the local machine?
2. Do I need to migrate first on the local machine (development) environment, test and then only can move to production server?
3. Could you let me know the steps or procedure for migrations from MS-Access to SQL Server using SSIS and the steps to configure SSRS and SSIS on the server?
4. Have you downloaded Oracle 10g client on your local machine or on the server? How do you test whether all your db have been migrated and also the linked tables.
Please let me know what is the best approach to follow? Appreciate all help.
Thank you
Overall Project Scope: Develop new reporting application which has scalability, availability and high performance system over existing reporting application.
A) Current design:
MS Access Reports ßàMS Access Databaseß----Oracle Link from Access DB--àOracle 10 G Database
Limitations of current design:
1. Reports can’t be published over the intranet
2. Database size limitation, can’t grow more than 2GB
3. Concurrent multi user access of the reports
4. Data security is also an issue with MS Access database
Tools/Software: MS Access 2000, MS Access Forms, Oracle 10G, (Oracle 7.3 has been installed on my desktop).
Do I need to uninstall Oracle 7.3 and then install Oracle 10g on my desktop? As when Oracle 10g client was installed on my system and I tried to run the existing queries in MS-Access I get ODBC Connection has failed.
B) New Design: SQL Server 2005 database and it’s tools were selected to overcome limitations with MS Access Database.
SQL Server Reporting Services ßàSQL Server 2005 Databaseß Linked ServersàOracle 10 G Database
New Design Status:
1. The SQL Server 2005 including SSRS and SSIS has been installed on the server (Windows 2003) with IIS and ASP.NET on the server.
New Design Pending Issues:
1. MS Access Migration to MS SQL Server 2005: Now the migration needs to take place from MS-Access but, in MS -Access we are using Oracle 10g tables as linked tables. I would still have to continue use the Oracle tables as linked tables.
Question is what is the best approach to be followed to migrate tables and also the code which is in VBA to SQL Server including using BIDS?
2. Linked Servers: SQL Server 2005 is capable to get data from different data sources like Oracle 10g, DB2 etc., provided SQL Server is configured with appropriate drivers and tools to access the new data sources like oracle.
Question is how I can link Oracle data source from SQL Server in order to run some distributed queries to fetch data feed for the reports?
C) What is happening now in this process?
I have the following questions in regards to Oracle server access from SQL Server 2005.
1. Oracle 10g client needs to be installed on my local machine initially and also the on the server. What are the steps for configuring Oracle 10g client on the local machine?
2. Do I need to migrate first on the local machine (development) environment, test and then only can move to production server?
3. Could you let me know the steps or procedure for migrations from MS-Access to SQL Server using SSIS and the steps to configure SSRS and SSIS on the server?
4. Have you downloaded Oracle 10g client on your local machine or on the server? How do you test whether all your db have been migrated and also the linked tables.
Please let me know what is the best approach to follow? Appreciate all help.
Thank you