migrating from access to sql server
Download
Skip this Video
Download Presentation
Migrating from Access to SQL Server

Loading in 2 Seconds...

play fullscreen
1 / 20

National Park Service template - PowerPoint PPT Presentation


  • 365 Views
  • Uploaded on

Migrating from Access to SQL Server. Simon Kingston, CSU / NPS NRGIS. Differences between Access and SQL Server Why Move from Access to SQL Server? Important Installation Options for SQL Server 2005 Express (SSE) How to Migrate Data from Access to SSE*

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'National Park Service template' - Olivia


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
migrating from access to sql server

Migrating from Access to SQL Server

Simon Kingston, CSU / NPS NRGIS

learning goals agenda
Differences between Access and SQL Server

Why Move from Access to SQL Server?

Important Installation Options for SQL Server 2005 Express (SSE)

How to Migrate Data from Access to SSE*

Access Data Project (ADP) vs. Linked Tables

Case Study: NCCN – John Boetsch

Objects in SSE / Managing the database with SQL Server Management Studio Express (SSMSE)*

Sharing Data with SQL Server Express*

Scripting the database with the SQL Server Database Publishing Wizard*

Questions/Convert your own Databases

Learning Goals/Agenda

*exercises

slide4
FREE!

Powerful database engine

Free management tool – SQL Server Management Studio Express

Integrates with Visual Studio Express

Can act as a subscriber in replication

Easy upgrade to other editions of SQL Server

SSE
why move from access to sql server
You have a multi-user database with concurrent users

You need to interchange data with other databases

You are having performance problems

You need better security

You have spotty network reliability

Why Move from Access to SQL Server?
installing sse
Required

.NET Framework 2.0

SQL Server 2005 Express Edition with Advanced Services*

Optional

SQL Server 2005 Express Edition Toolkit

SQL Server 2005 Samples

SQL Server 2005 Books Online

SQL Server Migration Assistant for Access*

SQL Server Database Publishing Wizard*

*required for today’s exercises

Installing SSE
installing sse continued
Key Installation Options

Not Everything is Installed by Default

Replication

Full Text Search

Connectivity Components

Software Development Kit

Management Studio Express

Authentication Mode

Windows Authentication or Mixed Mode

Collation Settings

SSE installs “secure by default”

Many features are turned off by default for security

Installing SSE (continued)
migrating data from access to sse
Upsizing Wizard in Access

SQL Server Migration Assistant (SSMA) for Access

Importing from full-blown version of SQL Server Management Studio

Migrating Data from Access to SSE
exercise
Start the exercise and go up to the end of the section on reviewing the upsized/migrated databases

Let me know if you have any problems or questions during the exercise

Exercise
access data project vs linked tables
Access Data Project vs. Linked Tables
  • when using Access 2000, 2002, or 2003 with SQL Server 2005 , you can’t make adds or changes to the SQL Server database objects from your ADP
adp or linked tables or something else
Start out using Linked Tables

Tends to be easier, esp. if you already have a front-end developed in Access

Can be optimized to reduce network traffic, but if records returned start to get too big, look into ADP

Use the SSMA for Access to migrate

If you really want client/server, look into ADP

If you’ve already got a front-end in Access that uses ADO

If you don’t need local tables

Use the Upsizing Wizard to migrate

Realize when it’s time to go with a web app.

Broad usage and you don’t want to distribute/install/support a client application on many desktops

ADP or Linked Tables or Something Else?
objects in sse managing the database with ssmse
Database

User

Schema

Table

Index

View

Stored Procedure

User-defined Function

Trigger

Objects in SSE / Managing the Database with SSMSE
exercise15
Start the exercise at the section on Creating Objects in SQL Server using T-SQL and the SSMSE Interface and stop at the section on Sharing Data

Let me know if you have any problems or questions during the exercise

Exercise
configuring sse to share data
SSE runs as a Windows Service

SQL Server Configuration Manager

Change networking protocol settings

Change SQL Service options

SQL Server Surface Area Configuration

Use this tool to enable, disable, start, or stop features, services, and remote connectivity

Configuring SSE to Share Data
exercise17
Do the exercise section on Sharing Data

Let me know if you have any problems or questions during the exercise

Exercise
scripting the database with the sql server database publishing wizard
Allows deployment of database to server by running script

Allows database structure to be preserved in version control with application code

Scripting the Database with the SQL Server Database Publishing Wizard
exercise19
Do the exercise section on Scripting the Database with the SQL Server Database Publishing Wizard

Let me know if you have any problems or questions during the exercise

Exercise
links
SQL Server 2005 Express Edition

SQL Server Migration Assistant for Access

SQL Server DB Publishing Wizard 1.1

Convert Jet SQL to T-SQL cheat sheet

Guide to Migrating from Access to SQL Server 2005

Optimizing Access Applications Linked to SQL Server

Transact-SQL (T-SQL) Reference

Links
ad