Migrating from Access 2000 SR1 1

[jeffcullina]

I am about to push a database in Access 2000 to SQL Server. I am very familiar with Access, but I do not yet know anything about SQL Server. I am guessing the two are similar, though SQL Server is obviously going to be somewhat more complicated. What kind of complications can I expect in the transition? Any resources around to help guide me through the process?

 

[SQLBill]

Start by reading the FAQs. The very first FAQ (1.a.) for this forum is Converting from Access to SQL Server.

Then do some searches on this forum. This question has been brought up many times.

There is also a Microsoft SQL Server: Setup and Administration forum.

-SQLBill

 

[jeffcullina]

I should have checked FAQ first. My experience is that FAQ has never been helpful, so I do not think to look there anymore. I have performed a search, but the topic is fairly broad, so it is difficult to perform a useful search. I have a good place to start in the FAQ. Thanks.

 

[SQLSister]

Jeff, I've been where you are and there is LOTS to learn.

The first thing you need to understand is the data types SQL server has and how they relate to the data types you used in Access.

NOw as to some of the things you will encounter (in no particular order):

IIF command in Access is the Case statement in SQL
Tables must have a unique identifier if you want to use them in an Access front end and add or update records.

The SQL flavor of SQL Server is T-SQL and it is very different from Access SQL. You will need to learn it.

The help files for SQL Server are known as Books Online (BOL for short). They are helpful if you know what ot look for and somewhat less than that if you are new to SQL Server. I suggest you find a nice T-SQL book to help you out.

There is no getting away from security in SQL Server unlike in Access. You must create users and set permissions. Of course you can make everybody sa and then everybody has permission to do everything. THis is what is known as a BAD idea. It opens you to hackers. It is best to create roles and assign users to roles rather than set individual permissions.

Another thing that snewaks in on Access users is the need to backup the database and particularly to backup the transaction log. Do not rely on nightly network backups for your database backups. The transaction log in particular will grow until it takes up the entire hard drive unless you frequently back it up or truncate and shrink it. Truncating the log is generally a bad idea in a productin database unless you don't think you will ever need to recreate the transactions. Never do a truncate log wihtout backing up the whole database afterward.

Stored procedures are one of the best ways to query SQL server. They are more efficient than using an Access query on a linked SQL table or writing the SQL inthe user interface.

Do not use cursors as they are very inefficient. Learn to do things in a set fashion rather than looping through records.

Indexes are necessary to efficiently query tables. Indexes can also get out of date and need to be recreated or statistics updated.

If you did not use relational tables in Access, now is the time to learn how to set them up. You should also get yourself a goos solid understanding of how joins are written in T-SQL code.

I'm sure there's more, but, hey, it's quitting time and I'm off to watch Animal House with my friends.

Have fun.

 

[LogicalmanUS]

Jeff,

Yet again, SQLSIster has just about all of it covered, except for one piece of advice, which I learned many years ago, DO NOT, repeat DO NOT use the Microsoft Upsizing Wizard.
I would describe it as a piece of rubbish, but that would be detrimental to everything that is rubbish. It's about as useful as a chocolate fireguard.
The last database I was given to maintain after the upsizing wizard was used took me months to debug. It would have been quicker to rewrite the SQL database from the start (which I have done many times since given the same circumstances).

Good Books/reference materials are a must. Here's a list of the one's I use:

1 BOL (Books OnLine) - as per SQLSister
2 Inside SQL Server (MS Press)
3

http://www.sql-server-performance.com/

4

http://www.fawcette.com/resources/spcollections/sql/

5

http://www.pinnaclepublishing.com/sq

6 Never be too scared to ask

Number 6 is the most important, there's always someone out there who knows how to do something better than you.

Good Luck, and welcome to SQL Server, still the best Enterprise RDBMS around.


Logicalman

 

[jeffcullina]

Thanks. I was planning on using the upsizing wizard. I am glad I know not to. I was also unaware that the SQL was substantially different. I figured they are both MS products.

About stored procedures. My application is a website. Rather than define queries and procedures in the database, I am calling the data in queries on each web page. It is plenty efficient for me. Besides, since each query is different, and I would have to call a stored query with a query on the web page anyway, I just do not see how stored procedures could be useful for me. Deep down I know I should be using stored procedures, but I don't know why. Thoughts?

Jeff

 

[SQLSister]

Stored procedures are precompiled and queries from web pages are not. This saves a small amount fo time, but in the website business, a tiny savings of time can be important when you have many users simultaneously. Another factor which can improve performance in the case of complex queries is that the queries do not need to go across the network to the server which can save netwrok bandwidth if you have a lot of users as a a website may typically have. Further, Permissions can be given at the stored procedure level rather than directly to tables. This is useful in making sure that only very specific actions are allowed onthe database by users. Queries from the website require permissions directly on the tables and views which opens up your database to more vunerability. Further, it is easier to do complex operations in a stored procedure than throught the web interface. Another advantage is that you can learn to exactly how the database will execute a stored procedure by viewing the execution plan and using this information to fine tune your queries and set proper indexes to improve performance. For instance, I have used this feature to improve performance on a stored procedure from several minutes down to milleseconds. There is also a debugger for stored procedures available in query analyzer. Of course from the dba perspective having all data access through stored procedures helps me to maintain the database and to be able to easily find out how a proposed change may affect the way the application works. It also allows me, as the database expert, to identify and fix poor query design by the web programmers which is one of the major causes of performance degredation.

All in all stored procedures are far better to use especially when accessing the database from the web where the total number of users is not known in advance and may suddenly become quite large.

 

[jeffcullina]

That is great information. Unfortunately, I am both the programmer and the supposed database expert - not sure I am qualified for either. I will consider converting to stored procedures as I go through this process. First I need to get the structure built and the data passed. It is not real clear to me how this will save me a measurable amount of time since the pages really load quickly already, but my number of users is about to double (or triple) so I can see how that could begin to really tax the site.

Thanks. I will be back with plenty more questions.

Jeff

 

[jeffcullina]

As I read through the book reviews and think back on other conversations I have had on this subject, I notice a blatantly clear distinction between programmers and DBAs. I do not understand how a person could be good at one while knowing little about the other. Is SQL Server so cumbersome that it takes a designated DBA and another desinated programmer? I hope one person can tackle this job, because it is all I have.

 

[SQLSister]

Depending on the complexity of what you do, yes you can do both. However, people who do both tend to concentrate on the programming end and put off the dba tasks because they generally come from the programmer world not the dba world.

Access has basically no tools for performaing database adminstration, so you can get away with this. But SQL server will require you to learn more about database administration and performance tuning if you want it to perform well as your database grows.

It is considerably less difficult to be a dba for SQL Server than for an Oracle system which often requires multiple dbas. Most tasks can be set up to run on a schedule or when an event occurs, so you may not have to spend much time doing dba work on a day-to-day basis once you have things set up.

I do agree that the best programmers understand database concepts, design and basic dba tasks and the best dbas have an understanding of what the programmers go through.

However, there are distinct differnces as the dbas are more concerned with the day-to-day operation of production systems and programmers are generally concerned more with development and the maintence of code.

A large shop might have dbas, database programmers and application programmers as well as systems analysts, architects, testers. etc. The smaller the shop, the more hats you wear. I'm a dba and database programmer, but I have done some .Net web pages in a crunch and I have written requirements, developed test plans, written sales proposals and a whole lot of other things because I work in a small shop.