December 3, 2007 at 4:44 pm
Right off the bat, i'm a newbie SQL Admin.
We have a Windows 2003 server with MSSQL 2000 standard running on it in Site A. We are planning on upgrading that to MSSQL 2005 Standard and creating a mirror to off-site B for disaster recovery.
This is what my question is. My boss wants me to also create a local failover in Site A.
So, This is what we're looking for:
Site A server has all it's data on a SAN. We setup another identical server and keep it in standby. If the Site A server has hardware failure, we put the stand by server in it's place and connect to SAN. This way our local users dont have to go over the WAN to Site B. While if a disaster happens to the whole site, Site B takes over.
Does it make sense? Is it possible? If yes, then how?
If no, any alternatives?
December 3, 2007 at 6:03 pm
you were not detailed...
best scenerio applicable to you....
Do cluster on site A....and do transactional replication on siteB
2005 std edition supports both above....std edition does not support log shipping, or mirroring in asynchoronus mode thus not usable
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 4, 2007 at 7:53 am
there are many, many options to do this.
for a SAN based solution, look into SDRF (EMC) or Metro Mirror (IBM)
for a SQL based solution, look into database mirroring. depending on your app that connects to the db, it may be the best solution.
geo-clustering is also and option if you partner with Veritas and use their solution called Veritas Storage Foundation for Windows.
December 4, 2007 at 9:25 am
Geoff
I am looking for a SQL based solution and would mirror from Site A to Site B in case of a disaster. However, when there is a hardware failure, I want to have a standby server that i can bring up and connect to SAN.
So the latter is what I am not sure about.
Prakash,
So I can have a cluster of two machines in Site A and also do a synchronous mirror to Site B? I am not sure what Transactional Replication is..
All this should work with SQL 2005 Standard license?
Let me know which part you want me to detail more.
December 4, 2007 at 10:16 am
what you are describing sounds like a candidate for SQL Mirroring.
automatic fail over is archived by introducing a witness server. In order to successfully implement a sql 2005 mirror, answer these questions;
can you alter the connection string that the app uses to connect to the db?
do you often create db users and logins?
do you often create sql agent jobs?
setting up database mirroring is very simple and very stable once running.
however, the limitation do not make it a good candidate for all solutions.
December 4, 2007 at 11:12 am
Mirror is not good for STD edition cause it does not allow asynchronos transaction(transaction is not commited unless both servers are updated)....check limitations of sql edition for the same...
i know for std edition log shipping is not supported also
clustering across 2 palces , if site A distroyed then ur san is also distroyed thus clustering on site B is of no use
thus do it locally and do transactional replication which is seamless once implemented without affecting performance...or best if possible to configure log shipping by scripts to site B....
if site A distroys then you less care if u have to manually start site B db and put it as primary....while if on site A first db goes down clustering will be transperent to users....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 5, 2007 at 9:48 am
log shipping does work with sql 2005 standard edition.
this is IMHO the best solution for your A ---> B solution, but as mentioned before you will need to also maintain objects outside your app database, i.e. logins and stuf in msdb. Use sp_help_revlogin or SSIS transfer logins task for logins. You can keep a copy of the msdb database on the failover server ready to restore. In 2000 after the msdb restore just had to run
'update msdb.sysjobs set originating_server = 'new server name', not sure if this is still applicable in 2005, anyone know?
On site A clustering best fits your requirements as a sql solution. I have seen a solution where a server is kept in standby, if the primary fails the secondary is rebuilt from an image and given the same server name as the old server, then srdf does its magic.
---------------------------------------------------------------------
December 5, 2007 at 10:49 am
yeap Log-shipping is there in STD edition and the best solutions fro Site A --> B
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 6, 2007 at 9:43 am
Ok so for local failover Cluster
for A-B, Log shipping or transactional replication.. they're the same right?
plus keep a copy of msdb on the failover cluster. Anything else?
December 6, 2007 at 9:59 am
log shipping and transactional replication not the same. Log shipping better suited to a Dr solution IMHO, replication more for scale out.
By all means keep msdb backup on failover server ready to be restored , but make sure you are able to recreate all other parts in SQL environment needed to get the app working - logins, agent jobs , SSIS packages, any osql jobs run from command line.......
---------------------------------------------------------------------
December 6, 2007 at 12:29 pm
hmmm so I should script out the jobs, logins and save them on the remote server. Would that work? or is there different utility to use?
Also, I have been looking at Neverfail and DoubleTake software. Would that be a cheaper/better solution?
Because for Clustering, we would have to upgrade Windows 2003 standard to enterprise. 🙁
December 6, 2007 at 6:21 pm
dont worry....windows 2k3 standard supports clustering
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 7, 2007 at 9:47 am
I am not doubting you Prakash, but i got my information from here:
http://www.microsoft.com/technet/windowsserver/evaluate/features/compare.mspx
And that page says that W2K3 Standard edition does not support clustering. Am i missing something? 🙂
December 7, 2007 at 10:13 am
I think you mean W2K3 Enterprise and SQL2K5 standard
December 7, 2007 at 10:48 am
sorry...w2k3 enterprise is needed
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply