| Author |
Topic |
|
Tallwood
Starting Member
11 Posts |
 Posted - 2008-12-04 : 10:48:36
|
| Hey guys,I've been assigned to setup a mirror configuration with witness. Let me start off by saying that SQL is not my niche.We are building an MPLS network for this client and he wants to make sure the mirrored setup will work and has handed the task of first setting up the mirror platform and then testing it across his network.I've read the basic tutorials of how to setup a mirrored arrangement and have run in to some problems.The first problem is that I can not seem to restore to any other physical server other than the one I am logged in to.Now I got around that briefly by exporting the DB to the mirror server instance and was then able to put it in a restored no recovery state and configure the mirror security with witness instance. The error at that point is that the mirror wizard states that it does not see and can not find the witness server instance. I am able to connect to it within the SQL Mgmt Studio though and it is a registered server.What are the basic things I should check first to make sure I have this group of servers ready to be mirrored?TIA |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-04 : 11:18:25
|
| Well,they need to have experienced people who has already done mirroring. You need to test it before you apply in production. You should check Principal,Mirror,Witness are in same domain otherwise you have to create certificates. Check you can ping from principal server to other mirror and witness server. Remember this is synchronous mirroring and it needs good network bandwidth otherwise you will have huge transaction log file. |
 |
|
|
Tallwood
Starting Member
11 Posts |
Posted - 2008-12-05 : 09:53:59
|
| Agreed. They do need experienced personnel for this, unfortunately they're limited on staff and have asked us to help oversee this task. First question first though, will SQL auto failover only work under active directory? or is it able to work within a workgroup? |
|
|
|
Tallwood
Starting Member
11 Posts |
Posted - 2008-12-05 : 10:14:11
|
| P.S. sorry for the extremely basic questions. Furthermore I am certain that this is something the client could do, but they have decided it is worth theirs and our time to hand this project off.My background is network design/mgmt., not Database design/mgmt. So I'm a bit out of my element, and oddly enough the Intro to SQL class I took years ago didn't go over this :PAlso, they may be upgrading to SQL 2008 in the near future. Are the failover processes drastically different between the two versions? From what I can tell, they didn't change too much. |
|
|
|
Tallwood
Starting Member
11 Posts |
Posted - 2008-12-05 : 10:23:11
|
| and sodeep, the bandwidth is one of our concerns and cause for testing. We have designed an MPLS network for them, with sites in Atlanta and Jacksonville. We will setup a VPN between the 3 total sites. The mirror will be in Atlanta with principal in Jacksonville. So, each SQL instance will be in a different physical location.Let me know your ideas and suggestions. Thanks. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-05 : 10:57:01
|
| Yes, Bandwidth is the main concern if you are doing Synchronous Mirroing. We implemented Synchronous Mirroring from Chicago to New York and We had to remove it because bandwidth was not good enough to keep up the transaction resulting in huge transaction log file.Remember in Synchronour mirroring,the transaction has to commit in Mirror before principal. My suggestion according to experience would be:Apply clustering in local site and use asynchronous (high protection) mirroring without Automatic failover to geographical sites so your data is consistent when your whole local sites is down.Also Accounts used in Database Mirroring needs to have full rights. I guess you can't use Mirroring between different versions of SQL Server. |
|
|
|
Tallwood
Starting Member
11 Posts |
Posted - 2008-12-05 : 14:46:49
|
| Is there a recommended standard for bandwidth relative to the synchronous mirroring?This will be a dedicated circuit independent of the cloud. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-12-05 : 14:51:19
|
| We tried using synchronous database mirroring (without a witness) from San Diego, CA to Las Vegas, NV. Although the link between the two sites is supposed to be super fast (I'm not a network person, I don't have the network details), we experienced hugh performance problems in the application due to the two-phase commit of sync mirroring. We are now using asynchronous database mirroring.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Tallwood
Starting Member
11 Posts |
Posted - 2008-12-05 : 15:01:11
|
| do you know if it was a private network between the two sites? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-05 : 20:01:13
|
quote:
Originally posted by Tallwood
Is there a recommended standard for bandwidth relative to the synchronous mirroring?This will be a dedicated circuit independent of the cloud.
See this for bandwidth:http://searchstoragechannel.techtarget.com/generic/0,295582,sid98_gci1229472,00.html |
|
|
|
Tallwood
Starting Member
11 Posts |
Posted - 2008-12-05 : 22:08:52
|
| Good link. thanks. I'll read over that this weekend.We're meeting with this client this coming week. I plan on mentioning the clustering without witness idea also.I feel that's almost a better route, dependent of course on his DB backup size/frequency, etc.The reasoning behind all of this is that they are applying for the SAS70. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-06 : 09:50:08
|
quote:
Originally posted by Tallwood
Good link. thanks. I'll read over that this weekend.We're meeting with this client this coming week. I plan on mentioning the clustering without witness idea also.We said High Protection Mode where there is no 2 phase-commit and called Asynchronous Mirroring.Please check Booksonline for it.I feel that's almost a better route, dependent of course on his DB backup size/frequency, etc.The reasoning behind all of this is that they are applying for the SAS70.
|
|
|
|
Tallwood
Starting Member
11 Posts |
Posted - 2008-12-09 : 09:31:25
|
| Correct. Maybe I spoke incorrectly. |
|
|
|
Tallwood
Starting Member
11 Posts |
Posted - 2009-01-08 : 09:16:27
|
| We are meeting with this client tomorrow finally to find out his Data transfer quantities and frequencies and to discuss this project going forward.I'll keep everyone updated. |
|
|
|
Tallwood
Starting Member
11 Posts |
Posted - 2009-01-12 : 14:20:17
|
| So, after all of my research, the client has decided that since they are able to replicate again then that would be fine for their needs. Their replication is only approx 1 minute difference. |
|
|
|
|
Basic auto failover setup