Mirroring: Fixing/Removing/Recreating/Pausing/Querying

Start/Stop Mirroring EndPoints:


ALTER ENDPOINT Mirroring STATE = STOPPED

ALTER ENDPOINT Mirroring STATE = STARTED


select * from sys.endpoints;
select * from sys.database_mirroring_endpoints;

SELECT
   d.name as 'td','',
   mirroring_state_desc as 'td','',
   mirroring_role_desc as 'td','',
   mirroring_partner_instance as 'td','',
   mirroring_role_sequence as 'td','',
   case (m.mirroring_witness_state_desc) when 'Unknown' then 'No Witness Server Configured'
      else m.mirroring_witness_state_desc end as 'td','' ,
   case(m.mirroring_witness_state_desc) when 'Unknown' then 'No Witness Server Configured'
      else m.mirroring_witness_name end  as  'td','',
   log_reuse_wait_desc  as 'td',''
FROM
  sys.database_mirroring M inner join SYS.DATABASES d
  on m.database_id = d.database_id
WHERE mirroring_state_desc is not null
ORDER BY d.name,mirroring_state_desc

How to resume:
ALTER DATABASE MirrorDB SET PARTNER RESUME;


select name,expiry_date,* from sys.certificates;

Break and Fix:


--From Principal
ALTER DATABASE DBName SET PARTNER OFF

--Set Recovery Mode for Principal 
ALTER DATABASE DBName SET RECOVERY FULL

--Backup the Principal Database + Transaction Log copy over to Mirror Server C:\Mirror
--Then restore Database to Mirror Server
RESTORE DATABASE DBName
FROM DISK = 'C:\Mirror\DBName.bak'
WITH MOVE 'Release_Data' TO 'F:\SQLData\DBName.mdf',
MOVE 'Release_log' TO 'G:\SQLLogs\DBName_log.ldf',
REPLACE,NORECOVERY;
GO

--Restore Log file to Mirror
RESTORE LOG DBName
FROM DISK = 'C:\Mirror\DBName.trn'
WITH NORECOVERY;
GO

--Set Partner on Mirror
Alter database DBName set partner = N'TCP://x.x.x.1:####'

--Set Partner on Master
Alter database DBName set partner = N'TCP://x.x.x.2:####'

 Via GUI:

To remove database mirroring

  1. During a database mirroring session, connect to the principal server instance, in Object Explorer, click the server name to expand the server tree.
  2. Expand Databases, and select the database.
  3. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
  4. In the Select a Page pane, click Mirroring.
  5. To remove mirroring, click Remove Mirroring. A prompt asks for confirmation. If you click Yes, the session is stopped and mirroring is removed from the database.

Find out which databases are in mirrored status:

select @@SERVERNAME as current_instance, DB_NAME(database_id) as database_name, mirroring_partner_name
, mirroring_partner_instance, mirroring_state_desc, mirroring_role_desc
, mirroring_safety_level_desc, mirroring_end_of_log_lsn, mirroring_replication_lsn
from sys.database_mirroring
where mirroring_partner_instance is not null;



FAIL OVER:

To manually fail over database mirroring

1.    Connect to the principal server instance and, in the Object Explorer pane, click the server name to expand the server tree.
2.    Expand Databases, and select the database to be failed over.
3.    Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
4.    Click Failover.
A confirmation box appears. The principal server begins by trying to connect to the mirror server by using Windows Authentication. If Windows Authentication does not work, the principal server displays the Connect to Server dialog box. If the mirror server uses SQL Server Authentication, select SQL Server Authentication in the Authentication box. In the Login text box, specify the login account to connect with on the mirror server, and in the Password text box, specify the password for that account.
If failover succeeds, the Database Properties dialog box closes. The mirror database becomes the principal database and the principal database becomes the mirror.
If failover fails, an error message is displayed and the dialog box remains open.

Via TSQL:

To manually fail over a database mirroring session

