How to Perform SQL Server 2012 Logshipping DR Test

Jephe Wu - http://linuxtechres.blogspot.com

Objective: showing step by step instruction for doing non-disruptive DR test and real world DR on existing logshipping SQL server 2012 production environment

Environment: SQL server 2012 prod and DR servers replicating through logshipping, both are running on Windows 2008 R2 SP1 64bit OS, production database name is LINUXTECHRES_LIVE, application server use LINUXTECHRES to connect to database.



High Level Concept 

1.  disable restore job and alert job on DR SQL server
2.  run command 'RESTORE DATABASE LINUXTECHRES_LIVE WITH RECOVERY' on DR SQL server new query window to bring database to online mode (refresh it to change status to remove 'restoring' after run command)
3.  find out which username is being used by application server to connect to production database
4. create same username with same sid on DR sql server 
5. try to login with application username and password on DR sql server with sql authentication.
6. rebuild DR database to enable logshipping again after done DR test

Steps

1. Preparation work before the actual day of DR test

After management decided to do non-disruptive DR Test, we should check the logshipping status to make sure it's working fine so that DR will have up-to-date data after we break logshipping for DR test

Also need to make sure daily full backup on production side are working correctly so that we will have working full database backup for restoring on DR side after finishing this DR test.

2. check again the version of SQL server on both prod and DR side - optional

run sql statement select @@version to decide which version you are running for SQL server.

refer to How to determine the version and edition of SQL Server and its components at http://support.microsoft.com/kb/321185 , in our case, it's SQL Server 2012 11.00.2100.60.  Also, please refer to http://sqlserverbuilds.blogspot.com.au/ for more detail versions for all SQL server versions.

3. Create application server username with password on DR SQL server so that DR application server is able to connect to DR database
sql server login on prod sql server has unique sid associated with it,  you have to create username with same sid on DR side to enable the database user to be able to connect to replicated database on DR instance.  each sid is associated with an SQL server instance which is unique.

run command below on prod sql server to find out sid of application user.

SELECT name, [sid] 
FROM sys.server_principals
WHERE [type] = 's'; 

e.g. app user LINUXTECHRES sid is 0x5B56330D7270CE4FB396226DA772ABCD

on prod sql server, expand prod database which is being logshipping to DR site, check database users to find out which user is used for application server to connect.

run command below on DR sql server query window to create application username:

CREATE LOGIN LINUXTECHRES WITH PASSWORD = 'strong password in plaintext', sid = 0x5B56330D7270CE4FB396226DA772ABCD, DEFAULT_DATABASE=[LINUXTECHRES_LIVE], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;
GO

4. try to login to DR sql server as app user linuxtechres with sql authentication

You should encounter error message like 'cannot open user default database, login failed', because the DR database is still in restoring state which cannot be accessed. Once we bring it online, you should be able to login to it.

5. Make sure the last backup transaction logfile on production server has already been copied and restored on DR site  - optional

run this on prod to check last_backup file name - 
select last_backup_file from msdb.dbo.log_shipping_monitor_primary

run this on DR to check last_copied_file and last_restored_file name - 
select last_copied_file, last_restored_file from msdb.dbo.log_shipping_monitor_secondary

If the latest backup file has not yet been copied or restored on DR site, manually run file copy and restore job on DR server to bring all 3 filenames are same


6.  break logshipping and bring DR database online

Disable restore and alert job on DR server [optional, to avoid job failure after bring DR database online ]

Run command below to bring DR database online 
RESTORE DATABASE LINUXTECHRES_LIVE WITH RECOVERY

do not run 'backup log with norecovery' on primary prod db, which will be doing tail log backup, as it will change prod database status to 'restoring',  We are doing non-disruptive DR test only, everything is still running independently on prod side.

7. You should be able to connect to database now
8. rebuild logshipping after DR test.

copy the latest full backup of database from prod to DR server, then run command below in SSMS query window.

RESTORE DATABASE LINUXTECHRES_LIVE FROM DISK = 'D:/backup/prod_full_backup_linuxtechres.bak' WITH NORECOVERY, REPLACE;

or you can use SSMS, right clicking database LINUXTECHRES_LIVE,  choose 'task', 'restore database' in SQL server 2012, if the logshipping DR has been running long time, the screen might need long time to appear. Then, source for restore, from device, add a file which is copied from production daily full backup, tick it to choose it. then it will do restore headonly to give brief detail, if it says timeout, logout SSMS , login and try again.

Go to options, choose 'overwrite the existing database', for 'Recovery state', choose the second options which is 'Leave the database non-operational, and do not roll back uncommitted transactions, Additional transaction logs can be restored (RESTORE WITH NORECOVERY), then click 'OK'.

After finishing it, the restore job will start it automatically and database will automatically try to find the transaction log file to recovery, you can right click 'restore job' , choose history to see the progress.

8. If Real DR is happening

Before above step 6, you should make sure you have restored the latest backup file on prod site, so manually run copy/restore job on DR side to bring all these 3 filename are same.

Then you should also do tail log backup on prod side if possible to reduce data lost. run this:

USE master 
GO 
BACKUP LOG LINUXTECRES_LIVE
TO DISK = 'C:\backups\prod_tail_log.bak' 
WITH NORECOVERY

then copy above prod_tail_log.bak to DR sql server then restore it manually like this:

RESTORE LOG LINUXTECHRES_LIVE
FROM DISK = 'path_to_prod_tail_log.bak'
WITH RECOVERY

References

1. http://www.sqlskills.com/blogs/glenn/how-to-avoid-orphaned-database-users-with-sql-server-authentication/

How to deal with SQL server disk space

Jephe Wu - http://linuxtechres.blogspot.com

Objective: understanding different scenario for disk space issues

scenario 1: How to reduce transaction log file size

Problem: transaction log file is huge (more than 100GB), due to weekly maintenance job running on production database

Environment:  MS SQL server 2005, recovery mode is 'full', transaction log backup is not enabled, logshipping is not enabled.

Solution: change database recovery mode from full to simple, then shrink transaction log to free up unused space, you cannot free up space if the unused space is 0%.

scenario 2: How to move database files to free up space

Problem: D drive has not enough space, you need to move one database to F drive on the same server instance.

Envrionment: MS SQL server 2005. recovery mode is 'simple'. not transaction log backup, no logshipping.

Solution: 

For moving database files within same instance server

a. alter database modify  (apply to move database files within same instance server)

For example:

USE master;
GO
-- Return the logical file name.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks2012')
    AND type_desc = N'LOG';
GO
ALTER DATABASE AdventureWorks2012 SET OFFLINE;
GO
-- Physically move the file to a new location.
-- In the following statement, modify the path specified in FILENAME to
-- the new location of the file on your server.
ALTER DATABASE AdventureWorks2012 
    MODIFY FILE ( NAME = AdventureWorks2012_Log, 
                  FILENAME = 'C:\NewLoc\AdventureWorks2008R2_Log.ldf');
GO
ALTER DATABASE AdventureWorks2012 SET ONLINE;
GO
--Verify the new location.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks2012')
    AND type_desc = N'LOG';

For moving database files within same instance or different instance/servers:

a. use database detach and reattach, this actually works for same server instance and also different server or different instance.

b. backup and restore database with move option to relocate files

References:

http://msdn.microsoft.com/en-us/library/ms190794

http://msdn.microsoft.com/en-us/library/ms345483.aspx

How to use standby sharepoint farm to access logshipped secondary database

Jephe Wu - http://linuxtechres.blogspot.com

Environment:  sharepoint 2010 farm(2 nodes plus a SQL 2008 database) at primary site, a logshipped database with sharepoint farm(1 node) at DR site.
Objective: If primary site database is down, how to use DR site sharepoint webfarm (or primary site sharepoint ) to connect to logshipped content database.


