Microsoft access to the database is a very popular and sufficient for most enterprise applications. But the purpose of the Microsoft Access for small businesses with multiple users. As it happens, and the growing demand of small incomes and slowly became the heart and soul of the company’s management systems.
The size and number of users increases, and the limitations of Microsoft Access quickly became clear. Response times are slow, routine stop, error messages is limited to a particular class, the function is not stable, and the problems of corruption and data security are common symptoms.
When the system must support access to more features, more records, and more users, it is necessary backend database systems such as the powerful SQL Server.
It is estimated that there is less data corruption
In Access, open the MDB file is the database directly. If what has been unexpectedly broken network connection or damage your computer or server, you are almost guaranteed that it will destroy the data.
SQL Server is different – run as a service. Access to a database front end does not have access directly to the database file current. If you turn off the server or network connection is suddenly broken, and is kept the integrity of data.
Enable the entry of the database data files recovery
SQL Server has a clear advantage to get that all transactions are saved (database updates, insertions and deletions) in a log file.
The log records all changes to data and information sufficient to cancel the amendments made during each transaction. In the event of system failure, and can use the log file to restore the data.
Support for more users at one time
Access supports a maximum of 255 concurrent users. But this is the theoretical limit, since there is no relationship to reality. In the real world, which traditionally the main performance problems with at least 10 users (and sometimes much less) in an attempt to simultaneously use the background of the Access database through the network.
SQL Server supports concurrent user base which is limited only by available system memory. Because it can not demand processing engine optimization and the ability to take advantage of multiple computers at one time, multiple processors, hard drives, and expansion to meet any requirement.
For larger databases support
Access size of the database supports a maximum of 2 gigabytes (GB) as well as the related tables. Although the use of related tables and allows theoretically can store much more data, and was usually performance issues when dealing with large amounts of records.
Consider upgrading the Access database and more than 100 MB database records in size.
SQL Server and on the other hand, has greatly increased storage capacity, enabling it to 1048516 terabytes of data to be stored efficiently on multiple devices. It also has a self-improvement, self-compacting feature, which makes database solutions is very strong and effective.
Performance and management
Limited access and the backup feature does not support a point in time restore. Access does not have the features of performance monitoring.
SQL Server on the other side provides a wizard that allows administrators to monitor and control the performance of the database. It also contains tools to automate data backup and data securely.
Upgrade to SQL Server
Upgrade the back end, Microsoft Access database to SQL Server is very easy.
Here are some issues that may need attention:
Change the schedule with non-numeric indexing or not indexing
Make sure that each table has a primary key
AutoNumbers make sure in the first column of the table
Reserved words have special treatment
Remove duplicates from the index table
Add timestamp to all the tables
The use of DRI (the declared referential integrity), and the players
Change all of NVARCHAR, ntext, and text fields to varchar and VARCHAR (max)
Change all this money to a decimal (8.2)
Change all the fields of history and time-to-date
Change table with the property sheet data subset of the car to None
Change field is true, yes to True / False / No
Most of these issues are easy to solve.