Microsoft Office Tutorials: Migrate an access database to sql server

We all have limits, and an Access database is no exception. For example, an Access database has a size limit of 2 GB and can't support more than 255 concurrent users. So when it's time for your Access database to go to the next level, you can migrate to SQL Server. SQL Server (whether on-premises or in the Azure cloud) supports larger amounts of data, more concurrent users, and has greater capacity than the JET/ACE database engine. This guide gives you a smooth start to your SQL Server journey, helps preserve Access front-end solutions you created, and hopefully motivates you to use Access for future database solutions. The Upsizing Wizard was removed from Access in Access 2013, so now you can use the Microsoft SQL Server Migration Assistant (SSMA). To successfully migrate, follow these stages.

The stages of database migration to SQL Server

Before you begin

The following sections provide background and other information to help you get started.

About split databases

All Access database objects can either be in one database file, or they can be stored in two database files: a front-end database and a back-end database. This is called splitting the database and is designed to facilitate sharing in a network environment. The back-end database file must only contain tables and relationships. The front-end file must only contain all other objects, including forms, reports, queries, macros, VBA modules, and linked tables to the back-end database. When you migrate an Access database, it's similar to a split database in that SQL Server is acting as a new back-end for the data that is now located on a server.

As a result, you can still maintain the front-end Access database with linked tables to the SQL Server tables. Effectively, you can derive the benefits of rapid application development that an Access database provides, along with the scalability of SQL Server.

SQL Server benefits

Still need some convincing to migrate to SQL Server? Here are some additional benefits to think about:

More concurrent users SQL Server can handle many more concurrent users than Access and minimizes memory requirements when more users are added.
Increased availability With SQL Server, you can dynamically backup, either incremental or complete, the database while it's in use. Consequently, you do not have to force users to exit the database to back up data.
High performance and scalability The SQL Server database usually performs better than an Access database, especially with a large, terabyte-sized database. Also, SQL Server processes queries much faster and efficiently by processing queries in parallel, using multiple native threads within a single process to handle user requests.
Improved security Using a trusted connection, SQL Server integrates with Windows system security to provide a single integrated access to the network and the database, employing the best of both security systems. This makes it much easier to administer complex security schemes. SQL Server is the ideal storage for sensitive information such as Social Security numbers, credit card data, and addresses that are confidential.
Immediate recoverability If the operating system crashes or the power goes out, SQL Server can automatically recover the database to a consistent state in a matter of minutes and with no database administrator intervention.
Usage of VPN Access and Virtual Private Networks (VPN) don't get along. But with SQL Server, remote users can use still use the Access front-end database on a desktop and the SQL Server back-end located behind the VPN firewall.
Azure SQL Server In addition to the benefits of SQL Server, offers dynamic scalability with no downtime, intelligent optimization, global scalability and availability, elimination of hardware costs, and reduced administration.

Choose the best Azure SQL Server option

If you are migrating to Azure SQL Server, there are three options to choose from, each with different benefits:

Single database/elastic pools This option has its own set of resources managed through a SQL Database server. A single database is like a contained database in SQL Server. You can also add an elastic pool, which is a collection of databases with a shared set of resources managed via the SQL Database server. The most commonly used SQL Server features are available with built-in backups, patching, and recovery. But there is no guaranteed exact maintenance time and migration from SQL Server might be hard.
Managed instance This option is a collection of system and user databases with a shared set of resources. A managed instance is like an instance of the SQL Server database that is highly compatibility with SQL Server on-premises. A managed instance has built-in backups, patching, recovery, and is easy to migrate from SQL Server. However, there are a small number of SQL Server features that are not available and no guaranteed exact maintenance time.
Azure Virtual Machine This option allows you to run SQL Server inside a virtual machine in the Azure cloud. You have full control over the SQL Server engine and an easy migration path. But you need to manage your backups, patches, and recovery.

For more information, see Choosing your database migration path to Azure and Choose the right SQL Server option in Azure.

First steps

There are a few issues you can address up front that can help streamline the migration process before you run SSMA:

Add table indexes and primary keys Make sure each Access table has an index and a primary key. SQL Server requires all tables to have at least one index and requires a linked table to have a primary key if the table can be updated.
Check primary/foreign key relationships Make sure these relationships are based on fields with consistent data types and sizes. SQL Server does not support joined columns with different data types and sizes in foreign key constraints.
Remove the Attachment column SSMA doesn't migrate tables that contain the Attachment column.

Before you run SSMA, take the following first steps.

Close the Access database.
Make sure that current users connected to the database also close the database.
If the database is in .mdb file format, then Remove user-level security.
Back up your database. For more information, see Protect your data with backup and restore processes.

Tip Consider installing Microsoft SQL Server Express edition on your desktop which supports up to 10 GB and is a free and easier way to run through and check your migration. When you connect, use LocalDB as the database instance.

