-
Do you know which is appropriate for you, Access or SQL Server?
Question:
My Company is considering starting development of a custom
application in Access 97. Because they don't want to spend much
money at this point, they want to use Access database at first and
switch to SQL Server some time in the future. Could you tell me
how difficult it is to switch between these two databases?
Anything I could do now to make the conversion easier when it
comes?
Answer:
Switching from Access (Jet) database to SQL Server isn't as simple
as it seems. Yes, the dialects of SQL are similar, but they're not
the same. More importantly, while SQL Sever is a true relational
database, the Jet database is an Indexed Sequential Access Method
(ISAM) database - also known as a flat file database. Although we
could argue the differences between the two for hours, suffice it
to say that, in many ways, they're exact opposites.
Putting this difference in simple terms: What's fast in Access is
slow in SQL Server, and vice versa. In Access, the fastest way to
get to a record is to open up a table object, specify an index,
and seek to the record. If you want to pull data from several
tables, you can open multiple table objects and seek on them all,
assembling the data programmatically.
With SQL Server, you can't use a table object at all. While
indexes certainly exit (and are vital), you don't normally concern
yourself with them directly. The best way to pull data from
several tables is using a join query. SQL Server is at its best
when it pulls small record sets - whether from one table or joined
from several. This technique is painfully slow in Access.
More importantly, with SQL Server, there are far more features and
protections for data. In a professionally configured SQL Server,
the users (and their front-end applications) can never write
directly to the tables at all. They access the data through views,
and modify the data using stored procedures. None of these
techniques are available in Access.
So, put this thought to your money-saving employer: Are you
actually saving any money by planning a conversion like this? In
short term, maybe. In the long term, no. Yes, for the moment,
you'll delay buying the hardware, software, and training needed to
configure SQL Server. In the long term, you'll still have to buy
all those things and convert the code. And you never know how
these code conversions can go; it's a difficult thing to predict.
Never underestimate the value of a known cost. You know now how
much the SQL Sever is going to cost, and you're already planning
the initial development costs. There's really no way to know for
sure what the cost of conversion will be. If you stick with the
known costs, at least you're certain of what you're in for.
Conversion can eat up a lot of dollars (and time) before it's
completed. It's an unknown, which makes it the worst cost of all.
-
Do you know why to Upsize to SQL Server?
This page is a summary of the SSW whitepapers, as upsizing and
developers will find a number of key differences while migrating
from Access to SQL Server. The 4 main reasons to upsize your
database from Access to SQL Server 2000 are:
- Less Chance of Data Corruption
- Database Log Files Enable Data Recovery
- Support for More Concurrent Users
- Support for a Larger Database
1. Less Chance of Data Corruption
In Access, you open the MDB file directly. If, for example, the
network connection is unexpectedly broken or a client or server
machine shuts off without properly shutting down the application,
there is a good chance your data will be corrupted.
SQL Server, on the other hand, runs as a service – you do
not have direct access to the file. Requests for data are managed
by the SQL Server service, so if the server shuts down
unexpectedly or the network connection is broken, the next request
for data will not be fulfilled. Therefore the integrity of the
data is maintained.
2. Database Log Files Enable Data Recovery
SQL Server has a distinct advantage over Access in that all
transactions (database updates, insertions and deletions) are kept
in a log file. This log records the changes to the data and enough
information to undo the modifications (if necessary later) made
during each transaction. These logs can also be used to recover
your data in the event of system failure.
3. Support for More Concurrent Users
Access supports a maximum of 255 concurrent users, and as such is
not a feasible enterprise-level data storage solution. In a real
world environment it is common to experience major performance
issues with as few as 20 users attempting to use the Access
database simultaneously over a network.
SQL Server supports a concurrent user base that
is limited only by available system memory
, and because of its optimized query processing engine and ability
to simultaneously utilize multiple computers, processors and hard
drives, it can scale to meet any enterprise requirements.
4. Support for a Larger Database
Access supports a maximum database size of 2GB plus linked tables.
Although use of linked tables theoretically enables you to store
much more data, it is common to experience performance issues due
to the amount of data being processed. Generally you should
consider upsizing your Access database(s) if the size of any one
of them reaches 100MB.
SQL Server, on the other hand, has vastly improved storage
capabilities, allowing for 1,048,516 TB of data to be stored
efficiently across multiple devices. It also has self-repairing
and self-compacting features, making it a very robust database
solution.
For many more reasons to upsize your database see the SSW
Whitepaper,
What’s New and Different when Moving Your Backend from
Access to SQL Server 2000
.
-
Do you know the Upsizing Tools?
Read more about these and other tools and how to use them, in the
SSW Whitepaper,
How to Migrate Your Backend from Access to SQL Server 2000
.
-
Do you know the 4 Steps to Migrating Your Backend from Access to
SQL Server 2000?
The Microsoft Upsizing Wizard that comes with Access converts most
of your Access database and database objects into SQL Server.
However, some features in Access are not supported by SQL Server
and vice versa, so it is important that you manually analyze and
rectify any potential issues that may arise before, during and
after the migration process. It is also crucial that once
converted, the resulting database manually be inspected to ensure
all tables, data and relationships were correctly migrated.
Follow these steps to ensure a smooth database migration:
-
Estimate how long it will take -
use the
Sample Release Plan for upsizing
(Upsizing Template)
-
Configure a Copy of Your Live Access Database for Testing
- The first step in upsizing is to configure a copy of your live
Access database. You will first perform a test migration on the
test copy. Once everything is functioning correctly you will
perform the same steps on the live database.
-
Migrate a Test Copy of Your Live Access Database -
Now that you have made an offline copy of your Access database,
you can perform a migration on it, ironing out and pre- and
post-deployment issues that arise. This includes checking the
performance of your forms, changing your DAO code to use ADODB,
running the Upsizing Wizard, and fixing issues in the upsized
SQL Server database.
-
Perform Migration of the Live Database -
Now that all deployment issues have been corrected on your test
database, you can perform the migration on your live database.
As you have already recorded and scripted any structural changes
required, these steps should take less time to perform; it is
simply a matter of running each script against the live Access
database and upsized SQL Server database.
For more details on these steps, see the SSW whitepaper,
How to Migrate Your Backend from Access to SQL Server 2000
.
-
Do you automatically run scripts to keep your dev, test and
production databases in sync?
Once your Access back-end has been migrated to SQL Server, how do
you keep your dev, test and production databases in sync? Back
when everything was in Access, you could just copy the database to
each server each time the schema changed. With SQL Server, on the
other hand, the best way is to script the changes and run them
using the supplied OSQL.EXE utility (good) or via the more
user-friendly
SSW SQL Deploy
(better).
-
Dev Server:
(C:\Northwind\NorthwindApp2003.mdb linked to SQL Data) Make a
note of all schema changes
-
Test Server:
(C:\Northwind\NorthwindApp2003.mdb linked to SQL Data)
Manually implement schema changes
-
Production Server:
(C:\Northwind\NorthwindApp2003.mdb linked to SQL Data)
Manually implement schema changes
Bad: Manually implementing schema changes across your servers is
tedious and prone to human error
-
-
Figure: The OSQL command line utility comes with SQL Server and
lets you batch deploy your SQL scripts
-
-
Figure: SQL Deploy provides an easy-to-use and efficient Windows
interface to deploy SQL scripts across your servers
-
Do you know the 4 Steps to Migrating Your Reports to Reporting
Services 2005?
Microsoft SQL Server Reporting Services is a new reporting
platform that has even more functionality and flexibility than
Microsoft Access and Crystal developers have become accustomed to.
In addition, it combines the speed, scalability, and manageability
of centrally managed reporting, as reporting has moved from the
client model to the client-server model. This brings all the
advantages of Web-based applications.
These are the main steps to migrating your Access reports into
Reporting Services 2005:
-
Import your reports into Reporting Services
- Use the built-in
Import Reports
feature to import the Access database reports into Reporting
Services
-
Fix any build warnings and errors
- some features such as conditional formatting and parameters
are not imported during the process and need to be recreated
using expressions
-
Create a shared data source
- so that all your reports are configured to point to the same
SQL database
-
Test and deploy the reports
- to your report server to enable them to be viewed over the web
For more details on these steps, see the SSW whitepaper,
How to Migrate Access Reports to SQL Server Reporting Services
.
-
Do you deploy your Reporting Services reports to your dev, test
and production servers using the Deploy command?
Once you have migrated your Access reports to Reporting Services,
how do you keep your dev, test and production reports in sync? The
best way is to use the Right-Click Deploy command in Visual Studio.
You will need to change the Report Server each time in the report
project properties.
-
-
Figure: Set the URL for your dev, test or production server then
deploy the report changes accordingly
-
Do you have a nice Access UI?
Now that you have your data in SQL Server and your Reports in
Reporting Services, you need to decide whether or not to keep your
forms in Access or move them to .NET.
If you do decide to keep your forms in Access, keep in mind that
just because you are using Access forms doesn't mean your forms
should look old and stuffy. You can use XP-style icons and layout
guidelines to fast forward your forms into the future. See how you
can quickly and effectively
improve the look and feel of your Access application
.
-
Do you know the 4 steps to Migrating your Forms to .NET?
Access developers generally consider a move to a .NET front-end
for performance, extensibility, security and stability reasons.
This process is known as application conversion and developers
will find a number of key differences while migrating from Access
to the .NET environment. It is crucial that these differences are
noted and appropriate action is taken to ensure a seamless and
incident-free migration from Access to .NET Windows Forms.
Migrating your forms to .NET involves 4 main steps:
-
Create the Data Access Components
- In Access, binding controls to the database is as
straightforward as setting the form’s
RecordSource
property to a query or table and then setting the
ControlSource
for any control to a field in the specified query or table. In
.NET, forms are disconnected from the database. To bind controls
to the database, projects are created that contain classes and
objects to specifically handle database operations.
-
Create the User Interface -
The Visual Studio form designer is quite similar to the Access
form designer. In Access, a new form can be added to a database
by clicking the
New
button on the Forms page of the database window. In Visual
Studio .NET, a form is added by right-clicking a project and
selecting
Add -> Add Windows Form…
-
Bind the Form Controls -
In Access, binding a form control is achieved by setting the
Record Source
property of a form to a query or table and setting the
Control Source
and/or
Row Source
of the control to a field of the form’s record source.
To bind the controls in the a .NET form to your DataSets:
a. Add references to the data access components
b. Create instances of the DataSets
c. Bind the form controls
d. Add code to fill the DataSets
-
Add the Form Functionality -
In Visual Studio .NET, there is no expression builder or macro
support: all form event code is written in functions behind the
form. This is similar to the
code builder
option in Access.
For more details on these steps, see the SSW whitepaper,
How to Migrate Access Forms to .NET Windows Forms
.