Yair,
See my answers below.
-Daran
Yair Sageev said:
Greetings,
We have a fairly comprehensive Access DB and realize now that we need a more
scalable backend without the file size limitations. I tried the upsize
wizard using MSDE and it looks fairly promising. If anybody can help me,
I'm wondering:
1. Is their a FAQ for this newsgroup?
This is a great newsgroup for your questions. You can also browse the SQL
Server newsgroups and post relevant questions there as well.
2. What is the best book for Access programmers seeking to upsize to SQL
Server?
I would suggest the following two books. One is for SQL Server and the
other is for Access. Neither covers ADP exclusively, however, to be
successful with MSDE/SQL Server you should understand the internal workings.
1. Kalen Delaney's Inside SQL Server 2000 - I have read this cover to cover
more than once. It is an excellect book on the inner workings of SQL
Server.
2. Alison Balter's Mastering Microsoft Access 2000 Development - Also have
read cover to cover more than once. It is an excellent book on Access
development.
I would also suggest looking at magazines like SQL Server magazine and
Access Advisor and Books On Line (BOL).
3. What happens to queries that reference forms, and do all the objects
migrate in general?
An ADP uses SQL Server as it's database. The JET engine is not envolved.
All of your tables and queries will need to be moved into SQL Server. You
should learn about stored procedures and best practices used when writing
stored procedures (see anything written by Itzak Ben-Gan). You should learn
about views and effective use of indexes as well. There is much to learn.
Do not use the upsize wizard! You need to map the datatypes from Access to
the appropriate datatypes in SQL Server.
4. What are the major pitfalls to watch out for when migrating?
What you must understand is that SQL Server is a more advanced system then
Access and opens up greater possiblity for development. Resist the
temptation to pack all related queries into one stored procedure using
multiple temp tables (DLL statements) and branching statements. This will
cause extensive recompilation which will come back to haunt you latter.
Judiciously comment your stored procedures so you understand what you are
looking at. Avoid prefixing your tables with tbl or other prefix - we know
they are tables. Do prefix your stored procedures but not with sp_ as that
will make SQL Server search for the stored procedure first in the Master
database. sp_ is used for system stored procedures. I would also advise
hiring a skilled SQL Server/Access consultant to look over your work. Trust
me - it will pay for itself in the end.
5. If we successfully migrate to MSDE, will it be 5 minutes to upsize to
SQL server?
Nothing ever takes five minutes. However, the heavy lifting will be done in
the migration from Access/JET to MSDE. But it is relatively easy to go from
MSDE to SQL Server. It's the same thing really.
6. How do you transfer a SQL server DB from one machine to another? With
access it's just a file.
SQL Server is an actual server. You would dettach the database file(s) from
one SQL Server and attach them to another. You can read more about that in
Books On Line (BOL).
