SAN Replication

Question

I'm wondering if the technology exists to use SAN replication for sql server 2005 disaster recovery.

I have a bunch of prod servers I want to add to a san, I then want to have another bunch of servers at a dr site connected to another san.

Is there a technolgy ( non sql ) to enable full ( real time ) san replication of the data ( for SQL Server databases ).

I don't need alternate suggestions, this solution has been proposed to my clients, I don't think it's available, any confirmation one way or another would be very helpful, thanks.

 

Answer 1

Yes,

       I am working with the HP tecnology Continuos Access, you can replicate the data on asyncrono or syncrono way. The answer is: If you have one hardware solution with high availibility ( duplicate cache, duplicate fibra,etc.) , you can use this solution as disaster solution to SQL, Exchange, etc. and it is a solution for the future ( it is independent of the software and version). 

Answer 2

Hmm I'll have word with HP, I tried to speak with EMC without much success, the one person I did get to speak with didn't think it was possible.

SAN replication only really gives availability I still need to be able to protect against data damage or loss, still it's a start thanks.

Answer 3

EMC does have a long-distance SAN-based replication technology, but it does not come cheap.  It is based on their Symmetrix line of storage arrays.

So, the answer is "yes, for a price".

 

Kevin Farlee

SQL Storage PM

Answer 4

You should take a look at this whitepaper SQL Server 2000 I/O Basics http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx#EADAE to get an understanding of some of the considerations. Be aware that simply replicating the changes on the disks might leave you with a version of the data on the second site that never really existed at the primary site, meaning that there's a possibility that SQL Server would not be able to recover the database once the files were attached to SQL Server. Since SQL Server relies on writes to the disks occuring in a certain order, you need some form of 'consistency group' mechanism that allows you to group the data and log files together so that the writes are properly ordered on the secondary as well.

There are geographically-dispersed failover clusters that have some storage-level replication for the SQL Server disks. You can check http://www.microsoft.com/windows/catalog/server/default.aspx for potential solutions (click on the Hardware tab, then on 'Cluster Solutions > Geographically Dispersed Cluster Solutions).

Answer 5

Hi Don,

    I talked several times with you on IT Forum and PASS (Barcelona) above this topic. We are using :

StorageWorks EVA5000 / CLX EVA for Windows / Secure Path / ProLiant BL45p with HPBALCF 105 and StorageWorks

      But the name of the technology that uses CLX EVA to replicate between EVAs is Continuos Acess.

With this solution, do you see the problems that you comment in your message with SQL?

Answer 6

Yes the solution is available today - we are currently developing a prototype for SAN based replication for Disaster Recovery. 

We have a EMC clariion CX500 and are using RepliStore...

Real time replication is limited by the money you wish to spend for the "Pipe" / bandwidth and the # of transactions...

Answer 7

I'm beginning to think this is overkill for what the business actually need. As a DBA I'm pretty well up on HA solutions but this is going beyond my knowledge. I hadn't actually thought about the sequence of i/o , with applications that work with mutliple databases that could be a nightmare. The proposal was that the DR SQL Servers would be on-line, but what you're saying is that SAN replication would only replicate the datafiles, in DR I'd actually then have to attach all the databases to the replicated files so my DR site wouldn't even be warm.

I should say that I'm interpreting a proposal, not to necesscarily dismiss it, but I have to understand the proposal ( and if it's actually possible/practical )  if I'm going to recommend we move forward with it.

Thanks for starting this form btw .. just what I was looking for!

Answer 8

As you can see from just these replies there are several different products and configurations from several vendors, and the options are continually changing. So within SQL Server, rather than try to test every combination from many different vendors, we make sure we document how our product works and what its requirements are so that customers and vendors can determine which of the various products and configurations are appropriate for use with SQL Server. And of course, we have our own built-in features for much of this space (such as Replication and Database Mirroring) which are designed with our requirements in mind and tested to extreme levels.

