Partner Works From Mirror To Principal, But Not Vice Versa.
Nov 7, 2006
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?
My asp.net website is using sqlexpress2005. I am planning to use sql2000 with this existing website. My questions:
1. How can I transfer the database definitions and actual data from 2005 to 2000? is this possible? 2. What if I have exisiting database in sql2000, can I convert it to be used in sql2005? 3. should I manually recreate the database when converting from one version of sqlserver to another? and how abt the actual data?
I work on the Clinical Surgery Department Data Application. The patient gets through the Admission, Surgery, PostSurgery and Discharge stages during his staying in the department. The Doctors just insert their medical data in to the different tables. I have three main classes: User, Patient and Portfolio ( patients portfolio ) and all the business bases on these classes. The problem is with the Classes per Medical Data Tables. The Medical tables should exist cause it includes the medical data about the patient however what is not clear: Whether I should create Business and Entity classes per each medical data table ( and this is something like 50 tables ) or I should insert all the functions in the Portfolio class and then there will be a mess of functions in one class? What is the accepted way to perform it ?
We have setup SQL Server 2000 32-bit (Publisher/Subscribers) replication with 5 different locations. We are planning to purchase SQL Server 2005 Enterprise Edition 64-bit and/or 32-bit. I need suggestion, if we have both version of 2005 Enterprise, can we setup replication with SQL Server 2005 64-bit with SQL Server 2005 32-bit? is that supporting?
Also can we run replication with SQL Server 2005 64-bit (Publisher) with SQL Server 2000 32-bit (Subscriber).
Is there any equivalent function in T-SQL (SQL Server 2014 / SQL Azure)?
Specifically, I want to be able to convert a time into UTC before inserting it into a table. Right now I am doing:
INSERT INTO Incident (IncidentDescription, dtOccurred) VALUES (@IncidentDescription, DATEADD(hour, 8, @dtOccurred))
I don't want to hardcode "8" and I want it to adjust for daylight savings time (DST).Â
Is there any concise solution that does not require me to define SQL functions or have lookup tables? I also don't want to use the difference between GETDATE() and SYSUTCDATETIME(), because the timezone of my server is not my local timezone.
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?
We have a pair of SQL 2005 SP2 with Rollups clusters. We have a series of DB's that we are migrating from an existing SQL 2000 cluster. I have scripted the process, however on one of the test DB's, it goes to "In Recovery" as soon as I issue the Set Partner statement. There are other DB's on the same cluster mirrored with no problems. As we have a bunch of DB's to migrate, I want to figure out what would cause it to start a recovery. After the initial restores are done, it is in "Restoring" for a status so everything works up to that point. Thanks Jon Macy
If I join Table1 to Table2 with a WHERE condition, isit the same if I would join Table2 to Table1 consideringthat the size of the tables are different.Let's assume Table2 is much bigger than Table1.I've never used MERGE, HASH JOINs etc, do any ofthese help in this scenario?Thank you
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.
1. Is it possible and what would be the gotchas for taking a backup of a database in a 32 bit instance of sql server 2000 and loading it into a 64 bit instance of 2000? How about 2005 to 2005?
2. Is it possible to reverse that and go from a 64 bit to a 32 bit?
3. How about going from a 32 bit sql server 2000 into a 64 bit sql server 2005?
We're on the verge of upgrading a rather large database from sqlserver 2000 (32 bit) into 2005 (32 bit) but they anticipate eventually upgrading the hardware to 64 bit at least for production. The test node would remain at 32 bit. Thanks for any help.
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 ?
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..
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.
Hi all,Here is the table and DML statmentsCREATE TABLE [jatpartnerMst] ([rowid] [int] ,[partnerid] [int] NULL ,[mcstat] [int] DEFAULT (1), -- 1 Pending ,2 Approved[sf] [varchar] (20))INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(1,1,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(2,1,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(3,1,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(4,1,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(5,1,1,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(6,1,2,'inactive')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(7,1,2,'inactive')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(8,1,2,'inactive')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(9,2,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(10,2,1,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(11,1,2,'active')What I wish to find is the latest record on the top and it's otherrecordse.g If partnerID 1 is changed it goes to the bottom of the table , atany given time I am interested only in max(rowid) for a partner withstat 1 or 2I am using this queryselect * from jatpartnerMst where rowid in (select max(rowid) fromjatpartnermst where mcstat in (1,2) group by partnerid,mcstat )This query does not give me the latest.On using this queryselect * from jatpartnerMst where rowid in (select max(rowid) fromjatpartnermst where mcstat in (1,2) group by partnerid,mcstat )order by rowid descThe partner's two records get seperated . I wish to show them followingone another.So the output should be1112active511active922active1021active11 & 5 rowids are following each other because they are rows of samepartner and 11 is the most recent row [ because new rows are insertedat the end]Is it possible to do the above using single queryI am using cursor to do the same.With Warm regardsJatinder
Hi, i am working with SMO object, At the time of accessing remote(LAN) database server I encountered the following message: "MirroringPartnerInstance" : Undefined error.
I m not able to figure out whats the exact problem is..
i had gone through the following link too but doesn't make any sense to me : msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.mirroringpartnerinstance.aspx
All i understand is i need to set some property of mirroring partner.
Does any on have any idea how to resolve this problem
I've read that when this run's, it removes all db mirroring information on that db. What exactly does it remove?
Here's my senario:
We are using SQL 2005€™s db mirroring process. We are using the certificate method of authentication between the principle and the mirror db€™s.
My question is that when the ALTER DATABASE dbname SET PARTNER OFF is run, does it remove these certificate settings as well? In other words when I want to enable the db mirroring, will I need to recreate these certificates or just recreate the endpoints to use these certificates?
I have the following error during setting partner on mirror server Msg 1431, Level 16, State 4, Line 1 Neither the partner nor the witness server instance for database "masterserver" is available. Reissue the command when at least one of the instances becomes available.
The partner is available through telnet. I've also checked ports vai netstat and have no found errors.
There are two noteworthy erros in the error log at mirror server Error: 9642, Severity: 16, State: 3. and An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')
We have a database,which has been mirrored.Also,We have an application which uses OpenRowSet to connect to this database.
Is it possible to set "Failover partner" in OpenRowSet connection string,so when we failover from Prinicple server to the mirrored database,The application still will continue to work?
Example:
select * from openrowset( 'SQLOLEDB', 'Data Source=Server1;Failover Partner=Server2;trusted_connection=yes;','select top 10 from Database1.dbo.Table1' ) temp
if server 161 is principal and server 162 mirror, it connects ok, but when I exchange server roles, connect fails (the error message is: Cannot open database "TestDB" requested by the login. The login failed. in LOGIN)
the connect string using OLE DB is:
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=test;Password=test;Failover Partner=10.0.1.162;Initial Catalog=TestDB;Data Source=10.0.1.161;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False
error message is the same
when I try to connect using VS 2005 using connection string Database=TestDB;User Id=test;Password=test;Server=10.0.1.161;Failover Partner=10.0.1.162, it works OK
i have installed SQL server 2005 (on local - client machine) with SQL Native Client and also
