locked
Can we Use SAN Mirroring for SQL Server 2005 RRS feed

  • Question

  • Hi, We have two SQL server 2005 servers on windows server 2003. One is primary and another is DR. Database files etc are kept identical on both servers. We have installed SQL Server 2005 on SAN drive. Now we want to use SAN mirroring to copy this whole installation drive ( along with DB, Log, TempDB files) from primary server to DR server. As we same files on both servers and installation directory location is also same. We think it should work. That means when we copy everything from Primary to DR server then SQL server should come up fine. As SQL Server was installed on two different servers separately so I am not sure if there is any registry settings etc which will create any issue.

    Can someone tell if it's fine to do SAN mirroring like this or not? We know there are other other options to mirror but we are looking into SAN mirroring only because of some reasons.
    Thanks,
    Friday, August 21, 2009 4:49 PM

Answers

  • Yes, this will work, but there will be a few resolution issues.  The names of the primary and DR machine aren't going to be the same.  Therefore, you would need to change connection strings as well as execute sp_dropserver/sp_addserver following a failover.  However, if you rename the DR machine so that it is the same as the primary you won't see any difference.  You could also load the IP address of the primary on the DR server and create an alias in DNS.  Then your applications and even SQL Server can continue to utilize the same name as it originally had when running on the primary.

    There are no issues from a database perspective, in THIS particular configuration.  That isn't meant to be a blanket statement for all of the various disk replication technologies.  The main issue with database files is when you have multiple files underneath a single database that span multiple drive letters.  While SQL Server performs a single I/O operation, Windows has a disk write boundary on a drive letter basis.  Windows will perform separate writes across each drive required for the I/O operation and when they all complete, will then respond to SQL Server that the I/O has finished allowing SQL Server to continue on successfully.  If you are using a technology that hooks directly to the disks and does block level replication, you can wind up with partial writes being replicated due to this drive letter boundary issue when a disaster forces a failover.  This issue does not exist when you are dealing with SAN replication.  That is because while Windows still runs on a drive letter boundary point for an I/O, the I/O is sent through your HBA and into the SAN controller.  Only when it is received at the controller cache does Windows get a success returned and the SAN will only return a success when all of the pieces of the Windows I/O transaction have been received.  So, regardless of the number of drive letters or mount points, the disk I/O effectively behaves as if you were writing to a single drive letter.  The SAN mirroring operators at the controller level, so it is only going to replicate committed transaction that have hit the controller and would not have the possibility of writing a partial I/O.  So, when you bring the system up at the DR site, it will always be consistent and current as of the last disk I/O that was mirrored.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Saturday, August 22, 2009 11:27 AM