2

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)?

3

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.

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.

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:

  1. Place Database in Full recovery mode
  2. Take a Full Backup and restore it with a new name and location without recovery
  3. Take a Tail Log Backup of the source database (taking it offline).
  4. Restore the Tail Log Backup on the new database with recovery.
  5. Rename or drop the old database
  6. Rename the new database (back online).
  7. Restore the desired recovery mode of the new database.
  • Yes, that seems to be a basic SQL Server based option, but requires services to go offline for quite some time depending on DB sizes. I was wondering if we could avoid it or at least minimize. Normally I would go with block level storage replication and then a hot-swap, but here we have different storage providers and this is not available. – Bartosz X Sep 4 at 14:33
  • There's not a simple Windows way to do that. For SQL Sever you can use log shipping, and reduce the downtime to a tail-log backup/restore and database rename. But it's a bit of scripting per database. – David Browne - Microsoft Sep 4 at 15:53
1

So after some digging and applying this solution in real world situation I can share with you that this is possible to migrate your SQL Server (system DBs, users ones or even the installation files) using stock Windows Server tools. All you have to do is:

  • introduce the new storage to your OS
  • open disk manager (diskmgmt.msc) and convert your disks (to migrate) to dynamic
  • set up a disk (partition) mirroring targeting your new drive
  • wait for them to synch
  • delete mirror from your old disk (partition)

There are limitations for this process and its obviously expensive for I\O and will definitely slow down your SQL Server instance, but most importantly - this is possible to move it while it remains online for the entire time, which was the prime need for my solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.