Failover Not Working Because Logins Are Not Synched
Aug 9, 2006
I have the failover working fine in the sense that the mirror becomes the principal when I issue a failover. The problem is that the login that I have set for the database becomes invalid and I get no permissions on my stored procs unless I go in and manually change the password for the login. I know the issue is that the logins have different SIDs on the two boxes. How do I sync them up? These machines are not on a domain and are using SQL authentication.
Brad
View 2 Replies
ADVERTISEMENT
Dec 13, 2006
Since the logins/jobs do not get stay in sync between principal and mirror after a failover (when using sql logins), is there a workaround to schedule a job or something to automate dropping, re-creating and autofixing logins?
I've read somewhere that an alert can be setup to run (after a failover) and a job can be executed, but cant seem to get this to work. Does anyone have any helpful hints on a solution to this
View 4 Replies
View Related
Aug 8, 2007
In an sql authentication environment with an automatic failover in database mirroring how to you manage new logins which have been created on the principle since the start of mirroring? Since the master cannot be mirrored, and the mirror database cannot be read during mirroring (except as a snapshot) in order to find the missing logins, I assume that only after failover a script should run to create the new logins and then run sp_change_users_login . The qestions are:
1) should the script create a new login first and then run sp_change_users_login with option update_one , or should sp_change_users_login using option
Auto_Fix create the missing logins?
2) But what is the password of these users? is it initially NULL , as a consequence of sp_change_users_login? What about the SIDs?
3) Or should we bypass sp_change_users_login altogether and use
CREATE LOGIN <loginname> WITH PASSWORD = <password>, SID = <sid for same login on principal server>,...as described in http://blogs.msdn.com/chadboyd/archive/2007/01/05/login-failures-connecting-to-new-principal-after-failover-using-database-mirroring.aspx
4) What is the event that would trigger this script to run after the aitomatic failover ?
Is there a definitive MIcrosoft agreed apon and recommended method to tackle this?
View 3 Replies
View Related
Apr 21, 2014
I've setup a two node Cluster Server (non-shared storage) with a file sharing witness. I'm testing some of the different failover scenarios to see that everything is working properly. Everything works fine until I try testing the failure of the SQL Server service. When I stop the SQL Service on the primary server, it fails over to the secondary server as expected. I then start the service on the (now) secondary server and it comes back online as the secondary server. I then try to test that the service will fail back over when I stop the service on the new primary server.
However, when I stop the service, the secondary server now shows "resolving" and never comes back online. When I bring the service back up on the primary server, the secondary now shows as secondary instead of resolving. So to see if it's something about failing over from one server to another, I do a manual failover making the original primary server the primary again and everything is as it was originally.
I then stop the service on the primary server, but the secondary server now says resolving and the AG will not become available again until I start the service on the primary server.
It seems that when I first configured the quorum it worked fine the first failover scenario, then stopped working. I then added the file sharing witness, and failover worked the first time again, but not after that. For some reason after the initial failover it won't automatically failover again after that.
Config:
Servers: Windows Server 2012 Standard
SQL : SQL Server 2012 Enterprise SP1
View 5 Replies
View Related
Jan 21, 2007
ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.
Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?
I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.
Any assistance would be greatly appreciated.
Thanks,
John
View 3 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
Jul 17, 2001
Hello,
I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.
What's the best way to solve this problem?
Please, help
Thanks
View 3 Replies
View Related
Apr 3, 2007
I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.
The app executable is tied to a Windows service.
When we install the app, we run a process that builds 2 dbs to include:
Tables, indexes, stored procedures, views and user accounts.
SQL Server is set up for mixed mode authentication.
Normally, the dbs run off the local db user accounts which are tied to local logins with the same names.
We have a client that wants to remove our standard logins so that they can run on only a Windows login.
I know I should be able to tie the db users to a Windows login.
And I can do the same for the service.
But I am at a loss as to how to get this done.
How do you associate db users with a Windows login?
When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)
Hope this all makes sense.
View 2 Replies
View Related
Mar 3, 2006
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
View 5 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 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
May 25, 2006
Is it possible to failover a single or a selected number of databasesonto a standby server? We're currently log shipping about 12 databasesto a standby and I was wondering if it was possible to randomlyfailover onto selected dbs.My understanding of log shipping is that when the primary server goesdown, the standby takes over. But, does that mean that all of the db'son the standby server now become the primary? Or, can failover occur onselected dbs?The reason I ask is because if one db on the primary, for example, wasto go down, I'd like to be able to bring the particular db on thestandby server online while I fix or restore what's on the primaryserver. Can it be done individually or is it an all or nothingscenario?Your personal advice or links is greatly appreciated.Thanks,Peps
View 1 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
