Before introducing the tutorial, let’s first learn about Access and SQL SERVER databases.
Introduction to Microsoft Office Access
Microsoft Office Access is an associated database management system released by Microsoft. It combines Microsoft Jet Database Engine and graphical user interface and is one of the system programs of Microsoft Office.
Microsoft Office Access Flaws
1. When the database is too large, the performance of the ACCESS database will generally decrease when it reaches about 100M! (For example: excessive number of visitors can easily cause IIS to die, consume too much server resources, etc.)
2. Various database problems are prone to occur due to the rapid database writing frequency.
3. ACCESS database security is not as safe as other types of databases.
4. When the ACCESS forum is big, it is easy to have database problems. When the forum database is above 50M, with about 50,000 posts, and about 100 online people, your forum basically spends time processing the database, and at this time, the database is likely to be slow.
The general symptom is that all pages involving the database suddenly run surprisingly slowly (the execution time reaches more than 5 seconds or even dozens of seconds), and the pages involving HTML and pure ASP operations are normal, and they suddenly recover after a while (about 10 minutes or more) (this problem sometimes occurs in my forum). At this time, you can use a general ASP probe to test it. If the server's computing time is normal, it is generally a database problem.
Introduction to MS SQL SERVER
SQL is the abbreviation of Structured Query Language in English, meaning structured query language. The main function of SQL language is to establish contacts and communicate with various databases. According to ANSI (American National Standards Association), SQL is used as the standard language for relational database management systems. SQL statements can be used to perform various operations, such as updating data in a database, extracting data from a database, etc. At present, most popular relational database management systems, such as Oracle, Sybase, Microsoft SQL Server, Access, etc., adopt the SQL language standard.
SQL Server is a relational database management system. It was originally developed by three companies, Microsoft, Sybase and Ashton-Tate, and launched the first OS/2 version in 1988. After Windows NT was launched, Microsoft and Sybase parted ways in the development of SQL Server. Microsoft ported SQL Server to Windows NT systems, focusing on developing and promoting the Windows NT version of SQL Server. Sybase focuses more on the application of SQL Server on UNⅨ operating systems.
Summary: For large databases, SQL SERVER is significantly better than ACCESS. Some websites may use ACCESS database for various reasons in the early stage, but after the continuous development of the website, ACCESS database has obviously been unable to meet the needs. So how to convert ACCESS database to SQL SERVER? Please read the following tutorial:
1. Microsoft Office Access 2003, and SQL Server 2000 must be installed first.
2. Back up the old database. After the backup is completed, use Access 2003 to open the MDB database. A warning will appear when opening it. Don’t pay attention to it (safety warning), press the Open key, and after opening it, press the toolbar - Database Utility - Convert the database - Convert it to 2002-2003 format, and convert the database to 2003 format.
3. After the conversion is completed, open it with Access 2003. After opening it, press the toolbar - Database Utility Tools - Upgrade Wizard - Create a New Database - Fill in the SQL database login name, password and the database to be created (prepared to convert to a new database), press the next step, press the " 》" key, press the next step, select all options, press the next step, select "No changes to the application", and press Finish.
4. Open SQL Enterprise Manager - Database - Select the corresponding database - Right-click all tasks - Generate SQL scripts - General - Display all - Write all object scripts - Determine (remember where to store).
5. Use Notepad to open the SQL script you just generated, and in the editing bar - replace - look up the content as "datetime" and replace it with "smalldatetime" and replace it all; after completion, in the editing bar - replace it with "nvarchar" and replace it with "varchar" and replace it all, save it after completion.
6. Open SQL Enterprise Manager - Database - Click this database to click on the newly created database, and then open the "SQL Query Analyzer - File - Execute it in the toolbar - SQL Query Analyzer - File - "SQL Scripts generated just now" - Query - Execute it, and then close the window.
7. Return to SQL Enterprise Manager - Database - Click this database to click on the newly created database, and then open the toolbar - Database Conversion Service - Import Data - Next - Data source "Microsoft Access" file name "Old Database" - Next - Next - Copy the table and view from the source data - Next - Select all - Next - Run now - Next - Complete.