Tip If possible, use a stand-alone version of Access. If you can only use Microsoft 365, then use the Access 2010 database engine to migrate your Access database when using SSMA. For more information, see Microsoft Access Database Engine 2010 Redistributable.

Run SSMA

Microsoft provides Microsoft SQL Server Migration Assistant (SSMA) to make migration easier. SSMA mainly migrates tables and select queries with no parameters. Forms, reports, macros, and VBA modules are not converted. The SQL Server Metadata Explorer displays your Access database objects and SQL Server objects allowing you to review the current content of both databases. These two connections are saved in your migration file should you decide to transfer additional objects in the future.

Note The migration process can take some time depending on the size of your database objects and the amount of data that must be transferred.

To migrate a database using SSMA, first download and install the software by double-clicking the downloaded MSI file. Make sure you install the appropriate 32 or 64 bit version for your computer.
After installing SSMA, open it on your desktop, preferably from the computer with the Access database file.

You can also open it on a machine that has access to the Access database from the network in a shared folder.
Follow the beginning instructions in SSMA to provide basic information such as the SQL Server location, the Access database and objects to migrate, connection information, and whether you want to create linked tables.
If you are migrating to SQL Server 2016 or later and want to update a linked table, add a rowversion column by selecting Review Tools > Project Settings > General.

The rowversion field helps avoid record conflicts. Access uses this rowversion field in an SQL Server linked table to determine when the record was last updated. Also, if you add the rowversion field to a query, Access uses it to re-select the row after an update operation. This improves efficiency by helping to avoid write conflict errors and record deletion scenarios that can happen when Access detects different results from the original submission, such as might occur with floating point number data types and triggers that modify columns. However, avoid using the rowversion field in forms, reports, or VBA code. For more information, see rowversion.

Note Avoid confusing rowversion with timestamps. Although the keyword timestamp is a synonym for rowversion in SQL Server, you can't use rowversion as a way to timestamp a data entry.
To set precise data types, select Review Tools > Project Settings > Type Mapping. For example, if you only store English text, you can use the varchar rather than nvarchar data type.

Convert objects

SSMA converts Access objects to SQL Server objects, but it doesn't copy the objects right away. SSMA provides a list of the following objects to migrate so you can decide whether you want to move them to SQL Server database:

Tables and Columns
Select Queries without parameters.
Primary and Foreign keys
Indexes and Default values
Check constraints (allow zero length column property, column validation rule, table validation)

As a best practice, use the SSMA assessment report, which shows the conversion results, including errors, warnings, informational messages, time estimates for performing the migration, and individual error correction steps to take before you actually move the objects.

Converting database objects takes the object definitions from the Access metadata, converts them into equivalent Transact-SQL (T-SQL) syntax, and then loads this information into the project. You can then view the SQL Server or SQL Azure objects and their properties by using SQL Server or SQL Azure Metadata Explorer.

To convert, load, and migrate objects to SQL Server, Follow this guide.

Tip Once you have successfully migrated your Access database, save the project file for later use, so you can migrate your data again for testing or final migration.

Link tables

Consider installing the latest version of the SQL Server OLE DB and ODBC drivers instead of using the native SQL Server drivers that ship with Windows. Not only are the newer drivers faster, but they support new features in Azure SQL that the previous drivers don't. You can install the drivers on each computer where the converted database is used. For more information, see Microsoft OLE DB Driver 18 for SQL Server and Microsoft ODBC Driver 17 for SQL Server.

After you migrate the Access tables, you can link to the tables in SQL Server which now hosts your data. Linking directly from Access also provides you with a simpler way to view your data rather than using the more complex SQL Server management tools. You can query and edit linked data depending on the permissions set up by your SQL Server database administrator.

Note If you create an ODBC DSN when you link to your SQL Server database during the linking process, either create the same DSN on all machines that use the new application or programmatically use the connection string stored in the DSN file.

For more information, see Link to or import data from an Azure SQL Server Database and Import or link to data in an SQL Server database.

Tip Don't forget to use the Linked Table Manager in Access to conveniently refresh and relink tables. For more information, see Manage linked tables.

Test and revise

The following sections describe common issues you can encounter during migration and how to deal with them.

Queries

Only Select Queries are converted; other queries are not, including Select Queries that take parameters. Some queries may not completely convert, and SSMA reports query errors during the conversion process. You can manually edit objects that do not convert by using T-SQL syntax. Syntax errors may also require manually converting Access-specific functions and data types to SQL Server ones. For more information, see Comparing Access SQL with SQL Server TSQL.

Data types

Access and SQL Server have similar data types, but be aware of the following potential issues.

Large Number The Large Number data type stores a non-monetary, numeric value and is compatible with the SQL bigint data type. You can use this data type to efficiently calculate large numbers but it requires using the Access 16 (16.0.7812 or later) .accdb database file format and performs better with the 64-bit version of Access. For more information, see Using the Large Number data type and Choose between the 64-bit or 32-bit version of Office.