1.      Connect to the principal server.
2.      Set the database context to the master database:
USE master;
3.      Issue the following statement on the principal server:
ALTER DATABASE [database_name] SET PARTNER FAILOVER;
where database_name is the mirrored database.
This initiates an immediate transition of the mirror server to the principal role.


PAUSING and RESUMING:

To pause database mirroring

1.      From Principal Server:
2.      Connect to the Database Engine for either partner.
3.      From the Standard bar, click New Query.
4.      Issue the following Transact-SQL statement:
ALTER DATABASE [database_name] SET PARTNER SUSPEND
where database_name is the mirrored database whose session you want to you want to suspend

To resume database mirroring

From Principal Server:
1.      Connect to the Database Engine for either partner.
2.      From the Standard bar, click New Query.
3.      Issue the following Transact-SQL statement:
ALTER DATABASE [database_name] SET PARTNER RESUME
where database_name is the mirrored database whose session you want to resume


Comments

Post a Comment

Popular posts from this blog

Log is not getting shipped in SQL Server Logshipping

Image
Well Problem could be of different types. But common issues are few. Cause Inside the LSRestore job history, you can find out two kind of messages  :  – Restore job skipping the logs on secondary server  Skipped log backup file. Secondary DB: ‘logshippingSecondary’, File: ‘\\myDB\logshipping\logshippingPrimary_20090808173803.trn’ – Backup log older is missing *** Error 4305: The file ‘\\myDB\logshipping\logshippingPrimary_20090808174201.trn’ is too recent to apply to the secondary database ‘logshippingSecondary’. **** Error : The log in this backup set begins at LSN 18000000005000001, which is too recent to apply to the database. An earlier log backup that includes LSN 18000000004900001 can be restored. Transaction Log backups can only be restored if they are in a sequence. If the LastLSN field and the FirstLSN field do not display the same number on consecutive transaction log backups, they are not restorable in that sequence. There may be several reasons for tran
Read more

SQL Loader with dynamic control file generation

Hi All, Now what i am writing, I believe many of new bees will require it while using sqlldr for ETL or any other data loading issue. The thing is while loading using sqlldr there is no option to enter the source file name in that table. So what I have done I just create a dynamic control file which generates before loading of each source file. Here I have used two scripts 1) proc1: for filtering files and running a loop. Every source file will be processed in this loop. 2) another procedure proc2 which is called by proc1 to perform load operation proc1 ------- while (( $i < $filecounter )); do echo "file number $i" ### Take the first File FILENAME=`ls -l *.txt|head -1|awk -F' ' '{print $9}'` dos2unix $FILENAME ### if filename exists already continue the loop ### FINDSQLSCRIPT="select 'COUNT:'||count(*) from TBLBRACSUMMARY where file_name='$FILENAME';" OUTPUT_FILECOUNT=`/app/u01/oracle/prod
Read more

Oracle Job Operations

As a DBA, often I need to deal with Client's Jobs. Sometimes the jobs got hault, needs to break etc etc. Here are some useful commands for this sort of operations. ## Job Owner Schema can do this execute dbms_job.remove(444); execute dbms_job.broken(444, true); ## If Sys User needs to remove the job exec SYS.DBMS_IJOB.REMOVE(444); ## to find which jobs are running SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, 'mm/dd/yyyy hh:mi pm') startdatetime, b.what FROM dba_jobs_running a, dba_jobs b, v$session c WHERE a.job = b.job AND a.sid = c.sid order by a.this_date ##let say we have only sid and serial and we need to kill the process from both OS and database. So first we need to find out the SPID. this query for any RAC database select P.SPID ,S.SID ,S.SERIAL#, S.SCHEMANAME, S.PROGRAM, S.INST_ID, S.OSUSER, S.STATUS from gv$process p, gv$session s where P.ADDR=S.PADDR and S.SID=888 and s.serial#=9293; ## kill the job al
Read more