TexCopy Sql Server 2005

Principal Server: SQL8
Mirrored Server: SQL 4
Pre-Requisites
- Ensure that SQL Service /SQL Agent Services on all the machines start with the same domain account, otherwise mirroring wont work.
Step 1 : Install database on Mirrored Server
In order to get the database onto the mirrored server, we do a full backup of the ‘YourDatabase’ database on the Principal server, followed by a backup of the Transaction Log.

• Perform a full backup of the database on the Principal server.

BACKUP DATABASE YourDatabase TO DISK = 'C:\YourDatabase_Full.Bak'

• Perform a Transaction Log backup on the Principal server.
  
  
  
  

  
  

  BACKUP DATABASE YourDatabase TO DISK = 'C:\YourDatabase_Full.Bak'

  
  

  
  


• Copy the backup files to the Mirror.


• Do a restore of the full backup With NORECOVERY (if you use the visual tool, go to Options, then ensure you check the No Recovery option! This is vital! )

RESTORE DATABASE YourDatabase


FROM DISK = 'C:\YourDatabase_Full.Bak' WITH NORECOVERY,


MOVE 'YourDatabase_Data' TO 'D:\Data\YourDatabase.mdf',


MOVE 'YourDatabase_Log' TO 'C:\Data\YourDatabase.ldf'

• Perform another restore of the Transaction Log, also with the NORECOVERY option. (This is important; otherwise you'll get an error when starting the mirror).
  
  
  
  

  
  

  RESTORE LOG YourDatabase FROM DISK ='C:\YourDatabase_Full.Bak' WITH NORECOVERY

  
  

  
  

You'll notice that the database on the Mirror server now is marked as "Restoring..." and can't be accessed. This is normal and expected! The Mirror is always in a permanent Restoring state to prevent users accessing the database, but will be receiving synchronization data. If the database fails over to the Mirror, then it will become an active database and the old Principal will go into the Recovering state.


Step 2: Setting up SQL Server Service impersonation
Make sure that the sql/ sql Agent services on both the principal and the mirrored servers are running under the same local user account and not Local system account.

• Create a local user on both the Principal and the Mirror server with the same username and password. For example, "sqluser".
• Edit the SQL Server Service and change the Logon to this user.
• Do the same for the SQL Server Agent service.
• Change the SQL Server Agent service to be Automatic.
• Re-start the SQL Server Service
• Do this on both the Principal and the Mirror

***Create a SQL Login on both SQL Servers for this user you created.
Step 3: Setting up the Mirror
Now it is time to setup Mirroring.
· Right click on YourDatabase database and select properties
· Click "Configure Security"
· Click Next on the wizard
· Choose whether you want a Witness server or not, choose “no” and click Next
· In the Principal Server Instance stage, leave everything as its default (you can't change anything anyway)








In the Mirror Server Instance stage, choose your Mirror server from the dropdown and click Connect to provide the credentials. Click Next.

• In the next dialog about Service Accounts, leave these blank (you only need to fill them in if the servers are in a domain or in trusted domains)

• Click Next and Finish
• Click "Do not start mirroring"

• Enter in the FQDN of the servers if you want, but this is not necessary (as long as it will resolve)
• Change the operating mode to “High performance (asynchronous)”, otherwise the principal database will become slower.
• Click Start Mirroring (if you do not have a FQDN entered, then a warning will appear, but you can ignore it)
• The mirror should then start, and within moments, the Status should be "synchronized: the databases are fully synchronized"

Doing a forced failover
--Run on mirror if principal isn't available
Safety Full Without Witness

This scenario provides high safety, but automatic failover is not allowed. In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available. You must break the mirroring session and then recover the mirror database.

For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

ALTER DATABASE SET PARTNER OFF

RESTORE DATABASE WITH RECOVERY

Safety Off

In the event of failure of the principal, the database service becomes unavailable. You can perform a force service to make the database service available on the mirror. However, since the safety level is OFF, it is possible that there were transactions that didn’t make it to the mirror at the time of the failure of the principal. These transactions will be lost. Therefore, manual failover with safety OFF involves acknowledging the possibility of data loss.

For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Once the database on Server_A becomes operational, it automatically assumes the role of the mirror. However, the mirroring session remains SUSPENDED, and you will need to manually RESUME the mirroring session.

 

USE MASTER

GO

ALTER DATABASE YourDatabase SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

GO

Within 10 seconds the Mirrored database will come online, if you need to change the default failover time, run this on the principal server

ALTER DATABASE YourDatabase SET PARTNER TIMEOUT <Value in Secs, should be greater than 5>

that’s it, do some testing :)