Database Mirroring Vs SQL Server FailOver (Performance)
Mar 15, 2006
Hi there.
I´m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.
The tests i´m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.
My question here is: What if i use Sql Server failover mechanism?
I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?
I have SQL Server 2008 R2 database mirroring setup with witness. Below database connection string is used by the application  jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER  During some maintenance activity at the OS side , I tried to manually failover database from Server_A to Server_B. It took long time for failover to Server_B. While failing over the Mirrored_DB database goes in In Recovery status and application is not able to connect to database. I waited for long time more than 40 mins for database to come online on Server_B but it did not work.So I have recovered database on Server_B using RESTORE DATABASE Mirrored_DB WITH RECOVERY.  After database was recovered on Server_B application was not able to connect to database with error as "The database <Mirrored_DB> on server Server_BMSSQLSERVER is not configured for database mirroring".  My Question is
Why manual failover did not get back database on secondary server upwhy the original  connection string below is not able to connect to the database while database is online on Server_B  but without mirroring
 jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER
I'm looking at a setup where they have server1 and server2 in a mirroring relationship with automatic failover.Â
Server1 is the principalThey are using transactional replication to replicate asingle databse to server3 is AWS.Distribution database is on Server1All Agents (log reader, snapshot, distributor) run on Server1Server2 has not been set up for replication...My understanding is that in this set up you would normallly place the distribution database on a separate server and enable publication on the mirror, Server2.
What happens if they failover? Replication would stop, and presumably records added while the mirror is the active database would not be marked for replication?How would they recover? Failback and reinintialize
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?
I'm testing mirroring in a high availability mode and getting some odd results. If I kill the SQL Service (or reboot the machine) fail-over works quickly and correctly. If I unplug the Network cable though I will get some of the databases failed over and some of the databases will either be Mirroring / In recovery or Principal / In recovery and never go live. Right clicking on them and going to properties lists there error that the database is unable to communicate with the Partner or Witness. The databases that this will occur on seem to move around (i.e. it isn't always the same databases, but will often be many of the same databases). If I reboot the databases that will not fail over tend to change fairly dramatically. I've seen instances where only 3 databases would fail over (I'm testing 17 databases) and other instances where all but 3 databases would fail over. I've had exactly one test where all databases failed over correctly. Again if I reboot a principal server, fail over works every time.
Has anyone else experienced only partial failovers for network issues or have a work around?
I have temporarily created a script that will force the databases to be the principal, but it's not great for high availability (probably will work for disaster recovery though).
We are planning to update our two node, active/passive clustered system to SQL 2005 from SQL2K. Would we be better off to use database mirroring rather than clustering with failover? I believe mirroring takes 3 systems rather than two (one for each mirror and one for monitoring), but can the monitor be a rather low powered PC?
Our current cluster uses two 4-processor Dells and an external PowerVault RAID array with fiber channel connections. Each server has two NICs, one for general network and one for heartbeat and a fiber channel card to connect to the external PowerVault.
Here are my questions:
1 - If I understand it correctly, mirroring uses just separate servers without the need for the rather expensive and complicated shared fiber channel array that must go between them. Do I still need a shared array or can I just use internal RAID arrays in each box?
2 - Are there any advantages to keeping our current system? Our current system has worked well, but the failover takes a couple of minutes. Is mirroring faster?
3 - Is there still a virtual IP address and network name that everyone connects to?
4 - What are the drawbacks?
5 - Can I run non-mirrored DBs on the same servers or are they really locked together tightly?
6 - How much different in configuration can the two boxes be? With the cluster, I really need duplicates in every way.
7 - Do I need a separate license for the SQL Server on each box in the mirror or is one enough, since they work in lock step?
we have to build high availability SQL 2012 cluster for VDI and we have two options. One option is to build a server cluster with combination of failover and mirroring and other option is to build failover cluster with AlwaysOn.We are not sure which option to chose. We have contacted Microsoft support to provide us some documents and instructions for failovermirroring combination but they have send us instructions for AlwaysOn option.
What would be best way to build high availability cluster for VDI? Also, since first option is very complicated.
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!
I'am currently testing a MSSQL 2005 mirroring implementation with automatically failover.
1 principal server ====> Server A 1 mirror server ====> Server B 1 witness server ====> Server C
When server A fails Server B becomes Principal. ( which is the general idea ) This is working perfectly.
The problem is when i try to connect to the mirror Server B ( actually the new principal )
I use the .NET connection string with the failover parameter ( even without the parameter ) the connection fails. ( the general error : unable to connect, or connection property has not been initialized )
Data Source=ServerA; Failover Partner=ServerB;Initial Catalog=DBMTest;uid=dbuser;pwd=dbpassword;
This connection property works fine when server A (original principal) is online.
When i run the SQL profiler with the mirror db as profile, i dont see the traffic.
So you think the mirror server has a network problem or was not connfigured for remote access. i thought that also, but when i create a 'stand alone - non mirrored' database the same application with the same connetion string ( well.....different db) works fine.
The problem must be in the whole mirroring implementation....... ( my idea )
Does anyone had the same problem, or better, doed anyone know how to solve it?
We have a Prinicipal, a Mirror and a Witness server. We have automatic failover configured between the Prinicipal and Mirrored server. When we stop MMSQL service on Prinicipal, not all the databases failover to the Mirrored instance. Any suggestions would be welcomed as we have a tight deadline to get this in Production.
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.
I've tried to replicated exactly what it says in the online documentation. At the end of the post are the SQL statements issued.
1. Enable encrypted outbound connections on the primary server 2. Enable encrypted outbound connections on the mirror server 3. Enable encrypted outbound connections on the witness server 4. Enable encrypted inbound connections on the primary server 5. Enable encrypted inbound connections on the mirror server 6. Enable encrypted inbound connections on the witness server 7. Set mirror''s partner to the primary 8. Set primary''s partner the mirror 9. Set principal€™s witness. Every step run''s normally,but why can''t auto failover.
I used the following Script:
/* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */ use master; go DROP USER PrincipalLogin1 DROP LOGIN PrincipalLogin1 DROP USER PrincipalLogin2 DROP LOGIN PrincipalLogin2 GO
Create login PrincipalLogin1 with password='PrincipalLogin' go Create user PrincipalLogin1 from login PrincipalLogin1 go
Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin1 go ---stop
create certificate MirrorCertForPartnerPub authorization PrincipalLogin1 from file = 'd:BackupCertMirrorCertForPartner.cer'; go
Create login PrincipalLogin2 with password='PrincipalLogin' go Create user PrincipalLogin2 from login PrincipalLogin2 go
Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin2 go create certificate WintnessCertForPartnerPub authorization PrincipalLogin2 from file ='D:BackupCertCertForDBRole.cer' GO
ALTER DATABASE Mirrortest SET PARTNER = 'TCP://10.1.40.158:5022' GO
ALTER DATABASE Mirrortest SET witness = 'TCP://10.10.11.30:7000' GO
select * from sys.certificates
/* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */ --On Mirror --Remove the database --Connect to either partner. --Issue the following Transact-SQL statement:
ALTER DATABASE Mirrortest SET PARTNER OFF
--Optionally, you can recover the former mirror database. On the server instance that was the mirror server, enter:
--RESTORE DATABASE Mirrortest WITH RECOVERY;
RESTORE DATABASE [Mirrortest] FROM DISK = N'D:BackupCertMirrortest.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10 GO select * from sys.endpoints
drop endpoint Mirror_Endpoint_Mirroring
select * from sys.certificates
Create certificate MirrorCertForPartner with subject ='this is the certificate for mirror', start_date='06/25/2006'
backup certificate MirrorCertForPartner to file='d:ackupcertMirrorCertForPartner.cer'
CREATE ENDPOINT Mirror_Endpoint_Mirroring STATE=STARTED AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING ( Authentication=Certificate MirrorCertForPartner ,ROLE=ALL) GO
/* -------- 3. ENABLE OUTBOUND CONNECTIONS ON THE WINTESS -------- */
--On Witness
USE [master] GO
select * from sys.certificates
drop certificate CertForDBRole GO
create master key encryption by password='asdf'
Create certificate CertForDBRole with subject='this is a certificate for pricipal role', start_date='06/25/2006'
backup certificate CertForDBrole to file='c:CertForDBRole.cer'
select * from sys.endpoints
drop endpoint Witness_Endpoint_Mirroring
CREATE ENDPOINT Witness_Endpoint_Mirroring STATE=STARTED AS TCP (LISTENER_PORT=7000) FOR DATABASE_MIRRORING ( Authentication=Certificate CertForDBRole ,ROLE=ALL) GO
/* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */ use master; go DROP USER PrincipalLogin1 DROP LOGIN PrincipalLogin1 DROP USER PrincipalLogin2 DROP LOGIN PrincipalLogin2 GO
Create login PrincipalLogin1 with password='PrincipalLogin' go Create user PrincipalLogin1 from login PrincipalLogin1 go
Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin1 go ---stop
create certificate MirrorCertForPartnerPub authorization PrincipalLogin1 from file = 'd:BackupCertMirrorCertForPartner.cer'; go
Create login PrincipalLogin2 with password='PrincipalLogin' go Create user PrincipalLogin2 from login PrincipalLogin2 go
Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin2 go create certificate WintnessCertForPartnerPub authorization PrincipalLogin2 from file ='D:BackupCertCertForDBRole.cer' GO
ALTER DATABASE Mirrortest SET PARTNER = 'TCP://10.1.40.158:5022' GO
ALTER DATABASE Mirrortest SET witness = 'TCP://10.10.11.30:7000' GO
select * from sys.certificates
/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */ use master; go DROP USER MirrorLogin1 DROP LOGIN MirrorLogin1 DROP USER MirrorLogin2 DROP LOGIN MirrorLogin2 GO
Create login MirrorLogin1 with password='MirrorLogin' go Create user MirrorLogin1 from login MirrorLogin1 go
Grant connect on endpoint::Mirror_Endpoint_Mirroring to MirrorLogin1 go
--stop create certificate PrincipalCertForPartnerPub authorization MirrorLogin1 from file = 'd:BackupCertPrincipalCertForPartner.cer'; go
Create login MirrorLogin2 with password='MirrorLogin' go Create user MirrorLogin2 from login MirrorLogin2 go create certificate WitnessCertForPartnerPub authorization MirrorLogin2 from file ='D:BackupCertCertForDBRole.cer' GO
Grant connect on endpoint::Mirror_Endpoint_Mirroring to MirrorLogin2 go
ALTER DATABASE Mirrortest SET PARTNER = 'TCP://10.10.116.42:5022' GO
ALTER DATABASE Mirrortest SET WITNESS = 'TCP://w7mis02:5024' GO
/* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */
use master; go DROP USER WitnessLogin1 drop login WitnessLogin1 DROP USER WitnessLogin2 drop login WitnessLogin2
Create login WitnessLogin1 with password='WitnessLogi*n@' go create user WitnessLogin1 from login WitnessLogin1 go
create certificate PrincipalCertForPartnerPub authorization WitnessLogin1 from file = 'c:ackupcertPrincipalCertForPartner.cer'; go --*************************************** Create login WitnessLogin2 with password='WitnessLogi*n@' go create user WitnessLogin2 from login WitnessLogin2 go
create certificate MirrorCertForPartnerPub authorization WitnessLogin2 from file = 'c:ackupcertMirrorCertForPartner.cer'; go
Grant connect on endpoint::Witness_Endpoint_Mirroring to WitnessLogin1 go Grant connect on endpoint::Witness_Endpoint_Mirroring to WitnessLogin2 go
We want to use sql 2005 failover clustering feature, so that upgrade sql 2000 is necessary. But some of the stored procedures built in 2000 are not working directly in 2005, set the compatibility level to 80 could make them work. In this case, can somebody here tell me if down grade the database level will affect failover clustering running properly? e.g. will data still be synchronized properly? Thanks in advance.
I have setup database mirroring for one big database.But now Transaction log is full and server is down. What should i do? We do bulk-insert in that database.
We had to failover our primary db server for maintenance to our secondary replica. The primary was rebooted during maintenance. We failed back after the maintenance and one of the databases is not synchronizing.
I checked sys.dm_hadr_database_replica_states, and it is showing that it is INITIALIZING.
It has been in this state for more than 45 mins now. The last_sent_time, last_received_time, last_hardened_time and last-redone_time are all stuck with a time stamp 45 mins ago.
They haven't changed. How do i resume this database and bring it back in sync?
I tried suspending and resuming the data movement, but hasn't worked.
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
Hello, My question is simply: according to BOL, a DB snapshot taken from a Mirror is read-only. Say I wanted a temporary (disposable) read-write DB created from the snapshot. Is there a way to generate a writable temp DB (that's not tempdb) from the snapshot? Thanks,AK
I get a general question about the feacture "Database Mirroring". Is it support by SQL Server Standard. Edition without SP1?? Of i still need to upgrade the server from SQL Server 2005 to SQL Server 2005 with SP1?
Another question: are there any problem if the one machine(server) has installed SQL server 2005 Standard edition and the other with "Developer Editon".
I hope some one can answer my question. Thanks anyway!
I have setup database mirroring with witness server. To prevent unnecessary failover because of network slow or other issue, I changed the timeout setting as
ALTER DATABASE <Database Name> SET PARTNER TIMEOUT 120
Which I understand if connection is broken between principal and mirror, principal database will wait for 120 second and after that only automatic failover will happen.
If this is true, it does not happen in my case. Failover happens before120 second.
I have multiple SQL 2008 severs with databases. Also, 1 mirroring server in place.
Since my database count is increasing can i have only 1 mirroring server. Is there any limit of db at mirroring server. I would have approx. 150 databases.
I have created mirroring... one of the column is encrypted on mirror database and I can see the decrypted result when I do query when I actually logged into server (through remote connection) but when I use the same query through using SSMS from my laptop the query result come as the column is not decrypted,
I am trying to enable database mirroring for 100 database. It goes error free till 59 databases (some times 60 databases) with the status (principal, synchronized) on principal. on the 60th or 61st database it gave the status (principal, disconnected). Also mirror starts acting abnormal. connection to mirror starts to give connection timeout and it is not enabling database mirroring on any more databases. I have SQL SERVER 2005 Enterprise with SP1 on the servers. witness is not included yet.
this are my test servers... i have more than 500 databases on my production servers.
principal and mirror both are using port 5022 for ENDPOINT communication.
I have a download "SQL Server 2005 Evalution Copy". I installed it to 3 machine, 1 - XP Professional (Witness) and 2 - Windows Server 2000 (Principle and Mirror) . And I want to try Database mirroring.
However, after restore the Log into mirror server I try to execute the following statement in Principle Server:
ALTER DATABASE TestDB SET PARTNER=N'TCP://[10.0.200.2]:5022' GO
The above 10.0.200.2 is the mirror server IP address.
it show error message as:
Msg 1416, Level 16, State 31, Line 1 Database "SCMPGroup" is not configured for database mirroring.
In Miorror Server
I execute the statement :
ALTER DATABASE TestDB SET PARTNER=N'TCP://[10.0.200.1]:5022' The above 10.0.200.1 is the principle server IP address.
It shows sucess
ALTER DATABASE TestDB SET WITNESS=N'TCP://[10.0.15.146]:5022'
it show error message as:
Msg 1416, Level 16, State 1, Line 1 Database "SCMPGroup" is not configured for database mirroring.
Please help me! I down't know if NT4 domain or evalution copy problem in there.
I am trying to enable database mirroring for 100 database. It goes error free till 59 databases (some times 60 databases) with the status (principal, synchronized) on principal. on the 60th or 61st database it gave the status (principal, disconnected). Also mirror starts acting abnormal. connection to mirror starts to give connection timeout and it is not enabling database mirroring on any more databases. I have SQL SERVER 2005 Enterprise with SP1 on the servers. witness is not included yet.
these are my test servers... i have more than 500 databases on my production servers.
principal and mirror both are using port 5022 for ENDPOINT communication.
All of the databases are critical and all must be included in the Database Mirroring. so, after that I tried to implement database mirroring again...... System has 3 GB of RAM, SQL SERVER (Mirror) using 85 MB of RAM but still giving this error while trying to enable database mirroring for 37th Database.....
"There is insufficient system Memory to run this query"
I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:
ALTER DATABASE MYDBNAME SET PARTNER FAILOVER
, ServerB becomes the principal, it means that mirroring works correctly.
My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :
Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:
ALTER DATABASE MYDBNAME SET PARTNER FAILOVER
, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:
Microsoft SQL Native Client error '80004005'
Cannot open database "MYDBNAME" requested by the login. The login failed.
This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.
When I swap server, I execute on server ServerB command:
ALTER DATABASE MYDBNAME SET PARTNER FAILOVER,
my IIS 6.0 application automaticly turn back to ServerA without any action on my side.
I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client
I configured a database mirroring on SQL server 2008 R2 on test environment. However It was not working, after some time. I deleted the mirroring configuration and also deleted database on both primary and mirror instance.
few days later I notices, On Primary, Continuous error events logged with message "database mirroring attempt by user 'domainservice account' failed with error 'connection handshake failed, 'domainservice account' does not have permission on the endpoint state 84.
I have a 2 node cluster having 4 cores each wherein having 3 instances of SQL 2008 R2 enterprise comprising of 60 databases, 20 on each instance. I need to setup mirroring for each of the databases to a secondary server having 4 cores and 3 instances. What i understand is that in this case the mirror server will be providing max of 512 worker threads and the 60 mirror databases would consume 240 threads.what all needs to be checked for looking into the feasabilty of going ahead with a async mirror setup as mentioned above.
Can someone at Microsoft comment on this article, specifically, how it relates to SQL Replication? Will SQL Replication fall into the category of what this article describes, or only the mirroring feature?
