Resources‎ > ‎

Upsizing to SQL Server

Do you have an existing database application that is creaking at the seams? Perhaps an Access/JET application which has grown from a small database to become a business critical system? Maybe your database runs too slowly due to the sheer volume of data? Or have you started to experiencing reliability issues or Access corrupting? Or maybe you need to extend your database to the Internet?

 

If you are in this situation then you should consider migrating your database to a client/server architecture such as SQL Server. For a discussion about the differences between a File Sharing system such as Access / Paradox / FoxPro / FileMaker / etc. and a Client/Server architecture such as SQL Server please click here (or give us a call and we can discuss the options with you).

Most people want to know how much it will cost to migrate their database to SQL Server, how long it will take and how much faster it will run afterwards. Unfortunately these are all of the 'how long is a piece of string' type of question. We broadly divide migrating a database to SQL Server into three main categories: Basic Migration, Pragmatic Conversion and Full Redesign.

Basic Migration

The simplest way to upsize your database to SQL Server is to convert and migrate the tables from their original format into SQL Server. With a simple Access database this can be a fairly straightforward procedure, Microsoft even provide a wizard to do this for you. However there are a number of problems to look out for!

The first issues that arise are all due to poor database design. Before upsizing you should make sure that the existing database structure is correct; that all tables have a Primary Key and that relationships between the tables have the same data type and size (no trying to join an integer with a real; which we have seen in more than one application!).

If you have used non-standard names for tables or fields, for example including hyphens, percent signs, semi colons or even spaces then this will inevitably cause issues. SQL Server can now cope with spaces in field names, but wherever they are used they need to be enclosed with field delimiters. Other characters, that are legal in Access, may not be allowed in SQL Server. To avoid such problems all tables and field names should consist just of the characters a-z, A-Z, 0-9 and the underscore character. If the original system was designed to Best Practice then this should already be the case; but less than a quarter of unconverted Access systems that we see are compliant! On a similar note there are certain words that should not be used as field names; Date is a common one (use something like OrderDate instead).

Field types and sizes will vary between the original database and SQL Server and this can cause subtle bugs in your application. For example on a recent conversion we experienced occasional rounding errors when calculating VAT. The original Access application stored the VAT rate (eg 0.175) in a 'Single' datatype and this was initially upsized to a SQL 'Real' datatype. However certain calculations, such a 5.00 x 0.175, gave results slightly adrift from the same calculation done in Access thus resulting in occasional discrepancies between the two systems. The problem was fixed by converting the VAT rate from a 'Real' to a 'Decimal (9,4)' data type (this data type does not exist in Access). Other data types that often cause problems are memo fields and Boolean (true/false) values which have fundament differences between Access/JET and SQL Server. For example a Yes/No field in Access can have a Null value, in SQL Server it must be 0 or 1. If you therefore migrate Access data with Null Boolean values to SQL Server this appears to work okay, however when you edit the record you will get an error message saying that you and another user are trying to modify the same record at the same time (to fix this first ensure that the SQL bit field has a default value and then run a query to set the bit values that are Null to 0 or 1).

Dates always cause problems. SQL Server is much stricter on dates than Access is. If you have strict validation on your date entry then you may be okay; but 90% of Access applications that we upsize have dates such as 3rd Feb, 206 (instead of 2006). Data issues such as these must be corrected prior to upsizing as SQL Server will not accept them and the upsize will fail.