Hopefully the various vendors strive to understand how SQL Server's needs differ from mere file-level replication. If they really are concerned with ensuring your data is reliably replicated while addressing our requirements, they likely have customer SQL Server-specific whitepapers and well-tested configurations that can provide the appropriate guarantees. However it is also up to the buying customer to be aware of the requirements (as described in our SQL Server IO Basics whitepaper) and do their own questioning and testing so they know they can trust the solution they ultimately choose.

Regarding 'continuous' replication forms, realize that SQL Server has requirements regarding write-ordering across devices. Any solution that does not take the relationship of devices into account can have the data in the files appear slightly differently at the secondary than at the primary. This might lead to an unrecoverable database at the secondary. 'Continuous' may also mean either synchronous or asynchronous, depending on the solution/vendor. Synchronous is great, but it probably will slow down operations on the primary since write have to occur locally as well as remotely before they can be acknowledged and the transaction committed locally; so for larger distances synchronous solutions cause unacceptable response times. Async solutions write locally and then write remotely at some 'later' time (perhaps sub-second, but still at a later time); this means there's information recorded at the primary that might not yet be recorded remotely when the primary fails. This means that committed transactions can be lost (in addition to the possible write-ordering consideration from above).

There's also considerations with block-level or track-level replication: Does the write at the secondary cause the data/log data to be rewritten, resulting in a possible loss of previously committed changes.

I've also heard vendors say that there's no data loss in the general case. But of course it's precisely in the disaster case when you need this to have no data loss, not the general case when everything is going well.

Anyway, strive to understand the vendor's solution, compare it to SQL Server's requirements, test it 'on paper' to find any theoretical issues, and test it hands-on to see if it can survive the scenarios you need it to. And of course, train your people, document your processes, and periodically test your failure processes so you know they work and so that there aren't surprises when a true disaster occurs and your most-junior person is trying to get your billion-dollar system back online at your secondary.

Answer 9

I'm testing san 2 san replication on 2  different sites (300+ miles between them). Xiotech, product to replicate san disks called kashya. It is a driver, the primary site sees SAN, to set up the DR side you'll need to disable the driver, then bring ms sql exactly as it is set up on the primary side exept of all the databases, just your system databases need to be in the right place, so when the replication starts anf the primaru site fails (simulated by posix library  kill in our case), then the sql on DR side actually can start and don't complain about things.

Kashya claims as many other venders to support consistency groups, have 0 data loss and work as fast as possible etc. Will see very soon.... 

Answer 10

I'm in a sensitive position both politically and professionally, as a SQL DBA contractor I'm seen as something of a sql server "expert" and politically I have to be aware of the people around me involved in the proposals and business plans. The whole project revolves around server consolidation and the introduction of a SAN, although I'm not totally sure it will all happen at once. The proposed soution shows database mirroring for 100 databases, which I understand is not really practical on top of san replication .. your new forum was just waht I needed - thanks. I appreciate the advice.

Answer 11

The project is similar, but most of the production is sql 2000 and I deal with VLDB, some of them produce their own size in transaction logs under 8h so it’s a nice one.

The task would be sensitive to amount of data to replicate and how busy your databases are and how thick is your channel between 2 data centers. How many databases 1 or 100 – doesn’t matter, att the business is happening under ms sql server any ways. And of course choose your SAN is, LUN, NICS, routers, OS on servers carefully and make your self nice 100 paged tech spec, it will save you head one day if thing will turn not the way you’d like them to be J. If it’s EMC even do not think about getting any way from their supported configurations, they will drop the support.

With sql 2005 I’m looking at their database mirroring. On other side I will not have to support plenty legacy applications in sql 2005 that have no clue about living somewhere else (not fault tolerant, do not reconnect automatically, use getdate() for timestamps so will go crazy when all that mumbo-jumbo will fail over on a server in a different time zone and the list goes on).

Honestly, when we are talking DR I’d worry about your applications first. Can they handle a simple fail over like cluster failover, can they handle DR comes next, are you going to have the same domain, the same or different server names, what are you running on that ms sql instance can have nice binary attributes that contain plenty configuration information in every reports meta data for example atc. So how to replicate a ms sql database is not a question, the question is if that thing that uses it is going to be in peace with DR environment. And if the answer is no, you are not there yet, your client is not there yet to be exact.

