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.
View 1 RepliesI 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,
All,
I have created an asynchronous database mirror and a snapshot of the mirrored database similar to what is described in the following article:
http://msdn2.microsoft.com/en-US/library/ms175511.aspx
The article says that once you create the mirror that "At this point, new client connections can be programmatically directed to the latest snapshot."
My question is, how can you programmatically direct connections to the latest snapshot?
Does anyone have a suggestion, or know what the article is getting at? We will use SQL Server Reporting services to query off of the database snapshot.
Thank you,
Racer-D
I have two questions here.
Question 1:
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?
Principal Database -- Mirror -- Mirror A
-- Mirror -- Mirror B
Please advise. Thanks
I am using MS SQL 2000 Standard, is it possible to create a mirror database in SQL 2000? If so, would you suggest how to do this. Thanks! I am a newbie about this.
View 4 Replies View Relatedanyone has the experience to solve this?
View 8 Replies View RelatedHas anyone tried mirroring any of the Dynamics GP databases yet?
I am looking at using this and what to see if anyone has had any problems or even know if it can be done.
Thanks
Hi
While a database upgrade schema changes were being made the Mirror became disconnected will this recover itself when it reconnects or will it be the case that we will have to copy the db files to the Mirror and set it up from scratch.
It was set up as a synchronise mirror
Hi
I tried to setup service broker to fail over to a mirror database. After the target database failed over, the message I send stays in sys.transmission_queue with no error in transmission_status. I check the mirror server log and I see the following.
An exception occurred while enqueueing a message in the target queue. Error: 15581, State: 3. Please create a master key in the database or open the master key in the session before performing this operation.
The database has the master key because its a mirrored database? Did I have to do something extra here
Paul
Hi, all.
here is the situation.
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.
any ideas?
thanks,
I need to migrate my SQL server Mirror Database to a new server. my current setup is as below
1. server A principal (192.168.1.100)
2. server B Mirror   (192.168.1.200)
Now i have a new server (Server C) to replace server B as below
1. server A principal (192.168.1.100)
2. server C Mirror   (192.168.1.300)
My question is how to migrate mirror db to new server without any affect or downtime on principal server.
note: SQL SERVER 2008R2 EE(64BIT), Win2008R2 EE 64bit.
Hi,
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?
Regards,
Gopi
Hello,
I've been running SQL 2005 Enterprise SP1 for some time. After installing SP2, I miss the "Database Mirror Monitor"-Feature. Where is it? Did MS kill the feature?
regards
alejandroo
We have an AG scenario where we are using WFC on a 2 node cluster. We are then using AG for mirroring the databases to both nodes and have a listener.
What I want to do next is to establish another copy of the database at a remote location. But I don't want to add the 3rd system to the WFC. I am not a big fan of WFC and I have seen it causes many more problems. The 3rd system will be in a remote location and the network not 100% reliable. I have seen in the past that it causes the entire cluster to hang and causing my production to crash which I don't want.
I there a way to add a 3rd node to the mirror configuration. I don't know if I can add a 3rd node to the AG unless it is part of the same cluster.
I know I can configure log shipping, I am fine with it but in the source, I have no control of which node the DB will be. I am not sure if a log shipping scenario can be configured using the listener instead of the physical host.
We have a database with AlwaysOn enabled, where the database is getting mirrored using AlwaysOn to a secondary node in a two node cluster. Now we want to set up a DR site for that database in a different data center.
So trying to analyse what technologies. We can use for setting up the DR for that database. Can we use mirroring / replication or what's the best way of setting up DR on 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......
All,
We have SQL 2005 db mirror configured with a witness server for high availability. Node 1 is the principal and Node 2 is the mirror. A nightly job creates a snapshot on Node 2. The snapshot is used for previous day reporting queries. We have now been asked to present another copy of the database for near-time reporting. I thought about possibly adding a peer-to-peer replication as part of my environment but was hoping to see what everyone else out there is doing.
Regards,
Ian
Hello All,
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
All runs normally,But how can't be auto failover?
On the machine DBSP01 we have the TEST database running. In the SQL environment we created the user Test. This user was set up to be the database owner of the TEST database. To make a connection to the Test database, the user Test was used and everything works perfect.
On the machine DBSP02 we also created a database called TEST and a user called Test, who was database owner of the TEST database. Again we where able to connect to the database by using the user Test.
Now we set up a so called mirror configuration. This means that we have one active database, called the principal database, and a standby database, called the mirror. The active database is used to communicate to the outside, while the standby database is copying every change from the active database. This way the principal database and the mirror database contain always the same data. When one of the databases is acting as a mirror database, this database cannot be reached from the outside.
After a while it can happen that the principal database fails for some reason. The database is not capable to run properly, so it stops. The mirror database will become the principal database and the outside can connect to the new principal database. Here we have a problem: the user Test is no longer the database owner of the TEST database. For some reason the user Test is disconnected as database owner of the TEST database when the database is acting as mirror. I can€™t find the reason for this problem and I can also not find a solution for this problem.
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.
Best Regards,
Hans
Hi,
Mirroring has been working fine on 2 identical servers. We even tested failover and that worked....but today something happened (still investigating) and suddenly the Principal is disconnected and we cannot force failover with:
ALTER DATABASE [LeoCTI] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
Error: The command failed because the database mirror is busy! Try again later!
How can I bring the principal back online? It won't let me do anything, won't let me turn off mirroring etc. etc.
HELP PLEASE!
Thanks
Hyper aka Richard
I have a database which uses "Database Mirroring", and I need to write stored procedure and pull data from "Principal Server".
My Current Logic:
CREATE PROCEDURE abc123
as
BEGIN
IF Server01 = 'ONLINE'
BEGIN
[Code] .....
The problem I am facing is: Stored procedure is not created because "One of the server is not Online"...
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.
we config our SAP system to use SQL Server 2005 database mirror. but the mirror server hang by accident, after restart mirror server,the server return to normal,but the mirror can't be resume.
ALTER DATABASE R3P
SET PARTNER resume
the error is:
Msg 1447, Level 16, State 21, Line 1
ALTER DATABASE "R3P" command cannot be executed until both partner server instances are up, running, and connected. Start the partner and reissue the command.
To configure database mirroring
After connecting to the principal server instance, in Object Explorer, click the server name to expand the server tree.
Expand Databases, and select the database to be mirrored.
Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
To begin configuring mirroring, click the Configure Security button to launch the Configure Database Mirroring Security Wizard.
but i do not see any "Mirror" Option in the "Tasks" menu.
Why ?
I want my database to be mirrored . is it possible to mirror it on the same machine.
I have tried to create a named instance but while trying to configure its not visible.?
Am using SQL SERVER 2008
Hi!
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 :
PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;
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 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.
View 3 Replies View RelatedWhen 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?
View 10 Replies View RelatedHi guys, can I know the steps on creating a database snapshot on a mirror database? Thx for the assistance. :)
Best Regards,
Hans
I wonder how do I shrink log file in a specific database which in Mirror/Synchronized/Restoring state..So that database is in Mirror server ( High availability ).
View 4 Replies View RelatedProcess to create snapshot failed (twice per day snapshot of the database is being created for some people to work on). Morning snapshot worked fine but the afternoon one failed. The snapshot is being created from Mirror database. I used this code:
CREATE DATABASE [DB_snapshot] ON
(name = N'DB',filename = N'D:SnapshotDB.SQLSnapshot' )
,(name = N'indexes',filename = N'D:SnapshotDB_indexes.SQLSnapshot' )
AS SNAPSHOT OF DB
And the error message i get
Msg 1823, Level 16, State 6, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 7, Line 1
A database snapshot cannot be created because it failed to start.
Msg 3456, Level 21, State 1, Line 1
Could not redo log record (202011:19306:2), for transaction ID (0:0), on page (1:1823948), allocation unit 281474979397632, database 'DB_snapshot' (database ID 6). Page: LSN = (201954:220201:1), allocation unit = 281474979397632, type = 1. Log: OpCode = 4, context 18, PrevPageLSN: (202010:23679:1). Restore from a backup of the database, or repair the database.
Msg 3313, Level 21, State 1, Line 1
During redoing of a logged operation in database 'DB_snapshot', an error occurred at log record ID (202011:19306:2). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
What might be causing this error? Mirroring seems to be working OK. status is "Synchronized"