How to add user and grant access to secondary database server on log shipping?

How to add user and grant access to secondary database server on log shipping?

If database is configured in logshipping the secondary server is in STANBY recovery mode. Because of this secondary server database was in read-only mode where you cannot add user to secondary database.

Workaround

1. Create Login in the primary server
USE [master]
GO
CREATE LOGIN [Kalyan_RO] WITH PASSWORD=N'kalyan_ro123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [KalyanDB]
GO
CREATE USER [Kalyan_RO] FOR LOGIN [Kalyan_RO]
GO
USE [KalyanDB]
GO
EXEC sp_addrolemember N'db_datareader', N'Kalyan_RO'
GO
With the above step you created SQL login in Primary server and provided read access on KalyanDB.

2. Create the login with same name in Secondary Server  (Here you need to remember one thing, even though you a login is created with same name in secondary server you wont get same SID, becoz SIDs will be different in primary and secondary servers)

For that reason you need to get the SID from primary server and create the same login in secondary server with same SID.

Before creating login in secondary server, use the below query to get the SID of the login
select name, sid from master..syslogins where name='Kalyan_RO'
Now use the below syntax to create the login in secondary server with same SID

CREATE LOGIN [Kalyan_RO] WITH PASSWORD= N'kalyan_ro123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON, SID=0x8389C417AA312E499C03DC4AE914899E

Using the above script login will be created and user rights will be mapped to it after log backup was restored on secondary server. User level mappings will come thru log backup as that is database level.

Tail Log Backups in SQL Server

What is Tail-Log Backup?

A tail-log backup captures the log records that have not been backed up since the recent last log backup to current point in time before restore. Generally we generate a tail log to make database into NoRecovery mode (Inaccessible). The tail log backup can be generated from FULL / BULK-LOGGED Recovery model databases.

Unlike other log backups, this tail log backup can be generated even if database is damaged. The tail log backup is also generated using backup log command only with two options a) NoRecovery and b) continue_after_error

NoRecovery – We need to use this option when database is online, After issuing this command it ensures Database is not changed after tail-log backup, it makes database in restoring state, if database is in restoring state, users requests will not processed.

Backup log database_name to disk =’path of tlog file’ with norecovery

No_Truncate / Continue_after_error – Using one of this options we can generate a tail-log backup on a damaged database.

a) Create a database and table in it
b) Insert few records into the table
c) Offline the database and rename data file
d) Try to bring the database online, it wont allow because, it wont find the data file

At that time we can use the below command to generate tail log file from a damaged database.

Backup log damaged_dbname to disk=’path of tlog file’ with no_truncate

Using No_Truncate option, the current active transaction log can be backedup when the data files are inaccessible or damaged or missing and log file is undamaged.

Backup log damaged_dbname to disk=’path of tlog file’ with continue_after_error

Logshipping Tables

The below are few tables provides the essential information about the logshipping databases.
For more information Browse BOL for "Log Shipping Tables and Stored Procedures"
All these tables are stored in MSDB database.

log_shipping_primary_databases -- Displays configuration information for primary databases on the server.

log_shipping_primary_secondaries -- Displays mapping information between primary databases to secondary databases.

log_shipping_secondary -- Displays secondary server database details along with Primary server database information.

log_shipping_secondary_databases -- Displays configuration information for secondary databases on the server.(Run command from Logshipping Secondary Server)

Using the above logshipping tables we can get the last restored log file, last backup file, last copied file, backup source directory, backup destination directory etc.,

Adding File To Logshipping Database -- Error: Could not apply log backup file

Sometimes due to disk space issue or with some other issue, we may need to add additional file to logshipping primary database, in that case the logshipping will fail from that point onwards. We will receive the below error message.

* Error: Could not apply log backup file 'C:\Temp\PrimaryDBLogs\PrimaryDB_20120419115948.trn' to secondary database 'SecondaryDB'.(Microsoft.SqlServer.Management.LogShipping) ***


Error: The file 'C:\Appl\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Secondary_File.ndf' cannot be overwritten. It is being used by database 'PrimaryDB'.

File 'Secondary_File' cannot be restored to 'C:\Appl\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Secondary_File.ndf'. Use WITH MOVE to identify a valid location for the file.
 
Steps
1)  I created two databases called PrimaryDB and Secondary DB
2)  Configured Logshipping between primarydb and secondarydb (Secondary in Readonly Mode)
3)  Ensure Logshipping is configured and running smooth between the servers
4)  Stop the logshipping and disable logshipping jobs
5)  Add data file (ndf) to primaryDB
6)  Run the LSBackup manually
7)  Run the copy job manually (ensure the file copied to target server)
8)  Run the below command to verify whether this trn file consists additional data file or not
         Restore filelistonly from disk='C:\temp\PrimaryDBLogs\PrimaryDB_20120419115948.trn'
9)  Run the below restore command to add the data file to secondaryDb on target server
Use Master

RESTORE log [SecondaryDB] FROM Disk='C:\temp\PrimaryDBLogs\PrimaryDB_20120419115948.trn'
WITH MOVE 'Secondary_File'
TO 'C:\Appl\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Secondary_File2.ndf',
STANDBY = N'C:\Appl\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_SecondaryDB.BAK'
10) Verify file added or not using the below command
select * from secondaryDb..sysfiles
11) Enable the backup, copy and restore jobs.

Error: 14420, 14421, Severity: 16, State: 1 LogShipping

The log shipping primary database %s.%s has backup threshold of %d minutes and has not performed a backup log operation for %d minutes. Check agent log and log shipping monitor information.

WorkAround

1. The Message 14420 does not necessarly indicate a problm with logshipping. And this message mostly occur when monitor server is configured. This message generally occured when the differnce between t-log backup and the current time on the monitor server is the greater than the time is set for backup threshold.

2. Ensure the transaction log backup happend on the primary server. If the t-log backup fails then also above error will occur.

3. You may set incorrect value for the backup alert.

4. The date and time of monitor server is different from the date and time of primary server.

5. The logshipping copy job is run on the primary server and may not update the entry in the msdb database at monitor server in the log_shipping_primaries table.

14421 Error :
The log shipping secondary database %s.%s has restore threshold of %d minutes and is out of sync. No restore was performed for %d minutes. Restored latency is %d minutes. Check agent log and logshipping monitor information.

The message doesn't necessarly indicate a problm with logshipping.

1. This may occur restore job on the secondary server is failing.

2. You may set out of sync alert is wrong.