Steps:

1.  configure logshipping to prepare for DR purpose
http://technet.microsoft.com/en-us/library/dd890507%28office.12%29.aspx#Fail_over

2. use standby mode when configuring logshiping rather than norecovery mode, also choose 'disconnect user when logshipping'

3. only wss content database need to be logshipped according to SharePoing 2010 Disaster Recovery Guid.

4. when DR is happening, stop sql restoration job, backup logshiping database from primary database.

5. rename secondary database  for wss content database, so standby sharepoint farm will use logshipped database for contenet database, please refer to this article
http://www.raregrooverider.com/post/2008/02/04/Renaming-a-SharePoint-Central-Administration-Database-with-a-GUID-Appended-to-the-Database-Name.aspx

1. Change the directory to the 12-hive bin where you can run the STSADM commands from:
   C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\
2. Delete the original content database with the following STSADM command using the database with the GUID you looked up previously (be sure to change UrlOfYourCentralAdministration and NamedInstanceOfYourSqlServer to your names):
   stsadm -o deletecontentdb -url http://UrlOfYourCentralAdministration -databasename SharePoint_AdminContent_<GUID> -databaseserver NamedInstanceOfYourSqlServer
3. Re-Associate the backed up database with your Central Administration with the following STSADM command (be sure to change UrlOfCentralYourAdministration and NamedInstanceOfYourSqlServer to your names):
   stsadm -o addcontentdb -url http:// UrlOfYourCentralAdministration -databasename SharePoint_AdminContent -databaseserver NamedInstanceOfYourSqlServer

 5. References:
 Sharepoint 2010 disaster recovery guide pdf book (this book talks about only logship wss content database)

How to setup MS SQL server logshipping and failover

Jephe Wu - http://linuxtechres.blogspot.com

Objective: setup a logshipping between primary and secondary. and failover it.
Environment: sql server 2005/2008.


Steps:


Setup requirments:
1. Installing both primary and standby SQL servers, configure primary database first
2. setup shared folder on primary or another network share(third server)

You must have a shared folder to copy the transaction log backups to.
The SQL Server Agent service account of the primary server must have read/write access either to the shared folder or to the local NTFS folder. The SQL Server Agent account of the standby server must have read access to the shared folder for applying transaction log
3. primary server must be in full or bulk-loged mode for generating transaction logs

You can configure log shipping with SQL Server Agent services stopped, but the process does not run until the agent is started.

Note: refer to step-by-step configuring logshipping at
http://www.mssqltips.com/tipprint.asp?tip=2301

How to failover - http://msdn.microsoft.com/en-us/library/ms191233.aspx
1. copy any uncopied backup files from backup share to file copy folder on each secondary server

You might want to generate tail log backup from primary if primary server is still online and running.

a. if primary db is online and you plan to perform transaction log apply for secondary server, before starting that, consider to  backup tail of the primary server transaction log with command below, so that you will have the latest data from primary db:

BACKUP LOG <dbname> TO <backup_device> WITH NORECOVERY;
go

note: This leaves the primary database in the restoring state, and eventually you will be able to roll this database forward by applying transaction log backups from the original secondary server.

b. if db is offline and cannot start.
As no transaction can occur this time, using 'with norecovery' is optional, if db is damaged, use 'with continue_after_error'

as follows:

backup log <dbname> to <backup_device> with continue_after_error

Use CONTINUE_AFTER_ERROR only if you are backing up the tail log for a damaged database.

c. if db is damaged and inaccessible, however, transaction log is undamaged and accessbible:

backup log <dbname> to <backup_device> with no_truncate;
go

note:
a.no_truncate=copy_only+continue_after_error
b. How to: Back Up the Tail of the Transaction Log (SQL Server Management Studio) - http://msdn.microsoft.com/en-us/library/dd297499
How to: Back Up the Transaction Log When the Database Is Damaged (Transact-SQL) - http://msdn.microsoft.com/en-us/library/ms189606
c. For details about tail-log backup, see http://msdn.microsoft.com/en-us/library/ms179314

