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.
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
missing 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. Â
If I am using Database Mirroring for my production database in SQL Serever 2005, can I do mirroring on the mirror database?
Principal Database -- Mirror -- Mirror A -- Mirror -- Mirror B
Can this be done?
Question 2:
Can I do 2 mirroring on a single principal database? If can, which mirror database it will goes when the failed over happen? can the other mirror switch to mirror the failed over database?
we designated one server as a mirrored server. this server will host all mirrored databases from different principles in different server using certificate.
1) Server A (Principle) Server B(Mirrored) 2) Server C (Principle) Server B(Mirrored)
I was able to set up mirroring between A and B. But unable to set up mirroring between C and B. My question is "Can I grant connect on endpoint to two different host logins?"
For example, on the server B I created two different logins for Server A and C in order to assign different certificates to two principle servers.
For Server A. create login server_A_login with password = 'djkkajnxks'; go create user server_A_user for login server_A_login go create certificate server_A_cert authorization Server_A_user from file = 'c:certificateserver_a_cert.cer' go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [server_a_login]; GO
For Server C. create login server_C_login with password = 'djkkajnxks'; go create user server_C_user for login server_c_login go create certificate server_c_cert authorization Server_c_user from file = 'c:certificateserver_c_cert.cer' go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [server_c_login]; GO
when I set up like this and try to start mirroring, I am getting error from server c saying, "the network tcp:serverb.corp.com can not be reached or does not exist"
But mirroring between server a and b is OK. I set up them first, then between c and b.
I've a very basic doubt about database mirroring. I did setup a database mirroring session with the help of SQL Server Management Studio between the server A (db1) and B(db1). When is stop database mirroring by using the command button "Stop Mirroring" available in the mirroring page of SQL Server Management Studio , the mirror database{ B(db1) } goes to state "Restoring...". After stopping the mirroring i'm not able to access the mirror database.
Can you please tell me how to bring mirror database B(db1) to operation mode so that we can start working with that database?
When I try to setup mirror on a SQLserver dbase using windows Authenticated login I get error:-
Login failed for user 'SERVERBAdministrator'. (Microsoft SQL Server , Error: 4060).The login connect string however on selection of SERVERB from SERVERA has a greyed out option for using Windows Authenticated login which clearly shows Username: SERVERAAdministrator.It is odd that the error coming back is showing SERVERBAdministrator on the rejection. (somehow the machines got swapped over in the authentication) The strange thing is if you use the SQLserver studio on SERVERA to connect to SERVERB using the object browser and Windows authenticated login it all works fine without errors. clearly the Mirror software is using some other means/privs of login to establish the connection.
The two machines SERVERA and SERVERB are in the same Domain (DOM1) but they are not using a domain user account.They are using Administrator user on both independent machines. The SQLSERVER install specified both Windows and sqlserver logins. The mirror is to use Windows authenticated login.Is there some login priv we are missing The sqlserver security has allowed this type of login......
Hi 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.
When Create Mirror Database Server, Where need to store the Transaction Log backup file?I took FULL Backup of my Primary Database, and I restored at my Mirror Server also. When I try to create a Mirror Database."The remote copy of database "<db_name>" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error:1412)".I am misplacing the Transaction Log backup file. Where I need to store that file?
I have created a Endpoint with "Mirroring Server" on the mirroring instance. Due to the keyword server i am not able to drop the endpoint now.
Also am not able to configure a mirror in a windows workgroup also( the two systems principal and mirror are under same network ). I have followed many ways as per msdn articles and other blogs.
I am not able to connect to the mirror server.Â
I have tried giving the ip addr and the port no, also the computer name:port no, computername.local:port no etc. Both the systems are running on sql server 2008 only
I always get the error as Connection cannot be established to the destination.
(I have also enabled the ports in firewall by creating inbound and outbound rules)
The issue probably lies in NAMING server address i guess.
Hi everybody, I have a mirroring set up for around 30 databases in a 64 bit - 2 powerfull servers scenario - wih latest sp2 + hotfix. Mirroring works perfectly most of the times but when the principal is getting busy during the busy period of the day all of a sudden the processor on the mirror jumps to 100% and stays there for a long time - there are no live databases on the mirror server - all the databases are in mirrored mode. The principal server processor is less than 5% all the time. The network load between the principal and the mirror is also in normal limits. The principal server is dedicated to SQL but the mirror has other things too but the other processes use up maximum 15-20% of the CPU and when I stop SQL Server in one of those moments when the CPU jumps to 100% everything comes back to normal so it's clear that the issue is SQL on the mirror. Any ideas would be greatly appreciated.
I have three win2k8 r2 servers. Â 2 are running SQL 2008 r2 and are mirrored. The 3rd server is a witness server. Every so often we get errors and failovers of the mirror. The communication errors are between the witness server and sql servers. No errors between SQL servers. There seem to be no network issues and happen randomly.Â
Event id 1474 and 1479. The mirroring connection to "TCP://witnesssrv:5022" has timed out for database "DB" after 30 seconds without a response.  Check the service and network connections. Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://witnesssrv:5022'.
I have configured active passive cluster in production environment. And we also have a dr which we have configured with asynchronous mirroring with no witness. Currently active node(node
a) is in sync with dr. When failover happens and the second node(node b) becomes active, the mirror is broken and goes to disconnected mode.
But when we failback again to node a mirror is connected again and is in sync again. In our setup we have active passive cluster and a standalone server as dr.
DB replication can set db recovery model to simple ,why db mirror can not  db recovery model to simple.
DB mirror must be set to full recovery model.
As far as I know, whatever db mirror  and db replication ,there is a log reader to read the log in the ldf file DB mirror and DB replication are almost the same principle to replicate the db to another db server.
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 feasibility of going ahead with a async mirror setup as mentioned above.
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?
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'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
I made a website with sqlserver membership when i migrate the website to another server i can not connect to the database aspnetdb how i can add a user to this data base in the visual web developer
We are specializing on Migrating, Replicating, Synchronizing data and databases on Windows, Windows NT, Unix, AS/400 and legacy. We deliver our software to migrate any data to MS SQL Server.
We are specialized to deliver DataMarts for any databases with :
a novative script and visual language VEXScript that you can play with W95, W98, WNT stand alone or with your prefered query tools or PC language
Independant database software vendors
Compatible XML (Q1 99)
Messaging interface ( MS Exchange, Lotus, Mapi, ......)
If you want visit our web site on www.dataexchanger.com and e-mail us to marketing@dataexchanger.com if you want ask us.
We will be pleased to enter in agreement with you.
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?
We use SQL server always on feather on my database and we distribute statement on main database server and mirror database server for raise performance.
My police for split statement is DML (insert, update and delete) statement go to main DB and Read Data (select) statement go to mirror DB.
I want know can I use different index on main DB and mirror Database?
Because some index are used in mirror DB not used in main database.
In my application,I used Sql Server 2000 Database where I used some Stored Procedures,View,Function and Triggers .Now I want to migrate it to Sql Server 2005. Is there any performance issues will come in future as the database is actually from SQL SERVER 2000? Is all these Stored procedures,Views,Function and Triggers will work perfect? If than what steps I have to take to over come them.
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.
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....
