SQL 2012 :: Migrating Server Database Over To Oracle
May 29, 2015
I just wanted to know what the easiest way would be to migrate a SQL Server 2012 database to Oracle?
Apart from the tables and views, the database also uses a number of stored procedures and functions which will also need to be migrated over onto Oracle.
I guess the problem with generating scripts for the database (right clicking database > tasks > generate scripts) is that the TSQL scripts will not run in Oracle which uses PSQL? Is this correct?
I have also considered using the import/export wizard. Do I need to install a feature to use this to export to Oracle?
i am migrating data and schema from oracle server to sql server., when i try to do the dts it gives me an error saying could not resolve tnsnames.ora. iam not sure what is the problem.
My oracle server host is dbserver.hari.com.edu and its name is Prod (name of the machine)
i tried both it still gives me the same error., but when i install an oracle client on the machine and configure the same it is working great.
My question is, is it necessary to install oracle client to migrate database from oracle to sql server. If not how do i do the same...what is the process to do it. since this is a prodction machine we don't want more of applications running on the same machine.
I am migrating a database TESTDB from SQL 2008R2 to a new server running SQL Server 2012.
Management has decided the current sql users should have "better" user names. So the login and username "BadUsername" on the old server should be called "GoodUserName". Goodusername should have the same permissions as Badusername.
I have now restored a backup from the old server to the new server.
I used the following script for creating the login:
CREATE LOGIN [GoodUserName] WITH PASSWORD=N'difficultpassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO
Then I run the following script:
ALTER USER Badusername WITH LOGIN = GoodUserName, NAME = GoodUserName
But the results are not what I wanted. I now have two database users: Badusername and GoodUserName. I would have preferred if BadUserName was "replaced" by GoodUserName, but it won't be a problem if I have to delete badusername manually. Worser is that GoodUserName have NOT "inherited" any permissions from Badusername.
Is there an easy way to transfer permissions or do I need to loop through the permissions of badusername and apply those to badusername?
I have a program that uses ADO to read/write to/from an Oracle back-end database. I'm hoping that given the underlying schema is the same, I can simply change the database driver I'm using and use a SQL Server database instead. Has anyone migrated an application using ADO from Oracle to SQL ? Are there any issues/problems/tips that I should be aware of ?
Hi I am trying to migrate my database in Oracle 8i to Sql Server 2000 but am unsure of how to carry out the migration. How do I do it and what do I have to consider. My OS is Windows 2003. Thanks in advance
I am migrating a database from Oracle to SQL Server 2005. I have a problem when migrating code from PL-SQL (oracle labguage) to T-SQL (sql server language). My problem is: there is pseudo-column in oracle called level that return the level from a register in a tree view of a select (a parent-child relationship). I would want to know how to translate that level column to T-SQL. Thanks!
I am taking over a Database set up where we have 2 databases. One is a SQL SERVER 2000 db and the other is an Oracle 9i db.
THe previous DBA had/has a problem with transferring CLOB and Blob datatypes From the ORACLE db to SQL SERVER. He says that the OLEDB driver SQL SERVER provides has limitations particularly on the CLOB data because it truncates data over 4000 characters in length.
For the Blob issue: We basically want to store/move attachments (word documents etc) into image datatypes (SS) from the blob datatype (9i).
I figured I could do this using DTS or even using the Linked server method. I figured worst case scenario I would just store the documents on the server and then use SQL SERVER's textcopy utility to upload the documents into the image datatype column of the SQL SERVER table.
For the Clob Issue I have no idea what to do, because I cant find anything online that mentions SQL SERVER having issues with importing CLOB data over 4000 charcters in length, afterall the text datatype accomdates more than 4000 characters.
Does anyone have any suggestions for moving the Blob and most importantly the Clob datatypes from 9i to SQL SERVER without any truncation in the data????
Can anyone help me out in migrating Oracle stored procedures & Functions which contains collection types (user-defined datatypes like VARRAYS) into SQL Server 2005. How to achieve this taks? This is very important for me and it's very urgent. Please someone help me.
If i want to migrate from SQL SERVER 2005 to Oracle, How Oracle will deal with my primary key columns in almost all tables having datatype of UNIQUEIDENTIFIER ???
is there any concept of uniqueidentifier in Oracle same as in SQL SERVER ????
I am migrating MS SQL Server 6.5 databases to a new server and wanted to create the data & log devices differently than they are on the old box. My dbcc checks are giving me 2558 errors because my devices weren't built the same size in the same order. I thought if I didn't copy the master, I didn't have to rebuild the database exactly the same. Can anyone tell me if there is a way to rebuild different device sizes and do a successful restore ? Thanks in advance !
I need to move all existing dbs to a new server. One of the database is over 90GB in size. If I plan to use to restore database option, should I restore all user database first, then the system databases(master, msdb) or should I restore the system database first and the user databases second?? Thanks.
I got a project of moving a web site of one a firm from one ISP machine to another ISP machine. They are using Cold Fusion with SQL Server as the database. I am new to SQL Server. I want some tips on how to migrate the database from one machine to another.
Hi, We are planning to migrate data from access to sqlserver. I have good knowledge on sql server...but never used access to migrate. Can anyone help me with the basics to be kept in mind and methods and errors you get in migrateing data.If possible explain in detail and what is to be done while migrating data.....thank you in advance.And its urgent please.
Hi all, I'm not sure if this is the right place to seek help. I'm trying to migrate a small sql 7 database to a new server runing sql 2005 server express. is that possible? if anyone has any instructions or point me to the information that i need to get this done. i'm not a dba so any details instructions would be really appreciated. Thanks in advance.
When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;
Performing operation...
- Add log for package (Success) - Add task for transferring database objects (Success) - Create package (Success) - Start SQL Server Agent Job (Success) - Execute SQL Server Agent Job (Error) Messages * The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;
InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).
I have a SQL Server 2012 instance set up with a linked server to an Oracle Database. I need to build a stored procedure (single NVARCHAR(6) parameter in: @accountID) which executes a select statement against the Oracle DB.
Everything's working fine when testing with 'EXEC (''<oraclecodeblock>'') at LINKEDSERVERNAME' if I hard code the value of @accountID in <oraclecodeblock> but when I encapsulate it all within sp_executesql and attempt to parameterize @accountID it's throwing ORA-01722 'invalid number' errors on the Oracle DB. The parameterized value is NVARCHAR as are the account numbers.
Working: DECLARE @accountID VARCHAR(6); SET @accountID = '10842'; DECLARE @sql NVARCHAR(MAX); SET @sql = N'EXEC (''SELECT * from A.Accounts WHERE ID = ''''10842'''';'') AT LINKEDSERVER';
I want to migrate my Access XP database to SQL Server 2005 Express Edition. The Start Menu does not display any migration assistant in SQL Server 2005 Express menu list.
i am migrating one database from SQL Server 2008 where I have around 20 databases to SQLServer 2012. To migrate the login I was thinking about to use SP_Help <g class="gr_ gr_114 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="114" id="114">revlogin</g> store procedure but now what I believe is this SP scripts out whole server level login and I don't need all the logins in the server except related to the database that I am migrating to.
I'm trying to replace special characters in SQL SERVER and all the solutions for this RDBMS that I found, it uses loops and the source of my data it's in Oracle. in ORACLE and they use REGULAR EXPRESIONS to solve it..Do you know what its the better option to replace special characters? Using loops in SQL SERVER or REGULAR EXPRESSIONS in ORACLE ?
I am new to SQL Server, but the current project that I am working on has the following requirement:-
1) Migrate the application (a servlet based web application on Apache Tomcat) from Solaris to Wintel 2) Migrate the supporting database from SQL Server 2000 to Sql Server 2005 3) Get IIS to communicate with Tomcat for serving servlet/jsp etc to the client
Though I successfully completed item 1 above, am stuck at item 2. Details are below
Actions taken for item 2
A. Installed MS 2005 B. Created new database in MS 2005 (logged in as user 'sa') C. Generated SQL scripts (such as create table table_A etc) from existing MS 2000 D. Genearted SQL scripts (such as insert into table_A etc) from existing MS 2000 E. Created new schema in MS 2005 F. Ran scripts C & D in the new schema. All tables are records populated. G. Obtained new JDBC driver and test run to see if connection is working fine, and it worked. Even ran an sql statment
Code Block[select count(*) from sa.table_A]
and got appropriate response.
H. When I made the application to talk to this new database (which is a copy of Production from step C, D above), it's behaving as though it cannot find the record. I. When I further debugged, I realised that the web application is excuting queries without mentioning the schema. For eg.
Code Block[Select firstName, lastName from table_A]
Or rather it assumes that the user connecting to database is same as the schema name.
J. To further ascertain my point, I ran the query
Code Block[Select firstName, lastName from sa.table_A]
and it worked!
Now the real problem is that I cannot modify the existing code to append a schema name and this approach is rather not recommended best practise.
I tried to match the user name with the schema name, even made this schema as default to the user. But still not finding any luck.
I request all you experts out there to help me out with this problem.
I have been using AlwaysON AG for a long time now and currently have about 10TB of data across 120 databases and 3 AG groups for any application that is on SQL 2012 with great success. Each AG group is running on patch level 11.0.5058.0 with 2 synchronous replica(on different SANS) in Primary Data center and 1 ASYNC replica in DR. Migration has been a non-issue because none of the databases weren't substantial enough that I could not fit into my maintenance window which is 12-4AM on SAT morning.
My issue is that my last application to migrate to 2012 includes a 4TB TDE encrypted databases database which is about 10x larger than any of the previous ones I have migrated. The database takes 4 hours to backup after tuning extensively(I hate TDE!!)
The restore to the primary replica is instant because of seeding incremental but the issue comes from having to backup the database before adding to the availability group. 4 hours is my exact outage window and I can't get any more. My plan to migrate application is to -
First Outage Window
1) Restore Database from 2008 to 2012 Primary Replica 2) Change application ARECORD(or cname not sure which) to Primary replica 3) Run database on single node until next outage window
Week Later 1) Add database to availability group 2) Change ARECORD/CNAME to listener
What I don't like about this is I am going an entire week with 1 node instead of 3 which is worrisome. How to accomplish this I would love to hear from you or any type of comment from people who have worked with VLDB in availability groups and what you like/hate/loved about doing it. I am trying to go all in on this software and have loved it so far but getting worried when it comes to the VLDB migration.
Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandy
I have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which I have heard works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.
Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get the following error:
Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.
Of course, some of the entries will be read only, since doctors have to sign off the charts and are not allowed to subsequently change them. But I should still be able to switch over to sql 2005?!?!?!?
Or... is there a way to attach the databases to SQLExpress manually?
Hi there, I have a problem of exporting the SQL Server 2000 database to Oracle 8i. Can anyone help regarding this?? Please help.... Thanking you in anticipation RGDS Amit
Hi i have a sql server instance on my system and it is linking into an oracle database on another server. When i run queries against this other server...it takes forever...
However, when i use access, and link the table and run the same query against the oracle database...it runs immediatly.
I am very confused as to why there would be such a performance difference and why sql server would run so slow.
I am wondering if it has something to do with the way i configured the linked server. there are several options that I didn't know what they meant.
collation compatible (not selected) Data access (selected) RPC (not selected) RPC Out (not selected) collation name connection timeout query timeout
In one of my applications running on a MSSQL database, I need to issue a select call to an Oracle database to pull information into my application.
To accomplish this,
1. the ORacle Admin has created a view of the Oracle database for me. 2. I have installed the Oracle client on the SQl server box. 3. A friend has told me that one way to accomplish this is create a linked server from SQL to use OLE/DB for Oracle to connect to the Oracle database 4. So I create the linked server successfully; create the server login sp_addlinkedsrvlogin successfully 5. When I go to query the database, I get the error below:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA' reported an error. [OLE/DB provider returned message: The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 (or greater) client software installation.
You will be unable to use this provider until these components have been installed.]
I am having an issue trying to get a linked server to work from SQL Server 2000 SP4 to an Oracle 10.1 database. This server runs Windows 2003 Server.
I had first installed the Oracle client 10.2 on the server and was able to access the Oracle db using the client.
However - when I set up the linked server on SQL Server - and click the "Tables" underneath the linked server - I get the following error.
OLE DB Provider reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned]
I uninstalled the 10.2 client and put the Oracle 9i client on the server (on a suggestion from a SQL Server DBA to put an earlier client on the server) - still able to connect using the Oracle client - but not via SQL Server.
I have checked to make sure the MSDAORA.dll is registered. I have also checked that the account that SQL Server starts under has proper permissions to the Oracle folders (that login has admin permissions on the server).
I have named the Linked Server the same name as the service name in tsanames.ora.
I'm at a loss - any ideas? Help - i'm tearing my hair out here.
I was at a conference and had it asserted to me by an Oracleafficiando that Oracle and DB2 handled low-level locks "better" thanSQL Server, and that this was likely the cause of SQL Server'srelatively slower and more deadlock-prone performance when running thesame application. (SQL does seem to perform more poorly for this app,a PeopleSoft customer service and billing app.)Is there any significant difference in lock escalation strategiesbetween major databases, and if so, what are the implications?Jeff Roughgarden, MSCD, MCDBA
