November 26, 2007 at 4:16 pm
I am due to oversee a SQL project. SQL is to house mission critical ERP application (25 user system) no metrics to hand regards I/O although not expecting huge performance hits.
Hardware 2 * HP DL380 G5 / Windows 2003 Standard R2 (64 bit) 2 * Xeon Quad Core + 8GB RAM (6 146GB 10000 RPM SAS drives). SQL 2005 64 bit.
I was looking to configure the server as follows:
RAID 1+0 2 logical partitions C:50GB for OS + Page File the remainder D:temp.db
RAID 1+0 Data
RAID 1+0 Logs
Should I mirror to a failover server (crossover) or implement 2 node cluster? Both servers will be housed side by side in comms room. Benefits / pitfalls of either approach? Also how to implement either approach as I am currently a SQL novice.
From what I gather I can mirror the application DB but not the system DB's (i.e. Master / model / msdb). Any tips here as I want a fault tolerant system, if I am unable to mirror the master.db etc.... then how will I have a seamless switchover in the event of failure?
I would very much appreciate expert opinion based on real world experience.
Thanks in advance,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
November 27, 2007 at 5:38 am
Both mirroring and clustering offer hardware failover support, but they offer different failure protection.
It is true that you mirror user databases, not the entire server. Since you are unlikely to have made changes to the system databases, this is usually not a problem. As for setup, you will need to configure your failover server and you will also need a third server (although it can run SQL Compact Edition so you don't need a thrid license) that can monitor your system. You have to correctly program your application to set up automatic failover if you want a totally seemless failover solution.
With clustering, you will use a single drive array (preferably with a RAID configuration that eliminates the worry of drive failure) and two servers. The servers are configured as a cluster. You cluster can be configured to load balance or to be just a failover cluster. On a failure of the primary server, the secondary server will automatically take on the name and IP address of the cluster and the users are only going to see a momentary failure (it may disconnect their open connections).
Clustering is generally more expensive. The OS and SQL licensing requirements are higher. Mirroring allows you to use a lesser server for your failover server (if slow but running is acceptable). Clustering is also a bit more complicated to configure and maintain.
This is not a full accounting of the differences. Look for some white papers from MS on each of these options. You may also want to price them both before you make a decision.
November 27, 2007 at 6:23 am
Thanks for the reply, I will search for those white papers.
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
November 27, 2007 at 10:06 am
i would cluster and mirror only if you need offsite protection. a failover cluster is common storage so nothing is lost.
we used to do log shipping years ago and there would always be problems where we would have to back up and restore the db to restart it at the remote location. there is no 100% guarantee that when a failure happens you won't be restoring it on the mirror server because there was some problem with mirroring.
i would choose mirroring if you need a remote location for a real emergency like a bomb or total power outage and you don't mind losing a few transactions or even a day's worth since your competition will also be affected and customers won't care too much
November 27, 2007 at 11:47 am
Database mirroring is a nice addition to SQL2005 and definitely has it's place for acheiving high availability. However, I highly recommend the following for all my clients -
-Clustering for local/'perfectly seemless' failover.
-Transactional Replication for offsite failover.
Mirroring cons:
- In the High-Availability and High-Protection Modes of database mirroring you will be affected by latency, since transactions must be committed at your priincipal and mirror servers guaranteeing protection of data. The principal waits for message from mirror stating transaction was 'hardened' to disk.
- Connection strings must be changed on client to redirect to mirror. ADO.NET supports this as a seemless transition, however an ERP package may not be using .net at the conn layer. Does your vendor app support failover to mirrored sql server?
- only user db's can be mirrored. tempdb, master, msdb, model can not be mirrored. Be ready to keep your sql logins, server objects, and jobs in sync on mirror.
Clustering:
- Misperceived concept of high cost is often drawback to clustering. This is no longer an issue, you can cluster the standard version of SQL2005, no longer needing enterprise. And you only need an os cal for both nodes, Win2003 Ent.Ed. is available for $2200 - 2600 per server license. You should already have your client cals.
- Offers seemless failover, users may see momentary disconnect w/ retry button depending on app. No need to change conn strings. The instance of SQL is virtual, and can live on either node in cluster w/ same name and IP.
- No more difficult to setup and administer than mirroring.
Replication:
- Clustering, Mirroring, log shipping are not the best tools for failover across a great distance. Meaning 1/4 mile or more.
- Transactional replication can be easily setup without affecting a vendors application - no additional table columns or db objects created on publisher. However, each table must have primary key to replicate.
- Depending on dml load and bandwidth, you can achieve latency in the seconds over a wan.
Regards,
ChrisB MCDBA
MSSQLConsulting.com
Chris Becker bcsdata.net
November 27, 2007 at 2:06 pm
Michael Earl (11/27/2007)
Clustering is generally more expensive. The OS and SQL licensing requirements are higher. Mirroring allows you to use a lesser server for your failover server (if slow but running is acceptable). Clustering is also a bit more complicated to configure and maintain.
I'm not sure how the licensing is higher in a cluster environment. While I do understand that the hardware is more expensive, you're only paying licensing on active nodes on a cluster (which in a failover only cluster is 1), which means at worse you're paying for the SAME licensing as you would in Mirroring. On the other hand, if you use your mirrored instance for anything other than being the passive mirror, it's no longer passive, so you are paying for multiple ACTIVE nodes.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 27, 2007 at 2:38 pm
Tremendous response, many thanks.
I was leaning towards a 2 node cluster (as SQL 2005 standard supports).
I have requested a SQL consultant configure due to my inexperience, he wants to mirror (crossover) + log ship?
The servers will be racked in same comms room. Should I insist on cluster as from a DR viewpoint offsite is not an option.
Thanks,
Phil.
PS: ERP system written in .NET.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
November 27, 2007 at 2:52 pm
Make sure erp vendor supports mirror. ADO.NET still needs to be mirror aware and special conn string config needs to be in place. Come time to failover to mirror, you could be dead in water.
Make your consultant offer you a pros & cons bullet point list for both technologies. He/she may not know (or have experience w/) clustering enough to implement hence the push for mirror and log ship.
Best of luck,
ChrisB
Chris Becker bcsdata.net
November 27, 2007 at 3:12 pm
Hi Chris. Took a look at your web site, shame your not UK based!!
I think you are correct regards the consultant, probably only ever used mirroring.
Trying to find a good article on configuring 2 node cluster (i.e. install steps, what to install and in what order + required configuration once installed).
Both servers are identical (DL380 G5) and will be running Windows 2003 R2 SP2 64 bit. SQL Server 2005 Standard 64 bit.
I have a day or 2 to play. Servers due to arrive Monday 3rd. I could build then play about with cluster (app due to be installed on the 7th) so could always wipe if I get it wrong!! 🙂
Could I use Transaction Replication to replicate to a subscriber running Server 2003 (or even XP Pro) + SQL Express 2005 on a remote site?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
November 27, 2007 at 3:17 pm
Philip Horan (11/27/2007)
Tremendous response, many thanks.I was leaning towards a 2 node cluster (as SQL 2005 standard supports).
I have requested a SQL consultant configure due to my inexperience, he wants to mirror (crossover) + log ship?
The servers will be racked in same comms room. Should I insist on cluster as from a DR viewpoint offsite is not an option.
Thanks,
Phil.
PS: ERP system written in .NET.
It really depends on your scenario. The word ERP alone tends to make me want to say "cluster" though, since this is the "whole enchilada" in terms of corporate data. you really don't want this down.
A few things to consider (this is the "pro-clustering" part):
- how much activity does your ERP generate on the DB side? Keep in mind that the other aspect of mirroring versus clustering is that clustering performs each data write ONCE (since the drives are shared), but the mirror needs to perform each update TWICE (once on the live data, once on the mirror), so your data subsystem will need to work twice as hard just to keep up with the work in a mirrored scenario. If the data backend is shared (SAN, for example), then mirroring is quite a bit harder on it than clustering.
- if you do NOT run in "high safety" mode (which forces the primary to wait for the mirror to acknowledge committing before proceeding forward), then there is no automatic failover in a mirror. If you do - you need to keep an eye on your activity levels, since mirroring could cause the primary to slow down (to wait on the secondary to finish synching)
- Is the UI in .NET 2.0? if you don't have access to using the SQL Native client, then mirroring will NOT allow for automatic redirection.
Pro-mirror:
- if you don't plan on setting up the shared data storage, then mirroring is about the only way to go. We are talking about a big increase in cost after all.
- if you have local disks, then the extra overhead might not be a big deal.
- you end up with more redundancy for your buck with mirroring.
- if your UI IS 2.0, then the auto-redirect is very slick and your downtime is much smaller, assuming 2 of the 3 nodes in the mirroring set up are still up.
Finally (even)
- if your servers are running on the SAME power, with non-redundant power supplies, DR ain't going to mean much.
Your mileage may vary.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 27, 2007 at 3:37 pm
Hi Matt. Servers fully fault tolerant (well as good as they can be):
Redundant Fans / PSU / Processor.........
Each server has 8 HDD Bays. 2 Xeon QUAD Core Processors 2.3Ghz (I think) so 8 cores in total + 8GB RAM.
I was going to use 6 in each server + have a Hot Spare (hardware controlled RAID p400 controller).
SAS 146GB 10000 RPM
Array 1: RAID 1+0 (Logical C: 50GB for OS + Paging File Logical D:94GB for temp.db)
Array 2: RAID 1+0 (Logical E: Data .mdf)
Array 3: RAID 1+0 (Logical F: Logs .ldf)
Second server the same. Looking to the second server to provide fault tolerance.
Application .NET 2.0 being re-written for 3.0 as we speak (so i am told!!).
Load should not be excessive, although overhead of mirroring makes me think .......makes me lean even more towards clustering.
Any thoughts?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
November 27, 2007 at 3:43 pm
Good place for white papers & tech content.
http://www.microsoft.com/windowsserver2003/technologies/clustering/resources.mspx
Tricky part is setting up hardware & shared drive array. Other than that it is pretty easy, plenty of documentation to follow.
You will be much happier w/ a cluster. I'm more than happy help offline - just shoot me an email.
Oh, and yes you can replicate to SQL Exress and even MSDE 2.0 - all versions of sql can be a subscriber to trans repl.
Regards,
Chris
Chris Becker bcsdata.net
November 27, 2007 at 3:52 pm
Hi once again Chris.
Tricky part is setting up hardware & shared drive array
Though each server would have it's own array! So does clustring see 2 servers as 1?
Would look to implement Active/Passive
Thanks for the link + email offer.
Phil.
Update: Going to sign off now as 11pm in UK.......need beauty sleep!!
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
November 27, 2007 at 3:58 pm
Philip Horan (11/27/2007)
Hi Matt. Servers fully fault tolerant (well as good as they can be):Redundant Fans / PSU / Processor.........
Each server has 8 HDD Bays. 2 Xeon QUAD Core Processors 2.3Ghz (I think) so 8 cores in total + 8GB RAM.
I was going to use 6 in each server + have a Hot Spare (hardware controlled RAID p400 controller).
SAS 146GB 10000 RPM
Array 1: RAID 1+0 (Logical C: 50GB for OS + Paging File Logical D:94GB for temp.db)
Array 2: RAID 1+0 (Logical E: Data .mdf)
Array 3: RAID 1+0 (Logical F: Logs .ldf)
Second server the same. Looking to the second server to provide fault tolerance.
Application .NET 2.0 being re-written for 3.0 as we speak (so i am told!!).
Load should not be excessive, although overhead of mirroring makes me think .......makes me lean even more towards clustering.
Any thoughts?
Thanks,
Phil.
Your servers look good redundancy wise - just make sure your power is as well (we had separate lines brought in from different electrical stations). It just adds another level (you plug the two PSU's into separate "lines") of redundancy.
Now for the "bad" news: if your disks are local (meaning - not shared between the servers using something like a SAN or a NAS), your ONLY choices are log shipping or mirroring. From what you were just describing....Mirroring is your friend.
You're looking at something configured differently if you do want to go with clustering. That's something you want to discuss with someone really sharp on that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 27, 2007 at 4:11 pm
Philip Horan (11/27/2007)
Hi Matt. Servers fully fault tolerant (well as good as they can be):Redundant Fans / PSU / Processor.........
Each server has 8 HDD Bays. 2 Xeon QUAD Core Processors 2.3Ghz (I think) so 8 cores in total + 8GB RAM.
I was going to use 6 in each server + have a Hot Spare (hardware controlled RAID p400 controller).
SAS 146GB 10000 RPM
Array 1: RAID 1+0 (Logical C: 50GB for OS + Paging File Logical D:94GB for temp.db)
Array 2: RAID 1+0 (Logical E: Data .mdf)
Array 3: RAID 1+0 (Logical F: Logs .ldf)
Second server the same. Looking to the second server to provide fault tolerance.
Application .NET 2.0 being re-written for 3.0 as we speak (so i am told!!).
Load should not be excessive, although overhead of mirroring makes me think .......makes me lean even more towards clustering.
Any thoughts?
Thanks,
Phil.
sounds like HP DL 380 G5 or the Dell equivelant. We have a bunch of the HP.
you didn't list any common storage and the bad news is unless you have a SAN I think only Dell has common storage where you can use SAS drives. you can get common storage with HP drives, but only with the slower SCSI Ultra 320 drives where the entire bus is shared bandwidth. SAS is dedicated per drive. or if you have an EMC or other SAN you should be OK.
if you are using a SAN and Emulex HBA's make sure you get the hotfix in the readme file from earlier in the year and there was 941276 from last month. otherwise there is a memory leak with the storport driver
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply