How I Migrated RAS database from Access to MSSQL without losing data.

This is NOT a step-by-step tutorial; it’s intended simply as a guideline. You must have some MS SQL and DSN configuration skills to implement this.

Why I did this:

The Access DB was working fine for me with a couple hundred clients. I want to access the RAS data for custom reporting reasons, so I need/want easy multi-user access.

Guaranties:

None, make backups, do at own peril. You may lose/miss data during the change over, especially if you do it on a live server like I did. I lost very little, if any, but I didn't care (no compliance issues for me).

Tools I used:

• Access 2007
• MS SQL Server 2005
• MS SQL Server Management Studio Express
• Access was used on my desktop, and the SQL server was (already) installed on the RAS server.
• I had VPN and RDP access to the Widows server that hosts the RAS and SQL, and I have full admin rights to all the computers being used.

Steps:

1. Use the SQL Server Configuration Manger to ensure TCP/IP connections are allowed.
2. Use the Mgmt Studio for the rest of the SQL stuff.
3. Create a new SQL Database (I used the existing SQL instance), name is whatever you’d like (i.e.: MyRASDB)
4. Create a new SQL Login for the database server (i.e.: MyRASUser), set the users’ default database to the MyRASDB you just made.
5. Create a new user for the MyRASDB, associate it to the login you just made (I named the user the same as the login name), and assign it as a db_owner.
6. Test to ensure you can log into the SQL server as the user you made.
7. Stop the RAS Service.
8. Navigate to the RAS Access DB folder and copy the era.mdb and era2.mdb databases to the machine with Access 2007 installed on it.
9. Open both the era.mdb and era2.mdb in Access— if you get security warnings on open ensure you ‘enable the content’.
10. Copy the ThreatSense1 and ThreatSense2 tables from era2.mdb to era.mdb (I just drag-n-dropped them between Access windows).
11. Open the IDGenerator table in both era databases.
12. Copy the two ThreatSense records from the era2.mdb, to the bottom of the IDGenerator table in the era.mdb.
13. Close the era2.mdb window, and go back to the era.mdb window.
14. Under Database Tools use the Move Data to SQL Server button.
15. Choose “Use an existing database”.
16. Click the Machine Data Source tab and create a new User Data Source that uses the SQL Driver.
17. Give the new DS a name and description, and pick the SQL server by hostname (i.e.: mySQLServer.domain.local)
18. Since this connection is temporary only to export the Access DB into the SQL server, and because I have full admin rights, I used Windows authentication. You should also be able to use SQL Authentication and use the same user you made earlier.
19. Enable “Change the default database to:” and pick the MyRASDB.
20. Finish off with the wizard, everything else was left at defaults.
21. You should be back the the Select Data Source window, where you can pick your newly made data source.
22. Add all “Available Tables” to the “Export to SQL Server” pane.
23. Leave next page of settings at default.
24. On next page select “No application changes”, and hit Finish.
25. It should now churn away for a while exporting to the SQL server. You can check its progress by refreshing the Mgmt. Studio on the SQL server; you should see the tables show up as it’s working, and they’ll be now preceded by “dbo”.
26. Once it’s done, close up Access, and you can delete the era.mdb’s and data source you made on the Access computer – the conversion is done.
27. Start the RAS service. Any new data between now and when we next restart the RAS will be lost.
28. Connect to the RAS via the RAC.
29. Navigate to the Server Options, Other Settings, and the Edit Advanced Settings to open the Configuration Editor.
30. Navigate to ERA Server, Setup, Database.
31. Change the “Database type” to “MS SQL Server”.
32. Change the “Database connection” to the following MS SQL connection string, only with your info in it:
    Code:

    Driver={SQL Native Client};Server=mySQLServer.domain.local;Database=MyRASDB

    *If you need the 'SQL Native Client' driver for the RAS server (i.e. SQL is on a different server) you can get it from MS (Feature Pack for Microsoft SQL Server 2005). You can also use "Driver={SQL Server}" instead, but you'll get a warning event about how slow it is in the application log everytime you start the RAS service.
33. Change “Database schema” to “dbo”.
34. Enter the SQL user’s name and password (ie: MyRASUser/thepasswordyougaveit) it the next two fields.
35. Save the configuration and exit the Configuration Editor.
36. Hit “OK” to close the “Server Options”.
37. Go restart the RAS service.
38. You can check the Windows Application event log to see how it goes, I get one warning about using a specific DSN type to get speeds up, but I’ll look into that later.
39. If all starts as expected, you should now be able to connect to the RAS via the RAC again, and all data should be there.
40. I went and renamed the RAS Access DB folder; just to prove to myself it was working.

Hope this helps some people!

d.

 

Nice, thanks.

 

It upgrades automatic, if u switch from access to mysql while upgrading ERAS