April 7, 2011 at 5:51 am
Hi all,
We are about to install a new SAN here and, in a meeting yesterday, I heard that we will be doing replication at the SAN level from our primary site to a SAN at our DR site.
I'm not a SQL Server expert by any means; however, I do know that - for instance - copying data and log files does not create a restorable backup. I.e. file level copies do not work as a backup.
So, my question is, does SAN-to-SAN transfer of data provide a usable copy of a SQL Server 2005 database?
If the question is too general, I'll research and provide more information. I'm just not sure how to start researching this topic.
Any thoughts or advice are much appreciated.
Steve
April 7, 2011 at 10:56 am
It depends on the SAN and the software used there. There are transaction aware softwares used on some SAN systems that can copy the database from one location to another, and do it accurately. The last winner of the outstanding DBA of the year award uses that mechanism with her databases because they're so large she couldn't back them up any other way.
But, you need to use that phrase "transaction aware" or "aware of transactions" or "deals with transactions" to validate that this process will work for you. As you say, if they just copy the file, it won't work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2011 at 4:47 pm
I cannot speak for all SAN replication technologies but one would hope they are all transactionally aware else they are useless for database purposes and should only be used on fileshares.
the purpose of these technologies is to provide up to the second DR capabilities rather than a way of 'backing up' databases. however, once the replication is suspended and the target disks made read\write you will have database files you can attach to a SQL instance, either by attaching to an instance already installed on the target server or by copying the files somewhere else.
You can also of course SAN replicate the drive storing your actual database backups. I would advise you do still backup your databases by the normal means, especially the system databases.
SAN replication is the best way to replicate either very large databases or a large no. of databases. It is expensive though.
If your want to leverage this technology to its fullest for a SQL installation I recommend investigating boot from SAN.
---------------------------------------------------------------------
April 8, 2011 at 4:50 am
Thanks Grant, George.
I couldn't have asked for better experts to weigh in on this.
I've been researching this and there are differing opinions, particularly in the case where the RPO dictates that a very low data loss is tolerable. One school puts its faith in SAN technology, while the other leans toward using SQL Server tools for replication.
The latter group claims that there could be inconsistencies that roll forward/rollback may not catch (e.g. page chain rewrites occurring because some rows were shifted as a result of an insert or update) and you could still end up with a corrupted secondary database using SAN replication.
In any event, and whichever way we go, I'll ask our DBA to go heavy on DBCC CHECK DB on the new SAN.
George, I'll look into boot from SAN.
Thanks again gentlemen.
April 8, 2011 at 5:10 am
talk to your SAN vendor, get them to to tell you whether or not they guarantee database consistency. If they won't, don't rely on it for DR or HA.
---------------------------------------------------------------------
April 8, 2011 at 5:51 am
You can also of course SAN replicate the drive storing your actual database backups.
We are doing this - we backup to a cifs share , then that is replicated to another site.
SAN replication is the best way to replicate either very large databases or a large no. of databases. It is expensive though.
We found that it didn't make sense in that we don't have any VLDB. But if you do, I would go that route. It also is very fast (helps your RPO).
FYI - I believe the SAN company whose name starts with N does a restore on another LUN and then a checkdb on the database after it is backed up.
April 8, 2011 at 6:47 am
Craig Purnell (4/8/2011)
You can also of course SAN replicate the drive storing your actual database backups.
We are doing this - we backup to a cifs share , then that is replicated to another site.
Thanks Craig. That approach feels like a good one for me too
SAN replication is the best way to replicate either very large databases or a large no. of databases. It is expensive though.
We found that it didn't make sense in that we don't have any VLDB. But if you do, I would go that route. It also is very fast (helps your RPO).
We don't have any VLDB either.
FYI - I believe the SAN company whose name starts with N does a restore on another LUN and then a checkdb on the database after it is backed up.
Interesting... we have a SAN company whose name starts with N (followed by 'etApp'). I'll explore this and see if they do it.
April 12, 2011 at 11:56 am
FYI - I believe the SAN company whose name starts with N does a restore on another LUN and then a checkdb on the database after it is backed up.
This 'N' company uses: snapshot the LUN, then attach to another SQL Server and then run checkdb on that server. Very nice!:-D
April 12, 2011 at 12:13 pm
Wildcat (4/12/2011)
FYI - I believe the SAN company whose name starts with N does a restore on another LUN and then a checkdb on the database after it is backed up.
This 'N' company uses: snapshot the LUN, then attach to another SQL Server and then run checkdb on that server. Very nice!:-D
That DOES sound nice...thank you both for mentioning it. I forwarded this thread to our network guru.. he's always interested in trying new capabilities.
April 12, 2011 at 12:21 pm
SwayneBell (4/12/2011)
Wildcat (4/12/2011)
FYI - I believe the SAN company whose name starts with N does a restore on another LUN and then a checkdb on the database after it is backed up.
This 'N' company uses: snapshot the LUN, then attach to another SQL Server and then run checkdb on that server. Very nice!:-D
That DOES sound nice...thank you both for mentioning it. I forwarded this thread to our network guru.. he's always interested in trying new capabilities.
Hi, Need to clarify this a little more. The technique described above is done by an addon product called Snapmanager for SQL Server. It is not cheap. That said, if you have a lot of databases or very large databases it may be viable to shorten the backup/restore time.
April 12, 2011 at 12:28 pm
Craig Purnell (4/12/2011)
Hi, Need to clarify this a little more. The technique described above is done by an addon product called Snapmanager for SQL Server. It is not cheap. That said, if you have a lot of databases or very large databases it may be viable to shorten the backup/restore time.
Thanks Craig. I found out this morning that we have purchased Snapmanager for SQL Server. We also have purchased a CIFS license that I asked you about earlier.
April 12, 2011 at 12:36 pm
SwayneBell (4/12/2011)
Craig Purnell (4/12/2011)
Hi, Need to clarify this a little more. The technique described above is done by an addon product called Snapmanager for SQL Server. It is not cheap. That said, if you have a lot of databases or very large databases it may be viable to shorten the backup/restore time.
Thanks Craig. I found out this morning that we have purchased Snapmanager for SQL Server. We also have purchased a CIFS license that I asked you about earlier.
Please post back and let us all how it is going. Also, 2 good articles on implementing Snapmanager Here and Here
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply