How can i do Failover in replication

Question

Post reply

How can i do Failover in replication

solomon.jernas

Hall of Fame

Points: 3680
More actions
December 15, 2011 at 7:24 am

#253480

Hi,
Configured Tranactional replicaiton in my envorionment , How to manually failover process steps in repolicaition?
Please help anyone
Thnkas
Jerry

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

salum SSCommitted Points: 1598 More actions · Answer 1

Can please elobrate what exactly your requirement?

java[/url]

solomon.jernas Hall of Fame Points: 3680 More actions · Answer 2

I requirement, production server database down ,then how to bring the secondary db (supscriper) online?

Steve Jones - SSC Editor SSC Guru Points: 736760 More actions · Answer 3

The subscriber is always online.

What you want to do is send the clients to the secondary. This is a manual process. You can do it a few ways, but it depends on how your clients connect and what control you have over that. I know people that have the client store a list of servers and if server 1 is down, query server2. I know some applications that query a 3rd server to get the connection string of the live server.

If you use DNS and FQDNs, then you can change the DNS, though the TTL will affect changeover time.

If this is a web server/db server environment, you should be able to edit the connection string on the web app.

Perry Whittle SSC Guru Points: 234024 More actions · Answer 4

a replicated database cannot really be considered a true 100% copy of your original database, what if a connection needs to use an object you have not replicated to the subscriber.

It sounds very much like you need to be looking at another form of high availability within sql server

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Bernardinho Grasshopper Points: 12 More actions · Answer 5

Perry Whittle (12/19/2011)
a replicated database cannot really be considered a true 100% copy of your original database, what if a connection needs to use an object you have not replicated to the subscriber.

Perry, replication in my eyes is the most effective of all Redundancy and Availability when set up and monitored correctly. It offers the best value for money for hardware usage (i.e. database accessibility in mirroring), does not have a single point of failure (i.e. disks in clustering), quicker up-time (i.e. log-shipping) and essentially provides a cheap "hot-site" option.

We currently replicate data via VPN between (say) "office A" which runs the ecommerce web-site, call center, etc... and "office B" which houses the financial team, management, etc...

I find it that Replication allows "office A" to make all transactional changes while replicating them through to "office B" which don't mind the slight latency (maximum 5 seconds we've encountered).

I do agree that replication cannot be considered

Perry Whittle (12/19/2011)
a true 100% copy of your original database

as per Microsoft specifications, however if you do your due-diligence and validate and/or do smart table comparison (via script) regularly you can ensure that it is.

The only issue we have currently is the automatically failing over between "office A" server to "office B" server for all applications.

Most applications are written in Java / Coldfusion connecting via a connection string and require manual intervention to switch. We're currently looking for a better way to "automatically" switch over if there's a disaster.

The only thing we can think of is manually monitoring the access via a standalone witness server and changing the Alias via SSIS. I'm going through the options now.

Happy to hear other ideas.

Qodo Business Solution - http://www.Qodo.com.au/[/url]

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 6

Bernardinho (1/5/2012)
Perry Whittle (12/19/2011)
a replicated database cannot really be considered a true 100% copy of your original database, what if a connection needs to use an object you have not replicated to the subscriber.
Perry, replication in my eyes is the most effective of all Redundancy and Availability when set up and monitored correctly. It offers the best value for money for hardware usage (i.e. database accessibility in mirroring), does not have a single point of failure (i.e. disks in clustering), quicker up-time (i.e. log-shipping) and essentially provides a cheap "hot-site" option.
We currently replicate data via VPN between (say) "office A" which runs the ecommerce web-site, call center, etc... and "office B" which houses the financial team, management, etc...
I find it that Replication allows "office A" to make all transactional changes while replicating them through to "office B" which don't mind the slight latency (maximum 5 seconds we've encountered).
I do agree that replication cannot be considered
Perry Whittle (12/19/2011)
a true 100% copy of your original database
as per Microsoft specifications, however if you do your due-diligence and validate and/or do smart table comparison (via script) regularly you can ensure that it is.
The only issue we have currently is the automatically failing over between "office A" server to "office B" server for all applications.
Most applications are written in Java / Coldfusion connecting via a connection string and require manual intervention to switch. We're currently looking for a better way to "automatically" switch over if there's a disaster.
The only thing we can think of is manually monitoring the access via a standalone witness server and changing the Alias via SSIS. I'm going through the options now.
Happy to hear other ideas.

You can achieve the same with Geo Clustering with a bit of work but it is much more robust and doesnt rely on administration of database changes. Geo Clustering is a bit of a misnomer as the servers could reside in the same rack but have seperate storage.

Take a look at this article for ideas.

http://clusteringformeremortals.com/2009/10/07/step-by-step-configuring-a-2-node-multi-site-cluster-on-windows-server-2008-r2-%E2%80%93-part-3/[/url]

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 7

I should point out, it requires disk mirroring software of some kind.

azdzn SSCarpal Tunnel Points: 4128 More actions · Answer 8

Perry, replication in my eyes is the most effective of all Redundancy and Availability when set up and monitored correctly. It offers the best value for money for hardware usage (i.e. database accessibility in mirroring), does not have a single point of failure (i.e. disks in clustering), quicker up-time (i.e. log-shipping) and essentially provides a cheap "hot-site" option.

- database accessibility in mirroring isn't an issue thanks to database snapshots.

- cheap hot-site option : maybe to set it up at first but not when you have to add another table in your db for example. And there are conditions to be able to replicate a table.

...

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 9

azdzn (1/6/2012)
- database accessibility in mirroring isn't an issue thanks to database snapshots.

Yes it is, to a degree. To refresh it you need to drop the existing snapshot which requires users to disconnect, thus making it unavailable.

george sibbald SSC Guru Points: 104210 More actions · Answer 10

If your failover site is for high availability or DR purposes and you don't require access to the failover database in normal circumstances I would never recommend replication as the 'replication' method, and neither I think would microsoft. Replication is intended more for scale-out then scale-up.

---------------------------------------------------------------------

Perry Whittle SSC Guru Points: 234024 More actions · Answer 11

Bernardinho (1/5/2012)
Perry, replication in my eyes is the most effective of all Redundancy and Availability when set up and monitored correctly. It offers the best value for money for hardware usage (i.e. database accessibility in mirroring), does not have a single point of failure (i.e. disks in clustering), quicker up-time (i.e. log-shipping) and essentially provides a cheap "hot-site" option.

Sorry, but i do not agree. It's all about usage and interpretation, if i were making a whole database highly available i would not be using replication. If i were replicating subsets of my production database to a central location for reporting i would use replication.

IMHO log shipping is the easiest to setup and maintain of all the SQL Server HA technologies

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 12

Perry Whittle (1/6/2012)
IMHO log shipping is the easiest to setup and maintain of all the SQL Server HA technologies

Yes, have to agree, its beautiful in its simplicity, if a little dated :-).

Microsoft used to lump replication in general into it high availability technologies but now they seperate it out but continue to refer to peer to peer replication as HA.

Steve Jones - SSC Editor SSC Guru Points: 736760 More actions · Answer 13

At the risk of being cliche, it depends.

If I have a relatively small database in terms of objects, then I think replication goes up in my mind. Fewer objects, fewer publications, and less administrative overhead. Not less then LS, but less than what I typically consider a hassle. Plus it can be used as a secondary server in some sort of reporting environment. However it's nowhere near as simple as mirroring or LS.

However if we're strictly HA, I think mirroring is the easiest and offers the most protection on balance, but I'd also include LS to have a 3rd location for my files. A nice way to handle HA is local mirroring and then LS to a remote location. If things really go south, you deal with the manual switching of clients in LS. But if it's a short outage, reboot/failed drive, you have synchronous mirroring in the same location picking things up.