Converting from MS Access to a SQL Server solutionBy Justin Hostettler-Davies (c) 2008
Have you outgrown your custom-built MS Access application? Are you experiencing slow performance, error messages, security problems, and/or database corruption in Access? Does your MS Access application continually crash? www.gre-sqlserver-solutions.com can assist. There are many reasons to convert Microsoft Access database to SQL Server, such as those mentioned above - number of concurrent users and allowable size of the database, being two more. Some businesses use Access for its ease of use and licence costs etc but as businesses & organisations continue to grow; the need for scalability, security and improved performance grows too. Reasons many businesses eventually have to convert Access databases to SQL Server include the following four: User base is rapidly growing having a negative effect on networks! As the number of users increases, the file-sharing mechanism of an Access database may result in a slower performance and increased network traffic. The client/server environment of Microsoft SQL Svr is designed for a large number of users and minimizing network traffic. SQL Svr is capable of scaling to support thousands of simultaneous users Database size increasing. There�s no issue here with SQL Server as it can support databases of up to 2 terabytes in size (even more in SQL 2005). And that should be ample for 99% of us!! Increased security. Access is limited to security in terms of username / password on the database. It also is subject to Windows security on the file itself (as well as the folder it resides in). SQL Server has two authentication modes, and neither are much like Access security at all. You can use Windows Authentication, which allows you direct access to domain Users and Groups from within the interface. You can also use Mixed Mode, which allows SQL Server to maintain usernames and passwords (thereby negating the need for a domain or other Windows user/group maintenance). Once you have determined an authentication mode, users have three different levels of access into the database: login (at the server level), user (at the database level), and object permissions within each database (for tables, views, stored procedures, etc). You can also take advantage of server-wide roles too, allowing users to become �cloned�. Higher availability. SQL Server can be backed up and restored dynamically while the database is in use. Users do not have to close the database while the daily backup is performed. In addition, the process of transaction logging makes it possible to restore the database up to the point of failure.
What about the cost?Many people think that developing applications in MS Access is less expensive than MS SQL Server. Developing custom applications with MS Access CAN BE SIGNIFICANTLY MORE EXPENSIVE than developing with MS SQL Server.Developers tend to spend more time, more effort, and run into more headaches with MS Access, primarily because it's difficult to know what the customer is running, easy-to-use MS Desktop-like User Interfaces conventions are more difficult to construct in MS Access, and MS Access is significantly more difficult to deploy and control as a distributed business application without driving up IT support costs. Finally, even if you've invested the extra time and effort to overcome these limitations, the MS Access platform simply will not scale. SQL Server is more scalable (has a longer shelf-life). Since time is money, applications that are built on the right components end up costing LESS, and you�ll get MORE! Our advice to you is to stop looking at MS Access as the lowest cost/cheapest solution, just because it ships with every copy of Office Professional. It simply isn't true.
So how do you actually convert from Access to SQL Server?
I�m going to discuss three ways to convert an Access database to SQL Server.
MS Access Upsizing Wizard. There are various choices in the Upsizing Wizard for the user/programmer to decide how to upsize. It preserves the database structure, including data, index and default settings whilst maintaining table relationships and referential integrity after upsizing. One can just upsize tables or only upsize structure and leave anything else the same in the Access database, thus all the application will be the same as before. You can also upsize the structure and data to a new database in SQL Server and not do anything to the Access database. Alternatively, it is possible to upsize the Access database to a new or existing database in SQL Server and creates a new Access project (ADP). I�ve never personally used this method, as business needs haven�t require me to. However, when testing the use of the Upsizing Wizard to upsize the database, I got an overflow error messages because of out-of-date Office service packs etc, so soon got frustrated! If your objective it is to keep some applications in Access or avoid changing the connection to Access, using Upsizing Wizard is a good conversion method. However, you will not take full advantage of SQL Server. Hence you will have limited security, scalability and performance.Note � you may need to download MS Access Upsizing tools from Microsoft web site too. Caution here : - the Upsizing Wizard will move Access to SQL, but in most cases, the resulting SQL Server database needs a lot of work, and often you�ll find that you have to undo a lot of what the wizard automatically did. Data Transformation Services (DTS) If you / your company are already familiar with SQL Server than this option is ideal.Using Data Transformation Services (DTS) allows more control over the database. It helps transfer the Access tables and queries to SQL Server, and lets one perform custom transformations on the data. Transformations were required with us to deal with things like Date datatypes etcOne can change a data type or write a SQL query to conditionally import some rows when transferring. One disadvantage of using DTS is that the forms, modules and reports in Access will not be transferred to SQL Server, and not even relationships and indexes. The Access Upsizing Wizard will automatically migrate all rules and defaults that exist in a table to SQL Server; DTS does not. But when migrated to SQL Server � a new relationship structure can easily be built up etc. Indexing becomes a lot more prominent in SQL Server too. Linked Servers Some say using Linked Servers is the simplest way to migrate. It simply makes a link to Access from SQL Server, and does not involve any changes to Access databases and the ASP codes. However, it cannot take full advantage of SQL Server, such as its management and security capabilities. And besides your main objective maybe to get rid of all standalone Access databases and move into the SQL Server mould. Why have both running in tandem?
Data Type MapsAs mentioned, when performing the conversion from Access to SQL Server, some data types required altering/transforming. For example: - you can get issues with both Date/Time:-The Date/Time data type in Access included seconds information; this information would�ve been lost after transferring. Therefore, we had to change the smalldatetime data type to the datetime data type before beginning to transfer tables.Another gotcha is, when inserting a record in Access, one need not list an AutoNumber data type in the values list, because when a new record is inserted into the table, Access will add that number in front of the new record automatically. However, in SQL Server, there is no such data type, and the AutoNumber data type in Access is mapped to the Int data type in SQL Server. Especially if this Int data type attribute is the primary key of the table, it must be included every time a new record is added. When working with data types, one should pay attention to these and other data type differences and change the code appropriately. For a thorough look at the differences between datatypes etc read this MSDN article. So there is the reasoning behind migrating from Access to SQL. The main issue to consider before migrating any Access database to SQL Server is to confirm the integrity of the original database. The best way to protect your database is to create a complete backup and safely store it away from the computers involved in migrating. Consider this step as the single most critical step of the entire operation. MS Access is great for small projects with just a few users. It's also a great tool to develop custom business applications, since it's free to distribute and relatively easy to use. However, problems associated with the limitations of MS Access become all too apparent when you need to scale the application to support more features, more data, and more users. This is when it becomes clear that more robust and secure SQL Server database solutions are in order. It's time to convert Access to SQL Server! Converting MS Access to SQL Server is challenging in some cases.Our team of DBAs at www.gre-sqlserver-solutions.com have many years of migration experience and can help you with MS Access to SQL Server migration by avoiding the migration traps. Usually, it is not necessary to completely re-write the entire system (back-end Database and front-end MS Access application). This is an unnecessary and more costly change that inexperienced developers often seek. www.gre-sqlserver-solutions.com can help you avoid complete rewrites by carefully analysing and planning throughout the application development process itself, if need be working along side your own developers. I�ve personally worked for the emergency response community and understand the need for some businesses to protect their legacy systems & data.For some systems, the MS Access user interface can simply be pointed to a new "SQL Server 2000" database, which is dedicated to safe-guarding and distributing the business/database information as needed. www.gre-sqlserver-solutions.com can assist in guiding you through the 3 main procedures involved in a successful migration:-
1. Creating a working version of the application for SQL Server
I have only brushed upon a few aspects of converting Access databases to SQL Server databases. Actually, there are many ways to set security, to improve the performance and to optimize queries, etc. If you need any more information on migrating your current MS Access database over to SQL Server please post questions on:
|
