Planning for Upsizing
In this section, you will look at how to plan for upsizing an Access database to SQL Server. This includes looking at how the available data types in SQL Server can be matched with those in Access, and the steps that you need to take to verify that your Access database can be correctly converted. This verification will save you a lot of time, regardless of what tool you choose to perform the conversion.
Text Data Types and UNICODE
The ASCII character encoding scheme has been in use for a long time, but is has an inherent restriction because it supports only 255 different characters in its code set. UNICODE, however, supports over 65,000 characters. The SQL Server data types for character data are CHAR, VARCHAR, and an older TEXT data type, which is equivalent to the Access Memo data type. CHAR is a fixed size and VARCHAR is varying size, so the Access Text data type matches the SQL Server VARCHAR data type.
The SQL Server UNICODE data types can be identified by the "N" prefix, such as NCHAR, NVARCHAR and NTEXT. If you choose to use UNICODE, this will double the required storage, which could have an impact on performance, but you gain a greater flexibility and support for international deployment of your application.
SQL Server has an 8 K page-the maximum column size is 8,000 bytes, and the maximum row size is 8,060 bytes (1 row per page). But SQL Server 2008 R2 does allow the combined length of character and certain other data types to exceed this maximum row size. When dealing with a Memo data type, you have the option of mapping this to an older TEXT or NTEXT data type, or, beginning with SQL Server 2005, you can use VARCHAR(MAX) or NVARCHAR(MAX) data type, which is a better choice. The VARCHAR(MAX) data type can hold up to two gigabytes of data, and is stored in-row, when possible, to improve performance.
Date and Time Data
The Access Date data type can hold dates from 1 January, 100 to 31 December, 9999. When no date is given and time-only data is provided, it uses the date 30 December, 1899 to indicate time-only data and hides the date information.
The corresponding data type in SQL Server is the DATETIME data type. This can hold dates from 1 January, 1753 to 31 December, 9999. When no date is provided, it defaults the date to 1 January, 1900 (which you might have spotted is different in Access). SQL Server also has a SMALLDATETIME data type.
Beginning with SQL Server 2008, there are some new date and time data types, they are: DATE, TIME, DATETIME2 and DATETIMEOFFSET. When you link Access to these data types, the DATE data type will appear as an Access Date data type, and can store dates in the range 1 January, 0000 to 31 December, 9999.
The most common choice of mapping is to map the Access Date data type to the DATETIME data type, but if you only had date data you could consider using the newer DATE data type.
If you use a map to the DATETIME data type, then because it is possible for your Access tables to contain badly entered dates-for example in the year 100-before you convert your database, you should verify that your date data is no earlier than 1 January, 1753 (this is one of the most common problems that applications can have and can cause data conversions to fail). If you find bad dates, and if the field is not a required field, you can set the value to NULL. If it is a required date, set the field to a safe value. The following VBA code in the Northwind_ProblemsAndFixes.accdb can be used to search through all the dates in your database, looking for bad dates and making corrections:
Sub modUpsizeTests_CheckDatesAndFix() ' Search through all tables with date fields ' Check that there are not tables containing dates ' which will not be accepted in SQL Server ' set illegal dates to NULL or a SafeDate Const strMinDate = "1 January 1753" Const strSafeDate = "1 January 1900" Dim db As DAO.Database Dim rst As DAO.Recordset Dim tdef As DAO.TableDef Dim fld As DAO.Field Dim strSQL As String Dim strCriteria As String Set db = CurrentDb For Each tdef In db.TableDefs If Left(tdef.Name, 4) <> "MSys" And _ Left(tdef.Name, 4) <> "USys" And _ Left(tdef.Name, 4) <> "~TMP" Then For Each fld In tdef.Fields If fld.Type = dbDate Then strCriteria = "WHERE " & _ "[" & fld.Name & "] < #" & _ strMinDate & "#" strSQL = "SELECT Count(*) FROM " & _ "[" & tdef.Name & "] " & strCriteria Set rst = db.OpenRecordset(strSQL, dbOpenDynaset) If rst(0) <> 0 Then Debug.Print tdef.Name, fld.Name If Not fld.Required Then strSQL = "UPDATE [" & tdef.Name & "]" & _ " SET [" & fld.Name & "] = NULL " & _ strCriteria Else strSQL = "UPDATE [" & tdef.Name & "]" & _ " SET [" & fld.Name & "] = #" & _ strSafeDate & "# " & strCriteria End If CurrentDb.Execute strSQL End If rst.Close End If Next End If Next End Sub
With TIME-only data, you need to be aware that as long as Access is used to continue entering this date, the default 30 December 1899 will be applied. But if you decide to write T-SQL in SQL Server, then ensure that you explicitly supply this date, because the default in SQL Server is 1 January 1900.
In this tutorial:
- Upsizing Access to SQL Server
- Planning for Upsizing
- Boolean Data
- IMAGE, VARBINARY(Max), and OLE Data
- Cycles and Multiple Cascade Paths
- Replicated Databases and Random Autonumbers
- Unique Index and Ignore Nulls
- Timestamps and Row Versioning
- Schemas and Synonyms
- Upsizing Wizard and the SQL Server Migration Assistant
- Upsizing to Use an Access Data Project
- Comparing Table Conversion in the Upsizing Wizard and SSMA
- Comparing Query Conversion in the Upsizing Wizard and SSMA
- Developing with Access and SQL Server
- Stored Procedures and Temporary Tables
- Handling Complex Queries
- Performance and Execution Plans
- SQL Server Profiler