Migrating from Access to SQL Server - PowerPoint PPT Presentation

Migrating from access to sql server l.jpg
Download
1 / 20

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*

Related searches for Migrating from Access to SQL Server

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

Download Presentation

Migrating from Access to SQL Server

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 l.jpg

Migrating from Access to SQL Server

Simon Kingston, CSU / NPS NRGIS


Learning goals agenda l.jpg

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


Sql server 2005 editions l.jpg

SQL Server 2005 Editions


Slide4 l.jpg

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


Differences between access and sse l.jpg

Differences Between Access and SSE


Why move from access to sql server l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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


Case study nccn john boetsch l.jpg

Case Study – NCCN – John Boetsch


Adp or linked tables or something else l.jpg

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 l.jpg

Database

User

Schema

Table

Index

View

Stored Procedure

User-defined Function

Trigger

Objects in SSE / Managing the Database with SSMSE


Exercise15 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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


  • Login