One of the nastier problems is the way that Autonumbers are handled. In Access a new Autonumber is assigned as soon as you create a new record, in other words BEFORE it has been saved. SQL Server on the other hand only generates a new Identity value (SQL Server's equivalent to an Autonumber) AFTER the record is saved. This may sound esoteric but if, for example, you are using a sub-form linked to a main form to enter data, such as Order Header and Order Details, and you are using Autonumbers, then your data entry will probably fail under SQL Server. This normally means that the logic for the form/subform needs to be rewritten!

Data access mechanisms (eg DAO, ADO) work differently and may need modifying to work with SQL Server.

However for straightforward databases which do not have complicated forms or lots of VBA code then Basic Conversion, ie just converting the tables across to SQL Server, may not be too difficult and gives you one of the main prizes; the data security of SQL Server.

Pragmatic Conversion

Basic Migration is fine, but often does not give you any performance increase and may actually decrease performance. The next step up from Basic Migration is Pragmatic Conversion where we migrate the tables to the server then redesign the main parts of the system to work correctly in a client/server architecture.

The main aim here is to reduce network traffic and hence increase performance since sending data over the network is the main bottleneck in most database systems. To achieve this we need to ensure that data filtering and updating occur at the server rather than at the client and there are several means by which this can be accomplished. 

Pass Through Queries. As the name suggests these are passed directly to the server where they are executed. Because of this, they have to be written in SQL Server syntax, not Access syntax (for example dates have to be formatted as SQL Server dates, the wildcard character is the % rather than the *, etc). Pass though queries are very useful but they suffer from the major limitation of being read only! 

Functions in Queries. Many Access queries are not just SQL statements but also include Access functions such as IIF and NZ. SQL Server does not understand these functions so it passes ALL the data that may be involved back to Access to sort out at the client end. Similar issues arise with Access specific SQL enhancements, Date manipulations, User Defined functions, etc. Instead of the half a dozen records you think you are retrieving you could instead be passing hundreds of thousands of records over your network! Often it can be difficult to tell what is happening and which queries are causing a problem; in this situation SQL Server's Profiler is invaluable to let you see the actual calls that Access/JET is making to SQL Server.

Disambiguate References . If a query refers to a local variable, for example the value of a combo box on a form, then SQL Server will be unable to resolve the query and may pass the contents of all the tables involved back to the client to sort out; the reverse of what we are trying to achieve! References therefore need to be resolved to actual values prior to being sent to the server.

Redesign Continuous Forms. One of the big issues in migrating from Access to SQL Server is inherent in the way that most Access databases are designed. The majority of Access systems are designed with continuous forms which is, after all, one of the main strengths of Access! However this design architecture is the exact opposite of the way that a correctly written client/server system should be designed; ie to retrieve only a small number of records at a time. Often there is not much that can be done in this situation without major effort but the redesign of a few continuous forms that are frequently used can have a significant effect on the overall performance.

Migrate Logic to the Server. Some processing, especially that which is close to the data, should be moved out of the client and into Stored Procedures on the server. For example say you want to look at all orders placed by each customer and then give each of them a variable discount, taken from a look-up table, based upon their order total over the past twelve months. With Access/JET this would involve a number of trips to the server as well as the dragging of several tables over the network. With a Stored Procedure the whole thing could be done on the server and would simply require one call over the network (and no tables being passed). The correct use of Stored Procedures can make an enormous difference to both the performance of the system and to amount of network traffic generated! In a new SQL Server system we usually end up with hundreds of Stored Procedures; with a Pragmatic Conversion this would be prohibitively expensive so we only convert those areas that will have the most impact.

To give you an example, we converted an Access / JET retail application to Access / SQL Server. Before the conversion it was taking about 30 seconds to retrieve the price from the JET database for each item scanned at the checkout. Needless to say, this was a very slow and frustrating experience for both customers and checkout staff. Redesigning the forms and creating the Stored Procedures took about a week but the time dropped to 1/10 of a second, a 300 fold increase!

Full Redesign

The final option is to redesign the system to work throughout using a proper client/server architecture. This may also involve adding new functionality to the application, or enabling it to work over the Internet. If looking at a full redesign then the development tool used to create the front end should also be re-evaluated; Access can make a reasonable front end for SQL Server in LAN situations but is less suitable for low bandwidth or intermittent connections such as the Internet, Wireless LANs, WANs, remote dial up, etc. For most situations we therefore usually advise the use of .NET clients such as VB.NET, ASP.NET or C#.NET.

Optimization

After going through the upsize process the last stage is to optimise the application, concentrating especially on areas where you are having performance issues and on the screens that are used frequently. Optimization can also be done on an existing SQL Server database that is experiencing speed problems.

Optimization can include a wide range of options including identifying problems using SQL Profiler, modifying stored procedures and UDFs (User Defined Functions), adding, deleting and modifying indexes, changing what is used for the clustered index, adjusting data types, altering client side logic, migrating processing from the client, etc., etc.

Guesstimated Timescales/Costs/Improvements/etc.

Speed Improvements

Overall speed improvements are very difficult to predict as it all depends upon how slow the system is to start with and on how big the data tables are! On most Access systems we would expect at least a three fold increase in overall speed but for some areas it can be literally hundreds of times faster! Where the application has been fully upsized to SQL Server we would expect most forms to open within a second or two (ie performance is not usually an issue).

Dial-Up Speeds

A system that has been properly designed for low bandwidth situations (and this probably means a Full Redesign rather than a Pragmatic Conversion) should operate at only slightly slower speeds over a dial-up than it does over a LAN. For example it may take two seconds to open a form instead of half a second but the application should still remain quite usable. Certainly most well-written client/server applications should be usable over a broadband Internet connection.

Reliability Improvements

It is very rare to experience crashes or corruption of SQL Server databases and reliability is at least several orders of magnitude better than Access/JET. With Access/JET a catastrophic failure usually means that you must fall back to the previous night's backup and hence lose the last day's data. With a correctly set-up SQL Server backup regime we can usually contain data loss to a maximum of 10 minutes (and frequently with no data loss at all). More advanced techniques, such as Custom Log Shipping or Mirroring can also prevent data loss even if the server catches fire and destroys all the hard disks. Failover support can also be implemented to swap to a second server if the original server goes down, but this is an expensive solution only suitable for those with deep pockets.

Timescales

A Basic Migration might take anything from 1 day to 4 weeks. 1 week would be a good average.
A Pragmatic Conversion may take anything from 2 weeks to 3 months. 1-2 Months would be a good average.
A Full Redesign may take anything from 2 months to 6 months plus. 3-4 months would be a good average.

Costs

'Typical' costs are :
Basic Migration from £3,000.
Pragmatic Conversion from £10,000-£15,000
Full Redesign ......?

All of these figures are merely indicative to give you some idea of what to expect and could vary significantly from those shown. Actual figures will depend upon the specific database being converted, it's size & complexity and the environment in which it is running.

Conclusion

There is no simple answer if you wish to know what will be involved in upsizing your database to SQL Server. It all depends..... upon why you are upsizing.... what your existing computing infrastructure is like.... what your future plans are.... how much money you have in the budget and of how much business advantage will be gained by converting.