Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Migration tools support only SQL server 2005 and up :-( –  Riccardo Mar 4 '13 at 16:41

2 Answers 2

I don't know about easy, but it's doable. I don't know of a way to automatically migrate everything, but you can set up your databases in Azure and then BCP the data up.

Access 2003 should be able to connect via ODBC to Azure. I only have 2010, so can't test it, but I know 2003 supports ODBC.

http://www.fmsinc.com/microsoftaccess/cloud/link-to-azure-sql-database.html

Make sure you plan your indexes. Azure requires you to have a clustered index on each table.

share|improve this answer
    
I don't think I'm using any clustered index at the moment. What this will bring to? I hoped I could simply import tables and point my application to the Azure! –  Riccardo Mar 5 '13 at 13:23
    
Not quite that simple :) Azure requires a clustered index on all tables. How comfortable are you with SQL 2000? Can you look and see what indexes you have? –  Cate Donoghue Mar 5 '13 at 13:33
    
Counter order! They all seem to be clustered (visual diagram tool->Indexes->create as clustered) :-) –  Riccardo Mar 5 '13 at 14:21
    
Okay. You'd just need to make sure you create those on Azure. This should also give you some guidance on how to script the tables and get the data up to the cloud initially. blogs.msdn.com/b/cesardelatorre/archive/2010/06/04/… –  Cate Donoghue Mar 5 '13 at 14:25
    
Great Cate! Do I need to convert databases to SQL server version 2008 before importing into Azure? –  Riccardo Mar 5 '13 at 14:54

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.