 |
Professional Database Developers |
| Aldex Software Ltd. | |
Upsizing
to SQL Server
|
|||||
|
Upsizing
to SQL Server
|
|||||
|
Introduction 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? |
 
|
|
|
|
|
Basic Migration 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. |
| 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. Dial-Up Speeds Reliability Improvements Timescales 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 |
|
What To Do Next... |
 Copyright ©2007, Aldex Software Ltd. |