December 12, 2010 at 12:42 pm
For SQL server 2005 server
1.Existing SQL server system databases are on SAN
2.Also replacing old SQL server machine with new one ,installing SQL Server on it
3.New SAN will be the same capacity and configuration as the old one, it's exact replica of the old SAN, old SAN had some hardware problem hence it will be replaced
4.I will have new SQL server and new SAN altogether.
My plan of action...
1.I have taken SQL server offline on old machine after taking all system db backups except MSSQLresource db as I can't take backup of that.
2.I have copied .mdf and .ldf files of systemdb's on to new SAN and renamed(_old) it .
3.I will install SQL server onto new machine, take SQL server offline and replace new systemdb files with _old ones and rename them as per SQL standards
4.restart the SQL server
I am not sure if the SQL server will restart? will this plan work?
Please advice
Thanks in advance
December 12, 2010 at 1:50 pm
In theory yes, but I question why you want to do it this way. There are a few issues that can cause you problems.
Firstly you can backup the resource database, you just can't do it from within SQL. You take a backup of the resource database as you would backup any other flat file.
If you are definitly going to go ahead with this plan you need to at least check that the new and old servers are at exactly the same patch level. This means not only Service Packs, but any cumulative updates or hot fixes applied. If they aren't you may have trouble starting the service after replacing the new files with the old files.
Are you keeping the same server name? If not you will have issues with MSDB as the source server names on the jobs will be wrong. This can cause them to fail, but it is possible to update the sysjobs table with the new server name.
What do you have in the Master database that you need to copy it from the old server?
Having said this I generally don't recommend doing this sort of action unless building the server from scratch is too complex, like if you have a few thousand jobs.
Everything can be scripted or exported and then imported to the new server, and this is by far the safest way of doing things. Since you are building a new server on a new SAN, it would also allow for full testing.
I would rather:
1) Install SQL on new server
2) Script all databases, logins (sp_helprevlogins), Jobs
3) Recreate all objects on the new server
4) Migrate all SSIS packages..... anything else that needs to be migrated
5) Restore all databases from last backup
6) TEST
For final migration:
1) Restore all databases from last backup with no recovery
2) Stop current live system and take a last set of log backups
3) Restore all log backups to last log
4) Bring the new system on line after testing.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
December 12, 2010 at 2:25 pm
Thanks a ton Leo for the detailed explanation.
The old server is on cluster and now company has decided to go ahead with standalone server...
I agree with the risk involved copying system db files and I have decided not to go ahead with that plan.Master database has some important stored procedure for server level activity, yes I would keep the same name for the new server as old and I will have old server until the new server goes online and tested successfully for a month or so...
well as you said i will backup the resource db .mdf and .ldf files and copy/replace on to new server .
what will you suggest for master db backup/restore?
I can backup/restore msdb and model on to new server?
I will follow your steps for all user db's though...
Thanks again
December 12, 2010 at 4:44 pm
Note this article: http://support.microsoft.com/kb/264474
Taking it one database at a time.
Model: Unless you have modified the Model database for your particular requirements there is no real reason to move it to the new server.
MSDB: Unless you have a lot of jobs, I'd prefer to just script things out rather than replace the database. If you want to copy MSDB from the old server, then I would just copy the *.mdf & *.ldf as you had planned to.
Master: If you must replace master, I would recommend restoring rather than copying, but you need to start the SQL Service with a /m flag to be able to restore master.
I still however prefer scripting out the objects you want and re-creating them on the new server.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
December 13, 2010 at 12:05 am
Would use a hybrid of what Leo has done.
You can pretty much pre-prepare your whole environment by Mirroring your databases across to the new server, log ship if you want. You can script out and sync logins from the old to the new.
You can then progressivly disable databases on the old and activate them on the new. Very low risk for each database as you can dedicate your time to each one to ensure it is working.
There by having the new environment up and running with as little downtime as possible.
You could also do a "big bang" approach as well with very little downtime and may suit your option to go from a cluster to a sinlge server. You may have to consider how to bring up your cluster instance if you need to go back to it while the other is still running.
December 13, 2010 at 12:25 am
I would restore norecovery, then log restores until ready. If you move the db's individually, you risk having problems if there are links between the db's. Also remember to check for any certificates, linked servers, extended proc files etc.. check mail is working, check for running trace flags and server configuration too.
December 13, 2010 at 1:11 am
I have a question:
Are we sure that restoring/copying the system databases from a clustered environment to a stand alone environment will work without any issue (as he mentioned that the existing environment is on cluster and the new one would be stand-alone)...I doubt.
December 13, 2010 at 5:44 am
Going from a cluster to a non Cluster should not be a problem. I don't know of anything that will cause any issues.
December 13, 2010 at 8:02 am
Thanks a lot everyone!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply