5

I have SQL Server 2014 cluster with 2 nodes and a third server with iSCSI target server configured on it, all of them are virtual Servers with Windows Server 2012 R2.

Both nodes of cluster are iSCSI initiators and connected to the iSCSI target which was converted to Cluster Shared Volume(CSV).

I'm using Ola Hallengren scripts to make backups, the idea here is backup to local Z:\ drive and mirror to C:\ClusterStorage\BackupSQL (this folder is the cluster shared volume), backup run successfully on each location by separate, but if I configure the script to use Mirror Device, it gives me an

Error 3212 The mirror device "%ls" and the mirror device "%ls" have different device specifications.

The block size in both drives C: and Z: is the same, tested with the command:

fsutil fsinfo ntfsinfo <drive:>

After the job runs unsuccessfully the folder structure was created, but no backups are inside, what am I missing?

This works:

BACKUP DATABASE [msdb] 
TO DISK = N'Z:\MSSQL\Backup\some_path_and_name.bak' 
--MIRROR 
--TO DISK = N'C:\ClusterStorage\BackupSQL\some_path_and_name.bak' 
WITH CHECKSUM, COMPRESSION, FORMAT

This also works:

BACKUP DATABASE [msdb] 
--TO DISK = N'Z:\MSSQL\Backup\some_path_and_name.bak' 
--MIRROR 
TO DISK = N'C:\ClusterStorage\BackupSQL\some_path_and_name.bak' 
WITH CHECKSUM, COMPRESSION, FORMAT

This NOT work:

BACKUP DATABASE [msdb] 
TO DISK = N'Z:\MSSQL\Backup\some_path_and_name.bak' 
MIRROR 
TO DISK = N'C:\ClusterStorage\BackupSQL\some_path_and_name.bak' 
WITH CHECKSUM, COMPRESSION, FORMAT

the last one gives me the Error 3212

  • Does the job give any errors? Are you writing to the command log table? Anything there? Can you write a file on Z:\ and see it on the C:\ path? Are they both listed in the resources available to the cluster at the Windows Side? Listed as dependencies for the SQL Server service? SQL won't work with a drive that its service isn't a dependency on - don't want a drive down but SQL up and trying to use the drive. – Mike Walsh Dec 6 '16 at 18:23
  • The job history and the command table gives me the same error, also if I try to extract and run TSQL directly on the MSSMS, ** Msg 3212, Level 16, State 0, Line 1 ** I can write files on both drives C: and Z: – cesarlinux Dec 6 '16 at 18:53
  • 3
    An aside here, mirrored backups are not a good idea. A successful backup would then require both targets to be written successfully (and your backup times are limited to the slowest target). It would be a lot better for you to write to one target and then have the file copied off to the secondary location. Using something like MinionWare backup (free) could handle that sort of thing for you. – Nic Dec 6 '16 at 19:28
  • Not sure what is different between CSV and a normal drive but check the "Hardware Requirements for Backup Mirrors" section in technet.microsoft.com/en-us/library/ms175053(v=sql.110).aspx – jesijesi Dec 7 '16 at 4:50
  • Thanks @Nic I didn't realized that a successfull backup must be made in both targets, I'll investigate about MinionWare Backup. – cesarlinux Dec 7 '16 at 11:28
1

I hope this is Partial answer and this might helpful to you.

Clustered Storage: clustered file system is a file system which is shared by being simultaneously mounted on multiple servers. There are several approaches to clustering, most of which do not employ a clustered file system (only direct attached storage for each node).

Local Drive Z: Microsoft says "drive", what they actually mean is a partition - a formatted block of space on an existing physical drive. In your case, the "Z" partition is most likely the EFI System Partition (ESP) which holds the boot information for the OS.

Cluster Storage and Local Drives are different from each other.

Hardware Requirements for Backup Mirrors:

Multiple-storage servers working together to respond to multiple read and write requests. clustered file system is a file system which is shared by being simultaneously mounted on multiple servers.

Mirroring applies both to disk and Storage file system (disks do not support continuation file system). As in earlier versions of SQL Server, all backup devices for a single backup or restore operation must be of the same type, disk or tape.

Within these broader classes, you must use similar devices that have the same properties. Insufficiently similar devices generate an error message (3212). To avoid the risk of a device mismatch, use devices that are equivalent,such as only drives with the same model number from the same manufacturer. or use same Cluster storage file system for Backup and Mirror paths with different folders.

This might work:

BACKUP DATABASE [msdb] 
TO DISK = N'C:\ClusterStorage\BackupSQL\\some_path_and_name.bak' 
MIRROR 
TO DISK = N'C:\ClusterStorage\MirrorBackupSQL\some_path_and_name.bak' 
WITH CHECKSUM, COMPRESSION, FORMAT

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.