2. apply any unapplied transaction log backups in sequence to each secondary database
How to: Recover a Database from a Backup Without Restoring Data (Transact-SQL) - http://msdn.microsoft.com/en-us/library/ms176039.aspx
How to: Restore a Transaction Log Backup (SQL Server Management Studio) - http://msdn.microsoft.com/en-us/library/ms177446.aspx

If you are  restoring the last log backup, you can do the following:
restore log <dbname> from <backup_device> with recovery;  bring database online from Read-only mode.
go

otherwise, do
restore log <dbname> from <backup_device> with norecovery;
restore database <dbname> with recovery; bring database online from Read-only mode.
go

note:
a.always explicitly specify either with norecovery or with recovery.
b. <backup_device> is the name of the device that contains the log backup being restored.

How to recover a database from a backup without restoring data - http://msdn.microsoft.com/en-us/library/ms176039.aspx
restore database <dbname> with recovery

3. after you have recovered secondary db, you can reconfigure it to act as a primary database for other secondary db, and you can redirect client to this server instance.

Before this, you might need to transfer login ids from primary to seconday server:
How to set up and perform a log shipping role change (Transact-SQL) - http://msdn.microsoft.com/en-us/library/aa215392(v=sql.80).aspx



PS: how to restore ms sql server from the previous full backup plus transaction logs, other than the most recent backup:

-----------
# make if offline then online first before restore
use master
ALTER DATABASE test SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE test SET ONLINE  WITH ROLLBACK IMMEDIATE

# restore full backup and all transaction logs, after x.bak, actually, we have done another full bakcup x2.bak, but we lost that, let's skip it.

restore database test from disk='i:\download\x.bak' with norecovery
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162700_8205296.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162704_9113476.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162737_0047650.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162801_1445920.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162830_7010021.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162900_8320271.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162950_0283644.trn' WITH NORECOVERY

RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_163001_1705924.trn' WITH RECOVERY
---------------------

References and Commands:
a. alter database <dbname> set recovery full
b. how to restore database with norecovery
restore database <dbname> from <device_name> with norecovery;
go
c. how to apply transaction log backup (T-SQL) - http://msdn.microsoft.com/en-us/library/ms187607.aspx
d. High Availability Solutions Overview -  http://msdn.microsoft.com/en-us/library/ms190202%28v=SQL.105%29.aspx 
e.  SQL 2005 logshipping tutorial video - http://www.dbsnaps.com/category/sql-server/high-availability-sql-server/

How to make SQL server 2005 logshipping secondary database to re-sync with primary database again

Jephe Wu - http://linuxtechres.blogspot.com

Objective: to make secondary db server to sync with primary again, logshipping is not working due to some required log files were deleted by some job.
Environment: Windows 2003 32bit, MS SQL server 2005 (management studio)

Problem: logshipping is not working according to Windows event log and sql job history, due to some required files were deleted, so the restoring is not working.



Steps:
1.  disable restoration job in job agent from SQL management studio
SQL Server Agent -> Jobs - LSRestore_DBxxxx\DBRSQLP4_xxxxx

 also, disable any other sql server agent job which might affect restoration process.
2.   restore the full database backup from primary

a. right clicking database DR_DB1 on secondary server.
b. choose 'task', 'restore database', General, source for restore, from device, add a file which is copied from production daily full backup, tick it to choose it.
c. go to options, choose 'overwrite the existing database', do not care about 'Restore the database file as' part even the 'Restore As' name is not same as what the DR server is having now for that database.
Because we have already choosen 'Overwrite the existing database', so don't have to care, it will overwrite the existing database name in DR server.

For 'Recovery state', choose the second options which is 'Leave the database non-operational, and do not roll back uncommitted transactions, Additional transaction logs can be restored (RESTORE WITH NORECOVERY), then click 'OK'.

