Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kenny782
Starting Member
6 Posts |
 Posted - 2011-04-26 : 00:31:56
|
I'm no SQL expert and trying to figure out what replication method I should be using to handle this setup.Each location has a Max of 20 users at a time/Each terminal server is hardcoded to read/write to it's local SQL server.I made up this diagram based on one I found on MSDN. Basically if the SQL and/or Term Srv in NY goes goes down then the NY users log into the Backup terminal server which modifies the backup sql server.When I bring the production server online the backup sends the changes over to Server A that it missed while offline.Users would then log into the local terminal server again and make changes to the local SQL server again.***If the replication is more reliable or requires having 2 servers at the backup location that's not a problem. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kenny782
Starting Member
6 Posts |
Posted - 2011-04-26 : 02:39:34
|
| Well I used replication as a generic term, but yes I read about mirroring somewhat too.Honestly I found so many different methods of getting data between the servers my head was going to explode.Mirroring was pretty close but one of the problems is only principle can be changed at any given time.In the event of a terminal server failure and an intact SQL server, I'd have to manually change the principle role before those users could get to work.I'm the companies only IT support but I'm not a full time employee, so if something happened I could be anywhere. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-04-26 : 12:55:39
|
| There isn't really a method available (that I know of at least) that will be able to recognize Terminal Server is down but not SQL Server. I think you're going to have to write some custom code for this. I think for the database side, you would use database mirroring either synchronous with a witness for automatic failover or asynchronous for manual failovers. We use async database mirroring because of the latency between our two sites.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Kenny782
Starting Member
6 Posts |
Posted - 2011-04-26 : 15:45:17
|
Okay I was just thinking about this again.For visual purposes I displayed the terminal server and SQL server as different boxes.At the time I didn't think it had anything to do with SQL's mirroring/replication.I know it's wrong but for this company has/still has one physical machine doing both.Anyway in this case mirroring should work if I put the witness at the backup location? Only have one last question, lets say the backup connection goes down.Now the witness and Backup server don't see Server A anymore.But Server A is fully principle and still in use.What's gonna happen when all three machines come online and they both think they're principleNo changes would have been made to the backup during this time but the witness would havwe made it the principle anyway.**Edit added problem to diagram |
|
|
|
Kenny782
Starting Member
6 Posts |
Posted - 2011-04-27 : 18:09:28
|
| Well here's the final resultNY Location: Server ABackup Location: Backup Server (A/B)MX Location: Server B / MX(B) WitnessChina Location: NY(A) WitnessIf the WAN goes down at NY location 2 users are dead in the water.But the other 20+ are still working via the backup location.So that seems like the best solution. |
|
|
|
|
|
|
SQL Failover for Terminal Servers