200 likes | 686 Vues
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*
E N D
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
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
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
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