Question About Comunication Issues Between Principal And Mirror
Mar 3, 2006
Hi there.
Imagine this scenario in High Availability mode :
The principal sends a transaction log to the mirror, then the mirror saves the log on disk and then notify the principal. What happen if the acknowledge is lost, because a network failure(back on track in 1 or 2 seconds) and the principal dont receive it ?
In this case an automatic failover will not occurr, and i dont really know what happen next...
Will the mirror resend the ack ?
The heartbeat mechanism plays an important role in solving this scenario ?
The principal must receive the ack to commit the transaction, and i´m not seeing how the problem is solved.
I have one other question. In "Database Mirroring in SQL Server 2005" document, is said that "...unlike a distributed transaction, failures to commit on the mirror will not cause a transaction rollback on the principal".
But in this case the mirror will be inconsistent with the principal. How is this problem solved ?
We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.
This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.
Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?
I have a Server Agent Job that does a full backup of my principal database. I then have a separate job which does an incremental backup of the log files every 20 mins throughout the working day - the incremental backup sends me an email if it fails.
If I establish a failover to the mirror server, the backups continually try to run, sending me an email every 20 mins telling me that it can't back up the database as it's a Mirror.
Is there any way to script a method in the job that checks to see if the DB is a principal or mirror. if it's a principal I want the backup to happen, if it's a mirror I want it to skip the job.
Hi guys, right now I am facing this scenario several times and I wonder why it can be happen. Both SQL servers are in Enterprise SP1 edition. The mirroring setup can be work fine to me. Once my principal get attacking by DDOS, then mirroring session change the status to 'DISCONNECTED'. However, after DDOS attack solved and line connection gets back on the principal server, the mirroring session still remain to the status ('DISCONNECTED'). If I restart the SQL services on principal server or mirror server, then only the mirroring session change to 'SYNCHRONIZING' status. Does anyone faced this happen before? I need some assistance on it and hope able to get any explanation or solution from you guys. Thanx and have a nice day.
In documentation about SQL mirroring, the only thing about version requirement are that: 1) enterprise, standard, developer edition; 2) SP1 for official support
Is there any requirement for the Principal and Mirror SQL instances? For example, can one be Enterprise, the other one be Standard? Can one be SP1 and the other one be SP2?
It seems we are having some problem with the above scenario, not sure if that's supported..
We were having problems setting up the mirroring, so I did it via command lines. I found out the "alter...set partnership" command works on the mirror server going to the principal, but gets a 1418 error when going from the principal to the mirror. So if A is the principal and B is the mirror, A to B fails but B to A works. If I reverse it so that B is the principal and A is the mirror, B to A fails and A to B works. Any suggestions?
I have checked my Production databases, both are went to Disconnected state, both are unable to connect to the application, i checked all the services and its working fine. and restarted the end points on Principal and Mirror, still issue not solved, after restart the witness server End point both the databases are working fine.
I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.
The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.
ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'
After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.
The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.
Any idea's why the state between Mirror and Witness Connection remains Disconnected?
I was trying to test mirroring and now would like to delete the mirror database but it says I need to remove database mirroring first. I deleted the endpoint and cannot figure out how to remove the mirroring. Can someone please help.
I have deleted a windows login user under security in SQL 2012 Management Studio. The users have also been removed from the associated databases. When I try to add back the login, I receive the message 'The server principal 'xxxx' already exists'. What do I need to do so that I can re-add the login ?
I am trying to grant various users permissions and am using the following sql command
GRANT Delete ON Resident TO hector AS barbara
barbara is a member of the fixed server role sysadmin and has Delete permission on the Resident table.
I log in to the server as barbara and try and execute this command and get: Msg 15151, Level 16, State 1, Line 1 Cannot find the object 'Resident', because it does not exist or you do not have permission.
If I just run
GRANT Delete ON Resident TO hector
then everything works fine but the grantor is then dbo. I want to be able to have it recorded that barbara was the one that actually gave hector permissino to delete on the Resident table. Is there a way to do this?
I can successfully execute my package via Business Studio but when I schedule it in SQL Agent I get the error message, 'could not get proxy data for proxy id = 2.' I am trying to execute the scheduled job with that proxy (call it abcd_proxy) because I have a logon id (call it abcd) with access to a specific network drive folder, which holds a required source flat file. The abcd logon is defined as a sysadmin account. I am unable to see that the abcd logon id is an added principal within the abcd_proxy, which I think is because the abcd logon principal has a sysadmin role. My proxy id = 2 definition looks to be defined the same way that my first proxy id was.
Does anyone have an idea about what the message means and how I can fix it?
I am using asynchronous mirroring. When I try to failover on the mirror server using €śalter database xxx set partner force_service_allow_data_loss€? (having disconnected the primary server from the network) it sometimes fails to failover (it reports that the database is not in the correct state to failover). I can re-connect the primary server, get them sync€™d again, then unplug the primary and it will failover ok.
Any ideas... or is this just an issue that will be fixed in the next SP?
In the ErrorLog of my Sql Server , i found this line :
2007-06-26 05:35:18.37 Serveur The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Operating System XP Home SP2
SQL Server 2005 Express Edition with Advanced SP1 )
( idem for another workstation with XP Pro SP2 and same version of SQL Server 2005 Express
My problem is :
i want use the windows authentification but my computers are on Worhkgroup linked by a router ( no window server )
i have read that's possible to connect from a remote computer to a computer having a SQL Server 2005 Express through SPN
How can i do it ? ( activating NTLM ? but how ?)
I'm writing a C# program which must be executing on several computers with a SQL Server 2005 Express installed on a particular computer. These computers will belong to a domain of Windows Server 2003.
As i can't connect to this "normal" network, i am trying to simulate this network at home because i want to test this program and especially the possible locks problems.
I don't know whether i'm querying with the "correct" forum.
Hi all, I've got two VLDBs on 64bit hardware platform + 64Bit-W2003K Std R2 + 64Bit SQL2005 Std Edition. If I set this guy up as the Principal, can I have the standby/member on 32Bit HW + 32Bit W2003 Std + 32Bit SQL2005?
Database server: SQL Server 2005 Developer Edition with SP1 Application: An application developed by Visual Studio 2005 using C# (.Net framework 2.0) and ADO .Net 2.0.
Principal server: computerA Mirror server: computerB Witness server: computerC Mirroring mode: High availability with auto failover
Connection String: Data Source=computerA;Failover Partner=computerB;Initial Catalog=test_mirroring;Persist Security Info=True;User ID=sa;
(Part A) At the beginning of the test, computerA was in principal role. I started my testing application and connected to computerA without any problem. Then I disconnected the connection of computerA to the network by unplugging the network cable of computerA. The failover of database from computerA to computerB was carried out without problem. computerB was in principal role at that time. The application was pending for about 45 seconds and running again without problem. Then I re-connected computerA to network and it became the mirror server. computerB was still in principal role. Up to this point, all works fine, but the problem was coming next. (Part B) I disconnected computerB from network, database failover occurred, computerA became principal again. But my application cannot switch the database connection to computerA and then kept pending. Then, I re-connected computerB to network. It was surprised that the application switched the database connection to computerA successfully at that point.
My questions:
1. I think Part B is abnormal, isn€™t it? The application should be able to failover from computerB to computerA, because it works fine when failover from computerA to computerB. 2. Is there anything wrong in my code leads to the abnormal behavior of the application in Part B. 3. How can I achieve Part B? Any suggestion or idea?
I have setup database mirroring on two Windows 2003 R2 x64 servers using SQL Server 2005 SP1 Developer Edition. Our application is connecting to SQL Server using a SQL Server login. The application is using ADO and SQL Native Client to connect to the server. After a failover, our app attempts to reconnect to the database. The reconnect fails with the error:
Cannot open database "db1" requested by the login. The login failed.
The login is not associated with a user in the new principal database. I run sp_change_users_login to reconnect the user and login. sp_change_users_login says that it fixed 1 orphaned user. Our app then reconnects successfully.
I have tried several failovers, and each time I see the same behavior. The association between the login and user gets lost.
The issue is definitely with the login. I tried using sa to connect to the database, and then our app was able to reconnect after a failover.
Is this a known issue with database mirroring? Is it fixed in SP2?
I have database in the database there are a few users that no one is used. When I try to drop thpse users I got next error message: "The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped." (Msg 15136)
Indeed, I think that those users have execute rights on store procedures. How do I find for wich procedures or other database objects those users have grants? How do I delete them from database (and maybe from logins of the server)? How can I see what grants a user has? How can I see what grants does STP has?
Hello All, I'm hoping someone else has run into this, because I can't find any posts about it that are helpfull. I just updated our Dev SQL 2005 db to be able to use new 2005 key words with the script: EXEC sp_dbcmptlevel 'myDbName' , 90 This is all well and good until I notice my sa account no longer has a user mapping to my database. In managment studio when I go to Security - Logins - and sa properties If I try to add user sa to my Db I get the following errors: >>>------------------------------------------------------------------------------------------------------------->>> Create failed for User 'sa'. (Microsoft.SqlServer.Smo) Cannot use the special principal 'sa'. (Microsoft SQL Server, Error: 15405) <<<-------------------------------------------------------------------------------------------------------------<<< I'm access the SQL Server through a client on my local machine, the Server is set to mixed authentication, actual check box is labeled Sql Server and WIndows Authentication Mode. Hopefully I'm just doing something dumb, which is highly likely since I'm a developer and not a DBA. Thanks in advance
In SQL2000, when the Guest account was assigned into a role, such as db_datareader, then querying across databases worked just fine.
Specifically: I have a Report Writer application that connects to the SQL Server with a login (ReportRunner) that actually has very limited permissions on a database. The connection is then set (sp_setapprole) to use an Application role (App_RR) that has the necessary permissions. The report-writer app calls a Stored Procedure that gathers data from several other databases (on the same SQL instance). In SQL 2000, accessing these other databases was done through Guest - we assigned Guest to the db_datareader role. All worked fine.
We've just upgraded to SQL2005: reports started failing. It seems that although guest is assigned to the db_datareader role, the permissions for Guest don't allow selecting from tables via the db_datareader role: we've had to GRANT SELECT TO Guest specifically on the tables necessary for the report.
Is anyone aware of a design change withing SQL Server such that the Guest principal's roles are disregarded when assessing permission? Is there a new and better way to structure the permissions?
I have a problem that has happened a couple of times now. I'm in the process of testing our 2005 deployment - Standard edition on 2003 x64 servers for principal and mirror and 2005 express witness. All are pre SP1. Two databases are being mirrored.
I have successfully set up mirroring which seems to work fine - but when I restart the MSSQL service on the principal, spurious things happen:
The server that *was* the principal before restart
database A is in (Mirror, Disconnected / In Recovery) state database B is in (In Recovery)
The server that *was* the mirror:
database A is in (Principal, Disconnected) database B is in (Principal, Disconnected)
I can connect to both servers via Studio Manager and when I try to go to the current principal database to stop mirroring, the Studio Manager hangs and becomes non-responsive.
SQL2005 on winserver 2003. I have a view in Xdb that accesses tables in 2 different databases (Xdb and Ydb) on the same server. I have mixed mode security. I have a SQL user (XYuser) that has read access to all tables and views on both databases, yet when I try to access the view using a C# windows application I get the following error:
The server principal "XYuser" is not able to access the database "Ydb" under the current security context
This same scenario works under SQL 2000. I looked through the postings and tried to set TRUSTWORTHY ON on both databases but that didn't help. I can access any other views or tables on the SQL 2005 server, just not the one that joins the tables cross databases. Any help is much appreciated... john
Hi guys. I created a database "MyDatabase" using an AD Account "user01" which is a member of a domain group "GroupA". I have created a login in my SQL Server 2005 for this AD group. I have also added the said login as a user of my database. I created other AD users and put them in the same group. When I try to access the database using the other users, I get this message
The server principal "MyDomainuser02" is not able to access the database "MyDatabase" under the current security context.
This KB tells me that my situation is triggered by the first case in which the owner of the database is windows authenticated. I have already set the database to be TRUSTWORTHY. I haven't tried CERTIFICATES though. I believe I haven't done anything complex to warrant this solution.
I'm really lost with the myriads of principals and securables that SQL2005 has. All I want is just to execute a certain set of stored procedures. I can't do this because I can't even execute "USE MyDatabase". What are the permissions I need to give to "GroupA" ?
I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
Hello, The problem which i am facing in SQL Server 2005 is that, I have 2 database and the owner of both the databases is sa but when I go to database > security > users here I cannot find 'sa' as user. when I am going to add that user I get following error : cannot use special principal 'sa'
also when I connect sa on other computer I will not connect
I have already checked following thing : The Authontication mode of SQL is mixed mode database owner is also sa sa is also enable
