Log Shipping – NORECOVERY vs. STANDBY mode

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

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 :)