Hello all,
Recently I was tasked to move our entire SQL Server 2000 to a new server. I
work as a programmer so I have limited knowledge working with SQL Server, but
not enough to handle the task I been given without direction and assistance.
This is where you people come in. THANKS IN ADVANCE!!!!
Currently our SQL Server 2000 SP2 is running on a NT 4.0 box. The target
server will be running 2003 and we'll need to install SQL Server 2000 service
pack 3. I was wondering if anyone experience the same task and if so, how
you went about moving the entire SQL Server 2000 to the new server? What
problems did you encounter and how you resolved it? Will it be possible to
copy over the system databases from the source SQL Server to the target SQL
Server under a new operating system and service pack and still work? Or will
I have to script out all the system level components and rebuild it on the
target SQL Server and then restore the user databases?
I have to make sure all of our DTS packages, management services, database
maintenance plans, replication and link servers are counted for and in
working order. God help me.
Any help or links would be greatly appreciated.
Thank you very much.I would be looking for another job at the same time. You know... just in
case. Also review your contract with the employer ensure that you can't be
sued if things go wrong, and they probably will. Do testing on another
machine first. Ask around for a good employment lawyer and it may be a good
idea to bring a small tape recorder to work to record your conversations
with superiors. You never know what they may start telling you, if things go
wrong. You can sue them afterwards. Dont be a pussy. Fight for yourself.
That is just life. Good luck!
"Scott Yu" <Scott Yu@.discussions.microsoft.com> wrote in message
news:028B088B-D36C-40D4-9D47-0B297F0838E4@.microsoft.com...
> Hello all,
> Recently I was tasked to move our entire SQL Server 2000 to a new server.
I
> work as a programmer so I have limited knowledge working with SQL Server,
but
> not enough to handle the task I been given without direction and
assistance.
> This is where you people come in. THANKS IN ADVANCE!!!!
> Currently our SQL Server 2000 SP2 is running on a NT 4.0 box. The target
> server will be running 2003 and we'll need to install SQL Server 2000
service
> pack 3. I was wondering if anyone experience the same task and if so, how
> you went about moving the entire SQL Server 2000 to the new server? What
> problems did you encounter and how you resolved it? Will it be possible
to
> copy over the system databases from the source SQL Server to the target
SQL
> Server under a new operating system and service pack and still work? Or
will
> I have to script out all the system level components and rebuild it on the
> target SQL Server and then restore the user databases?
> I have to make sure all of our DTS packages, management services, database
> maintenance plans, replication and link servers are counted for and in
> working order. God help me.
> Any help or links would be greatly appreciated.
> Thank you very much.
>|||I hope your office has carpet all over because it will be easier for you when
they will ask you to do some cleaning...
Anyways, take a test box (make sure they don't try to give you a PDA!),
restore your databases on the test box, export your sql logins from the
source database to the test. Also keep in mind the sql jobs and if you have
any custom sql error msgs, copy them over.
Sasan Saidi
"Jason Robertson" wrote:
> I would be looking for another job at the same time. You know... just in
> case. Also review your contract with the employer ensure that you can't be
> sued if things go wrong, and they probably will. Do testing on another
> machine first. Ask around for a good employment lawyer and it may be a good
> idea to bring a small tape recorder to work to record your conversations
> with superiors. You never know what they may start telling you, if things go
> wrong. You can sue them afterwards. Dont be a pussy. Fight for yourself.
> That is just life. Good luck!
> "Scott Yu" <Scott Yu@.discussions.microsoft.com> wrote in message
> news:028B088B-D36C-40D4-9D47-0B297F0838E4@.microsoft.com...
> > Hello all,
> >
> > Recently I was tasked to move our entire SQL Server 2000 to a new server.
> I
> > work as a programmer so I have limited knowledge working with SQL Server,
> but
> > not enough to handle the task I been given without direction and
> assistance.
> > This is where you people come in. THANKS IN ADVANCE!!!!
> >
> > Currently our SQL Server 2000 SP2 is running on a NT 4.0 box. The target
> > server will be running 2003 and we'll need to install SQL Server 2000
> service
> > pack 3. I was wondering if anyone experience the same task and if so, how
> > you went about moving the entire SQL Server 2000 to the new server? What
> > problems did you encounter and how you resolved it? Will it be possible
> to
> > copy over the system databases from the source SQL Server to the target
> SQL
> > Server under a new operating system and service pack and still work? Or
> will
> > I have to script out all the system level components and rebuild it on the
> > target SQL Server and then restore the user databases?
> >
> > I have to make sure all of our DTS packages, management services, database
> > maintenance plans, replication and link servers are counted for and in
> > working order. God help me.
> >
> > Any help or links would be greatly appreciated.
> >
> > Thank you very much.
> >
>
>|||In addition to all the CYA advise, you CAN move the system databases. Take
these measure to heart:
1. Make sure the physical paths for your system databases is the same for
both servers. Whatever they are on the old box, mimic those paths exactly on
the new one when you're installing SQL Server.
2. When building the new box, only upgrade to SP2 until you've migrated
your databases. Only AFTER my databases came online would I be continue to
upgrade to SP3.
3. Once you've got the new server at SQL2K, SP2, and the physical paths to
your system files match exactly on both servers, you can take both sql
servers offline, do a flatfile copy of your old master .MDF and .LDF files
from the old box over the files on your new box (after you've protected them
by copying them to a safe, unrelated location, please - we're professionals).
4. Restart the service on the new box. (You may notice some suspect
databases pop up in Enterprise Manager. It's ok.)
5. Assuming everything comes up ok (see Jason's submission), you can mimic
these steps with the other databases, taking flatfile copies from one box to
the new server with all physical paths to the flatfiles matching.
6. You'll have to address two issues you create when doing this. One is
that the master database is out of synch with what should be the default
instance name (assuming your using default instance names - it's a problem
regardless). You'll need to run sp_drop_server and sp_add_server (@.local ='local') to get the right server listed as the local server (see Books
Online). You'll also need to update the [originating_server] fiels in the
sysjobs table in MSDB. Update the field to match the local instance name you
used with the sp_add_server.
7. Once your databases (at the very least, the system databases) have come
online, then I'd backup up everything and complete the upgrade to SP3, and
backup everything again.
"Scott Yu" wrote:
> Hello all,
> Recently I was tasked to move our entire SQL Server 2000 to a new server. I
> work as a programmer so I have limited knowledge working with SQL Server, but
> not enough to handle the task I been given without direction and assistance.
> This is where you people come in. THANKS IN ADVANCE!!!!
> Currently our SQL Server 2000 SP2 is running on a NT 4.0 box. The target
> server will be running 2003 and we'll need to install SQL Server 2000 service
> pack 3. I was wondering if anyone experience the same task and if so, how
> you went about moving the entire SQL Server 2000 to the new server? What
> problems did you encounter and how you resolved it? Will it be possible to
> copy over the system databases from the source SQL Server to the target SQL
> Server under a new operating system and service pack and still work? Or will
> I have to script out all the system level components and rebuild it on the
> target SQL Server and then restore the user databases?
> I have to make sure all of our DTS packages, management services, database
> maintenance plans, replication and link servers are counted for and in
> working order. God help me.
> Any help or links would be greatly appreciated.
> Thank you very much.
>|||First of all, I would like to thank you for your response. Here are my
responses to your instructions.
I already setup a new domain on our test network which I will be using for
this purpose. The test network will consist of 5 SQL Servers to mimic my
production SQL Servers. The new servers are partitioned to mirror the old
servers, and I will build the directory structure to mirror the old server as
well. The test network is totally isolated from our current production
network, so I'll be naming the new 2003 servers and SQL Server 2000 like
there old NT4.0 SQL Server 2000 counter part. Once tested and built, I was
going to rename the old servers, disable all it's jobs and services and then
introduce the new servers to the production domain. Does this sound okay so
far?
> 2. When building the new box, only upgrade to SP2 until you've migrated
> your databases. Only AFTER my databases came online would I be continue to
> upgrade to SP3.
#######################################
Microsoft
states(http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp)
"Windows Server 2003 requires SQL Server 2000 Service Pack 3 or later to be
applied". Hmmm~ so I'm not sure if I can install SP2 for SQL Server 2000 on
my Windows 2003 test box. If I can, please explain.
> 3. Once you've got the new server at SQL2K, SP2, and the physical paths to
> your system files match exactly on both servers, you can take both sql
> servers offline, do a flatfile copy of your old master .MDF and .LDF files
> from the old box over the files on your new box (after you've protected them
> by copying them to a safe, unrelated location, please - we're professionals).
#######################################
I have a maintanence job that backup all the system databases and selected
user databases with log files on a daily basis. Instead of detaching the
.MDF and .LDF, I was going to restore it from a current backup. Will this
work as well?
> 6. You'll have to address two issues you create when doing this. One is
> that the master database is out of synch with what should be the default
> instance name (assuming your using default instance names - it's a problem
> regardless). You'll need to run sp_drop_server and sp_add_server (@.local => 'local') to get the right server listed as the local server (see Books
> Online). You'll also need to update the [originating_server] fiels in the
> sysjobs table in MSDB. Update the field to match the local instance name you
> used with the sp_add_server.
###############################################
Since my test box and SQL Server 2000 will be name the same as there
production counter parts, I should be able to skip step 6 right? If not,
please explain.
> 7. Once your databases (at the very least, the system databases) have come
> online, then I'd backup up everything and complete the upgrade to SP3, and
> backup everything again.
###############################################
Another idea of mine was to install SP3 on my old NT4.0 SQL Server 2000,
backup it and then move it. This way I might be able to over come the
service patch issue? What do you think of the idea? Are there any known
issues installing SQL Server 2000 SP3 running on a NT4.0 SP6 box.
Once again, thank you very much for your assistant. I know I am asking a
lot more questions and so I appreciate your time, knowledge and
professionalism.
Thank you very much.
Wanna be DBA software engineer
Scott Yu|||Hello, thank you very much for taking your time to respond to my post.
Currently I do have a test domain which is isolated from our production
domain. The 2003 servers running SQL Server 2000 will be name the same as
their old NT 4.0 SQL Server 2000 counter parts. The new servers will also
mirror the drive letters and directory structure. Currently all system
databases and selected user databases on the production servers are being
backup on a daily basis by a maintanence job.
After reading your reply I came up with few questions.
> 2. When building the new box, only upgrade to SP2 until you've migrated
> your databases. Only AFTER my databases came online would I be continue to
> upgrade to SP3.
#########################################
Microsoft states
(http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp)
"Windows Server 2003 requires SQL Server 2000 Service Pack 3 or later to be
appliced". Being the case, is it still possible to do what you mentioned in
step 2? I was thinking updating NT4.0 with SP3 first to resolve this issue.
Do you know of any issues NT4.0 might have with SQL Server 2000 SP3?
> 3. Once you've got the new server at SQL2K, SP2, and the physical paths to
> your system files match exactly on both servers, you can take both sql
> servers offline, do a flatfile copy of your old master .MDF and .LDF files
> from the old box over the files on your new box (after you've protected them
> by copying them to a safe, unrelated location, please - we're professionals).
##########################################
Instead of disattaching the .mdf and .ldf files, can I just restore it from
a current complete backup?
> 6. You'll have to address two issues you create when doing this. One is
> that the master database is out of synch with what should be the default
> instance name (assuming your using default instance names - it's a problem
> regardless). You'll need to run sp_drop_server and sp_add_server (@.local => 'local') to get the right server listed as the local server (see Books
> Online). You'll also need to update the [originating_server] fiels in the
> sysjobs table in MSDB. Update the field to match the local instance name you
> used with the sp_add_server.
#########################################
If I name my test machine the same name as my old production servers in the
test domain, can I skip step 6?
Once again, thank you very much for your time and knowledge.
Sincerely
Scott Yu|||More questions...
Should I restore the system databases in the new server in any particular
order?
Should I restore permissions before or after restoring user databases?
When I restore the system databases, will I detect all of the DTS packages,
Maintanence Jobs, Linked Servers, permissions, etc. or will I need to script
it out or recreate it?
Once I completed and tested the new servers. Do you have any advice on the
order I should start introducing the servers into the new domain.
As I mentioned I have five servers.
1. Production Server. Act as a Publisher/Distributor and Link Server with
Server 5
2. Development server
3. Gateway for user to adhoc and Subscriber to Server 1
4. Internet Development server
5. Remove server and Link Server with Server 1
Tons of questions right? Sorry, but your time and response are very
appreciated.
Thank you.
:)
Scott Yu
