SQLServerCentral Article

Upsizing the Access Database into the SQL Server

,

Introduction

    Most of the small and medium scale organizations are used

to start their operations with Access 2000 due to many reasons. Main influence

behind this is the licensing fees. Other than the above use, more developers are

using Access as a prototype. Nevertheless, after a while there are many complains

from the access users about the corruption, data losses, etc etc.

Therefore, there are many reasons needed to be consider before selecting a

database system for your organization or for your duties.

Performance : Performance is the major factor need to be considered

when it comes to choosing a database. Obviously, SQL Server is far ahead on this as it is a

RDBMS not like a file flat as Access. SQL Server is designed for multiple users

in a network environment, whereas Access Jet Database was never designed for

multiple users. 

Robustness : If you are a heavy access user you must know how many

times that you need to repair access databases. I have experiences of loosing

data after repairing the access database. There is no such pains in database

systems like SQL Server.

Backup Management: As SQL Server offering a true database server

environment it has integrated backup, maintenance and management features like

scheduling that access lacks.

Security : Security is a major concern in the Access, where

as in SQL

Server has many facilities with users and user groups. In SQL Server even it has

the ability to integrate windows authentication into it.

More importantly access will give tremendous headaches when the mdb file grows into more

than 2 GB.

Above are just the few of the features the SQL Server has over the Access( I

am sure many of the DBAs and gurus would know these)

Data Types Access vs. SQL Server

When upsizing the database it is very essential to know equivalent data types.

Access

SQL Server

Yes/Nobit
Currencymoney, small money
Date/TimeDatetime, smalldatetime
Doublefloat
ReplicationIDuniqueidentifier
Long Integerint
AutoNumberidentity
OLE Objectimage
Memotext
Singlereal
Integersmallint
Bytetinyint

Methods of Upsizing

from the available methods of upsizing, following two methods are most commonly and more

frequently used.

1. DTS Import/Export Wizard.

        Above SQL Server wizard is very

much used due to its most features. In DTS, developers have found that it is

much flexible due to following reasons.

  •     Ability to transfer selected data into to the SQL

    Server.

  •     Ability to change designation table's format
  •     Ability to schedule the upsizing task so that

    developers do not have to configure and execute this again and again

Dispute the its capabilities the wizard won't automatically

copy primary keys from the data source to the SQL Server destination tables. You

have set those primary keys manually, or you

can simply set them in the finished destination tables by editing SQL Script at

the Column Mapping and Transforming screen of the DTS wizard.

There are a few other things that the DTS wizard will not do for you.

  •  The wizard won't maintain relationships which are needed to be

    recreated in SQL Server.

  •  The wizard won't enforce referential integrity rules set in the data

    source.

  • The wizard won't create auto identity property in the SQL Server which is

    supposed to be equivalent to Access AutoNumber

2. Access Upsizing Wizard (AUW)

    Above method may not be as quite frequent

as the DTS but another method of achieving our objectives. This wizard operates

inside Access to copy data to SQL Server. Unlike DTS which is common tool for

the data importing and exporting , AUW  is a customized tool to upsize

Access into SQL Server.

 Even though, there are few drawbacks with compared to

DTS there are few advantages also than that to the DTS. Most important feature

is, it does have an ability of transferring relationships , primary keys and auto

identity properties to the SQL Server. In addition, AUW also creates an link to

the SQL Server in Access database where it duplicates the data which are entered

in the SQL Server. Following User interface is provided at the Access to link

the SQL Server data in to Access

Main drawback that this AUW has is , that it does not have a facility to select

only the needed data to be transferred to the SQL Server. In addition, it

does not have a feature change the SQL Server create table script.

Conclusion

When you are given with an upsizing task, use either the

Access upsizing wizard or SQL Server's DTS. Any of the methods will provide you

with the specified job. However, you may find the DTS wizard less confusing and

more dependable than to its competitor Access Upsize Wizard. But it is a

decision which will be made by you for your benefit and easiness.

Rate

3.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3.5 (2)

You rated this post out of 5. Change rating