Migrating to SQL 2003

[Neil]

We are running SQL 7, using Access 2000 as a front end. Our network person
is wanting to migrate to Windows 2003 (we're currently on Windows 2000), and
wants to know if we should migrate to SQL 2003 at the same time. Are there
major changes between SQL 7 and SQL 2003, and how hard of a task would it be
to migrate our single database to a new version of SQL?

Thanks,

Neil

[MC]

Ummm, do you want to migrate to SQL 2000 or SQL 2005?

MC

"Neil" <no****@nospam.net> wrote in message
news:Y_***************@newsread3.news.pas.earthlin k.net...

We are running SQL 7, using Access 2000 as a front end. Our network person
is wanting to migrate to Windows 2003 (we're currently on Windows 2000),
and wants to know if we should migrate to SQL 2003 at the same time. Are
there major changes between SQL 7 and SQL 2003, and how hard of a task
would it be to migrate our single database to a new version of SQL?

Thanks,

Neil

[Erland Sommarskog]

Neil (no****@nospam.net) writes:

We are running SQL 7, using Access 2000 as a front end. Our network
person is wanting to migrate to Windows 2003 (we're currently on Windows
2000), and wants to know if we should migrate to SQL 2003 at the same
time. Are there major changes between SQL 7 and SQL 2003, and how hard
of a task would it be to migrate our single database to a new version of
SQL?

There is no SQL 2003. There is SQL 2000 which is very mature now, and
there is SQL 2005 which was released just over a month ago.

While there are vast improvements in SQL 2005 over SQL 2000, migrating
your database to SQL 2005 can very well be a simple task, although there
are some incompatibilities, particularly if you head for compatibility
level 90 (which I recommend).
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Neil]

Hi, Erland. Thanks for your reply. Given the incompatibilities between SQL
2005 and SQL 97, do you think it would be wise to just upgrade to SQL 2000?
Also, any ideas about which one might be better suited for Windows 2003
and/or an ODBC environment (using Access MDB as a front end)? Thanks!

Neil
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Neil (no****@nospam.net) writes:

We are running SQL 7, using Access 2000 as a front end. Our network
person is wanting to migrate to Windows 2003 (we're currently on Windows
2000), and wants to know if we should migrate to SQL 2003 at the same
time. Are there major changes between SQL 7 and SQL 2003, and how hard
of a task would it be to migrate our single database to a new version of
SQL?

There is no SQL 2003. There is SQL 2000 which is very mature now, and
there is SQL 2005 which was released just over a month ago.

While there are vast improvements in SQL 2005 over SQL 2000, migrating
your database to SQL 2005 can very well be a simple task, although there
are some incompatibilities, particularly if you head for compatibility
level 90 (which I recommend).
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Neil (no****@nospam.net) writes:

Hi, Erland. Thanks for your reply. Given the incompatibilities between
SQL 2005 and SQL 97, do you think it would be wise to just upgrade to
SQL 2000? Also, any ideas about which one might be better suited for
Windows 2003 and/or an ODBC environment (using Access MDB as a front
end)? Thanks!

SQL 97? Do you mean Access 97?

I can't give any advice that involves Access as this is an unknown product
to me.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On Thu, 26 Jan 2006 05:02:36 GMT, Neil wrote:

Hi, Erland. Thanks for your reply. Given the incompatibilities between SQL
2005 and SQL 97, do you think it would be wise to just upgrade to SQL 2000?
Also, any ideas about which one might be better suited for Windows 2003
and/or an ODBC environment (using Access MDB as a front end)? Thanks!

Neil

Hi Neil,

If you're upgrading anyway (whether from SQL 7 or fram Access 97, I
don't know), then I'd recommend you to go for SQL 2005. Every upgrade
you ever do will require you to deal with SOME incompatibilities. But
from SQL 7 to SQL 2005 should be relatively easy. (From Access [any
version] to SQL Server [any version] might be more trouble).

The payoff will be bigger if you go straight for 2005 - especially when
you start using the new features. Plus you'll be settled for some time
to come!

--
Hugo Kornelis, SQL Server MVP

[Neil]

I meant SQL 7.

I'm mainly looking for advice re. compatibility with Windows 2003, and
possibly ODBC environments.

Thanks.
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Neil (no****@nospam.net) writes:

Hi, Erland. Thanks for your reply. Given the incompatibilities between
SQL 2005 and SQL 97, do you think it would be wise to just upgrade to
SQL 2000? Also, any ideas about which one might be better suited for
Windows 2003 and/or an ODBC environment (using Access MDB as a front
end)? Thanks!

SQL 97? Do you mean Access 97?

I can't give any advice that involves Access as this is an unknown product
to me.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Neil]

Hi, Hugo. Thanks for the reply. See below.

"Hugo Kornelis" <hu**@perFact.REMOVETHIS.info> wrote in message
news:1k********************************@4ax.com...

On Thu, 26 Jan 2006 05:02:36 GMT, Neil wrote:

Hi, Erland. Thanks for your reply. Given the incompatibilities between SQL
2005 and SQL 97, do you think it would be wise to just upgrade to SQL
2000?
Also, any ideas about which one might be better suited for Windows 2003
and/or an ODBC environment (using Access MDB as a front end)? Thanks!

