Migrating from MSDE to SQL 2000

[bkirkflex]

We are running SharePoint with the default MSDE database. Because of the sizei limit of MSDE we want to "upgrade" this database to SQL 2000. New to SQL and not sure how this is done. Been looking for a utility in SQL for this but don't see one, am I missing something?

How would I upgrade the MSDE database to SQL 2000?

Thanks.

 

[ptheriault]

Are you planning on using the same hardware? If you are moving the db to a new SQL server you can just detach the db and copy it. You also might want to consider using SQL 2005 instead of 2000.

- Paul
- Database performance looks fine, it must be the Network!

 

[bkirkflex]

Paul,

We will be using the same hardware. Just installed SQL 2000 so MSDE and SQL 2000 are on the same box. Since I don't have to move the database what would be entailed to upgrade the MSDE database?

Thanks

Bill

 

[ptheriault]

you don't need to 'Upgrage' the db.
1. Shut down the MSDE engine.
2. Copy the database .mdf and .ldf files to data folder in you SQL 2000 instance.

something like this.
d:\program files\microsoft SQL server\MSSQL\DATA\

Open enterprise manager.
open the database instance.
Right click on database and select attach database.

Make sure that 'sa' is the database owner.

Uninstall MSDE if you don't need it.


- Paul
- Database performance looks fine, it must be the Network!

 

[bkirkflex]

Paul-

Okay, so far so good. We did move the MSDE database to a different partition for space reasons and would like to keep it on that partition. Once the database is SQL 2000 can we move the database back to the partition where it was living?

Thanks again,

Bill

 

[ptheriault]

yes you can. There is a detach method. If you right click on the database and select tasks -> detach. You can then copy the mdf and ldf file to where every you want them.
If you have different RAIDS you should put your .mdf file on one and your .ldf file on the other.

Keep in mind that if this is a production db, users can not connect to a db that has been detached.

Also now that you using SQL 2000 make sure you backup your database with SQL Server agent. (also check the recovery model)
Right click on databsae and select properties -> options.
If you don't need to recover this db to a point in time set it to simple. This will prevent your log file from growing out of control.

- Paul
- Database performance looks fine, it must be the Network!

 

[bkirkflex]

Once moved to where we want the database, Right click and attach?

Bill

 

[ptheriault]

Yes.
Click the + next to the server name
Right clikc on Databases and select all tasks -> attach database.

You can also detach and attach database from Query Analyzer.

sp_detach_db [ @dbname= ] 'dbname'

EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

- Paul
- Database performance looks fine, it must be the Network!

 

[bkirkflex]

Thanks for your help. Now just need to make sure SharePoint sees the new database.

Bill

 

[bkirkflex]

Paul;

Instead of moving the MSDE data base around, can I just detach the database from MSDE and then attach with SQL 2000?

Bill

 

[ptheriault]

yup...
Just be careful not to turn on the MSDE engine. SQL will have a lock on those files and your MSDE will try to lock them. It could cause problems. Expecially if MSDE starts before SQL Server, then MSDE would get the files.

- Paul
- Database performance looks fine, it must be the Network!

 

[bkirkflex]

So I guess the smart thing to do would be to:

Detach from MSDE. De-install MSDE
Re-attach with SQL 2000?

Bill

 

[ptheriault]

I would agree with that.

- Paul
- Database performance looks fine, it must be the Network!

 

[bkirkflex]

Paul;

is this the command to change the ownership of a database:

USE pubs
GO
EXEC sp_changedbowner 'John'
GO

Where pubs is the name of the databse and we are chaning the ownership to "John">

Bill

 

[ptheriault]

yes it is. You don't want to have a user own a db. You can place a user as memeber of the dbo role but don't make them the owner. You will only have to change it again at some point.


USE pubs
GO
EXEC sp_changedbowner 'sa'
GO

- Paul
- Database performance looks fine, it must be the Network!

 

[bkirkflex]

Thanks.

 

[bkirkflex]

Paul,

Thanks for all your help on this. Getting ready to do this, but my question is will SharePoint know that I have attached the database to SQL 2000? I don't want to remove MSDE and then attch databases to SQL if sharepoint cannot figure out that there is a new database engine. Will sharepoint know what engine is running i.e. SQL 2000? SharePoint may not be your thing,, but thought I would ask. I have a fear that I will deinstall MSDE and attch the Sharepoint databases to SQL 2000 and our sharepoint page wil not come up.

Thanks,

 

[ptheriault]

I'm not sure how sharepoint is connecting to the db. Does it use an ODBC connection? I have some experience with sharepoint only through project server. That had a setting from within sharepoint\project that pointed to the db server. Most likely you will have to repoint the sharepoint app to the new db. I say this because you can't have two instances of SQL running on a server with the same name. like msde and sql 2000. One must be a default instance and the other named? I did just find this KB article for you.
I hope it helps.

http://support.microsoft.com/kb/837367

- Paul
- Database performance looks fine, it must be the Network!