List of Problem Faced
1. Unable to restore msdb from old server backup.
2. Unable to restore master because not used WITH REPLACE command
3. Restore took much time after completing 90%.
4. Unable to create ODBC Connection (for DSN) to Oracle though Oracle client was already installed.
5. Linked Servers were failing to connect. When deleted and created new still error.
6. Oracle Provider for OLEDB (OraOLEDB.Oracle) is not getting listed in Linked Server Provider though Oracle Client is already installed.
Please go through below sections listing root cause and solution for each of above problems.
Solutions for all above problems
Details about Point 6
Error Message:
No Error Message but when you go to Providers in Linked Server or When you create UDL file you wont see OraOLEDB.Oracle there though you have Oracle Client or Oracle Server already installed. Though using SQL Plus you can connect without any problem to Oracle. Even after adding TNS_NAMES Registry Entry in SOFTWARE\Oracle or adding TNS_NAMES system variable to Environment Variable will do no help!
Root Cause:
64-bit ODAC Installation is required for creating linked server using OraOLEDB.Oracle Driver. Same applies for creating connection manager from SSIS or Visual Studio connection.
Solution:
We also need ODAC to be installed (Refer links below for download path) and give a reboot to the server and now you can see Oracle OLE DB drivers listed there.
Details about Point 5
Error Message:
Failed to retrive data for the request. An Error occured During Decryption.
Create failed for LinkedserverLogin. Error occured During Decryption. There is no remote user mapped to local user from the remote server. Microsoft SQL Server, Error: 15466
Root Cause:
Service Master key was enabled in the old server. We migrated only the master database backup but we have not migrated Service master keys.
Solution:
Backup Service Master Key and Restore it to the new server and now existing linked server and newly created linked servers are working fine. Incase existing linked server is not working then just delete and re-create it. (Refer links below for help)
Details about Point 4
Error Message:
There is no error message. But when you try to create DSN and search for "Oracle in ORAHOME91" or "Oracle in ORAHOME9x" Driver in list, there will be nothing
Root Cause:
If you go to Control Panel --> Administrative Tools --> Data Sources (ODBC) there when you list the drivers you will get only 64-bit drivers because it calls "C:\Windows\System32\odbcad32.exe". So then how to create ODBC Connections using 32-bit Drivers in 64-bit Windows Server?
Solution:
Simple...If you have installed 32-bit driver then you have to to manually run "C:\Windows\SysWOW64\odbcad32.exe" which will list all the 32-bit Drivers and now you can create DSN from here..
BTW. Don’t try to open both (the 32bit and 64bit odbcad32.exe tools) at the same time, then something goes wrong and it opens only 1 time but with the wrong settings.
Details about Point 3
Error Message:
There is no error message but it took 5 mins to complete 90% RESTORE but to complete next 10% it is taking too long like more than another 10 minutes
Root Cause:
Because log file was 46 GB but Data file was 6 GB, It completes restoration of data file till 90% and then it starts generating LOG file of size 46 GB and fill data wherever needed and remaining space it fills with "0"
Solution:
Take a backup of LOG and shrink the source database, then take a backup and now restore this backup in the newly migrated server.
Details about Point 2
Error Message:
The backup set holds a backup of a database other than the existing 'master' database.
Root Cause:
When we are restoring a database which already exists then we need to use a extra TOKEN in RESTORE command.
Solution:
That extra token is REPLACE...
Details about Point 2
Error Message:
An error occurred on a query to database msdb.The backup of the system database on device cannot be restored because it was created by a different version of the server than this server.
Root Cause:
Service Pack Level of old server from where backup was taken is 9.00.3068 but service pack level of newly built server is 9.00.3042. So msdb cannot be restored because of this SP Level mismatch.
Solution:
Always match Patch versions between source and destination server during migration.
Other Tips:
1. Some Problems May occur when you’re running IIS in 64bit mode and have a (ASP) website using some 32bit components.
Change IIS to 32bit mode using this one-liner: "cscript C:\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 0"
2. Check wether your third party components are capable of running in 64bit mode before migration.
Links
64-bit Oracle ODAC for Windows x64
BACKUP SERVICE MASTER KEY
SQL Server 2005: A look at the service master keys
