1 / 20

Migrating from Access to SQL Server

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*

Olivia
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Migrating from Access to SQL Server Simon Kingston, CSU / NPS NRGIS

  2. 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

  3. SQL Server 2005 Editions

  4. 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

  5. Differences Between Access and SSE

  6. 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?

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

  8. 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)

  9. 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

  10. 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

  11. 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

  12. Case Study – NCCN – John Boetsch

  13. 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?

  14. Database User Schema Table Index View Stored Procedure User-defined Function Trigger Objects in SSE / Managing the Database with SSMSE

  15. 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

  16. 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

  17. Do the exercise section on Sharing Data Let me know if you have any problems or questions during the exercise Exercise

  18. 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

  19. 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

  20. 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

More Related