Migrating from access to sql server
Download
1 / 20

Migrating from Access to SQL Server - PowerPoint PPT Presentation


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

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

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

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*

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


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


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?


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


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)


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


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

  • 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


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?


Database

User

Schema

Table

Index

View

Stored Procedure

User-defined Function

Trigger

Objects in SSE / Managing the Database with SSMSE


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


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


Do the exercise section on Sharing Data

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

Exercise


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


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


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
  • Login