Database Decisions: MS Access vs' SQL Server

1
Database DecisionsMS Access vs. SQL Server

• The DAD Group
• Tracy Scharer
• Lew Burrus
• Russ Dinsmore
• Tom Loach
• Alan Shook
• Tom Walsh
• itc-dad_at_virginia.edu

2
Overview

• Distinctions between Access and SQL Server
• Preparation for upsizing
• Upsizing walk through

3
Distinctions Architecture Options

• ACCESS has an embedded front end
• ACCESS Monolithic - data and presentation
  together in a single MDB file
• Common configuration for personal applications
• ACCESS with split data and presentation
• Common configuration for small departmental
  applications
• Prerequisite for upsizing

4
Distinctions Architecture Options

• SQLServer makes you choose (and configure) the
  front end
• SQLSERVER with ACCESS Front end
• Linked Tables (JET engine)
• ACCESS Project (ADP with Bound Forms)
• ACCESS with unbound forms
• SQLServer with VB, ASP or other VISUAL Studio
  Development Tools
• ADP enables SQL Server features. The time to
  learn it is well spent.

5
Distinctions Administration

• ACCESS Administration is embedded in the ACCESS
  application
• DBO and DBA are one job
• SQLServer uses Enterprise Manager for
  administration and configuration management
• DBO and DBA are separated
• central management offered by ITC Microsystems
• SQLServer provides
• event logging
• messaging and performance monitoring
• capacity planning tools

6
Distinctions Security

• SQLServer will enforce better SECURITY Model
• ACCESS has a thin barrier based on Windows File
  System
• Weakly attached to the OS security subsystem
• OS Security at DB level
• Field level security implemented thru internal
  security methods and queries
• SQL is more versatile, convenient and effective
• Audit trails available
• Integrated with NT Authentication (at table
  level)
• Object level security can be implemented through
  OS Security

7
Distinctions Data Integrity

• An Access DB is a file within the file system
• Recovery from file copy (snapshot) no
  transaction level recovery
• Inadvertent corruption--(opening an ACCESS DB
  with a foreign application such as WORD, WORDPAD
  )
• SQLServer Tables are specialized entities
  manipulated exclusively by SQL Server
• Protected and hidden from anything but SQLServer
• Recovery from transaction log to any point in
  time

8
Distinctions Performance

• ACCESS intended to support personal or small
  departmental applications
• lt15-20 Users
• Low transaction rates with few conflicts
• Limited Growth
• DB Size lt 2GB

9
Distinctions Performance

• SQLServer intended to support Departmental or
  Enterprise Applications
• 1000s of Users
• Higher Transaction rates
• Multiple requests for same row
• Large DB can be TERABytes
• TRANSACT-SQL enforces standards
• Provides scalability features such as
  multiprocessor configurations, DB Replication

10
Distinctions Performance

• SQLServer is no magic bullet --- good design the
  key not platform selection

11
To upsize or not to upsize

• Number of concurrent users and how they use the
  database
• Amount of data
• Server environment
• Scalability
• Security models
• Friendly vs. Robust
• Performance
• Data corruption
• Programming advantages
• Database administration
• Stored procedures improve performance
• The wizard does not rewrite queries.

12
Upsizing-Preparation

• Data check
• Bad dates range check
• Indices (Unique index required)
• Data integrity checks are more strict
• Indexed fields cannot be NULL
• Cannot have or in table names
• Updateable queries may not propagate (multi-join
  tables a problem)

13
Upsizing - Preparation

• ACCESS Front end VBA Functions must be
  translated
• Query criteria, aggregate expressions, grouping,
  sorting validation rules default values all must
  be examined by hand

14
Upsizing - Preparation

• Field relationships more strictly enforced (e.g.,
  size conflicts between key/foreign key pair will
  cause upsize to fail)
• Naming conventions
• Spaces in names are not a good idea

15
Running the Wizard

• Repeat this process as necessary
• Plan for downtime for final upsizing
• Wizard creates a NEW Instance of tables each run
• Recommend dropping tables each time

16
Running the Wizard

• Repeat whatever corrections you discover after
  final cutover
• Reestablish Relationships
• Re-verify front end operation
• Correct table ownership from login id to DBO
• Data verification
• Permissions and role definitions (within
  SQLServer)

17
Demo--
18
Two Helpful References

• Microsoft Access Developers Guide to SQL Server,
  Authors Mary Chiman and Andy Baron Publisher
  SAMS
• Professional SQL Server Development with Access
  2000, Author Rick Dobson Publisher WROX

19
Where to Get More Information

• 1. Microsoft Access Developer's Guide to SQL
  Server
• Authors Mary Chiman and Andy Baron.
  Publisher SAMS
• 2. Professional SQL Server Development with
  Access 2000
• Authors Rick Dobson, Publisher WROX