Hello every one. Hope all is well. Its been a while since my last post. Today I am going to write about SAN upgrade for one of my test sqlserver cluster instances. I am using SQL Server 2012 SE Active passive cluster and just upgraded the SAN from SAS to an SSD SAN. The upgrade involved 5 phases.
Phase 1: Capture the storage requirements and create storage
- Carve the LUNS on the new SAN and present them to the database server. I sent the storage requirements to our SAN Admin and he created the LUNS and gave me the discovery portal IP Address.
Phase 2: Present the new storage to the database server
- To make the new LUNS available to the database server launch the iSCSI initiator discovery tab in the database server. In the discovery tab click on Discover portal and enter the discovery portal IP. Now go to Targets tab and click refresh and you should be able to see all the new LUNS with status inactive. Select the appropriate target and hit connect until you connect all your drives. When you click connect enable Multi path IO and ensure you checked the ‘add to favorites’ check box. At this time the server should be able to see all the drives.
- Go to ‘Create and format hard disk partitions’ or Disk Management
- In the bottom portion the new disks should appear. Initialize the disk and assign the drive letters and format the disk.
- Do this on Active node and Passive node of the cluster
- On the active node get the disks online
Phase 3: Present the storage to the cluster and SQL Server
- Go to the Failover cluster manager –> ClusteràStorage–> disks in the Actions on the right side click on Add Disk and add the new disks individually
- The cluster is now able to see the new disks
- To make the disks available to the SQL Server Instance go to Failover Cluster Manager–>Cluster–>Roles–>MSSQLServer–> right click select Add Storage
- Right click MSSQLServer in Roles and check the show dependency report and the new disks are still not associated with the actual instance and freely lying
- To make them available to the instance go to Roles–>MSSQLServer in the bottom select the resources tab and go the other resources right click on SQLServer–>properties–> dependencies tab and add the new cluster disks as shown below:
- Now the new drives are available to the SQLServer instance.
Phase 4: Backup and Move the databases
- Backup all the databases (user and system databases)
- Script out all the logins and their appropriate permissions and save it (not required but just safer side)
- Stop if there is any replication setup and disable and stop publications. If you want you can also delete the distribution database if it is not already deleted after disabling the replication
- Script out and save sql server agent jobs
- I ran the following commands to move the Tempdb from T:\ to U:\
USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'U:\Data\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'U:\Log\templog.ldf');
- To move the quorum drive go to Failover Cluster Manager –> Cluster right click–>More actions–> Configure Cluster Quorum Settings:
Selected the new quorum drive
Click on Finish. This would not require SQLServer service or cluster service down.
- Took all the app services down and Detached all the user databases and then took the sql server services down
- Manually copied the Tempdb files to the new drive which is U:
- I gave the root folder as D:\ at the time of installing the cluster. Hence the Master, Model and MSDB databases were created in
- I copied the entire folder MSSQL11.MSSQLSERVER from D:\ to the appropriate new drive
- Changed Drive letter of D:\ to unused letter and then changed the drive letter of the new drive to D:\
- Brought the sql server services online
- Changed the Old data, log, Tempdb data, Tempdb log letters to an unused letter
- Changed to new data, new log drive letters to the previous ones
- Attached all user databases
- I was also using filestream in one of my databases. I restored that database instead of detaching/attaching
- I wanted the Tempdb drive letter as T:\ so ran the above commands again with T:\ since the old tempdb drive letter was changed to unused drive letter
- Took sql server services down, changed drive letter U:\ to T:\
- Started the sql server services
- Checked if all database are accessible and if I am able to run my queries and see the correct results
- Started the app services back
Phase 5: Remove the Old storage
- Go to failover cluster manageràcluster–>Storage–>Disks
- Select appropriate disk right click and select Take offline
- Once all the drives are offline go to iSCSI intiator and in the Targets tab select the appropriate target and click on Disconnect
- Once this is done go to Favorite targets and remove them from there as well.
That was the final step. This is a very small environment and it took me around 10 mins to copy around 60GB of data and log files and a total of 30 mins to complete the upgrade. Hope this helps.