April 17, 2007 at 3:42 pm
Hey all,
We have purchased a new SAN and want to move our production environment with the least affect possible. I know that I can detach and reattach my production dbs, but what about the SQL server dbs such as master, model, msdb and tempdb? Can these also be detached and reattached, or are we going to need to create a new SQL Server installation? This could be a problem because of our clustered environment and the way our apps connect to the dbms.
Any advice is greatly appreciated.
Regards,
Keith
April 17, 2007 at 3:51 pm
April 19, 2007 at 2:26 am
Hi there,
There is not standart way for moving msdb and model databases.
There is no real need to move master database. Master db will automatically updated while attaching your other databases. Users and logins can be migrated by script (You can find it on internet).
We faced same chalange last year - migrating databases to microsoft cluster environment.
April 19, 2007 at 5:37 am
I've used the link David refers to in the past...it works well. However be careful when using it for Win2003 R2 environments as the account you use needs higher privs to be able to physically copy the .mdf and .ldf files to another partition area. Will be testing this again soon. Probably best to use a Windows Admin account.
April 19, 2007 at 6:28 am
The responses so far seem to believe not only did you purchase a SAN, but that you have a new server as well.
If you are connecting an existing server to the SAN, the process is easier provided your databases aren't on the system partition.
This process doesn't involve SQL, and allows you to move the master, MSDB, and temp databases too.
Connect your server to the SAN. Assign disks to the server (in my experience with an EMC SAN, this requires reboots, so plan accordingly). Format the disks and assign a drive letter (any one will do at this time).
Shut down SQL server, and all SQL Server services. Now, copy your exact folder structure and files from the existing location to the new location. Then, rename your old disk to a different drive letter, rename you new disk to the new drive letter, and start SQL. It shouldn't notice anything has moved and just respond faster.
The key is shutting down SQL when doing this, as it closes your database files so they can be copied.
April 19, 2007 at 7:21 am
Ditto!!!
We purchased a new SAN several years ago and had the same situation (40TB). One thing to consider if you detach and reattach is that your user's default databases will change. When a user's default database is dropped/detached, the default becomes the next db in line (alphabetical I think).
The configuration we have is all software is installed on internal drives including SQL Server (binaries). ALL dbs (system and user) are on SAN drives. If you have ANY of your dbs on internal drives, I highly suggest that you move them to your SAN drives.
We also use the "copy" concept described above when we have to "expand" a large Raid 10 drive (Raid 5 expands on the fly, Raid 10 is less forgiving).
In the case of a cluster, you will need to remove the drives from the cluster group before you rename the drives and then add them back after you rename the drive letters. Net affect is that you have swapped out the drives as opposed to moving the databases to a new drive letter.
I hope that I didn't misunderstand the question. I've done this so many times, that I've ran out of fingers and toes. 8-|
-Joseph
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply