One of my client asking mirroring between two server with witness.Is it possible witness and mirror in same server(with same instance)? OR Is it possible witness and primary in same server(with same instance) ? Best method to establishing mirroring with in two servers(with witness) ?
View 4 Repliesmissing witness server information and the fail-over is broken suddenly? 4:00am no maintenance job. I have one sql job on 10pm for backup on database transaction log only.
I can see the primary have problem then perform fail-over to mirror database, the auto fail-over was broken.
I re-build the sql mirror is OK , but i want to find the root cause.
Windows application event was full when there have many failed event, i have increase log size for application event.
Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)
For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?
I have managed to set up mirroring on SQl 2005 however i have a come across a problem that i dont know how to resolve.
Im running the mirroring with 'High Safety with automatic failover.'
If i instagate a failover on the Principal Database, the mirror becomes the Principal, and vice versa ... great .. how ever .. i tried the golden test by making the principal server dissapear by yanking out the network connection, expecting the mirror to become the principal.....nope it just stays there in a disconnected / restoring state which isnt any good to man nor beast . I thought that the Witness should have dectected that the principal database had 'dissapeared' and would have made the mirror the Principal.
Any pointers on where i might have gone wrong in the setting up process, i used the mirror wizard
Can 1 witness server work for 4 Principal and mirror servers?
Needs expert advise.
I need to start mirroring the databases without witness server.
View 3 Replies View RelatedI'm devlving into 2005 and specifically mirroring.Having read the following article (http://www.microsoft.com/technet/pr...5/dbmirror.mspx ),is mirroring a sensible option as it indicates that it should not beused in a production environment, yet the SQL2005 seminars I've been toindicate that this is a worthy option.Do we also need a witness server, or can this be done (sensibly) withtwo SQL2005 servers ?Currently we have 2xSQL2000 (clustered) servers and a SAN for the data.If we go down the route of mirroring, it looks like we can get rid ofthe SAN and Cluster and use the mirroring instead.Any advice on the pro's / cons would be appreciated.ThanksRyan
View 5 Replies View RelatedI am unable to add witness server to my mirror configuration. my error log is throwing error'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89'.
View 5 Replies View RelatedHi guys , I would like to know what's the exactly results able to get in this following scenario.
Server A : Principal
Server M : Mirror
Server W : Witness
1) Firstly, disable server M, following by server W. So probably i will unable to connect to Server A eventhough it acts as a principal.
2) Next, if I enable either server M or server W , what will I able to get actually ? Is it the mirroring works like the article which written in Microsoft page ?(http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx#ETPAE)
At here, I get an error message and unable to continue the mirroring section. ( "Database EMS is enabled for Database Mirroring, but neither the partner nor witness server instances are available: the database cannot be opened." or "Database 'EMS' is being recovered. Waiting until recovery is finished." )
3) If I enable both server M and server W, the mirroring continue to work again.
Can I have any assistance on (2) issue? Thx for the assistance.
Best Regards,
Hans
We have 2 data centers, site 1 and site 2. Site 1 is generally our primary, and site 2 is our Disaster Recovery (DR) site. I want to setup a SQL instance to have extremely high availability. Therefore I was looking at using DB mirroring, with synchronous data writing, high-safety and auto-failover. This requires the usage of a witness server.My problem with this setup, is that if the witness and principal are both at site 1, if site 1 goes away (power failure, asteroid impact, lol, anything else that would be a *true* DR scenario), then there is no failover to the mirror server at site 2. However, if you put the witness at site 2, anytime the WAN link between site 1 and site 2 has an issue, the DB will auto-failover to site 2. Is the reason for the recommendation of the witness being at the primary site because the thinking is that WAN link failure is likely more common than a *true* DR scenario that takes out all of site 1?
View 3 Replies View RelatedI 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.
View 4 Replies View Related
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?
Thanks,
I have 2 web servers in a workgroup both running SQl 2005 SP2. both servers have a user called sqluser which is the main login for sql on both boxes. When I use the mirroring wizard it creates the end points ok but does not like the fqdn of each server, these are called webserver1 and webserver2. Is the problem because of the name and lack of domain ? Can I add a user for server1 on server2 or do I need to use certificates to get Mirroring working in this type of environment ?
View 10 Replies View Related
we are having 2 prodction servers in our environment.
and we are planning to perform database mirroring between them.i want to mirror server 1 databases to server2 and server 2 databases to server 1.
is this possible??
if possible , can any one give me a link to document where i can find more info on this, or send me the steps what i need to done for this...
thanks
I created a mirrored DB, added a new datafile to the principal using a path the mirror can't access. As a result the mirroring session was suspended because the mirror stated: "CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file".
Fine, so I disabled mirroring on the principal (SET PARTNER OFF) which worked just fine, but the mirror stayed in a mirrored state, Object Explorer saying:"(Mirror, Disconnected / In Recovery). Sooo, I tried to disable db mirroring on the mirror using ALTER DATABASE db SET PARTNER OFF;, which completed successfully, but the DB STILL remained in a mirrored-configuration.
I tried ALTER DATABASE db SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS which resulted in
"Msg 1404, Level 16, State 4, Line 1
The command failed because the database mirror is busy. Reissue the command later."
I tried taking the DB offline or restoring it from a backup, but all these operations resulted in "The operation cannot be performed on database "db" because it is involved in a database mirroring session or an availability group."
The only solution I can think of at the moment is shutting down the instance and deleting the data and log-files of the mirrored db, which would be just fine because this is just a test installation, but it would be not quite as easy in a productive environment.
Is there any other way to remove the mirrored state from a disconnected mirror or to simply get rid of the db entirely to perform a recovery?
I am setting up mirroring on one of our live databases tonight. The database is quite big, around 300GB.
At the moment we are doing log backups once a day and full backups every night.
My question is, when I set up the mirror, will the log backups on my Principle DB affect the mirror in anyway?
I am trying to configure database mirroring with sql server 2012.
My principal instance and my mirror instance are on different server.
Both SQL Server service run with NT SERVICEMSSQLSERVER.
Now when I am configuring database mirroring, which service account should I use ? database account or server account ?
Instead of using domain account, can I :
1- create local account on my two server
2- Add these 2 logins in my 2 instances.
3- use these local account for configuring database mirroring.
After breaking the mirroring database to allow testing on the DR site, is the only way to re-establish the mirroring again to copy the backups from the actual production site ?. Can something be done on the DR site to put back the mirroring after the test ?.
View 9 Replies View RelatedI have the following mirroing setup for DR.
Prinical(ServerA) and Mirror(ServerB)
When we do a DR test we do manual failover from Principal(ServerA) to Mirror(ServerB)
Now
ServerB is Prinicpal
ServerA is Mirror
Applications do their testing against the new principal which is ServerB but dont want to take those changes or sync to ServerA(Mirror).
Is there any way to do this without breaking the mirror?
Finally It will go back to original setup after the test which Server A is Principal and Server B will be its mirror.
what are the commonly used system tables for mirroring
View 3 Replies View RelatedNeed to know some of the DMV'S and System Tables related to Database Mirroring.
View 2 Replies View RelatedI'm setting up AG replication over our WAN (1GB MPLS). Using sql stress, AG replication quickly falls behind. Network throughput plateaus at ~14Mbs.
The problem ISN'T NETWORK
using conventional mirroring, the same workload never falls behind, throughput plateaus at ~26Mbs.
it's almost like log stream compression isn't happening for AG...
both mirroring and AG are using async.
I have created a new login in principle server and provided dbowner permission to principle db.
How do I transfer this login to mirror server and assign the same permission to mirror dd?
As the title suggests we are looking to change the service account of a SQL mirror implementation. I will be using the same account on all 3 servers involved in the configuration.
I know each server requires the account of the other two adding but as this will be the same account I assume this doesn't apply?
Also for mirrored databases already set up would I need to reconfigure the security for each one?
Is there anything I am missing?
I have to mirror 3 DBs on my sql server instance . I have restored there full backups and log backups on mirror.I have also created endpoints on principal and mirror using default ports 5022 and 5023.
know if i can use the same endpoint for the databases on principal instance ?
I was told that if I wanted to setup mirroring between multiple instance on the same server that each endpoint had to be different number. Is that true? Or can I use the default 5022 for each instance?
Ex.
Server1Instance1
Use endpoint 5022
Server1Instance2
Use endpoint 5022
VS
Server1Instance1
Use endpoint 5022
Server1Instance2
Use endpoint 5023
We currently use Zabbix to monitor our SQL Servers, generally we use Perf_Counters to gather performance information (CPU Usage, Memory, Blocked process etc.), Zabbix also lets us monitor services as to whether they are up or down. I have searched google looking for any information regarding the monitoring of AlwaysOn/Mirroring within Zabbix and to date ave found nothing. What I am looking to alert on is an indication that Mirroring has stopped/ been interrupted with say something like "Not Synchronized"... Zabbix and monitoring SQL Server 2012 and specifically monitoring AlwaysOn...
View 9 Replies View RelatedI've read here at [URL] that with 32 bit it's recommended to only have 10 database mirrors per instance. However, is there a limit for 64 bit SQL Server 2012?
We're having a problem where we have about 300+ databases and now any new db's we add are timing out when it comes to setting up mirroring and am wondering if this is limited by the instance.
I have 2 server PROD n DR server all the PROD server DB is synched to DR server through log shipping. Now to chk whether the DR server is working or not in case of any disaster we shut down the PROD server now all the app request is handled by DR server,it is working fine. For 1 week we work in DR server now its time to move back to PROD server so that all the 1 week data in DR server should be replicate back to PROD server. Log shipping is deleted once the DR Server is up. What r the options other than Backup? It is SQL 2012
View 4 Replies View RelatedI would like to know if it is possible to split a database across different servers, in the same manner you can split it over multiple drives on the same server We are trying to balance the load cause we are finding that the current server can't handle the load
View 4 Replies View RelatedI want to compare the database structures(columns,datatypes..etc) across same databases located in different servers.
View 9 Replies View RelatedI have multiple instances of SQL 2012 Std Edition on a 40 physical core server.What I have done is the use the Process - SQLServr -% Processor time Stat and divided by 16 ( the max number of Cores Std ed. can use) as a instance level measure. I also use processor object stats to show how busy the server is. How to represent the servers CPU utilization?
View 1 Replies View RelatedI am trying to reproduce a problem that is probably related to how a particular SQL server is configured. Is there a tool or best practice that is useful to compare all of the configuration settings between two SQL Servers (same version)?
View 2 Replies View Related