Sql 2005 replication and clustering

Question

Hello All,

Dear i need urgent help please, i have no experience in SQL one of my customer want to have his application server "SQL" to be replicated to another site in case if the SQL in the main site goes down the other server in the branch office will be up automatically and the client have access to the application without any problem, is this scenario possible?

consider that there is VPN connection between the main office and the remote office and the SQL server in the main office installed on windows 2008 R2 standard edition?

Your help is really needed.

Thanks

---

Ahmed Al-Ahmed MCITP Enterprise Administrator

Answer 1

You have defined a basic Disaster Recovery scenario.  When talking about High Availability and Disaster Recovery, there are two key metrics that determine which solutions are viable.  Recovery Point Objective (RPO) is the amount of data that is lost due to a site failure.  Recovery Time Objective is how long the system is down due to site failure.  Both metrics are in units of time.  These are important because the smaller they are the more expensive the solution. 

SQL 2005 has some technologies that can help implement a multi-site failover system such as Clustering, Mirroring, and Log Shipping.  In general, true HA/DR technologies are Enterprise Edition only.  HA/DR is also one of the more complex tasks in the SQL Infrastructure universe.  I would definitely seek someone with experience.  You don't want to be figuring things out when your client's system is down.

---

Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

Answer 2

Thank you Mr Geoff. well the fact is i have to do this solution. the guys want if the server in the main office failed another server in the remote office will pick up the traffic and serve the users as if nothing happened. i don't what you called this and what i should do to be frank with you. so any help will be great because i must start and there is choice.

Many Thanks

---

Ahmed Al-Ahmed MCITP Enterprise Administrator

Answer 3

Clustering is the first option. But your client infrastructure do not support. It will be expensive to implement.

In my opinion Database mirroring will fit. It supports automatic fail over. Database Connections can be used fail over partner details.

So if your principal database goes down your secondary database take responsibility.

Thanks

Kumar

 

Answer 4

Thanks Mr. Kumar for helping me here. what i should do exactly if i need to have clustering?

except that install windows enterprise edition? is it possible to have 2 server in different area and configure them as cluster?

Best,

---

Ahmed Al-Ahmed MCITP Enterprise Administrator

Answer 5

Windows 2008 standard edition do not support clustering. You must have enterprise edition to implement clustering.

Clustering fail over is in instance level, not at the database level. Your databases will be stored in shared disks. if your primary goes down your secondary will be up and running. if you lose shared disks you lose everything....

So if you can have two servers back up the database from primary database and restore at secondary and configure for mirroring..

Answer 6

Mr Kumar, let me tell you what i have in my mind now

1- Install windows Server 2008 R2 enterprise edition on the server in main office.

2- Store the database on a SAN disk.

3- Having VPN connection between the Main office and remote office.

4- install a second server on the remote office with windows 2008 R2 enterprise edition.

Note that right now we are using SQL 2005 delveloper edition SP3 is this enough?

configre clustering on the servers.

Is this a possible solution? Do i need any extra Hard Ware? and if there is any setup better than this please tell me.

how to replicate the database in case if the SAN down we still able to work.

---

Ahmed Al-Ahmed MCITP Enterprise Administrator

Answer 7

please go through the link. it will give you an idea...

http://www.dell.com/downloads/global/solutions/public/white_papers/sql2005_clustering_wp.pdf

Answer 8

The big issue is synchronizing the data.  If you are trying to implement a multi-site cluster, you need external software or hardware to synchronize the storage.  Clustering just fails over the database management and connectivity parts.  You need either SAN-based replication or a third party block replication tool that is supportable in clustering.  Database mirroring is usually used for multi-site disaster recovery because it handles failover and data synchronization.

---

Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

Answer 9

Thank you Mr. Geoff again and again, as i understand from your post you and Mr. Kumar i should have the following:

1- windows server 2008 r2 enterprise edition and SQL 2008 R2 in the main office

2- SAN storage in the main office.

3- Connectvity between the main office and remote office either VPN or dedicated line.

4- windows server 2008 R2 Enterprise and SQL 2008 R2 in the remote office.

5- SAN storage in the remote office.

Or instead of 2 and 5 i could use Third party block replication.

Please Guys correct me if I'm wrong or missed anything.

---

Ahmed Al-Ahmed MCITP Enterprise Administrator

Answer 10

Hello Mr. Geoff,

Any Additional Help.

---

Ahmed Al-Ahmed MCITP Enterprise Administrator