Moving from Access2002 to SQL Server - Help please!

[neillovell]

Hi,
I've been asked to move our Access2002 database to SQL Server, and I know I can use the upsizing wizard to convert it to SQL Server, but after that I'm stuck. What do I need to know about SQL Server??? How is it different to what I've done so far (drop the database on the regular intranet server and access it using ASP) ?

Many thanks

 

[SQLSister]

There's so much it's hard to know where to start. First, unlike Access, you can't build a front end application in SQL server. YOu will have to use your Access one or build a new one.

A new thing you won't be used to are stored procedures. SQL server uses them to store the code to run queries and more. If you continue to use your access front end, you'll want to convert your Access queries to stored procedures and call the stored procedure to improve performance. Triggers are also new but you can probaly wait alittle to learn them.

Data types are simliar but somewhat changed and you need to learn what equates to what (I had a cheat sheet for the first couple of months.)

In Access you could get away without security and usually relied on network admin for backups. Now you need to learn about these subjects. Pay rapticular attention to roles and transaction log backups. You probably also need to learn about jobs ans alerts.

I'm presuming you are moving to SQL Server for improved performance and ability to store more records. In this case you need to go ahead and set up for performance now while you are doing the initial set up. If you have the hardware. set up your transaction logs and indexes on separate drives with separate drive controllers (different partitions won't be the help in performance that physically separate drives are). Also set up a development server, so you aren't developing on your production database.

If you are a typical Access user, you developed your queries using the graphical interface. If so you may not be very familiar with SQL itself. Access also uses SQL, but a differnt flavor so if you are familiar with Access SQL, learning T-SQL will not be too bad. If not, well you probably need to get a good book on stored procedure programming and/or the T-SQL language.

SQL Server comes with Books Online (known as BOL around here) which is a good reference to all the commands, but somewhat intimidating to the beginner. Also you do kind of have to know what you're looking for to find things.

There's more but this should get you started. I'd run down to the local bookstore and get at least one book on SQL Server admin and one on stored procedures or T-SQL.

But take heart, a lot of us came from the Access world and we all have survived, so you can too. Just be prepared to learn a lot of new ways to think about things.

 

[neillovell]

Holy crap, our IT department led me to belive this would be simple.

So I can't just use the upsizing wizard in Access and tell it to create a new database on an SQL Server (say, SSERV1 as it came up on my machine), and provide the user name and password of an account with CREATE DATABASE privilages?

 

[neillovell]

T-SQL is Transact-SQL yes? That's to do with relationships between tables I think, though shouldn't that not be used in my case because SQL Server 2000 works like Access does, and doesn;t require triggers (if this makes no sense let me know, I've just read a bit from a book and think I know what it means). the book recommends DRI instead of Triggers when upsizing, so do I need T-SQL ?

 

[neillovell]

Ok reading ahead it looks like I'll need to change my SQL (hard coded btw) in to T-SQL, so I guess I'll need it.

I'll do a search on where to learn about it.

 

[petermeachem]

You don't need to use stored procedures at all if you don't want to. All you need to do is to use the upsizing thing to upsize the back end. (Purists go ballistic at this point). You might subsequently find that a stored procedure might make a query quicker, or not. SQL backup works very nicely and is easy peasy. You can still use Access to create queries even if you are going to make them into SP's, you just need to fiddle about with them a bit first.
I understand that Access Projects (as apposed to mdb's) use MSDE to store the data. MSDE is sqlserver without any gui programmes, so it can't be that hard can it.

http://www.therockalltimes.co.uk/

 

[wg26]

If you already have SQL Server installed in your machine, I believe that the best way to move your Access2002 database to SQL is to use DTS, you can create a empty database in SQL and use DTS to transfer all the data from Access to SQL or even better that you can create a new database on the fly when you are using DTS to transfer data from Access to SQL...I have done this before and it worked well...

 

[SQLSister]

wg26 and petermeachem, note first that he doesn't appear to be using Access for the user interface and that his code is hard coded in ASP. Therefore he does need to look at the difference between Access SQL and T-SQL.

Since people do this ugrade usually due to performance issues, at the time he makes the changes, he should go ahead and move the queries to stored procedures which are faster than using ASP code for queries. Nor did I recommend he convert all his code at once, he can leave what is working (simple selects should be pretty much the same), write new code to fix what does not, and then change the stuff that is significantly slow to improve performance.

And I'm not a purist, I do know what is more effective. Just because something is working now doesn't mean it is the best way to do things, especially with web sites where the usage is often not predicable in advance and is usually much higher than a client-server application. If you don't design for performance initially you will be redoing the whole thing again in a matter of months or weeks.

The other issues, backup. security, etc. are brought up because if you do not know anything about SQL server they can create problems. Look through this site and see just how many people come in and ask how to truncate their transaction log because it is taking up their whole hard drive. Most Access programmers are not used to thinking about these issues because Access backup is usually done in the nightly network backup and a separate transaction log is not maintained.

 

[petermeachem]

Sorry didn't spot the asp. I think your first article frightened the life out of the poor bloke.

http://www.therockalltimes.co.uk/

 

[wg26]

oops...I did not mean that my way is the best...all I am saying is that using DTS is the easy way to move data around...I totally agree with you SQLsister...But he does need to move all Access data to the SQL server...

 

[SQLSister]

wg26 and petermeachem, note first that he doesn't appear to be using Access for the user interface and that his code is hard coded in ASP. Therefore he does need to look at the difference between Access SQL and T-SQL.

Since people do this ugrade usually due to performance issues, at the time he makes the changes, he should go ahead and move the queries to stored procedures which are faster than using ASP code for queries. Nor did I recommend he convert all his code at once, he can leave what is working (simple selects should be pretty much the same), write new code to fix what does not, and then change the stuff that is significantly slow to improve performance.

And I'm not a purist, I do know what is more effective. Just because something is working now doesn't mean it is the best way to do things, especially with web sites where the usage is often not predicable in advance and is usually much higher than a client-server application. If you don't design for performance initially you will be redoing the whole thing again in a matter of months or weeks.

The other issues, backup. security, etc. are brought up because if you do not know anything about SQL server they can create problems. Look through this site and see just how many people come in and ask how to truncate their transaction log because it is taking up their whole hard drive. Most Access programmers are not used to thinking about these issues because Access backup is usually done in the nightly network backup and a separate transaction log is not maintained.

 

[neillovell]

I'll have a more detailed read through this and respond in the morning.

Many thanks in advance for your input

 

[tlbroadbent]

I recommend the following articles.

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download Center

http://support.microsoft.com/default.aspx?kbid=294407

Microsoft Access and SQL Integration Resources

http://msdn.microsoft.com/library/en-us/dnacc97/html/acsqlres.asp

The 2nd article contains links to many useful articles. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SQLSister]

oops, sorry didn't mean to post twice, I got an error message and thought it hadn't posted the first time.

 

[neillovell]

I think the best thing to do is just explain the status and what I plan to do.

The Access2002 database is on a server on our network. It is not split nor is it a project, and it is not secured.

To access the records, make changes etc. the user visits an ASP page on the server that gets the username and uses it to pull the relevant records. Changes are made and a recordset object opens the database in read write mode to add records, delete records etc.

My plan is to run the upsize wizard (pointing it at an SQL Server on our network) and create a new database. I assume I'll have to rewrite my SQL Queries both in the ASP pages and in the database itself. Then I'll rewrite the web pages so the links are correct.

That's the plan. Sound good?

Thanks for all the input so far!