Log Shipping Failover
May 25, 2006
Is it possible to failover a single or a selected number of databases
onto a standby server? We're currently log shipping about 12 databases
to a standby and I was wondering if it was possible to randomly
failover onto selected dbs.
My understanding of log shipping is that when the primary server goes
down, the standby takes over. But, does that mean that all of the db's
on the standby server now become the primary? Or, can failover occur on
selected dbs?
The reason I ask is because if one db on the primary, for example, was
to go down, I'd like to be able to bring the particular db on the
standby server online while I fix or restore what's on the primary
server. Can it be done individually or is it an all or nothing
scenario?
Your personal advice or links is greatly appreciated.
Thanks,
Peps
View 1 Replies
ADVERTISEMENT
Jun 8, 2006
Hi,
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I appreciate any suggestion.
Thanks in advance
View 5 Replies
View Related
Oct 12, 2006
Hello all,
I'm about to setup Log Shipping in our SQL Server 2000 environment. I will have the primary server, one standby server, and the witness server in this setup. I understand the process of failover and the stored procedures needed to make this happen, but my main question is about failback. Once the standby server has assumed the primary role, will log shipping be automatically configured to start flowing from the new primary server to the old primary server, or will I have to go back and setup log shipping from the new primary server to the old primary server?
Thanks for any help or insight you can provide on this subject!
Eric
View 7 Replies
View Related
Jan 21, 2015
I am trying to create a failover cluster without the log shipping in 2012 as i've done it with a static instance with some database.Is the "AlwaysON" feature the solution when an application creates random and numerous databases within the instance and we need a failover scenario ?
View 9 Replies
View Related
Jun 14, 2007
Hello.
I am confuse and cant decide on how to setup high availability on our SQL 2005. Here's what on my mind and on resources list:
I plan to have mirroring on my SQL1 to SQL2 with the help of SQL3 as witness. So this would be automatic failover. My idea on mirroring is when SQL1 goes down, SQL3 would tell SQL2 to run and be the primary. It will automatically failover to SQL2. Right? My questions are:
1) How can I revert back to SQL1 once it is ready?
2) I read in one of the post that it is impossible to write in a mirrored DB, is this true? I mean, what's the use of failing over to the next node when it's not possible to write and update data/records?
3) If number 2 is false (i hope so), how would the data be synchronize from SQL2 back to SQL1. Those transaction that were made while SQL1 is down.
4) How about the connection string from the web applications? Would it be automatically point to SQL2? We have load balancing setup in place, would this help web application connection to automatically point to SQL2?
Another setup:
We have SAN in place (not yet used, but is planning to use for this SQL thing), EMC to be specific. My question would be:
1) For SAN setup, the data storage would be centralize. So would that mean that SQL1 and SQL2 services will use the same data and log file from the SAN storage?
2) How would you call this setup then? Can this be clustering type of high availability? Will clustering work under load balancing setup? I believe mirroring is not possible here? Right?
3) How can I setup my 3 SQL servers with the same theory in mind: when SQL1 goes down, SQL2 will take over. Data will be synchronize when SQL1 is up and running again. With automatic failover and reverting back to primary.
I read so much topics about this, but the more I research, the more I get confuse.
Any suggestions, comments, advice is greatly appreciated!
View 6 Replies
View Related
Oct 29, 2015
1. Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. after fail over done to 2nd secondary node what mode in production(readonly or read write).
4. how to rollback to production primary ,will change data in secondary will get updated in primary.
View 5 Replies
View Related
Mar 3, 2007
We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.
However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.
The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.
Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.
The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.
Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.
Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.
We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?
Are we missing a service pack on the client?
Thanks,
Kimball
View 1 Replies
View Related
Oct 29, 2015
1. In alwaysON fail over cluster, Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. After fail over done to 2nd secondary node what mode in production(readonly or read write).
4. How to rollback to production primary ,will change data in secondary will get updated in primary.
View 3 Replies
View Related
May 13, 2007
Hi
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
Is there any possibility of loosing data ?
Thanks
View 1 Replies
View Related
Jan 22, 2007
I have to figure out a way to handle a failover in SQL Server 2005. I was reading a few articles about it.
1. Do I need to do anything in application layer to let a failover occur in SQL Server 2005
2. I have a web site that connects to a production server, the connection string is defined in web.config. If I implement Mirroring in SQL Server 2005, and when failover occurs, shouldn’t I change the connection string to point the application to the new server?
View 2 Replies
View Related
Oct 2, 2007
Hi I just set up a new cluster and everything was working. Now when I try to test failovers like unplug the public network card etc...
The cluster group fails over correctly but my data and log array's dont failover.
So I unplug the network cable from node 1 (active) the cluster group with the IP, name, and Quorum disk fails over to node 2 but the other groups sql data and sql log stay on node1 and dont failover.
Any ideas?
TIA
View 14 Replies
View Related
May 13, 2004
I have 4 servers, the first two are web app and load balanced the second two are to be configured for failover. Let us call them SvrA and SvrB. SvrA will have active SQL (2000EE) and passive Content (large directory of courses offered) SvrB will have passive SQL(2000EE) and active Content. Both SvrA and SvrB will be connected to a redundant SCSI 18 drive Drive bay (cheap SAN) which will be configured with a) mirrored OS drives, b) a RAID 5 partition for SQL and c) a RAID 5 partition for the content folders.
SvrA & SvrB will be quad processor machines. EE license for 4 processors as 2nd svr is failover for SQL.
My concern is the failover set up for the content portion, or is it the failure of the server that causes the failover? This is quite confusing to me.
Any thoughts would be most helpful.
View 2 Replies
View Related
May 25, 2008
Hi there,
I am testing the db mirroring, making sure it will auto failover. I've stopped the SQL services on my principal and then I looked at the mirror db is says it's restoring. It stayed like that for 10 min before I enabled the mirroring again. Anyone knows why it's not failing over??????
Here's my setup: SQL 2005 Standard, Server 1 Principal, Server 2 Mirror & Witness.
View 4 Replies
View Related
Mar 9, 2007
Hi all:
I was wondering what will happend to the transaction when the cluster failover from one node to another?
In my case the database is in node-001 and I need to restart the server on node-001. So I need to have the database failover to node-002, so I can work on node-001. During this process, will it effect to end-user?
Thanks
vua
View 5 Replies
View Related
Jul 20, 2005
I have a 2 node Microsoft 2000 cluster with a shared storage device. Iwant to create automatic failover for MS SQL 2000 server. I can dothat wit the following options:1. Active/Pasive (one instance installed)2. Active/Active (More then one instance/virtual sql serversinstalled)I have a question about option 2.Is it possible to have more then one instance/virtual SQL server,service only one database for example loadbalancing. ?, or when youcreate multiple instances/virtual SQL servers, does that mean you havemultiple databases ? If not why does a Virtual SQL server has to betaken over by a cluster node that didnt fail. Then you can actuallyuse option 1. Please advise!The documentation is not very clear on this issues.Regards,Nico de Groot
View 1 Replies
View Related
Oct 15, 2015
Could not able to connect secondary replica below is the error message I am getting when I tried to failover. Cannot connect to VLDBATEAM.
The secondary replica that you selected to become the new primary replica does not belong to the specified availability group. A possible explanation is that the replica has not been joined the availability group. Â (Microsoft.SqlServer.Management.HadrTasks)
View 6 Replies
View Related
Jan 2, 2008
Hi there!
I use different udls for a website which is partly asp pages and partly aspx pages.
Every database is mirrored, so each udl indicates a "failover partner". However we decided recently that one of the database would not be mirrored anymore. For this database we still use in the udl a "failover partner" to redirect the connection to the second database in case of a failover. For the asp pages the failover works fine even if the database is not mirrored, but it doesn't work for the aspx pages.
It would be great if anyone had an idea why the behaviour is not the same for the aspx pages, and if we can still use a "failover partner" in udls even if the database is not mirrored.. if not, what should we use to connect to another database?
Any help is welcome!
idel
View 3 Replies
View Related
Sep 13, 2006
How do I test failover to point to the mirrored database instead of the principal without having to stop the primary instance? I would like to just bring the mirrored database on the primary instance to an offline status and see that the same database on the mirrored instance getting activated as the primary. This does not happen if I try to put the database on the primary server "offline" as it complains that any database that is being mirrored cannot be taken offline.
View 1 Replies
View Related
Feb 9, 2007
Our reports are connecting to a database snapshot for their data. Periodically this snapshot loads, which means for a minute or two the reports go down. There is however another snapshot available, and between the two one will always be up. Is there a way with Reporting Services to dynamically switch between the two, depending on which one is up? These snapshots are on a constant schedule, so it would also acceptible (but not as nice), to have the database connection automatically switch at certain times of the day.
These snapshots are on the same server, or I would try using a failover partner in the connection string. To the best of my knowledge, this only switches servers, not the database instances.
View 1 Replies
View Related
Sep 5, 2007
We're planning on moving some applications to a SQL server. We're likely going to go with 64-bit for obvious reasons. Since these are critical apps, we plan on having a failover cluster set up. We're aware that all nodes in the cluster have to be 64-bit, if that's the way we decide to go.
The question is this: is there any "non-standard" way to have a 32-bit failover solution, perhaps via a 3rd party application? The only reason we're considering this is from a budget standpoint.
"Hurricane" Andrew
Milford, DE
View 5 Replies
View Related
Jun 7, 2006
Hi,
I have successfully set-up a DB mirroring. When the pricipal goes down, the mirror picksup and the other way around too. So the mirroring part is working great. However, when I use my web applicaiton (dot net 2.0), it is not able to connect to Mirror server. I get the following error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
My connection string in web app:
"Server=serverAAA;Failover Partner=serverBBB;Database=TestDB;User Id=testUser;Password=testPassword;"
I have checked the surface are config on "serverBBB" and it is set to use tcpip only for remote connections.
Any suggestions are higly appreciated. TIA.
View 9 Replies
View Related
Jan 23, 2007
I have to figure out a way to handle a failover in SQL Server 2005. I was reading a few articles about it.
1. Do I need to do anything in application layer to let a failover occur in SQL Server 2005
2. I have a web site that connects to a production server, the connection string is defined in web.config. If I implement Mirroring in SQL Server 2005, and when failover occurs, shouldn€™t I change the connection string to point the application to the new server?
View 1 Replies
View Related
Jan 25, 2007
Hi there,
We've recently set up a Principle, Mirror and Witness configuration with the Mirror and Witness in a separate building to the Principle. All three are part of the same domain (DMZ) and are different servers, the buildings are connected via a fiber optic cable. All servers and SQL Server instances are logged in with the same domain admin account DMZesAdmin.
Mirroring is all set-up and the databases are synchronized. Every once in a while some (not all, normally 6 out of 15) databases will switch roles and become active on the mirror. The SQL Server mirroring monitor job then reports:
Date 25/01/2007 12:37:01
Log Job History (Database Mirroring Monitor Job)
Step ID 1
Server DMZSQL01
Job Name Database Mirroring Monitor Job
Step Name
Duration 00:00:02
Sql Severity 16
Sql Message ID 32038
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: DMZesadmin. An internal error has occurred in the database mirroring monitor. [SQLSTATE 42000] (Error 32038). The step failed.
I have no idea, what causes the failover, it could be a slow network or a bad set-up, can anyone give me some ideas of what to do to track down the problem or any experience of what could be causing this, it happens randomly every day or three. No warning and if I go to the mirror and failover back to the principle again then it's all just fine. However I don't want half my databases working on 1 server and half on the other.
Any ideas?
Thanks
Ed
UPDATE:
I've just been looking at the logs on my Mirror and at the same time it reports in this order
Error: 1479, Severity: 16, State: 1.
The mirroring connection to "TCP://DMZSQL01.dmz.local:5022" has timed out for database "WARCMedia" after 10 seconds without a response. Check the service and network connections.
Database mirroring is inactive for database 'WARCMedia'. This is an informational message only. No user action is required.
Recovery is writing a checkpoint in database 'WARCMedia' (41). This is an informational message only. No user action is required.
The mirrored database "WARCMedia" is changing roles from "PRINCIPAL" to "MIRROR" due to Failover.
Database mirroring is inactive for database 'WARCMedia'. This is an informational message only. No user action is required.
...
This looks like a time out, is there any way to set the TimeOut threashold for Database mirroring or set retry intervals??
View 5 Replies
View Related
Feb 29, 2000
We need to start testing our Microsoft cluster servers.
Has anyone heard of any plan or products one can use to test
the failovers?
Please advise
Thanks
Susan
View 1 Replies
View Related
Jul 16, 1999
I need to know if there are any other failover options available for SQL Server/NT, beyond Microsoft Clustered Services. If there are any, which ones are advised?
Thanks,
Jon
jon_waldow@emeraldsolutions.com
View 1 Replies
View Related
Sep 24, 2002
Hi all,
We are the studying the possibilty of implementing SQL - Clustering for
Failover server.
Are there any third party tools readily available.
Any suggestion or checklist ??
Any suggestions or comments will be appreciated.
Thanks and have a great day.
View 1 Replies
View Related
Dec 13, 2004
Question(s):
If you are using Legato to mirror your database server are you running any specialized scripts against the system tables at failover time? Are you running any kind of script at failover?
Background:
We are using Legato to mirror several of our critical database servers and it’s really worked well for us but a situation occurred last week that causes me some concern and I’d like to know what other Legator users are doing.
Normally our failovers are for short durations, a few hours at most. This time the failover occurred over several days. I found that any job that executed a step with a DTS package was failing.
It seems that there’s a documented issue from Microsoft, that when the originating_server column in the sysjobs table contained a value other than the name of the current server, the job fails. The fix was simple. I changed the originating_server name to the current server and all was well. When we went back to the primary server, I changed the value back again.
This brings me to the question about any special scripts or processes that you may be running at failover time. Any information you share is greatly appreciated.
Sidney Ives
Database Administrator
Sentara Healthcare
View 1 Replies
View Related
Feb 15, 2006
This one is a needle in the haystack. I have a fairly robust Dell server running WIn 2003, 16 gig of memory, SQL SP3a using log shipping failing over to a local server and then back to my location across a WAN, using a vb6 app with about 60 users, a 24X7 shop Mon-Sat. 2 times in the past month the production server has created a corrupt log ship file which gets applied to my failover server and then corrupts the failover DB to both failover servers. Of course it is my 20 gig db and the only time I can recreate the failover is Saturday night. The production DB is not corrupt and is fine. Has anyone ever run into this before. Currently working with MS and Dell, but hoping someone had experience with this !
View 2 Replies
View Related
Mar 10, 2014
If I have multiple servers in my AG group and set two of the secondaries to synchronous with auto failover, how do I control which one one of the two secondaries will become primary if the original primary goes down?We need to maintain HA even during patching so need a first choice auto failover and a second choice auto failover.
View 9 Replies
View Related
May 13, 2008
Hi all,
Is it possible to setup auto-autofailower on 2 servers? Do i need to setup a witness on each server or what?
View 1 Replies
View Related
Jun 5, 2008
Dear All,
we have one production server and continuously getting the data from application.
we have replication server with transactional replication for the main database.
so, for taking the data, we are using prod server, and for fetching the data, we are using replication server.
now what i'm expecting from you is, if something happend to the main server, there is no cluster except backup. what is the best plan for this?
and one more thing, if replication server fails, then the application will not fetch the data then what is the plan for this?
please give me a good plan for this kind of strategy.
View 5 Replies
View Related
Aug 30, 2007
I'm sure this has been discussed in the forums before, but I'll ask anyways. I'm replacing our GP server next year to one that will run SQL2k5 64-bit. The current server is only capable of 32bit SQL. I know with some databases changing from x64 to x32 is a bad idea...will SQL Server support it?
Thanks
View 3 Replies
View Related
Apr 23, 2007
We are running on SQL 2000 on Windows 2003. We have active-passiveclustering set up. We have 16 GB of RAM on each box. This pastweekend, we failed over. I noticed that the Total Server Memory wentfrom about 15.5 GB to 8 GB. I have been trying to figure out why thishas occurred. Any help would be greatly appreciated.
View 1 Replies
View Related