Yes/No By default, an Access Yes/No column is converted to an SQL Server bit field. To avoid record locking, Make sure the bit field is set to disallow NULL values. IN SSMA, you can select the bit column to set the Allow Nulls property to NO. In TSQL, use the CREATE TABLE or ALTER TABLE statements.

Date and Time There are several date and time considerations:

If the compatibility level of the database is 130 (SQL Server 2016) or higher, and a linked table contains one or more datetime or datetime2 columns, the table may return the message #deleted in the results. For more information, see Access linked table to SQL-Server database returns #deleted.
Use the Access Date/Time data type to map to the datetime data type. Use the Access Date/Time Extended data type to map to the datetime2 data type which has a larger date and time range. For more information, see Using the Date/Time Extended data type.
When querying for dates in SQL Server, take into account the time as well as the date. For example:
- DateOrdered Between 1/1/19 and 1/31/19 may not include all orders.
- DateOrdered Between 1/1/19 00:00:00 AM And 1/31/19 11:59:59 PM does include all orders.

Attachment The Attachment data type stores a file in Access database. In SQL Server, you have several options to consider. You can extract the files from the Access database and then consider storing links to the files in your SQL Server database. Alternatively, you can use FILESTREAM, FileTables, or Remote BLOB store (RBS) to keep attachments stored in the SQL Server database.

Hyperlink Access tables have hyperlink columns that SQL Server does not support. By default, these columns will be converted to nvarchar(max) columns in SQL Server, but you can customize the mapping to choose a smaller data type. In your Access solution, you can still use the hyperlink behavior in forms and reports if you set the Hyperlink property for the control to true.

Multivalued field The Access multivalued field is converted to SQL Server as an ntext field that contains the delimited set of values. Because SQL Server does not support a multivalued data type that models a many-to-many relationship, additional design and conversion work might be needed.

For more information about mapping Access and SQL Server data types, see Compare data types.

Note Multivalued fields are not converted and were discontinued in Access 2010.

For more information, see Date and time types, String and binary types, and Numeric types.

Visual Basic

Although VBA is not supported by SQL Server, note the following possible issues:

VBA functions in Queries Access queries support VBA functions on data in a query column. But Access queries that use VBA functions cannot be run on SQL Server, so all requested data is passed to Microsoft Access for processing. In most cases, these queries should be converted to pass-through queries.

User Defined Functions in Queries Microsoft Access queries support the use of functions defined in VBA modules to process data passed to them. Queries can be standalone queries, SQL statements in form/report record sources, data sources of combo boxes and list boxes on forms, reports and table fields, and default or validation rule expressions. SQL Server cannot run these user defined functions. You may need to manually redesign these functions and convert them to stored procedures on SQL Server.

Optimize performance

By far, the most important way to optimize performance with your new, back-end SQL Server is to decide when to use local or remote queries. When you migrate your data to SQL Server, you are also moving from a file server to a client-server database model of computing. Follow these general guidelines:

Run small, read-only queries on the client for quickest access.
Run long, read/write queries on the server to take advantage of the greater processing power.
Minimize network traffic with filters and aggregation to transfer only the data you need.

Optimize performance in the client server database model

For more information, see Create a pass-through query.

The following are additional, recommended guidelines.

Put logic on the server Your application can also use views, user-defined functions, stored procedures, calculated fields, and triggers to centralize and share application logic, business rules and policies, complex queries, data validation, and referential integrity code on the server, rather than on the client. Ask yourself, can this query or task be performed on the server better and faster? Finally, test each query to ensure optimal performance.

Use views in forms and reports In Access, do the following:

For forms, use an SQL view for a read-only form and an SQL indexed view for a read/write form as the record source.
For reports, use an SQL view as the record source. However, create a separate view for each report, so that you can more easily update a specific report, without impacting other reports.

Minimize loading data in a form or report Don't display data until the user asks for it. For example, keep the recordsource property blank, make users select a filter on your form, and then populate the recordsource property with your filter. Or, use the where clause of DoCmd.OpenForm and DoCmd.OpenReport to display the exact record(s) needed by the user. Consider turning off record navigation.

Be careful with heterogeneous queries Avoid running a query that combines a local Access table and SQL Server linked table, sometimes called a hybrid query. This type of query still requires Access to download all the SQL Server data to the local machine and then run the query, it does not run the query in SQL Server.

When to use local tables Consider using local tables for data that rarely changes, such as the list of states or provinces in a country or region. Static tables are often used for filtering and can perform better on the Access front-end.

For more information, see Database Engine Tuning Advisor, Use the Performance Analyzer to optimize an Access database, and Optimizing Microsoft Office Access Applications Linked to SQL Server.

Microsoft Office Tutorials

Saturday, August 7, 2021

Migrate an access database to sql server