Skip Navigation LinksHome > SSW Standards > Rules > SSW Rules to Upsizing your Access Database to SQL Server

Do you agree with them all? Are we missing some? Email us your tips, thoughts or arguments. Let us know what you think.


The Goal

Microsoft Access developers generally consider a move to SQL Server for performance, security and stability reasons. This process is known as upsizing and developers will find a number of key differences while migrating from Access to SQL Server. The goal is to:

  1. Find out what’s new and different when moving your backend from Access to SQL Server 2000
  2. Successfully migrate your Access database to SQL Server 2000 with no problems

  1. Do you know which is appropriate for you, Access or SQL Server?

    Question:

    My Company is considering starting development of a custom application in Access 97. Because they don't want to spend much money at this point, they want to use Access database at first and switch to SQL Server some time in the future. Could you tell me how difficult it is to switch between these two databases? Anything I could do now to make the conversion easier when it comes?

    Answer:

    Switching from Access (Jet) database to SQL Server isn't as simple as it seems. Yes, the dialects of SQL are similar, but they're not the same. More importantly, while SQL Sever is a true relational database, the Jet database is an Indexed Sequential Access Method (ISAM) database - also known as a flat file database. Although we could argue the differences between the two for hours, suffice it to say that, in many ways, they're exact opposites.

    Putting this difference in simple terms: What's fast in Access is slow in SQL Server, and vice versa. In Access, the fastest way to get to a record is to open up a table object, specify an index, and seek to the record. If you want to pull data from several tables, you can open multiple table objects and seek on them all, assembling the data programmatically.

    With SQL Server, you can't use a table object at all. While indexes certainly exit (and are vital), you don't normally concern yourself with them directly. The best way to pull data from several tables is using a join query. SQL Server is at its best when it pulls small record sets - whether from one table or joined from several. This technique is painfully slow in Access.

    More importantly, with SQL Server, there are far more features and protections for data. In a professionally configured SQL Server, the users (and their front-end applications) can never write directly to the tables at all. They access the data through views, and modify the data using stored procedures. None of these techniques are available in Access.

    So, put this thought to your money-saving employer: Are you actually saving any money by planning a conversion like this? In short term, maybe. In the long term, no. Yes, for the moment, you'll delay buying the hardware, software, and training needed to configure SQL Server. In the long term, you'll still have to buy all those things and convert the code. And you never know how these code conversions can go; it's a difficult thing to predict.

    Never underestimate the value of a known cost. You know now how much the SQL Sever is going to cost, and you're already planning the initial development costs. There's really no way to know for sure what the cost of conversion will be. If you stick with the known costs, at least you're certain of what you're in for. Conversion can eat up a lot of dollars (and time) before it's completed. It's an unknown, which makes it the worst cost of all.

  2. Do you know why to Upsize to SQL Server?

    This page is a summary of the SSW whitepapers, as upsizing and developers will find a number of key differences while migrating from Access to SQL Server. The 4 main reasons to upsize your database from Access to SQL Server 2000 are:

    1. Less Chance of Data Corruption
    2. Database Log Files Enable Data Recovery
    3. Support for More Concurrent Users
    4. Support for a Larger Database


    1. Less Chance of Data Corruption

    In Access, you open the MDB file directly. If, for example, the network connection is unexpectedly broken or a client or server machine shuts off without properly shutting down the application, there is a good chance your data will be corrupted.

    SQL Server, on the other hand, runs as a service – you do not have direct access to the file. Requests for data are managed by the SQL Server service, so if the server shuts down unexpectedly or the network connection is broken, the next request for data will not be fulfilled. Therefore the integrity of the data is maintained.
     

    2. Database Log Files Enable Data Recovery

    SQL Server has a distinct advantage over Access in that all transactions (database updates, insertions and deletions) are kept in a log file. This log records the changes to the data and enough information to undo the modifications (if necessary later) made during each transaction. These logs can also be used to recover your data in the event of system failure.
     

    3. Support for More Concurrent Users

    Access supports a maximum of 255 concurrent users, and as such is not a feasible enterprise-level data storage solution. In a real world environment it is common to experience major performance issues with as few as 20 users attempting to use the Access database simultaneously over a network.

    SQL Server supports a concurrent user base that is limited only by available system memory, and because of its optimized query processing engine and ability to simultaneously utilize multiple computers, processors and hard drives, it can scale to meet any enterprise requirements.

    4. Support for a Larger Database

    Access supports a maximum database size of 2GB plus linked tables. Although use of linked tables theoretically enables you to store much more data, it is common to experience performance issues due to the amount of data being processed. Generally you should consider upsizing your Access database(s) if the size of any one of them reaches 100MB.

    SQL Server, on the other hand, has vastly improved storage capabilities, allowing for 1,048,516 TB of data to be stored efficiently across multiple devices. It also has self-repairing and self-compacting features, making it a very robust database solution.

    For many more reasons to upsize your database see the SSW Whitepaper, What’s New and Different when Moving Your Backend from Access to SQL Server 2000.

  3. Do you know the Upsizing Tools?

    Read more about these and other tools and how to use them, in the SSW Whitepaper,  How to Migrate Your Backend from Access to SQL Server 2000.

  4. Do you know the 4 Steps to Migrating Your Backend from Access to SQL Server 2000?

    The Microsoft Upsizing Wizard that comes with Access converts most of your Access database and database objects into SQL Server. However, some features in Access are not supported by SQL Server and vice versa, so it is important that you manually analyze and rectify any potential issues that may arise before, during and after the migration process. It is also crucial that once converted, the resulting database manually be inspected to ensure all tables, data and relationships were correctly migrated.

    Follow these steps to ensure a smooth database migration:

    1. Estimate how long it will take - use the Sample Release Plan for upsizing (Upsizing Template)
       
    2. Configure a Copy of Your Live Access Database for Testing - The first step in upsizing is to configure a copy of your live Access database. You will first perform a test migration on the test copy. Once everything is functioning correctly you will perform the same steps on the live database.
       
    3. Migrate a Test Copy of Your Live Access Database - Now that you have made an offline copy of your Access database, you can perform a migration on it, ironing out and pre- and post-deployment issues that arise. This includes checking the performance of your forms, changing your DAO code to use ADODB, running the Upsizing Wizard, and fixing issues in the upsized SQL Server database.
       
    4. Perform Migration of the Live Database - Now that all deployment issues have been corrected on your test database, you can perform the migration on your live database. As you have already recorded and scripted any structural changes required, these steps should take less time to perform; it is simply a matter of running each script against the live Access database and upsized SQL Server database.

    For more details on these steps, see the SSW whitepaper, How to Migrate Your Backend from Access to SQL Server 2000.

  5. Do you automatically run scripts to keep your dev, test and production databases in sync?

    Once your Access back-end has been migrated to SQL Server, how do you keep your dev, test and production databases in sync? Back when everything was in Access, you could just copy the database to each server each time the schema changed. With SQL Server, on the other hand, the best way is to script the changes and run them using the supplied OSQL.EXE utility (good) or via the more user-friendly SSW SQL Deploy (better).

    1. Dev Server: (C:\Northwind\NorthwindApp2003.mdb linked to SQL Data) Make a note of all schema changes
    2. Test Server: (C:\Northwind\NorthwindApp2003.mdb linked to SQL Data) Manually implement schema changes
    3. Production Server: (C:\Northwind\NorthwindApp2003.mdb linked to SQL Data) Manually implement schema changes
    Bad: Manually implementing schema changes across your servers is tedious and prone to human error
    Figure: The OSQL command line utility comes with SQL Server and lets you batch deploy your SQL scripts
    Figure: SQL Deploy provides an easy-to-use and efficient Windows interface to deploy SQL scripts across your servers
  6. Do you know the 4 Steps to Migrating Your Reports to Reporting Services 2005?

    Microsoft SQL Server Reporting Services is a new reporting platform that has even more functionality and flexibility than Microsoft Access and Crystal developers have become accustomed to. In addition, it combines the speed, scalability, and manageability of centrally managed reporting, as reporting has moved from the client model to the client-server model. This brings all the advantages of Web-based applications.

    These are the main steps to migrating your Access reports into Reporting Services 2005:

    1. Import your reports into Reporting Services -  Use the built-in Import Reports feature to import the Access database reports into Reporting Services
       
    2. Fix any build warnings and errors - some features such as conditional formatting and parameters are not imported during the process and need to be recreated using expressions
       
    3. Create a shared data source - so that all your reports are configured to point to the same SQL database
       
    4. Test and deploy the reports - to your report server to enable them to be viewed over the web

    For more details on these steps, see the SSW whitepaper, How to Migrate Access Reports to SQL Server Reporting Services.

  7. Do you deploy your Reporting Services reports to your dev, test and production servers using the Deploy command?

    Once you have migrated your Access reports to Reporting Services, how do you keep your dev, test and production reports in sync? The best way is to use the Right-Click Deploy command in Visual Studio. You will need to change the Report Server each time in the report project properties.


    Figure: Set the URL for your dev, test or production server then deploy the report changes accordingly
  8. Do you have a nice Access UI?

    Now that you have your data in SQL Server and your Reports in Reporting Services, you need to decide whether or not to keep your forms in Access or move them to .NET.

    If you do decide to keep your forms in Access, keep in mind that just because you are using Access forms doesn't mean your forms should look old and stuffy. You can use XP-style icons and layout guidelines to fast forward your forms into the future. See how you can quickly and effectively improve the look and feel of your Access application.

  9. Do you know the 4 steps to Migrating your Forms to .NET?

    Access developers generally consider a move to a .NET front-end for performance, extensibility, security and stability reasons. This process is known as application conversion and developers will find a number of key differences while migrating from Access to the .NET environment. It is crucial that these differences are noted and appropriate action is taken to ensure a seamless and incident-free migration from Access to .NET Windows Forms.

    Migrating your forms to .NET involves 4 main steps:

    1. Create the Data Access Components -  In Access, binding controls to the database is as straightforward as setting the form’s RecordSource property to a query or table and then setting the ControlSource for any control to a field in the specified query or table. In .NET, forms are disconnected from the database. To bind controls to the database, projects are created that contain classes and objects to specifically handle database operations.
       
    2. Create the User Interface - The Visual Studio form designer is quite similar to the Access form designer. In Access, a new form can be added to a database by clicking the New button on the Forms page of the database window. In Visual Studio .NET, a form is added by right-clicking a project and selecting Add -> Add Windows Form…
       
    3. Bind the Form Controls - In Access, binding a form control is achieved by setting the Record Source property of a form to a query or table and setting the Control Source and/or Row Source of the control to a field of the form’s record source.

      To bind the controls in the a .NET form to your DataSets:

      a. Add references to the data access components
      b. Create instances of the DataSets
      c. Bind the form controls
      d. Add code to fill the DataSets
       
    4. Add the Form Functionality - In Visual Studio .NET, there is no expression builder or macro support: all form event code is written in functions behind the form. This is similar to the code builder option in Access.

    For more details on these steps, see the SSW whitepaper, How to Migrate Access Forms to .NET Windows Forms.

Acknowledgements

Adam Cogan


Benefit from our knowledge and experience!

SSW is the industry leader in Custom Software Development, Software Auditing & Developer Training.

Call us on +61 2 9953 3000 or email us for a free consultation

What does it cost? I’m not in Australia. Can you still help?