October 24, 2017 at 11:04 am
I've been trying to find some info on SAN replication and AG's. My situation is that I have a two node cluster with an AG. The nodes are local to each other. Trying to work with System Engineering to set up DR. Right now there is not an option for a third machine offsite right away. The plan offered is to use SAN replication to replicate the drives from the nodes to a dr site. If we needed to use the DR site then Engineering would spin up a VM and attach the replicated drives to the VM. I would install SQL and then start up the instance and away we go.
However, I don't think this is going to work. The databases on those VM's will think they are part of a cluster / AG, when based on this plan, that really wouldn't be the case when residing in the DR machine that was never part of the cluster or AG. Isn't that going to pose problems? I also have the situation where not every database on the instance is part of the AG. One reason for this is that these instances have SSISDB involved and its SQL 2014 and there is no native support for those being in the AG.
Thoughts, concerns and recommendations needed please.
October 24, 2017 at 2:37 pm
SAN replication of SQL Server database files is often just a way to spend money and corrupt databases. Way more people will promise a working solution than deliver one. SQL Server does not use files the way a file server does, and most SAN replication is geared toward filers. I'm not saying it's impossible, just test and re-test recovery in the remote site often. This is definitely something to which I would respond to the requester with "I won't believe you until you prove it."
Should you go down the SAN replication road, you will occasionally need a server in the remote site anyway, because if you don't validate that the replication produces a recoverable database before the disaster, you can pretty much guarantee it won't work when you need it.
If you wish to have a recoverable database in a remote location without having an instance in place to test your process, then your safest bet is simple log shipping: copy log backup files and occasional full backup files to the remote site (or SAN replicate your backup folders, which is far safer than replicating raw DB files), keep enough copies around that you can reliably restore (you still must test and prepare this process), and have a plan in place to restore the database with as much log data as you can get to the site. A database restored from a backup file knows nothing about it having been in an AG at the time of backup. You can restore that database anywhere that will hold it, and you're ready to go. Because of this, you can stand up a simple stand-alone SQL Server (does not need production specs), perform a restore-and-then-DBCC-CHECKDB test to be sure your stuff works correctly, and then drop everything.
Additionally, you should increase the frequency of log backups and ensure those backups are quickly moved to the DR site. There will be data loss on recovery if you switch to the remote site without grabbing the final log backup from the main site before it shuts down. SAN replication can advertise no data loss, but you'll have to prove it in your environment with your data before it can be trusted.
Eddie Wuerch
MCM: SQL
October 25, 2017 at 7:55 am
Thanks Eddie. That's helpful, Much appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply