| Author |
Topic |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
 Posted - 2007-12-21 : 11:36:37
|
| Guys, I really need a help . I have one database (1TB) on two servers in different location( About 1200 miles distance). SO which options will be good:Clustering or Database MirroringI am totally confused . As i read from SQl-server- performance , Clustering doesn't protect data. It is server level.Can any expert throw a light on me? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-21 : 13:34:42
|
| What are your business requirements?We use both clustering and mirroring due to our business requirements.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-21 : 13:37:44
|
| It's hard to build cluster on servers 1200 miles apart. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-21 : 13:41:53
|
| And it's extremely expensive.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-12-23 : 14:32:03
|
| yes, cluster will be bad option for this since there is only one database .Database mirroring will be the best option for this but Database mirroring has shortcoming:If there is bulk loading going on for primary server( 20,000,000) then it takes a lot of time and transaction log will be tremendously full.So, i really need expert's advice. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-23 : 16:48:44
|
| Do you have to keep those dbs in sync in real time? |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-12-23 : 16:54:00
|
| The Cluster does not need to be at the other site it can be in the main computer room. We use Cluster so that if we want to apply patches we can role everything to the other Node and no one is affected.Mirroring does the same thing too.If you want data to be present at the other site for reports etc you could use replication. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-23 : 17:38:29
|
| sodeep, you say you need expert's advice on this, however you haven't provided your business requirements yet. Let us know what they are and we'll try to come up with a solution for you.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-23 : 19:23:22
|
| If I were consulting on this project I would want to know the business requirements, a timetable for implementation and how much of a budget you have to work with. a geographically dispersed cluster solution will easily be 10 - 20x the cost of a mirrored solution. It will also be far more complex with many more points where things can go wrong. And it will take months to implement.If you skimp, and don't build something like this the right way you will most likely have more downtime due to the complexity of the solution than you would if you went with something simpler.-ec |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-12-24 : 15:02:12
|
| Our business requirements are:1) Both databases have to be in real time sync and both servers can't reside in one physical location 2) The company doesn't care about budget but whatever We as a DBA recommend, they will apply.3) The main requirement is database has to be available immediately whether in cluster or mirroring.According to Tracey SQL, Replication won't be good option for this. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-24 : 16:08:13
|
| Replication is not just for reporting. You need to test those options and pick one that works for you. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-24 : 18:34:31
|
| If the company is willing to spending over 1,000,000 bucks on a cluster across a WAN, then that's a great availability solution. If the company isn't willing to spend this much, then I'd suggest database mirroring.I personally don't agree with using replication as a disaster recovery solution.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-25 : 16:30:39
|
| replication would be a pain for dr because it doesn't catch new objects as they are created. You would have to constantly monitor new object creation and drop/create new replication articles. it would be a serious pita.-ec |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-25 : 16:50:14
|
| Another option is third party DR tool, take look at CA's Xosoft. |
|
|
|
MuadDBA
628 Posts |
Posted - 2007-12-27 : 16:06:02
|
| Well I don't want to be insulting here, but I am afraid it might be unavoidable. Apologies if any of this offends. Sodeep, your first recommendation to the company should be that they hire someone with a better grasp of DR deployment strategies. Based on your comments here, you knowledge int his area seems bery limited. This is OK, everyone need to learn this stuff at some point, BUT, your company should not be placing its future inthe hands of someone inexperienced in designing a DR strategy.You need to read up on replication and learn why it's a bad option for DR. Also read up on database mirroring and the benefits (cost) and drawbacks (latency, server performance) of using it for your DR scenario. There are also options like log shipping, though that does not meet the real-time requirement. You need to be able to explain clearly to the business how expensive it is to do real-time replication across a wide geographic area, and what other options are available. All of this is erally beyond the scope of an internet message forum, but we're happy to answer specific questions if you have them. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-12-27 : 16:23:37
|
| what do you mean by your knowledge is very limited.So Crazy joe, Since you are an expert in DRCan you explain what you would do when you are in this situation.?It is easy to say but hard to do. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-27 : 16:54:55
|
| crazyjoe is probably saying that your knowledge is limited due to your posts here.We had a meeting with Microsoft a couple of weeks back and they claim that peer-to-peer replication is a solution that provides active/active databases at two sites/servers. We haven't looked at it much, but perhaps it's a cheap solution for what you are looking for.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-12-27 : 17:16:18
|
| Tara sweetheart,but Peer-to-Peer replication only works for Enterprise edition. |
|
|
|
sqlsquirrel
Starting Member
21 Posts |
Posted - 2007-12-27 : 17:51:03
|
| Hey soDeep..From my DR experience I have implemented the following solution for SQL Server 2000:We had 2 separate servers, one server was production and the other server was a "warm standby" server. The way we kept them in-sync was by RPC calls from the SQL Server Agent backup jobs on the production database. We had full, differential, log backups and after each time the backups occur the next step in the job was to call (RPC) a restore job on the "warm standby" server. This was a very inexpensive DR implementation and worked great. The ONLY draw back we had was that we were only in-sync from the previous transaction log backup (assuming of course of total production failure). However, the business units were agreeable to lose 1 hour worth of data (hourly transaction log backups).This can also be implemented on SQL Server 2005. If you decide to go with database mirroring please post here and I will reply with the database mirroring implementation that I did in detail. Good Luck!Brett DavisSenior SQL Server DBAFor more helpful tips checkout my blog at: http://www.lockergnome.com/sqlsquirrel/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-12-27 : 17:53:23
|
| Thanks a lot.It sounds good. Can you explain about your Mirroring Experience as well. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-27 : 18:56:03
|
quote:
Originally posted by sodeep
Tara sweetheart,but Peer-to-Peer replication only works for Enterprise edition.
i think we are close to being done with this thread.if cost is no object, then running EE is not a problem.-ec |
|
|
|
Next Page
|
Whether to do CLustering or Mirroring