August 8, 2008 at 3:41 am
Can you use Sql Server Migration Assistant (SSMA) for Access against the SQL Server Express version?
August 8, 2008 at 5:27 am
Scott MacCready (8/7/2008)
Actually, Standard can support 16 instances on a single physical server.
I can't figure out how to add another instance to SQL 2003 Standard. The only "instance' I see under "Registered Servers" is the server that SQL 2003 is installed upon, then the folders underneatth.
In the past I remember on IBM DB2, you'd have a Server > Instance > Database structure.
SQL just has the Server > Database structure.
Any suggestions?
August 8, 2008 at 5:35 am
grovelli (8/8/2008)
Can you use Sql Server Migration Assistant (SSMA) for Access against the SQL Server Express version?
Although at MS site for SQL Server Migration Assistant for Access, they say "SQL 2005" only , the express version is merely a lighter version of standard. The database eng. itself is the same.
The only way to find out is to migrate 1 table and 1 query from Access to SQL Express using SSMA and see what happens. Obviously make backups of your Access DB first before running SSMA against it.
I'm very interested in this myself since I need to get the DB on my laptop for work.
August 8, 2008 at 6:28 am
I'm pretty sure you just do another install, and then that process will detect the existing instance(s) and allow you to create another one. Each instance is another install.
Steve
(aka smunson)
:):):)
umanpowered (8/8/2008)
Scott MacCready (8/7/2008)
Actually, Standard can support 16 instances on a single physical server.I can't figure out how to add another instance to SQL 2003 Standard. The only "instance' I see under "Registered Servers" is the server that SQL 2003 is installed upon, then the folders underneatth.
In the past I remember on IBM DB2, you'd have a Server > Instance > Database structure.
SQL just has the Server > Database structure.
Any suggestions?
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
August 8, 2008 at 6:38 am
I just downloaded the migration assistant tool (for SQL 2005 version), last night, and wow, what an extraordinary tool. It took me 5 times through the process to finally get it right, but that was more me than the tool - although I would have appreciated the opportunity to customize things through the wizard process instead of having to stop the wizard then configure stuff and then go back through the steps. One thing that you may want to avoid the wizard for is any text fields where you don't want to convert to nvarchar, and instead want varchar. That requires a change from the defaults, and so does loading tables into a particular schema OTHER than the default one for your SQL login. However, you can get around that by changing the default schema for your login and then if necessary, change back after the migration. Overall, that's one spectacular tool, and I'll NEVER AGAIN use the upsizing wizard. As posted before, make copies (yes, more than one) of your Access DB before using this tool. Then, if you get it wrong, you can always just copy your copy back over your original and delete everything that got to the SQL Server, and try again. I did that at least 4 or 5 times, but I learned quite a bit in the process.
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
August 8, 2008 at 8:15 am
If I migrate only the backend(i.e. tables), what do I use to relink the tables from the frontend? I don't suppose you can use the Linked Table Manager utility from within Access, can you?
August 8, 2008 at 8:31 am
grovelli
Can you use Sql Server Migration Assistant (SSMA) for Access against the SQL Server Express version?If I migrate only the backend(i.e. tables), what do I use to relink the tables from the frontend? I don't suppose you can use the Linked Table Manager utility from within Access, can you?
I've been testing SSMA with Access 2003 and SQL Server Express 2005 and it works very well.
SSMA has an option check-box to "link tables" that's what you want to do. The SSMA will then automatically relink your Access app to the new SQL db, so you don't have do any extra steps. You can still use the Linked Table Manager to maintain those links afterwards.
Bill Mitchell
August 8, 2008 at 9:01 am
Hi Bill,
Oh so even after the migration, you'd still be working with frontend.mdb and backend.mdb(which is the only file I would feed to the SSMA), only that in the backend you wouldn't have the tables anymore, only the links to the tables been migrated to SQL Server?
August 8, 2008 at 12:20 pm
That is a good point. If you already have links to a back-end file, after the conversion you would have two sets of links - front-end to back-end and then back-end to MSSQL. Not the best arrangement.
I suppose that you could try this (after making backups of course):
1. Convert the back-end
2. Delete all the links from the front-end to the back-end
3. Import the links from the back-end into the front-end
4. Run the front-end and verify that everything connects directly into MSSQL
5. Archive and discard the back-end
Does that make sense?
Bill
August 8, 2008 at 12:33 pm
It does. Thank you:)
August 8, 2008 at 12:46 pm
Ok, let's clarify, as I'm a bit confused. I had the impression that your tables were within the Access file (.mdb I presume), so if that's the case, then the migration tool will, if directed to do so, just create backups of the local tables and then link the existing tables to the real data on the SQL Server. Once you're comfortable that the tables on the SQL Server are working ok, then you delete the backup tables that are local to the Access .mdb file, and everything is AOK.
However, if you are already using Access as a front-end to some other database as a backend, I still think the net result is that you get the tables re-linked, but don't know what kind of backup, if any, would occur.
Either way, as long as you back up the original Access file, then you should be able to survive even the worst-case scenario.
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
August 8, 2008 at 8:34 pm
For level II users:
SSMA4A can migrate tables from mulitple MDB files in the same 'migration', including from the 'front end' if any.
In this way, it can be used for an incremental consolidation of muliple backends without consolidation of the front ends. I've found this architecture quite useful in hybrid (MSAccess-SQL) systems.
And, if you are running DAO (rather than ADO/ADP) you can retain the 'local' tables if you'd rather. This is useful for lookups that are relatively static and for temp tables.
One of the best features of SSMA4A is the detailed information it offers about issues that may conflict with a migration. This includes out of range date values, etc.
August 9, 2008 at 1:07 am
Hi Scott,
"if you are running DAO (rather than ADO/ADP) you can retain the 'local' tables if you'd rather"
So you'd have a backend containing a mix of "local tables" and links to tables in SQL Server? If so, and in view of what Bill said, "after the conversion you would have two sets of links - front-end to back-end and then back-end to MSSQL. Not the best arrangement." I'd move the 'local' tables to the frontend so that I could archive and discard the backend.
Also, can you please explain why having ADO would prevent you from retaining 'local' tables? I guess I'm mistaken here, but tables are just there, you create them from the Access database window by selecting the Tables object and clicking 'New', irrespective of whether code in the VBA editor references DAO or ADO libraries.
August 9, 2008 at 3:20 am
smunson,
Had the same thing happen to me. I tried using the Upsizer through Access 2003. It sucked. It did tables okay, but couldn't interpret simple select queries. It changed all the queries to some kind of code SQL wouldn't take.
SSMA saved me. It converted all queries and tables, and automatically ran the queries to make sure they worked with the tables. But the thing is, it can't migrate to earlier versions of SQL. It only works with SQL 2005.
August 9, 2008 at 3:35 am
WILLIAM MITCHELL (8/8/2008)
That is a good point. If you already have links to a back-end file, after the conversion you would have two sets of links - front-end to back-end and then back-end to MSSQL. Not the best arrangement.I suppose that you could try this (after making backups of course):
1. Convert the back-end
2. Delete all the links from the front-end to the back-end
3. Import the links from the back-end into the front-end
4. Run the front-end and verify that everything connects directly into MSSQL
5. Archive and discard the back-end
Does that make sense?
Bill
Hi Bill, just following your logic, and hopefully you could provide some clarification on your procedures. I've rewritten it below. My words are in bold
1. Convert the back-end using SSMA to SQL 2005
2. Delete all the links from the front-end .mdb to the back-end of the .mdb ? Where do these back-end tables reside on before the conversion step 1?
3. Import the links from the SQL back-end tables to the front-end Access
4. Run queries in Access and verify that everything connects directly into MSSQL
5. I don't understand this step. The back-end is now the SQL tables from the conversion in step 1.
Thanks for your help.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply