best way to change servers for a live database?

I have a 24/7 live Sql Server 7 database. The server hardware is old. I
have a new computer for this Sql Server, and I will also be switching to SQL
Server 2000. What's the best way to transition from one to the other? I
can install everything on the 2nd server, and start using a different server
name, and I can tolerate 20 minutes of downtime when I finally switch from
one machine to the other. But I don't know the best way to get the
database from one machine to the new machine, in a fast way. Other times
I've moved databases, I've found problems with the user records not matching
to the same user IDs in the Master System tables. And stored procedures
that have "dbo" owners suddenly freak out because that dbo isn't a valid
user ID anymore, etc.Safest way of copying the data is backup and restore, if it will fit in =your 20 minute window.
To avoid problems of user mismatches make all the guids for the logins =the same on both systems - sp_grantlogin or ap-addlogin (can't remember =which) allowyou to force the internal id to ensure your second machine =is same as first.
dbo should always be a valid db user - can be fixed by =sp_changeuserslogin, this happens if the internal login ids are out of sync.
As an alternative to backup restore you could detach sp_detachdb, copy =the f iles and then sp_attachdb, but personally I always prefer the =backup./restore route.
Good Luck
Mike John
"AFN" <DELETEnewsgroupCAPSaccount@.yahoo.com> wrote in message =news:%vjLb.46123$C87.11549@.twister.socal.rr.com...
> I have a 24/7 live Sql Server 7 database. The server hardware is =old. I
> have a new computer for this Sql Server, and I will also be switching =to SQL
> Server 2000. What's the best way to transition from one to the =other? I
> can install everything on the 2nd server, and start using a different =server
> name, and I can tolerate 20 minutes of downtime when I finally switch =from
> one machine to the other. But I don't know the best way to get the
> database from one machine to the new machine, in a fast way. Other =times
> I've moved databases, I've found problems with the user records not =matching
> to the same user IDs in the Master System tables. And stored =procedures
> that have "dbo" owners suddenly freak out because that dbo isn't a =valid
> user ID anymore, etc.
> >|||You could use the Database Copy/Move wizard. It will detach the database
from SQL7, copy it to the destination server, re-attached the database on
the new machine and resolve all the logins. It's pretty slick.
Jim
"AFN" <DELETEnewsgroupCAPSaccount@.yahoo.com> wrote in message
news:%vjLb.46123$C87.11549@.twister.socal.rr.com...
> I have a 24/7 live Sql Server 7 database. The server hardware is old.
I
> have a new computer for this Sql Server, and I will also be switching to
SQL
> Server 2000. What's the best way to transition from one to the other?
I
> can install everything on the 2nd server, and start using a different
server
> name, and I can tolerate 20 minutes of downtime when I finally switch from
> one machine to the other. But I don't know the best way to get the
> database from one machine to the new machine, in a fast way. Other times
> I've moved databases, I've found problems with the user records not
matching
> to the same user IDs in the Master System tables. And stored procedures
> that have "dbo" owners suddenly freak out because that dbo isn't a valid
> user ID anymore, etc.
>|||Hi,
Try sp_help_revlogin to migrate the logins. This article has the details.
http://support.microsoft.com/default.aspx?kbid=246133
Backup and restore like you do for a standby server and with minimum
downtime can commission the new server of.
Sankar Renganathan DBA
SPAR Group Inc.,
sankariahr@.hotmail.com
1-248-568-7164
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:e%23fRUti1DHA.2324@.TK2MSFTNGP09.phx.gbl...
Safest way of copying the data is backup and restore, if it will fit in your
20 minute window.
To avoid problems of user mismatches make all the guids for the logins the
same on both systems - sp_grantlogin or ap-addlogin (can't remember which)
allowyou to force the internal id to ensure your second machine is same as
first.
dbo should always be a valid db user - can be fixed by sp_changeuserslogin,
this happens if the internal login ids are out of
sync.
As an alternative to backup restore you could detach sp_detachdb, copy the f
iles and then sp_attachdb, but personally I always prefer the
backup./restore route.
Good Luck
Mike John
"AFN" <DELETEnewsgroupCAPSaccount@.yahoo.com> wrote in message
news:%vjLb.46123$C87.11549@.twister.socal.rr.com...
> I have a 24/7 live Sql Server 7 database. The server hardware is old.
I
> have a new computer for this Sql Server, and I will also be switching to
SQL
> Server 2000. What's the best way to transition from one to the other?
I
> can install everything on the 2nd server, and start using a different
server
> name, and I can tolerate 20 minutes of downtime when I finally switch from
> one machine to the other. But I don't know the best way to get the
> database from one machine to the new machine, in a fast way. Other times
> I've moved databases, I've found problems with the user records not
matching
> to the same user IDs in the Master System tables. And stored procedures
> that have "dbo" owners suddenly freak out because that dbo isn't a valid
> user ID anymore, etc.
>|||If you can copy the files within the window I like copy wizard or
attach/detach.
If the files are TOO big then backup the orig, restore it on the new..
(Users are still working on the orig.)
When the restore is complete, kick users OFF of orig.. Backup log on orig,
restore on new, and you should be good to go. (with the exception of
matching up logins, which can be done while users are still using the orig
database.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"AFN" <DELETEnewsgroupCAPSaccount@.yahoo.com> wrote in message
news:%vjLb.46123$C87.11549@.twister.socal.rr.com...
> I have a 24/7 live Sql Server 7 database. The server hardware is old.
I
> have a new computer for this Sql Server, and I will also be switching to
SQL
> Server 2000. What's the best way to transition from one to the other?
I
> can install everything on the 2nd server, and start using a different
server
> name, and I can tolerate 20 minutes of downtime when I finally switch from
> one machine to the other. But I don't know the best way to get the
> database from one machine to the new machine, in a fast way. Other times
> I've moved databases, I've found problems with the user records not
matching
> to the same user IDs in the Master System tables. And stored procedures
> that have "dbo" owners suddenly freak out because that dbo isn't a valid
> user ID anymore, etc.
>