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 |
|
bhunter1
Starting Member
4 Posts |
 Posted - 2002-03-12 : 15:30:12
|
| Are there any reasons I should be aware of that would prohibit me from using Transactional Replication as a failover solution?What are the downsides to failing over and then failing back to the main server? What ongoing maintenance type issues are there that I would otherwise not experience?I have seen a few articles stating that Replication shouldn't be a failover solution but they fail to state why that is. |
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2002-03-13 : 11:47:10
|
| I would guess some of the main reasons that I wouldn't use the Transactional (or any other) type of Replication for failover would be: 1. Only Tables (With Primary Keys defined) can be replicated2. If changes are made to the tables on the publisher SQL Server has a habit of creating a partitioned view which would leave attributes out of the article being published.3. Before SQL 2000 views couldn't be replicated, they can now, but it is no picnic.4. User defined functions aren't replicated.5. Schema's aren't replicated.My point is Why Take the Chance(s). It would be much easier to use a combination of Backups (Database/Trans Logs) and then have a dts/sql/etc. job restore them frequently.Example: have Backups run nightly transaction logs every hour. after the backup is complete you can restore it to you other server and then do the same every hour. In this scenario the 'failover' server will only be an hour behind. There are ways to get the current transaction log from the failed server and restore it to the last checkpoint, but that's another post. There are many ways to accomplish what you want without using replication and it will be much less painful. Hope this helps.JamesH. |
 |
|
|
bhunter1
Starting Member
4 Posts |
Posted - 2002-03-13 : 12:54:36
|
| I appreciate your response...It sounds like you are describing log shipping or some variation of it. With the thoughts you expressed and my own reservations, it seems we are also leaving out the other issues such as: DTS Packages, Scheduled Jobs, User logins, Alerts. These don't get replicated and in my environment change often. However, log shipping doesn't necessarily solve that either does it?I would have to write some type of routine to synch those items up. The other issue I have here, is that this failover server is supposed to be our reporting database as well. So the db needs to be accessible as much as possible.Thanks for any further input you may have. |
|
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2002-03-13 : 13:18:47
|
| Depending on your review process for scripting new objects, users, etc. you could implement a process where you would script new logins, jobs, Alerts, Operators onto both servers and then perform either log shipping or the alternative that I mentioned. I have been doing this type of Backup and Restore onto a Standby server for over 4 years with little or no hitch (Knock on wood). My Standby servers always act as the reporting servers in production scenarios and the switch over is as easy as changing an .ini file in 90% of the cases whenever a Production server fails. To get by with the DTS packages and SQL Jobs you could set your standby server up as the Master Server that executes all SQLJobs for both servers and then have all DTS packages managed from your standby server as well. This leaves you with making sure that your logins are synched on both servers and if you use the sid option in the sp_addlogin script you shouldn't run into any problems with that. Luckily, there are many ways to skin the SQL Cat...JamesH. |
|
|
|
|
|
|
|
|
Replication as a failover