After finishing it, the restore job will start it automatically and database will automatically try to find the transaction log file to recovery . 

5. right click on the restoratin job to check history, It will check all transaction log files in the specified directory, skip those which already contained in the restored database.  click refresh and get more information.
6. check reports for transaction log.

References:
1. Index rebuild generate huge log file makes logshipping and mirroring out of sync - http://connect.microsoft.com/SQLServer/feedback/details/352338/index-rebuild-generate-huge-log-file-makes-logshipping-and-mirroring-out-of-sync

2. How to: Remove Log Shipping (SQL Server Management Studio)
This topic explains how to remove log shipping using Microsoft SQL Server Management Studio.
To remove log shipping

    Right-click the database you want to use as your primary database in the log shipping configuration, and then click Properties.

    Under Select a page, click Transaction Log Shipping.

    Clear the Enable this as a primary database in a log shipping configuration check box.

    Click OK to remove log shipping from this primary database.

=============
Part II - after DR test, reconfiguring logshipping 

You don't have to delete database on secondary, just re-configure everything from primary; if you don't have enough space, you can delete it.

Steps:

1. right click database on primary, transaction log shipping , click 'enable this as a primary atabase in a log shipping configuration
2. click backup settings.type in network path of the folder on primary where we will generate transaction log for sharing with secondary for read
\\pridb\logshipping
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup\logshipping

note:
a. grant read/write permission to the sql server agent service account of primary server instance so that the backup job on primary can delete old transaction log files successfully.
b. read only permission to sql server agent service account for the secondary server instance in the domain
c. grant sysadmin privilege to primary db sql server agent service account on secondary db, so that the logshipping status 'last backup created' can be updated on primary db , also on secondary db server log shipping report page, Backup - Time Since Last will also be updated. Otherwise, it will only show the initial primary db backup date during logshipping configuration wizard.

in Schedule, to change default interval from 15 minutes to whatever you need. (e.g. 2 mins)

3. click 'add' to add secondary server instance, click connect to choose secondary server name and windows authentication.
click to choose a existing secondary database or enter a name to create new database. e.g. 'DR_PRODUCTION'

in 'Initialize Secondary Database' tab, choose one of three options, if you have recent full backup, choose the second option, then type in a

network path to the backup file that is accessible by secondary instance, e.g. \\primdb\backup\PRODUCTION_backup_201205150110.bak
or
after copying file to secondary, use \\secdb\d$\PRODUCTION_backup_201205150110.bak
Note: you cannot directly use d:\PRODUCTION_backup_201205150110.bak since it's asking network path, otherwise, you are not able to add it once clicking 'add'.

click 'restore options', type in data and log files path on secondary server: e.g.
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PRODUCTION
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PRODUCTION

in 'copy files' tab, type in  destination folder on secondary where transaction log should be copied to
e.g. D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\logshipping

click on 'Schedule', change default 15 minutes interval to whatever you need (e.g. 2mins)

click on 'Restore Transaction Log' tab, choose 'no recovery mode' or 'standby mode' (readonly while applying transaction log).

choose 45 minutes for alert if no restore occurs within.

Note: you can also restore the full backup from secondary database first by right clicking 'database', choose 'restore', in 'options', choose the second option to leave database in non-operational state to be able to apply for transaction log and do not undo the uncommited transaction.

4. in Monitor server instance, choose secondary server.

5. user zabbix/dbforbix to monitor logshipping progress
refer to http://sqlmonitormetrics.red-gate.com/time-since-last-restore/

Measure the time since the last restore, in minutes.
Enter the T-SQL query that will collect data:

SELECT DATEDIFF(MINUTE, restore_date, GETDATE())
  FROM (SELECT TOP 1 restore_date
          FROM msdb.dbo.restorehistory
          WHERE destination_database_name = DB_NAME()
          ORDER BY restore_date DESC

       ) rd;