When you will talk san 2 san replication in many cases you will get warm stand by, and will have to deploy your applications on both sides at the same time, so if there is no good configuration management, I’d say solve that problem first or the maintenance will bite you good… at least. And warm stand by is not so easy for a business to swallow because they have to keep the clone of your production in some other place in some other state with all the w2 admins in place and you are telling them that all that stuff will get busy if and only if your main site will get some nasty X days power outage when your generators are out of juice or earth quake or hurricane or something and if not there is no way to load balance 2 sites because it’s whole different song J.

With sql 2000 one does not have plenty options when it comes to DR: replication, log shipping, VDI like solution (same log shipping in profile) san 2 san replication 9depends what are they using inside) , with sql 2005 there is one more option – mirroring, didn’t try it yet, don’t know how good is it, it’s in my plan soon enough.

If your business can tolerate 5-15 min data loss, maybe you are better of with log shipping to a hot standby or some similar implementation of ‘pure man’s cluster’ J, of course you will not flood your channel in process. Many san 2 san replication venders claim they can do stuff faster and transfer less then any software on top of them and it as sync, not as async (log shipping, sql replication etc. custom stuff sitting on VSS, VDI). Be aware of commercials… esp white paper stuff. This is geared not for you to buy it’s for them to sell it to you. To ‘flush’ ms sql cache they will have to call one of 2 things: VDI (ms backup api like VERITAS and plenty 3-rd party venders do) or VSS (same 3-rd party venders but applicable to server 2003), in other words they will have to flush cache, and mo often they take a snapshot more often they will have to do it, at least transaction log they will have to flush and system databases or when your files grow and your master is not aware of it yet the restore on DR side if not going to look pretty.

san 2 san replication can translate into some form replicate disk blocks replication, ask for a nice trip to the venders site, have your scripts and databases to test ready and sit there and start breaking their system. This will show you what is for real and what a commercial indeed is. This can save you and your client some good money. As a contractor once I was involved into a database project on san when they even didn’t go as far as replication even, they had trouble setting up their clusters on the hardware purchased because EMC specs were not checked to begin with, it was not very nice, they ended up paying for 64bit clusters and were forced to run on the monsters 32bit os with all the consequences and deal with plenty other troubles... if the hardware is already on site and the vender was not ‘handled’ properly there is not so much you as a contractor can do, some things just weren’t meant to work together and you are the one who has to say it and they are not going to like it… I do not like cases like that one, so the homework comes first.

 

Answer 12

Apps are something else, I don't even want to go there. As my client is a bank any loss of data would be unacceptable. I have three issues really, HA for the databases for the business, DR to cover site loss and business continuity, which is HA and DR rolled into one. I have a number of proposed solutions and requirements to cover these. It's the replication of data between SAN's which interests me as this is the proposed solution for almoat all scenarios. I'm unconvinced ( as you might guess ! ) as I don't think it actually meets any of my requirements.

I understand where Don is coming from and I'm grateful for all assistance and input - but has anyone implemented SAN replication with sql server and tested it in failover ? It doesn't seem to me to be the case.

My experiences with SAN's to date have not been good and a colleague had absolute nightmares where the Vendor would not agree with his requests for configuration and dismissed the Siebel SAN doc as being largely incorrect. In the end he was right, they were wrong, but his employer lost a bundle of money and repuatation, still that's an aside.

 

Answer 13

0 data loss can be guatanteed in 2 cases:

distributed database and your apps are running distributed transactions. consequences you know, they have to be developed that way.

sql 2005 mirroring when you tell it to commit if and only if the data were delivered on the otehr side. will slow down your oltp.

Replication assumes data loass, it's async no matter what white papper sais. All the solutions I know are taking snapshots. so get yourself a simple test, kill your server,  and see what does restore do for you, how many transactions were rolled forward, how many were rolled back. Document that one. Then repeat the processs and start your replication afte the server is killed. On the other side you will see who much data did you loose if any in comparison with the restore on the same side. identity insert is not a bad thing or sequential int (works faster :) ) to test your data consistency. In your case I'd test very short transactions like under 5 statenets (one statement works one row).

In my case on xiotech san I do expect data loss. How much - tests will show.

Answer 14

Take a look at http://www.microsoft.com/sql/alwayson.mspx .. it's a new site (just announced here at TechEd in Boston) where we list solutions from various vendors that we've reviewed. We point to a whitepaper that they provide that describes how their solution addresses the requirements that SQL Server has on storage systems, including those that use some sort of replication to provide HA or DR capabilities.

There are synchronous solutions that do provide zero data loss, not acknowledging the disk write until it's recorded both locally and remotely. Of course there will likely be some amount of hit on the response time, but if the remote site isn't too remote the app can likely tolerate it.

There are some asynchronous systems, but of course there can be some amount of data loss when the local write is acknowledged before it's sent to the remote site, if a crash occurs at just the wrong time, but with appropriate consistency mechanisms you at least have a guarantee that the remote site has a version of the data/log that actually existed at some point at the local site, and is therefore valid and recoverable.

Don

Answer 15

We use a couple of technologies, EMC SRDF and Kashya Replication and have run many failover tests. The SRDF is asynchronous as is Kashya (but with lower latency). All servers boot from SAN and we buy everything in pairs so changing the DR site from its QA role to live running is simply a case of presenting the replicated disk. This is purely for site DR, local HA is provided by clustering.

Answer 16

Very interesting. How much data do you loose with kashya?

I do not boot server from san, san carries just databases, OS etc sits on local drives, so we make our CM busy :).

In our case all is also in pairs, disks that kashya works with are on different sans, but their size can differ more then several MB or they don't work right, at least that cam from the vender so we didn't argue or experiment other wise. I do not know what exactly lays under that particuar advice, would like to find out, maybe you know...

n our case local HA's are provided by clustering as well, we have single instance on each cluster.

Answer 17

test description:

We have a set of databases on our primary site. SQL 2000, sp4, san xiotech 3000, kashya. disks are set very close on both sides (1-2 MB size difference).

@@version:

Microsoft SQL Server  2000 - 8.00.2187 (Intel X86)
 Mar  9 2006 11:38:51
 Copyright (c) 1988-2003 Microsoft Corporation
 Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Workload: insert (int, string) 100 rows per transaction. While this is working we shut down ms sql and after that bring on DR side the last snapshot from kashya. I did expect this snapshot to be fine, because the server was shut down, all the files are closed, shouldn't be a trouble with cache etc. from RDBMS's side.

Here is what I'm getting after the server was started on DR side (database names are changed for obvious reasons, the rest of the data as is)

dbcc checkdb ('MYDB')

Server: Msg 8946, Level 16, State 12, Line 2
Table error: Allocation page (1:3785184) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.

--1:3785184
dbcc traceon (3604)
DBCC PAGE('MYDB',1,3785184,1)

Server: Msg 8966, Level 16, State 2, Line 1
Could not read and latch page (1:3785184) with latch type SH. 27(The drive cannot find the sector requested.) failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:3785184) with latch type SH. 27(The drive cannot find the sector requested.) failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:3785184) with latch type SH. 27(The drive cannot find the sector requested.) failed.
Page not found.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

error log on DR side (names are changed):

 2006-06-14 10:59:34.85 server    Microsoft SQL Server  2000 - 8.00.2187 (Intel X86)
 Mar  9 2006 11:38:51
 Copyright (c) 1988-2003 Microsoft Corporation
 Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

