SSMA is the migration tool. You can download it from a MS site. Make sure you download the bit version that matches your Access application. If your Access is 32-bit, then you MUST use the 32-bit version of SSMA. As Isaac mentioned there are some different datatypes. that you might need to worry about. As a first time conversion, I wouldn't worry about trying to optimize anything. BUT, you will need to go into SSMA's defaults and change the default for dates to datetime. It is the old format but unless you want to deal with distributing different versions of the ODBC driver to every single user of the application from now until eternity, stick with the "old" data types, at least for now. I also convert my Y/N fields to integers.
Make sure your Access application has NO compile errors. Make sure you are using Option Explicit in EVERY procedure and that all your variables are defined. Make sure you have primary keys on EVERY table. Make sure you have RI defined and enforced. Make sure you have the indexes you need for common searches. Be careful here though. Access has a default that builds indexes for fields that have particular suffixes such as ID. So, if a field is named ClientID, Access "helpfully" or "unhelpfully" depending on your POV builds an index automatically. You want to disable this feature if it was on and then go and delete the vast majority of indexes that were created. One particular thing to look for is indexes on your FK fields. So, in the Order table you have a FK named CustomerID. When you defined this relationship, Access automatically built a HIDDEN index because Access knew that one would be beneficial on this field. However, the default might have also built an index and even you might have build an index if you knew a little about indexes but didn't know about Access already building one for you. So, you probably have more indexes to delete than to build. You could potentially end up with THREE indexes on each FK field. You absolutely need to get back to only ONE.
Now, you are ready for a test. This is of course preliminary but it will give you a sense of what parts of the application you need to concentrate on. For example, if your forms are bound to tables or to queries without indexes and you use filtering to get to the record your user wants to update, be prepared for these forms to load at the speed of paint drying after the conversion. But, you don't want to waste your time starting with the low dollar payoffs so just do the conversion once the tables are cleaned up and then see where you stand.
I recommend fixing up your tables in Access FIRST but why? Well, the answer is, how many times do you want to fix them up in SQL Server? Since conversions are multiple run processes. You might be able to fix the tables in Access once or twice and convert a few times until you get all the data fixed up so I find it just saves me time, otherwise, every time I run a conversion, I have to fix up the tables on the Server side and I'd rather not. Instead, I prefer to convert them as clean as possible. Don't forget, you've got a lot of testing before the final conversion happens.
