June 12, 2006 at 5:07 am
Anyone using SAN replication to provide DR for sql server?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 12, 2006 at 5:17 am
We are looking into it, but thus far haven't attempted it. I'd be interested in any comments on the subject as well.
K. Brian Kelley
@kbriankelley
June 13, 2006 at 4:03 am
We use SAN replication for SQL between our GB and US data centers. We use HDS 9900 series SANS at both sites. Our applications are classed as active/active or active/pasive, and we handle the databases for these differently.
Active/active applications have databases running simultaneously at the GB and US centers. Failover for these is at most notifying clients that one site is not available.
The active/passive databases are on separeate LUNs to the a/a databases, and these LUNs are mounted only at the active site. The databases on the a/p LUNs are online at the active site, and offline at the passive site. Behind the scenes, the mounted LUNs are being replicated to the other site. Failover is all scripted, and involves a) Taking the a/p databases offline to SQL on the source site, b) Un-mounting the a/p LUNS from Windows on the source site, c) Suspending SAN replication, d) Mounting the replicated LUNs to Windows on the target site, e) Making the a/p databases online at the target site, f) Switching replication so the target site is now the primary site for SAN replication. g) Performing sanity checks on the applications before releasing them to users. Steps a) to f) normally complete within 10 minutes, with about a further 10 minutes for step g).
As most of our staff is located in GB, the GG site is normally the active site, but we always run for at least 2 days per month from the US site, and sometimes run for a few weeks with the US site being the primary.
This works for us, but the process needed careful planning and scripting to get the reliability we now take for granted.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 13, 2006 at 5:21 am
How are you handling the active/active situation? Synchronous connections between the two SAN storage devices?
K. Brian Kelley
@kbriankelley
June 13, 2006 at 6:06 am
The active/active applications update the databases in GB and US independantly. There is no replication or other link between them. We have a reconciliation process that periodically checks the two a/a databases are aligned.
The active/passive appilcations connect to the active server via a DNS alias. Part of the failover script changes the alias to the new active machine.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 13, 2006 at 1:24 pm
I can follow the a/p scenario, this I've had described. In a full failover situation how do you ensure your data integrity? The explanation I've had is that san replication may not work at the same level or order of disk writes that sql server does, so in a failover the database(s) may be left in an inconsistant state, so much so that the dr databases may not attach. ( I'm advised to beware of classroom proof of concept that is shown to work in a controlled environment )
I'm interested with the a/a does this mean the replication is two way with both sites updating? And data is totally handled by san replication?
Sorry for all the questions but this is a tricky scenario to get info about, so far I am waiting for SAN Vendors to get back to me as so far no-one I've spoken to can answer my questions concerning sql server 2005.
thanks
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 14, 2006 at 3:18 am
microsoft tell me there is a new web site which should help in this area
http://www.microsoft.com/sql/alwayson.mspx
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 14, 2006 at 11:11 am
Yes, they announced the Always On initiative here at TechEd. I still haven't gotten a chance to pigeon-hole some SAN vendors, but basically there are certified vendors (HP, EMC, etc.) that are participating in the Always On program. Microsoft has only certified at the storage hardware level thus far.
K. Brian Kelley
@kbriankelley
June 15, 2006 at 2:53 am
Colin,
You will need to talk to your choice of SAN vendors concerning the serialisation of replicated data. With HDS and asynchronous replication, we have an assurance that replicated data is serialised correctly, so that we should always end up with a working database. This has certainly been our experience.
Our failover process for a/p databases includes a process of mounting the replicated volumes at the target site and bringing the databases on those volumes online. We have had some issues with this process that all seem to be timing issues. SQL can only see those files that are in the Windows file cache, and Windows needs some time to populate that cache when volumes are mounted. If you try to bring the databases online (i.e. open the disk files) too quickly, SQL can object in various ways and even mark the database as suspect. Our scripts now have a short wait coded in them between the mount request and bringing the databases online, which has resolved this issue.
Also, we have kept all the LUNs we mount and dismount as Windows Basic disks, but have had no issues with permanently mounted disks being Dynamic. In our Test environment we do a failover each week, but only reboot the servers for planned maintenance or crashes, so a DB server is often up for a few months between reboots. Windows (we run W2003) has not proved reliable in handling multiple mounts and dismounts of dynamic disks in the same period of server uptime, due we think to the interaction with the internal Windows database that manages dynamic disk details. We have had no such issues with Basic disks.
Finally, for us active/active means completely independant databases at both sites. Ther is no replication involved with our a/a databases in any way.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 15, 2006 at 9:26 am
Cheers Ed
I've spoken to Hitachi and they have some excellent solutions, and I got to speak to people who could explain to me what I was asking. More than can be said for other vendors. My task is find a workable solution that can be presented to business - been quite a steep learning curve to be sure - thanks for your assistance.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 15, 2006 at 10:11 am
When we were looking at how to provision the storage we wanted, we looked at SAN, NAS and DAS. NAS got rejected quickly due to performance issues. The cost for SAN came to about GBP 50k more than a DAS solution for 3 or 4 TB split bewteen GB and US sites. We reckoned the greater ease of management was worth the difference in price, which has definitely been true.
At the time of our initial purchase, HDS and another vendor were about equal in most aspects. The ability with HDS kit to replicate some data in the SAN box in one direction while sumultaneously replicationg other data in the other direction was a key thing for us. It allows us treat the GB and US sites as peers. We can perform failovers of test and production at different times. Also, our production environment is split into different groups, and we often have 1 group reploicating US to GB while the rest is GB to US. I think most of the major vendors can do bi-directional replication now, but it is a major thing to look out for.
Also, most major vendors can attach multiple categories of disk to their SAN. As well as the standard RAID-5 setup that HDS uses, they can atttach SATA and other disks. All of these get presented to Windows, *nix, etc as LUNs. This allows the DBA and storage administrator to work together to provide the most cost-effective storage for different categories of data, following Information Lifecycle Management (ILM) principles. Your live databases get to live on RAID-5, while historical or backup data could live on SATA. With the installed cost of 1TB of SATA being probably under GBP 5K, it can be cheaper to keep historical data on SATA than on tape.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 16, 2006 at 2:53 am
A colleague had sata disks on his san -- a TB of storage is almost normal for home use now - certainly I almost have that sort of storage on home test servers. I was at a meeting with Jim Gray and he thinks single disks will hit a TB very soon - for a 50 spindle SAN on raid 10 that gives 25TB of storage - of cource the downside is trying to convince non-dba's that databases need spindles , this can lead to huge amounts of free space - imagine putting your tempdb on a 7 x 1Tb disk array and using 250mb out of the 7Tb - doesn't bear thinking about!!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply