November 6, 2006 at 7:39 am
I am in the planning stage of migrating all servers for an application to new hardware. Part of the project is migrating a central server that 12 other sites talk to, to a cluster environment. I came into the middle of this effort and am a bit lost on what is going on, but the plan is to use the existing disk array that the stand alone server is attached to for the cluster. The amount allocated to the databases on the server is huge 1.7 TB. I'm trying to think of the most efficient and safest way to migrate the 83 user databases. Is it possible to detach the databases on the stand alone server and then when building the cluster reuse the disks for the cluster, so that I could just attach the databases to the cluster. I know this is probably wishful thinking, and it sounds way too easy. If this isn't possible, what would be recommended for migrating to the new cluster.
Also, the databases are currently being backed up using Litespeed, which I have no experience with and am trying to understand. Does anyone know of any documentation on Litespeed that will help me to understand how it works for backup and recovery.
TIA
Michelle
November 6, 2006 at 8:25 am
Michelle
I don't know anything about Litespeed, I'm afraid, but here, off the top of my head, is how I'd go about moving the databases.
(1) Back up all databases in case there is any problem with re-attaching later
(2) Script out the creation of users in the user databases
(3) Script out creation of jobs, logins and anything else you can think of
(4) Detach user databases
(5) Stop SQL Server
(6) Back up the contents of your storage array to tape in case there are any problems with connecting it to your new cluster server
(7) Build cluster server, with storage array attached
(8) Attach user databases
(9) Recreate logins, jobs, etc from scripts
(10) Recreate users in user databases from scripts
I'm sure others will chip in with details I have left out that may make your life easier.
John
November 6, 2006 at 9:33 am
I agree with you that the most efficient approach is to detach/attach databases. However, if you would like to move your data to other disks, such as SANs storage a much more reliable storage at this moment, it will be another story. Be sure, your existing array can be used as a shared disk.
February 20, 2007 at 7:21 am
The migration from the standalone server to the cluster is fast approaching. I was wondering if the following plan can be used. Keep in mind the cluster will be using the same storage array that is currently being used by the standalone. The only thing I am concerned about is if the master databases are different between a standalone and a clustered environment. If so, this would not work. Please advise.
1) Shutdown SQL on the standalone server.
2) Rename or move the master and msdb files to a new location. The master and msdb files are currently on one of the EMC array drives.
3) Remove the old server from the network or rename
4) Install the SQL Server cluster on the new server using a local install with the virtual servername being the same name as the old server.
5) Shutdown SQL on the cluster server.
6) Rename the master and msdb files on the cluster and then copy the master and msdb files from the old server for use by the cluster.
7) Startup SQL on the cluster and verify that all the databases come online.
Will this work? My concern is that the master database created in a standalone install may be different than one created during a cluster install. Is it? Please help, I don't have much time to decide how to do the migration.
TIA
Michelle
February 20, 2007 at 7:53 am
Michelle
I wouldn't risk trying to restore the system databases. That's why I suggested scripting out logins, jobs and so on, so that you can do a clean install and then recreate them.
John
February 20, 2007 at 8:21 am
I realize your advice is the safest way, but it also could be error prone if I miss anything. The other problem is that the server is heavily involved in replication as both a publisher to and a subscriber from the 13 remote sites. How will that work? When I reattach the databases to the new cluster server will it restore the replication or will I need to recreate it all? I am trying to find the easiest way to do this with the least amount of risk for error.
February 20, 2007 at 8:38 am
Michelle
That's just too tricky. If you don't have somewhere you can test this before you do it for real, then a large part of it is going to be keeping your fingers crossed. I think a clustered instance of SQL Server uses the same master database, but you will need to use sp_addserver once you've restored it. As for the replication, I'm really not sure. I don't know how a change of server name will affect your publications and subscriptions. If, as you say, you create the new virtual SQL Server with the same name as the old server then I imagine the risk will be slightly less. You will need to make sure that the same snapshot location exists, and, if it's a network share on the server you are replacing, make sure you create the share as a clustered resource.
Don't forget to take backups every step of the way.
You might want to post this question again since it's only people who have already contributed to it who will have been notified that it has come back to life.
Good luck
John
February 20, 2007 at 8:55 am
If you replication, I suggest not to use the same master databases. YOu just never know when in future your replication fails !!!!!!!
I would suggest to create a new system databases which would be created with the new install. Then mapping the existing drive where you have the databases so that you can attach those. Scripting logins and jobs is very easy.
refer the below KB article
http://support.microsoft.com/kb/246133/en-us
But as far as the replication goes everyone would recommend to set it up again for the new server. Even Microsoft wont support problems arising in replication if you do it the way you plan to (i.e. using same system databases)
February 20, 2007 at 10:10 am
We have already migrated two of the sites successfully by copying over the system databases. What we did was prepare the new server to look like the old server by creating any share folders, installing SQL in the same location as the old server. When the time for migration occurred we shutdown SQL on both servers, copied over the physical data files from the old server to the new server (including the system databases). Start the new server making sure all databases came online successfully and checking their integrity. Then shutting down the new server, renaming the old server to a differnt name, and renaming the new server to what the old servename was. Then start up SQL Server on the new server. This was very successful and we didn't have to rename SQL Server with sp_dropserver, sp_addserver as it picked up the new name automatically. Everything including replication restarted successfully. The difference with this migration though is that we are migrating from a standalone to a cluster and that it is using the same storage that is currently being used. If we go the safe route and migrate logins, jobs, DTS, etc..., what about replication. Will we be able to attach the databases and replication will be restored or will it need to be recreated too? The distribution database is on a separate servers, the server that is being migrated is both a publisher and a subscriber.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply