Saturday, 23 March 2013

MS Access DB to SQL Server Migration Post Migration Issues

Last week I had to migrate a Access database to SQL Server database with Access Front end, Though the actual migration of data was pretty much straight forward using SQL Server Migration Assistant I had to do a lot of post migration corrections and taught might me a good idea to share, I won’t go much into how the data need to be migrated as SSMA does everything for you, the only thing you need to remember is if are going to use MS Access as frontend Application after the data is migrated to SQL server don’t forget to check the Link table option to link the tables in SQL server to the Application data source.

Right, now once the data is migrated the below are the post migration tasks you might need to take care.

1.       Most of the issues comes around the datetime datatype as the date formats are different in SQL server and MS Access, I usually run the following query to find all the datetime columns and verify the format.

select a.* from sys.columns a
Join sys.objects b ON a.object_id = b.object_id
where a.user_type_id = 42 and b.type = 'U'

2.       Some of the date columns might be recognized as nvarchar due to US/UK date formats and use of date() functions within MS Access tables, Remember dates are saved in #<date># format in MS Access; Eg:- #01-01/2013#

I use the following TSQL to find obvious date/Time columns, You can add few other strings based on your application. Remember this just obvious date columns assuming the naming conventions will have date or time string on all datetime columns there may some date columns with different column name which should be dealt case by case basis.

select a.* from sys.columns a
Join sys.objects b ON a.object_id = b.object_id
where a.user_type_id <> 42 and b.type = 'U' and ( like '%date%' OR like'%time%' OR like '%dob%')

3.       In the Front End MS Access Application change all Date input box format property to yyyy-mm-dd, and delete any format given in Input Mask property. This is because when you have the date column for the text box input in SQL Server the input mask and default Access format will send the date ad Varchar to SQL server and the Application will error out with string conversion failure.

4.       After the Access database was migrated to SQL there may be issues with some of the Access reports if it’s using Date() function in Access query due to the date format, we need to modify all date() function to format(date(),”yyyy-mm-dd”). If any reports uses hard coded date in their query change it using format function Eg:-Format(#01/10/2012#,"yyyy-mm-dd") 
5.       It is always a best practice to disable the Navigation buttons in all the forms and subforms after migration, Any search issued using the Navigation button will kill the front-end as it issues a very very bad designed query to SQL server using In-built Find command in MS Access, The search created 100 MB Trace file for one of my search for a record in 3000 rows SQL server table that because MS Access does not do row based search instead does a search for every element in the table, say there are 3000 rows and 10 columns the search runs for all 3000 * 10 rows to find a single record. Instead create search customized search functionality in the form using Filter function. Eg:- I used a button for customized search and created a filed in Click event with the following code,

Option Compare Database
Private Sub Command26_Click()
      Dim strIntBoxname As String
Dim strFilter As String
strIntBoxname = InputBox("Enter Input", _
      "Input Filter", "")
      If strIntBoxname = "" Then
      Me.FilterOn = False
      strFilter = BuildCriteria("[Col Name]", dbText, strIntBoxname)
      Me.Filter = strFilter
      Me.FilterOn = True
      End If
End Sub

6.       If you have subform in MS Access, use dataset table object instead of SQL query as some queries may not be recognized in SQL server, so it’s better to use object based approach. In my case my sub form did not populate or allow edit functionality to the user.

Hope the above step helps, There were not a lot of article in the web specific to these post migration issues.

No comments:

Post a Comment