Upsizing To SQL Server

I have no experience with SQL server and have been asked the following
questions:

If the owner of a split Access database (frontend/backend) wants to
upsize to SQL server can it be as simple as migrating the backend
tables to SQL server, link the frontend to the new datasource and do
nothing else? Will anything be broken?

Can the frontend linking be set up at a remote location without having
to actually visit the site? This is the case at the moment. Frontend
is linked to appropriate Jet backend file path and then emailed to the
user who installs them in the same file path location.

Re: Upsizing To SQL Server

Wayne wrote:
> I have no experience with SQL server and have been asked the following
> questions:
>
> If the owner of a split Access database (frontend/backend) wants to
> upsize to SQL server can it be as simple as migrating the backend
> tables to SQL server, link the frontend to the new datasource and do
> nothing else? Will anything be broken?
>
> Can the frontend linking be set up at a remote location without having
> to actually visit the site? This is the case at the moment. Frontend
> is linked to appropriate Jet backend file path and then emailed to the
> user who installs them in the same file path location.

It *can* be that simple, but it depends on the app. Having said that, even if
your app falls into the category where you do as described and everything works,
there is no certainty that it will work as well as it does now and it will
almost certainly not work as well as it could be made to work.

There is definitely a lot of bad information out there much of which indicates
that *everything* has to be re-written to use Views and Stored Procedures or
that all code has to be switched from using DAO to ADO. Those notions are
completely absurd, but it is true that some of your queries, reports, and forms
will not work very efficiently with SQL Server as the back end. What is usually
the case though is that MOST of the app will work just fine and in the short
term all you have to do is tweak those areas that either don't work at all after
the conversion or which perform badly.

For the most part if you are starting with a split Access app that was designed
using the best practices for a multi-user networked application, then it will
likely require very little rework when the tables are moved to SQL Server. If
however, it is an app put together over a long period of time by an amatuer who
relied on the wizards and examples in the help file then it is likely to be a
complete mess after moving to SQL Server.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Re: Upsizing To SQL Server

"Rick Brandt" wrote in
news:841kj.64972$eY.32790@newssvr13.news.prodigy.net:

> There is definitely a lot of bad information out there much of which
> indicates that *everything* has to be re-written to use Views and
> Stored Procedures or that all code has to be switched from using DAO
> to ADO. Those notions are completely absurd, but it is true that some
> of your queries, reports, and forms will not work very efficiently
> with SQL Server as the back end. What is usually the case though is
> that MOST of the app will work just fine and in the short term all you
> have to do is tweak those areas that either don't work at all after
> the conversion or which perform badly.

While the notions are absurd I think it's understandable that many people,
including me, before you educated me, have thought so. If Microsoft
publicized the power of ODBC well in this respect, I missed it. And I think
you are the only contributor here who regularly reminds us of this power.
Having started without ODBC I doubt that I'll convert, other than with a
few experiments. But if I had understood all about it at the beginning of
using SQL-Server, I might have gone that route.

I will point out that if one is planning or thinking about using the SQL
Database with other front ends, the time and effort required to create
sprocs and views may be worthwhile. When we do this, the Server becomes
black-boxish and we can concentrate on other things.


--
lyle fairfield

In ancient times Ulysses had to outwit the sirens, who had the power to
charm by their song all sailors who heard them, so that the mariners were
compelled to cast themselves into the sea to their destruction.
Today, he would have to deal with (free) Aim-Mail.

Re: Upsizing To SQL Server

Thanks Rick and Lyle. It is my suspicion that it is the IT department
at the database owners site that wants to move the database backend to
SQL server. In light of your comments and the fact that the database
is small, runs well, doesn't give problems, has only a handful of
users and is not mission critical, it would seem that moving to SQL
server would be a waste of time and resources.

Re: Upsizing To SQL Server

lyle fairfield wrote:
> I will point out that if one is planning or thinking about using the
> SQL Database with other front ends, the time and effort required to
> create sprocs and views may be worthwhile. When we do this, the
> Server becomes black-boxish and we can concentrate on other things.

I definitely agree with that. Most of my new development is with Ajax type web
apps with a Java/JDBC middle tier and I find it much easier to use stored
procedures so that more intelligence is in the back end and less programming is
required in the middle. Not only is that intelligence now automatically
provided to any other front end that might use it, but it is just plain faster
to make a change in a stored procedure or view then it is to modify the client
and/or middle tier which then have to be re-deployed.

Of course, many changes require modifications to the other layers, but there are
times when a quick change can be made to an SP or View and all of the consumers
of same just happily plug along and pick up the modification seamlessly.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com