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
EDIT 1:
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.
EDIT 2:
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)?
Best Answer
Follow the process here: Move Database Files to move the databases to the new storage. You can leave the insance online, but each database will need to be OFFLINE while its files are copied to the new location.
Alternatively you can present the new drives, shut down SQL Server, copy all the files to the new drives, unmount the old drives, mount the new drives in the same folders or drive letters as the old drives, and restart SQL Server.
In both cases take backups of your databases before you start DO NOT delete any files from the old drives until you have sucessfully migrated to the new drives and taken new backups of your databases.
To do that, use Backup/Restore, not Transnational Replication. Replication is more complicated, expensive, and does not support tables without a primary key, or all database objects. So, for each database: