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 (a.name like '%date%' OR     a.name like'%time%' OR a.name 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
      Else
      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.


Saturday, 2 March 2013

PowerShell Script to Find last Backup and Restore of Databases

The below script helps to find Database last backup and restore time on a given set of SQL server instances
 
1:  # Script to find Database last backup and restore time on a given set of SQL server instances  
2:  # Created by - Vinoth N Manoharan  
3:  # Version 1.1  
4:  # Date - 15/02/2013  
5:  # Script Help :-  
6:  #---------------  
7:  # Parameter 1 :- "-s" to run powershell for Single Instance of SQL  
8:  # "-f" to give filename with list of SQL Servers(Please provide SQL server name with instance details like <servername\instancename>  
9:  # Parameter 2 :- Server name(if -s is the parameter 1) or Filename with fully defined path(if -f is the parameter 1)  
10:  #  
11:  # Example1:- FindDBBkp.ps1 -s <SQLservername\instancename>  
12:  # Example2:- FindDBBkp.ps1 -f <filename with fully define path like c:\test.txt>  
13:  # 
 
14:  Clear-Host  
15:  #Write-Host "Num Args:" $args.Length;  
16:  $List =@()  
17:     if($args.Length -ne 2)  
18:     {  
19:        Write-Host "Incorrect Paramenter Count use either -s or -f to specify the servername/Serverlist"  
20:     }  
21:     elseif(($args[0] -eq "-s") -or ($args[0] -eq "-S"))  
22:     {  
23:        $computer = $args[1]  
24:        if($computer -ne $null)  
25:        {  
26:           $srt = "Server Name :- " + $computer  
27:           $srt  
28:           Echo "---------------------------"  
29:           [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |out-null  
30:           $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"  
31:           $dbsall = $srv.Databases  
32:           #$dbs = $dbsall|Where {!$_.IsSystemObject -and $_.IsAccessible}  
33:           $dbs = $dbsall|Where {$_.IsAccessible}  
34:           $bkp = $dbs|Add-Member -MemberType ScriptProperty -Name FullBkp -Value{if($this.LastBackupDate -eq "01/01/0001 00:00:00"){"No FULL backup"}else{$this.LastBackupDate}}-PassThru |  
35:               Add-Member -MemberType ScriptProperty -Name DiffBkp -Value {if($this.LastDifferentialBackupDate -eq "01/01/0001 00:00:00"){"No Diff backup"}else{$this.LastDifferentialBackupDate}} -PassThru|  
36:                Add-Member -MemberType ScriptProperty -Name LogBkp -Value{if($db.RecoveryModel.ToString() -eq "Simple"){"DB in Simple Recovery"}elseif($db.LastLogBackupDate -eq "01/01/0001 00:00:00"){"No Log backup"}  
37:                else{$this.LastLogBackupDate}}-PassThru|SELECTName,RecoveryModel,FullBkp,DiffBkp,LogBkp  
38:           $dt = new-object "System.Data.DataTable"  
39:           $cn = new-object System.Data.SqlClient.SqlConnection"server=$computer;database=msdb;Integrated Security=sspi"  
40:           $cn.Open()  
41:           $sql = $cn.CreateCommand()  
42:           $sql.CommandText = "select destination_database_name As dbname, restore_date, user_name As RestoredBy from msdb.dbo.restorehistory"  
43:           $rdr = $sql.ExecuteReader()  
44:           $dt.Load($rdr)  
45:           $cn.Close()  
46:           $List = @()  
47:           $List1 = @()  
48:           Foreach($bkprcd in $bkp)  
49:           {  
50:           $dbnamechk = $bkprcd.name  
51:           $restrcr = $dt|where {$_.dbname -eq $dbnamechk}  
52:           $restrdt = $restrcr.restore_date  
53:           $restrby = $restrcr.RestoredBy  
54:           if($restrdt -eq $null){$restrdt = "No Restore"}  
55:           if($restrby -eq $null){$restrby = "No Restore"}  
56:           $List1 = $bkprcd | Add-Member -MemberType NoteProperty -Name Restore_Date -Value $restrdt -PassThru| Add-Member -MemberType NoteProperty -Name Restore_By -Value $restrby -PassThru  
57:           $List += $List1   
58:           }  
59:           $List|ft -AutoSize  
60:        }  
61:    }elseif(($args[0] -eq "-f") -or ($args[0] -eq "-F"))  
62:     {  
63:        $filename = $args[1]  
64:        $computers = get-content $filename  
65:        $List = @()  
66:        foreach ($computer in $computers)  
67:        {  
68:        if($computer -ne $null)  
69:        {  
70:           $srt = "Server Name :- " + $computer  
71:           $srt  
72:           Echo "---------------------------"  
73:           "`n"  
74:           [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |out-null  
75:           $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"  
76:           $dbsall = $srv.Databases  
77:           #$dbs = $dbsall|Where {!$_.IsSystemObject -and $_.IsAccessible}  
78:           $dbs = $dbsall|Where {$_.IsAccessible}  
79:           $bkp = $dbs|Add-Member -MemberType ScriptProperty -Name FullBkp -Value{if($this.LastBackupDate -eq "01/01/0001 00:00:00"){"No FULL backup"}else{$this.LastBackupDate}}-PassThru |  
80:               Add-Member -MemberType ScriptProperty -Name DiffBkp -Value {if($this.LastDifferentialBackupDate -eq "01/01/0001 00:00:00"){"No Diff backup"}else{$this.LastDifferentialBackupDate}} -PassThru|  
81:                Add-Member -MemberType ScriptProperty -Name LogBkp -Value{if($db.RecoveryModel.ToString() -eq "Simple"){"DB in Simple Recovery"}elseif($db.LastLogBackupDate -eq "01/01/0001 00:00:00"){"No Log backup"}  
82:                else{$this.LastLogBackupDate}}-PassThru|SELECTName,RecoveryModel,FullBkp,DiffBkp,LogBkp  
83:           $dt = new-object "System.Data.DataTable"  
84:           $cn = new-object System.Data.SqlClient.SqlConnection"server=$computer;database=msdb;Integrated Security=sspi"  
85:           $cn.Open()  
86:           $sql = $cn.CreateCommand()  
87:           $sql.CommandText = "select destination_database_name As dbname, restore_date, user_name As RestoredBy from msdb.dbo.restorehistory"  
88:           $rdr = $sql.ExecuteReader()  
89:           $dt.Load($rdr)  
90:           $cn.Close()  
91:           $List = @()  
92:           $List1 = @()  
93:           Foreach($bkprcd in $bkp)  
94:           {  
95:           $dbnamechk = $bkprcd.name  
96:           $restrcr = $dt|where {$_.dbname -eq $dbnamechk}  
97:           $restrdt = $restrcr.restore_date  
98:           $restrby = $restrcr.RestoredBy  
99:           if($restrdt -eq $null){$restrdt = "No Restore"}  
100:           if($restrby -eq $null){$restrby = "No Restore"}  
101:           $List1 = $bkprcd | Add-Member -MemberType NoteProperty -Name LastRestore_Date-Value $restrdt -PassThru| Add-Member -MemberType NoteProperty -Name Restore_By -Value $restrby-PassThru  
102:           $List += $List1   
103:           }  
104:           $List|ft -AutoSize  
105:           "`n"  
106:        }  
107:        }  
108:     }else  
109:  {  
110:  Write-Host "Incorrect Paramenter, use either -s or -f to specify the servername/Serverlist"  
111:  }  

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.