Migrating Access data to SQL Server is commonly done so data can be available in the browser either through Internet or Intranet connections. IT professionals want to provide their colleagues access to company tracking information, and often the Web is the most accessible place for others to find that content.
Access utilizes a File Server design while SQL Server employs a Client/Server approach. This is a fundamentally different architecture. When the data is moved to SQL Server, workers can still connect to it through link tables, but IT professionals can begin to build custom ASP.NET Web forms and SQL Server reports for parts of the application that need broader visibility inside the organization.
Take advantage of SQL Server and upsize from Microsoft Access for performance, security, and reliability reasons. The four main reasons people tend to upsize to a client/server approach are:
- Increased scalability
- Data Integrity
- Maximum Database Size
Scalability is often a primary motivator for upsizing. Scalability is defined as the capability of an application to operate in an acceptable manner as the number of users exhausting the application increases. Access, with the Jet database engine, is not a scalable solution and can support up to a max of 255 concurrent users, however this limit is theoretical rather than practical. Above this level performance starts to degrade as more users are added, not being a sustainable approach for enterprise level data storage.
SQL server can support hundreds or even thousands of concurrent users without any significant performance degradation due to its optimized query processing engine and ability to simultaneously utilize multiple computers, processors and hard drives. This approach can scale to meet any enterprise level requirements.
Using SQL Server provides less chance for data corruption. SQL runs on as a service, which means it does not connect directly to files, instead requests of data are managed by the SQL server service, so in the case where network connection is broken the next request for data is not fulfilled maintaining secure data integrity. Access on the other hand, is directly linked to the MDB file so if the sever happens to go down or there is a loss of network connection chances are likely that your data will be corrupted. SQL Server also keeps all database updates, insertions and deletions in a database log file which can be used to recover your data in the event of a system failure.
Access uses the file share-based Jet database engine, unlike Microsoft SQL Server, file share databases are not optimized for large datasets. For instance, an Access query that needs to provide a total of 12,000 orders, needs to pull all 12,000 orders across the network, do the computation locally, and then provide the total.
In SQL Server the same query is handled directly by the server and only the result is returned to the client application. Access cannot scale well because it uses Jet or MDAC for database management which is limited to run on a single CPU, whereas SQL Server can support multiple CPUs.
Maximum Database Size
Microsoft Access can support up to 2 GB of data in addition to linked tables. The use of linked tables is supposed to enable much greater data storage, however it is common to experience performance issues due to the amount of data being processed. Over 100MB of linked tables should typically call for an upsize to SQL Server which supports 1 Terabyte or greater of data. With improved storage capabilities, the client/server approach allows data to be stored efficiently across multiple devices. It also has self-repairing and self-compacting features for a robust database solution.
Drawbacks are an increased deployment cost and a more complex support environment. For small workgroups of up to a dozen users on a Local Area Network with modest data requirements (no more than 50,000 records) and without ultra-high reliability requirements then Access is probably your best bet. Outside of these parameters you should probably look to a client/server solution such as SQL Server.