SQL Server: logshipping

I am in the process of putting my notes into web pages, please keep checking in later for more contents

top   prev   next  

Logging Shipping


Step By Step SQL Server Log Shipping  

What is SQL Server log shipping? 

Understanding and dealing with orphaned users in a SQL Server database

How to Configure Log Shipping for SQL Server 

security 

sql server 2016 



top   prev   next  

Full Recovery Mode


USE [master]
GO
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'mydb';
GO

USE [master]
GO
ALTER DATABASE [mydb] SET RECOVERY FULL WITH NO_WAIT
GO




top   prev   next  

Logshipping Transaction Log Gaps


exec sys.sp_check_log_shipping_monitor_alert 
sp_help_log_shipping_monitor
sqllogship.exe -backup -copy -restore

select primary_server, primary_database, last_backup_date, last_backup_file from msdb.dbo.log_shipping_monitor_primary;

select secondary_server, secondary_database, last_copied_date, last_copied_file from msdb.dbo.log_shipping_monitor_secondary;

select secondary_server, secondary_database, last_restored_date, last_restored_file from msdb.dbo.log_shipping_monitor_secondary;




top   prev   next  

Logshipping Interview Question

logshipping interview question I 

logshipping interview question II




top   prev   next  

Break Secondary

-- break secondary
EXEC Master.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'VirendraTest',
@secondary_server = N'VIRENDRA_PC',
@primary_database = N'LSVirendraTest';
GO



top   prev   next  

Related Views And Stored Procedures

SQL Server 2008 : Transaction log shipping 

mssqltips: Step By Step SQL Server Log Shipping 

logshipping 

interview question 

monitor log shipping job status 
1. Right click on Instance> report >transaction Log shipping status

sp_processlogshippingmonitorprimary
sp_processlogshippingmonitorsecondary

SP_help_log_shipping_monitor
sp_help_log_shipping_monitor_primary
sp_help_log_shipping_monitor_secondary

log_shipping_monitor_primary . Stores one monitor record per primary database in each log shipping configuration.
log_shipping_monitor_secondary . Stores one monitor record per secondary database in a log shipping configuration.
log_shipping_primary_databases . Stores one record for the primary database in a log shipping configuration.
log_shipping_secondary . Stores one record per secondary ID

SELECT * FROM msdb.dbo.log_shipping_primary_secondaries ps
INNER JOIN msdb.dbo.log_shipping_primary_databases pd
ON ps.primary_id = pd.primary_id
GO

select top 10 * from log_shipping_monitor_history_detail with (nolock);
select top 10 * from log_shipping_monitor_error_detail;

Exec SP_SpaceUsed log_shipping_monitor_history_detail

sp_cleanup_log_shipping_history

sp_cleanup_log_shipping_history @sql_agent_job_id , @agent_type
agent_type: 0 = backup, 1 = copy, 2 = restore

sp_add_log_shipping_primary_database 
sp_add_jobschedule to add a schedule

sp_add_log_shipping_alert_job
sp_add_log_shipping_secondary_primary
sp_add_jobschedule

sp_add_log_shipping_secondary_database 
sp_add_log_shipping_primary_secondary

-- -- break secondary
-- EXEC Master.dbo.sp_delete_log_shipping_primary_secondary
-- @primary_database = N'VirendraTest',
-- @secondary_server = N'VIRENDRA_PC',
-- @primary_database = N'LSVirendraTest';
-- GO



top   prev   next  

Step By Step In SQL Server Management Studio


Step By Step SQL Server Log Shipping 

Log Shipping Deployment 

How to: View the Log Shipping Report (SQL Server Management Studio) 
1.Connect to a monitor server, primary server, or secondary server.
2.Right-click the server instance in Object Explorer, 
  point to Reports, and point to Standard Reports.
3. Click Transaction Log Shipping Status. 

How to: Enable Log Shipping (SQL Server Management Studio) 

How to: Remove Log Shipping (SQL Server Management Studio) 
1. Right-click the database you want to use as your primary database in the log shipping configuration, 
   and then click Properties.
2. Under Select a page, click Transaction Log Shipping.
3. Clear the Enable this as a primary database in a log shipping configuration check box.
4. Click OK to remove log shipping from this primary database.

How to: Add a Secondary Database (SQL Server Management Studio) 

How to: Remove a Secondary Database (SQL Server Management Studio) 
To remove a secondary server

1. Right-click the database you want to use as your primary database in the log shipping configuration, 
   and then click Properties.
2. Under Select a page, click Transaction Log Shipping.
3. Under Secondary server instances and databases, click the database you want to remove.
4. Click Remove.
5. Click OK to update the configuration

Failing Over to a Log Shipping Secondary 
backup database WITH NORECOVERY
This leaves the database in the restoring state and therefore unavailable to users. 
Eventually you will be able to roll this database forward by applying transaction log backups 
from the replacement primary database

Changing Roles Between Primary and Secondary Servers 
-- Statement to execute on the new primary server
USE msdb
GO
EXEC master.dbo.sp_change_log_shipping_secondary_database @secondary_database = N'database_name', @threshold_alert_enabled = 0;
GO
-- Statement to execute on the new secondary server
USE msdb
GO
EXEC master.dbo.sp_change_log_shipping_primary_database @database=N'database_name', @threshold_alert_enabled = 0;
GO


-- ------------------------------------------------------------------------------------




top   prev   next  

Viewing And Fixing Orphaned Users

Understanding and dealing with orphaned users in a SQL Server database

1 

2 

3 

1):
-- alter login cannot change sid, but re-create login can specify sid
ALTER LOGIN TestUser WITH   
PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED ;  
GO 

-- CREATE LOGIN

-- DROP LOGIN
-- CREATE LOGIN with sid

2):
ALTER USER  WITH Login = ;
alter user [user_name] with login = [login_name] to fix orphaned windows login. 

1 

EXEC sp_validatelogins;  
GO
sp_validatelogins 

SELECT NAME,SID FROM SYS.DATABASE_PRINCIPALS
WHERE
TYPE = 'U' --Users only
AND AUTHENTICATION_TYPE = 3 --Windows Logins only
AND NAME NOT LIKE 'DBO'
EXCEPT
(SELECT DP.NAME, DP.SID FROM SYS.DATABASE_PRINCIPALS DP
INNER JOIN SYS.SERVER_PRINCIPALS SP ON DP.SID = SP.SID 
WHERE DP.TYPE = 'U'
AND DP.AUTHENTICATION_TYPE = 3)

3):
orphaned users 
2 
EXEC sp_change_users_login 'Report'
EXEC sp_change_users_login 'Auto_Fix', 'user'
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

USE MASTER
GO 
SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins
WHERE [name] = 'TestUser3'
GO
SELECT name, sid FROM sys.sql_logins WHERE name = 'TestLogin';  
GO 

USE AdventureWorks
GO 
SELECT name DataBaseID,SID as DatabaseSID FROM sysusers
WHERE [name] = 'TestUser3'
GO

USE adventureWorks
GO
sp_change_users_login @Action='Report'
GO

USE AdventureWorks
GO
--Command to map an orphaned user
sp_change_users_login @Action='update_one', 
@UserNamePattern='TestUser1', 
@LoginName='TestUser1'
GO

-- Command to map an orphaned user
EXEC sp_change_users_login 'Auto_Fix', 'TestUser2'
GO

-- Command to map an orphaned user to a login that is not present but will be created
EXEC sp_change_users_login 'Auto_Fix', 'TestUser3', null,'pwd'
GO