Neil
Hi Neil,

If you're upgrading anyway (whether from SQL 7 or fram Access 97, I
don't know),

It's from SQL 7. We are currently using Access with a SQL 7 back end.
then I'd recommend you to go for SQL 2005. Every upgrade
you ever do will require you to deal with SOME incompatibilities. But
from SQL 7 to SQL 2005 should be relatively easy.
Erland mentioned "compatibility level 90" as possibly entailing some
incompatibilities. Would you concur?

(From Access [any version] to SQL Server [any version] might be more trouble).

The payoff will be bigger if you go straight for 2005 - especially when
you start using the new features. Plus you'll be settled for some time
to come!
I certainly agree with that. The only issue would be if there were a main
difference between upgrading to 2000 vs. 2005. But if upgrading to 2000 or
2005 were more or less the same, then I'd agree that 2005 would be the way
to go.

Thanks!

Neil


--
Hugo Kornelis, SQL Server MVP

[Hugo Kornelis]

On Fri, 27 Jan 2006 08:27:25 GMT, Neil wrote:

then I'd recommend you to go for SQL 2005. Every upgrade
you ever do will require you to deal with SOME incompatibilities. But
from SQL 7 to SQL 2005 should be relatively easy.

Erland mentioned "compatibility level 90" as possibly entailing some
incompatibilities. Would you concur?

Hi Neil,

Compatibility level 90 means that you unlock all the new features that
were implemented in SQL Server 2005. Upgrading to SQL Server 2005
without striving for compatibility level 90 is, IMHO, a bit silly.

Lower compatibility levels are included to make upgrading easier. At
compatibility level 70, many features still behave the same as in SQL
Server 7.0. Not all, though - some changes to the core of the DB engine
were just too drastic to allow mimicking the "old" behaviour.

Upgrading from SQL Server 7.0 to SQL Server 2005 at compatibility level
70 means that you have to make only minimal changes - you don't have to
change the features that are still supported in the compatibility level.
But this should only be a first step - the next step should be to
replace old functionality with new functionality, then set the
compatbility level to 90. And after that, you can begin to unleash the
true power of all the new features!

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Neil (no****@nospam.net) writes:

Erland mentioned "compatibility level 90" as possibly entailing some
incompatibilities. Would you concur?

You can choose compatibiliy level 70. There still are cases of what
is is illegal syntax bot not caught by SQL 2000. (Don't know if SQL 7
has the same issues.) But with compatibility level 70, some functionalit
will not be available to you.

Beside mew reserved keywords, the main issue is the old-style outer
join (*= and =*) that are not permitted in level 90. If you have this,
rewrite it, because this is crap.

Note that you could run into issues if you upgrade to SQL 2000 as
well, althogh I think difference between level 70 and 80 is just
three keywords.
I've seen some mentions that Access 2003 is not co-operative with SQL2005,
but you probably get better answer on that in an Access newsgroup.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Neil]

Sounds like a plan. Thanks.

"Hugo Kornelis" <hu**@perFact.REMOVETHIS.info> wrote in message
news:ct********************************@4ax.com...

On Fri, 27 Jan 2006 08:27:25 GMT, Neil wrote:

then I'd recommend you to go for SQL 2005. Every upgrade
you ever do will require you to deal with SOME incompatibilities. But
from SQL 7 to SQL 2005 should be relatively easy.

Erland mentioned "compatibility level 90" as possibly entailing some
incompatibilities. Would you concur?

Hi Neil,

Compatibility level 90 means that you unlock all the new features that
were implemented in SQL Server 2005. Upgrading to SQL Server 2005
without striving for compatibility level 90 is, IMHO, a bit silly.

Lower compatibility levels are included to make upgrading easier. At
compatibility level 70, many features still behave the same as in SQL
Server 7.0. Not all, though - some changes to the core of the DB engine
were just too drastic to allow mimicking the "old" behaviour.

Upgrading from SQL Server 7.0 to SQL Server 2005 at compatibility level
70 means that you have to make only minimal changes - you don't have to
change the features that are still supported in the compatibility level.
But this should only be a first step - the next step should be to
replace old functionality with new functionality, then set the
compatbility level to 90. And after that, you can begin to unleash the
true power of all the new features!

--
Hugo Kornelis, SQL Server MVP

[Neil]

Thanks, and thanks for the note about Access 2003 and SQL 2005.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Neil (no****@nospam.net) writes:

Erland mentioned "compatibility level 90" as possibly entailing some
incompatibilities. Would you concur?

You can choose compatibiliy level 70. There still are cases of what
is is illegal syntax bot not caught by SQL 2000. (Don't know if SQL 7
has the same issues.) But with compatibility level 70, some functionalit
will not be available to you.

Beside mew reserved keywords, the main issue is the old-style outer
join (*= and =*) that are not permitted in level 90. If you have this,
rewrite it, because this is crap.

Note that you could run into issues if you upgrade to SQL 2000 as
well, althogh I think difference between level 70 and 80 is just
three keywords.
I've seen some mentions that Access 2003 is not co-operative with SQL2005,
but you probably get better answer on that in an Access newsgroup.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx