Sql-server – Access 2003 (SQL Server 2000) migration to SQL Azure

azure-sql-databasems accesssql server

As my old Windows 2003 RAID controller started throwing errors, I am seriously thinking about switching current Access 2003 (adp/ADO) clients to use a Windows SQL Azure solution, in place of current SQL Server 2000.

Does anybody knows if this is a feasable/painless operation?

Best Answer

If your back end is currently SQL 2000, then you need to do a dance to get this to work, but it should be fairly straightforward to do something as follows:

  1. Spin up a VM in Windows Azure, using a template for SQL2008R2 (that's the oldest available template, I believe).
  2. Take backup of your SQL2000 instance, restore it to the Azure VM instance you just created.
  3. Using Visual Studio, create a new database project pointing to the new SQL2008 R2 database.
  4. Change the properties of the project to be SQL Database as the target.
  5. Fix all the errors.
  6. Export/Import to your SQL Database instance.
  7. Remove your Azure VM (unless you want to keep it, of course, but why pay for something you don't need?)

I have no idea how long this will take you to accomplish, but that's the general outline that should work.

Related Question