从SQL Server 2000升级到SQL Server 2005 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

从SQL Server 2000升级到SQL Server 2005

从SQL Server 2000升级到SQL Server 2005# Database - 数据库

y*o2008-07-17 07:07

1 楼

到底有什么典型的问题要注意？
我们公司要从SS2000升级到SS2005，想让我来试一下。但是我又没有做过DBA, 只是做
web development的时候才跟数据库打交道而已。
难道将SS2000的数据库back up然后restore到SS2005不行吗？会有什么大问题吗? 别笑
。

B*g2008-07-17 07:07

2 楼

http://www.microsoft.com/sql/solutions/upgrade/default.mspx

【在 y********o 的大作中提到】

: 到底有什么典型的问题要注意？
: 我们公司要从SS2000升级到SS2005，想让我来试一下。但是我又没有做过DBA, 只是做
: web development的时候才跟数据库打交道而已。
: 难道将SS2000的数据库back up然后restore到SS2005不行吗？会有什么大问题吗? 别笑
: 。

a*t2008-07-17 07:07

3 楼

the link from Beijing has a lot of info
but in summary, yes, backup restore is ok. Few things thought
1. run upgrade advisor; take action as needed.
2. set compebility level to 90 (2005) unless you need to keep it at 80

j*n2008-07-17 07:07

4 楼

get upgrade advisor as the start point.
http://www.microsoft.com/downloads/details.aspx?familyid=1470e86b-7e05-4322-a677-95ab44f12d75&displaylang=en
generally DB and SPs are ok, if you have DTS, that's another issue.

y*o2008-07-17 07:07

5 楼

Thank you very much. Very helpful, esp., got to know the Upgrade Advisor,
which really helped.

【在 B*****g 的大作中提到】

: http://www.microsoft.com/sql/solutions/upgrade/default.mspx

y*o2008-07-17 07:07

6 楼

If we set the compatibility level to 80, then we don't really need to make
any change to the original database, right? I understand that then we won't
be able to use the SS2005 features. But that's fine.
The documentation at MSDN says that compatibility level 80 will be removed
in future releases of SQL Server.

【在 a*******t 的大作中提到】

: the link from Beijing has a lot of info
: but in summary, yes, backup restore is ok. Few things thought
: 1. run upgrade advisor; take action as needed.
: 2. set compebility level to 90 (2005) unless you need to keep it at 80

y*o2008-07-17 07:07

7 楼

I carefully read the documentation. It seems it has a lot of changes in
system stored procedures. User stored procedures are mostly fine. The outer
join operators *= and =* are deprecated, a slight concern.
I don't know nothing about DTS yet.

【在 j*****n 的大作中提到】

: get upgrade advisor as the start point.
: http://www.microsoft.com/downloads/details.aspx?familyid=1470e86b-7e05-4322-a677-95ab44f12d75&displaylang=en
: generally DB and SPs are ok, if you have DTS, that's another issue.

j*n2008-07-17 07:07

8 楼

*= and =* are just outer joins, so easy to fix, not a big deal.
quick check: how do I find all SPs contain particular strings?
USE DB_you_want_search
go

y*o2008-07-17 07:07

9 楼

Thanks. I think the upgrade advisor can find them out, too.
For practice purpose, I have been trying to iterate through all databases
and all tables of each through sp_msForEachDb and sp_msForEachTable. But it
hasn't been successful.

【在 j*****n 的大作中提到】

: *= and =* are just outer joins, so easy to fix, not a big deal.
: quick check: how do I find all SPs contain particular strings?
: USE DB_you_want_search
: go

b*a2008-07-17 07:07

10 楼

Backup everything before you do the migration.
Some DTS packages need to be rewritten.

n*62008-07-17 07:07

11 楼

The system accounts and user accounts, priviledge, and security might also
be an issue and increase the complexity. SS2005 has new system objects.
Complex DTS packages cannot be directly upgrade to SSIS.
It's just my knowledge. I never upgraded from SS2000 to 2005.

【在 y********o 的大作中提到】

j*n2008-07-17 07:07

12 楼

in addition, there is NO WAY to run DTS under 2k5 64bit, no run-time env
support for it.

【在 n********6 的大作中提到】

: The system accounts and user accounts, priviledge, and security might also
: be an issue and increase the complexity. SS2005 has new system objects.
: Complex DTS packages cannot be directly upgrade to SSIS.
: It's just my knowledge. I never upgraded from SS2000 to 2005.