2006-06-14 10:59:34.85 server    Copyright (C) 1988-2002 Microsoft Corporation.
2006-06-14 10:59:34.85 server    All rights reserved.
2006-06-14 10:59:34.85 server    Server Process ID is 3080.
2006-06-14 10:59:34.87 server    Logging SQL Server messages in file 'd:\program files\microsoft sql server\mssql\data\mssql\log\ERRORLOG'.
2006-06-14 10:59:34.96 server    SQL Server is starting at priority class 'normal'(4 CPUs detected).
2006-06-14 10:59:35.07 server    SQL Server configured for thread mode processing.
2006-06-14 10:59:35.09 server    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2006-06-14 10:59:35.23 server    Attempting to initialize Distributed Transaction Coordinator.
2006-06-14 10:59:37.40 spid3     Starting up database 'master'.
2006-06-14 10:59:37.81 spid3     0 transactions rolled back in database 'master' (1).
2006-06-14 10:59:37.81 spid3     Recovery is checkpointing database 'master' (1)
2006-06-14 10:59:38.06 server    Using 'SSNETLIB.DLL' version '8.0.2039'.
2006-06-14 10:59:38.06 spid5     Starting up database 'model'.
2006-06-14 10:59:38.09 spid3     Server name is 'SQLDR'.
2006-06-14 10:59:38.09 spid9     Starting up database 'MYDB1'.
2006-06-14 10:59:38.09 spid11    Starting up database 'MYDB'.
2006-06-14 10:59:38.09 spid8     Starting up database 'msdb'.
2006-06-14 10:59:38.09 spid10    Starting up database 'DBA'.
2006-06-14 10:59:38.29 server    SQL server listening on 10.3.102.11: 1433.
2006-06-14 10:59:38.29 server    SQL server listening on 127.0.0.1: 1433.
2006-06-14 10:59:38.31 spid8     21 transactions rolled forward in database 'msdb' (4).
2006-06-14 10:59:38.31 spid10    152 transactions rolled forward in database 'DBA' (6).
2006-06-14 10:59:38.45 spid8     0 transactions rolled back in database 'msdb' (4).
2006-06-14 10:59:38.46 server    SQL server listening on TCP, Shared Memory, Named Pipes.
2006-06-14 10:59:38.46 server    SQL Server is ready for client connections
2006-06-14 10:59:38.48 spid8     Recovery is checkpointing database 'msdb' (4)
2006-06-14 10:59:38.81 spid10    0 transactions rolled back in database 'DBA' (6).
2006-06-14 10:59:38.82 spid10    Recovery is checkpointing database 'DBA' (6)
2006-06-14 10:59:38.92 spid5     Clearing tempdb database.
2006-06-14 10:59:40.70 spid5     Starting up database 'tempdb'.
2006-06-14 10:59:45.98 spid11    Analysis of database 'MYDB' (7) is 100% complete (approximately 0 more seconds)
2006-06-14 10:59:46.03 spid11    Recovery of database 'MYDB' (7) is 0% complete (approximately 3 more seconds) (Phase 2 of 3).
2006-06-14 10:59:46.76 spid11    Recovery of database 'MYDB' (7) is 100% complete (approximately 0 more seconds) (Phase 2 of 3).
2006-06-14 10:59:46.76 spid11    697 transactions rolled forward in database 'MYDB' (7).
2006-06-14 10:59:46.90 spid11    0 transactions rolled back in database 'MYDB' (7).
2006-06-14 10:59:46.92 spid11    Recovery is checkpointing database 'MYDB' (7)
2006-06-14 10:59:52.64 spid3     Recovery complete.
2006-06-14 10:59:52.64 spid3     SQL global counter collection task is created.
2006-06-14 10:59:53.57 spid51    Using 'xpsqlbot.dll' version '2000.80.2039' to execute extended stored procedure 'xp_qv'.
2006-06-14 11:00:00.26 spid52    Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'sp_MSgetversion'.

...after that goes my dbcc stuff in the log (see above).

when kashya is paused, then we can bring both sides live and see them. The primary side is fine as expected, no dbcc errors as expected as well.

DR side was bad only for the database that had some workload.

Did you see such a thing? Where would you dig?

Ok, data loss, async - can understand, nobody has promised an ideal thing... but getting a corrupted database and finding out it with dbcc on last snapshot ... after ms sql shut down - this one I don't understand and don't like. If I have to roll back to prev snapshot and test it find out it's bad and so on till I hit a good one... Can you think of how much time will be DR down just because dbcc checkdb is never being fast and nobody promised it to be fast... I mean I have VLDB... I can not predict DR recovery time because I don't know when will I get a good snapshot.

And this thing I've got on the first test. Could you share something about your tests? icq, skype, e-mail? any contact info is appreciated. my icq is 63-697-048, I'll authorize you, just send a message so I know who is coming.

