Solved

Sql Server Migration Assistant

Posted on 2014-10-17
2
234 Views
Last Modified: 2014-10-23
I am thinking about using SSMA to migrate an access database to SQL Server;

Does (SSMA) SQL Server Migration Assistant migrate only tables?
0
Comment
Question by:metro156
2 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 40387302
I recommend use rather the migration tool from Microsoft like described in detail below:

http://support.microsoft.com/kb/237980
http://sqlmag.com/database-administration/15-steps-convert-access-data-sql-server
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 40387451
Ichohan,
Although I agree with much of your advice in the 15 steps, your instructions are very old and I've never run into some of the problems you outline.  In addition, the upsizing wizard should be allowed to convert the data.  This should not be done as a separate step.  That way you don't have to worry about foreign keys.   I also allow the wizard to create the database.  After the upsizing is complete, you can optimize the database and define any security.  I rarely have to run the wizard more than once because I've been through this before and so do all my clean up ahead of time.  But if I do have to run it again, I delete the previous attempt from the server and start fresh.

MS has removed the upsizing wizard from Access so as of A2013, it is no longer an option.  You are stuck with SSMA.  I ran into two problems my first time using SSMA.  The first was that SSMA installs a 64-bit version and assumes that is what you need when in fact, you will almost certainly need the 32-bit version since bit-wise you have to match your Office version NOT your Windows version.  The second problem was with SSMA's default choice for date fields.  What it chose was not recognized as a date by the SQL driver I was using so be careful or specifically choose the datetime format you want for consistency.

I also prefer DRI to Triggers and I have never had a problem with upsizing self-referencing tables.  I'm sure Icohan's problem was caused by using append queries rather than allowing the wizard to port the data.  

You can actually run append queries in SQL server that preserve identity column values.  You just have to set a "switch" first.   The following code snippet shows how.
        Set db = CurrentDb()
        Set qd = db.CreateQueryDef("")
            qd.Connect = "ODBC;" & strConnect
            qd.ReturnsRecords = False
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblAuditParms" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qReset011AppendAuditParms"
            qd.SQL = "SET IDENTITY_INSERT " & "tblAuditParms" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblListValues" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qReset016AppendListValues"
            qd.SQL = "SET IDENTITY_INSERT " & "tblListValues" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~

Open in new window

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now