I am going to upgrade SAN to new and faster SAN. My 80+ databases are stored on old SAN and I want to bring them to new SAN with minimum downtime. One approach is to add new disks to resource and physically molve mdf and ldf files and reattach . I am limited with one cluster only. Is there any faster approach ?
What kind of SAN are you using and what additional software have you purchased with it? There is some SAN technology that will do a bit by bit copy of all the data on your SAN. This way you could pasue your SQL Server, veryify that all your data has been commited. Then you could Pause, and replace. I have a few DELL Equaligc SAN's that I have worked with in the past that have been able to do this.
Seeing that you are going from one manafactur to another your best bet may be to copy the file. In the past when I have done migrations, where the server could not be down for long periods of time, I have used the phased approach. For example, the night before I would run a full backup, I would then copy that backup and restore it, but leave it in a restoring state. This way I could run a diff later so that I did not have as much data to move, one I was ready to move I would do one final backup, pause the server, started the restore. Hope this helps.
Just curious, are you installing a new SQL Server to go along with the new SAN or are you attaching the new SAN to existing hardware and have to move the files? If so, detach/attach might be the best option as with a backup/restore method you'd have to restore to a different name and then then rename/drop the databases once completed. Certainly doable, however with 80+ databases to manage that might become cumbersome. If it's a new SQL Server as well as a new SAN, I like the backup/restore method that Chris mentioned and have used it on several occasions in the past. Very easy way to minimize the down time. What is an average size of the databases? Are the SAN's located next to each other? What kind of downtime can your business support? These are some questions that might help drive a solution.
Hi John, Just adding new SAN and SQL Cluster will remain the same.Average Size of Database is somewhere 5-10 Gigs.We are also thinking about detach/attach.I will just generate the script for detach and attach for test database and based on that I can generate the script for all databases.The tiem consuming part is transferring files.The company has agreed for 1 hour window. Any suggestion is welcomed.
I think one of the big questions is can you do the transfer of 40-80 gigs worth of data, before your window closes. In addition to the copy don't forget you need to move the jobs, enable the jobs (if you move them before the databases), move the User Accounts then attache the databases. I think your best bet is the backup/restore. You can do the majority of the work while the databases are online, in addition you can script the whole process. Not that you can't script the copy step, but it is a bit more easier to script a Backup/restore process.