Answer 18

Don,

        Thanks for your opinion. Why  does not HP  appear as partner?.  Is  HP continuos Access  a supported solution by Microsoft?

Jose Antonio Pineda.

 

 

Answer 19

colin leversuch-roberts wrote:

Apps are something else, I don't even want to go there. As my client is a bank any loss of data would be unacceptable. I have three issues really, HA for the databases for the business, DR to cover site loss and business continuity, which is HA and DR rolled into one. I have a number of proposed solutions and requirements to cover these. It's the replication of data between SAN's which interests me as this is the proposed solution for almoat all scenarios. I'm unconvinced ( as you might guess ! ) as I don't think it actually meets any of my requirements. I understand where Don is coming from and I'm grateful for all assistance and input - but has anyone implemented SAN replication with sql server and tested it in failover ? It doesn't seem to me to be the case. My experiences with SAN's to date have not been good and a colleague had absolute nightmares where the Vendor would not agree with his requests for configuration and dismissed the Siebel SAN doc as being largely incorrect. In the end he was right, they were wrong, but his employer lost a bundle of money and repuatation, still that's an aside.

One client I have worked with has done remote mirroring, and it does work assuming you work with the vendor to help get it configured properly.  There are certainly challeneges. For example, if you are not running a certified geographically dispersed cluster, it may be better to keep the SQL Servers themselves offline until you need to bring them online depending on if they need to be brought online with the same domain and name.

I have implemented Siebel and since it is pretty strict in what you can and can't do (i.e. you can't use things like replication), you have to use things like log shipping, database mirroring, clustering, or other hardware options for DR.

There's no perfect solution, and even if you're doing synchronous writes either with database mirroring from SQL Server or mirroring at the disk level, if a transaction is incomplete, it will get rolled back on the standby server should the primary crash and not complete the transaction.  Does your business consider that data loss?  What happens with requests from end users that are submitted during the server switch?  How are those dealt with?  My point is that "zero data loss" is more than just dealing with data going from site A to B.

Most clients I've worked with at one point or another have stated they want zero data loss, but the reality is that most businesses - including some financial - can tolerate a short amount of data loss depending on the system. You can never, ever guarantee that one piece of the puzzle will not cause failure (i.e. what happens in disk mirroring if your fibre goes down; do you have redundant pathing?), so you do your best to mitigate and provide a solution which 9 times out of 10 will get you as close to no data loss as possible.

Answer 20

From a pointer in another posting on another forum I ended up at Hitachi. SQL DBA's might be interested to know they have a SAN replication which is write order aware, this does ensure that your two sites are in step.

Full Honours to Hitachi who found me the right person in one phone call, compare this to HP who after 8 phone calls still hadn't found me someone who worked with SANs, in most cases I couldn't find anyone who knew what a SAN was OR EMC who are almost impossible to talk to person to person. After nearly three days I'm still waiting contact from both.

So check out    http://www.hds.com/products_services/san/

I suggest a download of the whitepapers, they make excellent reading.

Answer 21

You may also want to take a look at

http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA2_premm.ppt

which describes a SRDF solution. It is for SQL Server 2000, but it shows that disk mirroring is out there, implemented, and reliable if it is the solution you need.

Answer 22

Cheers doesn't quite match my requirements but is still very useful, thanks.

Answer 23

I believe HP is one of several vendors who are working on submitting their information for inclusion into the AlwaysOn program. I’m not sure which products they will be submitting information on.

Answer 24

tests (better to say the first series of tests) with kashya driver and san 2 san replication are completed.

got negative results on detached database even... the database with an error like below has to be restored from backups. in case of the real disaster that would be disks, tape etc. there was no load on the database, just asked kashya to replicate 80gh worth of data to DR side. It sure works fast, but the consistency in our case was not there. I guess there will be some nice chat with the vender about this case. at this point kutils was not  involved, and as far as we are talking detached database or sql server shutdown, then replication shouldn’t care. it does indeed, why is it so is not exactly clear.

dbcc checkdb ('mydb')

Server: Msg 8946, Level 16, State 12, Line 2

Table error: Allocation page (1:3785184) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.