I have a VM cluster designed to store multiple SQL Server instances. We use variety of storage providers (i.e. 3PAR, XIV) which have to be changed now (not relevant to what brand). On Oracle on Linux we use VLM / Oracle ASM to perform the storage swap while the Oracle instance remains online. I need to swap the storage of disks used as User_DB, User_DB_Logs and Backup partitions in Windows. Is there anything I can do in the SQL Server / Windows Server world to achieve it?
SQL Server Std & Enterprise 2012-2017
Windows Server 2012 R2 - 2019
I am looking for options like writing both data and log into two targets at the same time (using one MSSQL instance) or Windows Server / VMware level disk mirroring which would allow me to do a hot swap onece both disk are in synch.
My set up has well more than 500 individual SQL Server instances, many with 100+ DBs on it. Solution must be orchestrated and rather generic.
As far as I know there is nothing like that in Windows/MSSQL world, but this forum surprised me enough times to raise this question anyway.
I ended up with an idea to create second set of my databases on the same instance as they are primarily located. That would allow me to have a copy of my DB set located of freshly introduced disks (backed by fresh storage provider behind the VM). In my set up I need to migrate only user databases (including their logs) everything else stays on its current drive set (backups are easy to move). Therefore what I need to achieve now is to force one of the available HA technologies to create a copy of my DB set into the same SQL Server instance. Therefore the plan I have looks like that:
- Spin up new disks for SQL DB data and DB logs
- Introduce them to Windows and SQL Server
- Set up a Transactional Replication of all user DBs into the same SQL Server instance, changing DB names and their physical location on drives
- Once everything in synch, stop the SQL traffic, rename DB sets and fail-over into DB located on fresh drives
- Clean up (old DBs, replication, buffer pools etc)
That would allow me to introduce new drives with minimal time off.
Can you think about anything faster than (not involving introducing of new SQL Server instances)?