Migrating Log Shipping Database

Log Shipping is one of the HADR solution available in SQL Server and it works in a simple way – take log backup on the primary database and restore it on the secondary database. There can be more than one secondary databases. All this can be scheduled using SQL server agent jobs and even if the feature is not available is directly, it should be fairly easy to set one up in a traditional way.

I recently had to move one of our log shipping primary database to a different database server. Below are the steps, I followed the below steps to eliminate the need to reconfigure the log shipping from scratch.

Scenario :
Database – TESTLOGSHIP
Database Server : Mercury – Primary Log shipping ;
Database Server : Venus – Secondary Log shipping
There is no witness server configured.

Now, I need to migrate ‘TestLogship’ database to new database server ‘EARTH’ and configure it as log shipping primary.

1.Take full backup on the mercury server for ‘testlogship ‘ database.

2.Restore the full backup on the Earth database server in recovering mode.

3.Run select * from msdb.dbo.restorehistory where destination_database_name=’testlogship’ on the venus database server, to find which was the last log backup applied.

4.Make sure none of the log shipping jobs(backup\copy\restore) are running and disable them.

5.If there are any log backups taken but not applied on to Venus database server – apply them. This can be tracked by checking backupset table on Mercury and comparing it with restorehistory table on Venus.

6.Check if there are any log backups taken after the full backup(step1) was taken and restore them on database server Earth in ‘recovering’ mode.

7. Make sure both the Earth and Venus, database servers have same log backups applied. You can verify this by checking ‘select * from msdb.dbo.restorehistory where destination_database_name=’testlogship”.

8.Restore the database on earth with recovery.

9. Right click on the database ‘testlogship’ on the EARTH server and –tasks — ship transaction logs and configure log shipping, like you normal would but choose ‘secondary database is initialized option’ and everything else would obvious, if you have configured log shipping before.

NOTE: One easy way to do this is instead of restoring all the log backups, you can stop the log shipping jobs and take differential backup on the primary ‘Mercury’ and restore the differential backups on both ‘Venus and ‘Earth’ and then continue from step 8 above.

Advertisement

How much time does Restore VerifyOnly take?

While,it is important to verify the database backups by doing a complete restore,it might not be possible to do that in every scenario. So, in such cases, the next best thing is to do “Restore VerifyOnly”. Restore VerifyOnly does not actually restore the database but will validate the check sum, if enabled on the backup and restore some of the data page headers and will try to make sure the backup is readable and restorable as much as possible but cannot be guaranteed.

For some of our backup jobs, we have “Restore VerifyOnly” set up in the same job step as the backup. This means that SQL Agent job step runs till the database backup is taken and restore verify process has completed. Now, if we want to have a break down on how much time was spent to backup the database and verify the backup individually, we need to subtract the database backup time from the job run time to get the time taken to perform restore verify. All the backup information is available in MSDB database in backupset table. We can use msdb.dbo.backupset table to calculate the total time the backup has taken.

The backup job in this case is Full Backup job that runs once a day. So, it is easier to join the database backup with sql agent job info on the date column.

declare @databasename varchar(200)
set @databasename = mydatabase
declare @JobName varchar(200)
set @JobName=mybackupjobname

Select A.database_name as [DatabaseName],B.Name as [JobName],B.run_date as [Job_RunDate],B.Run_time as [Job_RunTime],
B.[JobRunTimeINMins] as [TotalJobRunTime_In_Mins],BackupSize_In_GB,[BackupTime_In_Mins],[BackupType],backup_start_date,backup_finish_date,
(JobRunTimeINMins -[BackupTime_In_Mins]) as RestoreVerify_Time_In_Mins
from
(select top 100 percent database_name,cast(backup_size/1024/1024/1024 as decimal(8,3)) as BackupSize_In_GB,Case WHEN type='D' then 'FULL' WHEN type='L' then 'Log' When type='I' then 'Diferential' End as [BackupType],
backup_start_date,backup_finish_date, datediff(minute,backup_start_date,backup_finish_date) as [BackupTime_In_Mins]
from msdb.dbo.backupset where database_name= @databasename and type='D' and Backup_start_date >=dateadd(month,-6,getdate())
order by backup_set_id desc) A INNER JOIN
(select top 100 percent B.Name,A.run_date,A.Run_time,
(cast(SUBSTRING(right('000000'+cast(run_duration as varchar(6)),6),1,2)*3600 as int)+
Cast(SUBSTRING(right('000000'+cast(run_duration as varchar(6)),6),3,2)*60 as int)+
Cast(SUBSTRING(right('000000'+cast(run_duration as varchar(6)),6),5,2) as int))/60 as [JobRunTimeINMins]
from msdb.dbo.sysjobhistory A INNER JOIN msdb.dbo.sysjobs B on A.job_id=B.job_id
where step_id=0 and B.name=@JobName
order by instance_ID desc) B on B.run_date=convert(varchar(8),A.backup_start_date,112)
order by run_date desc

SQL Server Audits

SQL Server has built in capability to audit certain user actions both on server level and database level. This is a useful feature as it helps to log user actions such dropping objects,changing permissions on the objects\database etc without having to build a customize auditing solution.
--Query to find the events logged by Server Audit.
select a.Audit_ID as [ServerAuditID],A.Name as [ServerAuditName],B.Name as [ServerAuditSpecificationName],C.Audit_action_id as [Audit_ActionID],C.Audit_action_Name as [Audit_ActionName],D.action_id as [Sub_ActionID],D.Name as [Sub_ActionName],E.database_name,statement,object_name
from sys.server_audits A
INNER JOIN sys.server_audit_specifications B on A.audit_guid=B.audit_guid
INNER JOIN sys.server_audit_specification_details C on B.server_specification_id=C.server_specification_id
INNER JOIN sys.dm_audit_actions D on D.covering_action_name collate SQL_Latin1_General_CP1_CI_AS=C.Audit_Action_Name
INNER JOIN fn_get_audit_file('C:\Users\sqlwhisper\Documents\SQL\ServerAudit_12663303-53EF-4FFD-BBC5CCC0AA4EE477_0_130711508902400000.sqlaudit',default,default) E
on E.action_id=D.action_id

Users associated with database roles.

Below code lists all the database users that are associated with all the roles present in the database.

select B.Name as [Database Role],C.Name as [Database UserName]
from sys.database_role_members A
INNER JOIN sys.database_principals B on B.principal_id=A.role_principal_id
INNER JOIN sys.database_principals C on A.member_principal_id=C.principal_id

SSIS Packages in SQL 2012/14

From SQL 2012 onwards, ssis packages can be deployed in project model. This requires creating a catalog and enabling ‘CLR’ integration. Once the packages are deployed in project model, they can be browsed in the ‘Integration Services Catalog’ in the management studio. Below code retrieves the packages along with the folder and packages present in the integration services catalog.

select A.name as [FolderName],B.name as[ProjectName],C.name as [PackageName]
from [catalog].[folders] A
INNER JOIN [catalog].[projects] B ON A.folder_id=B.folder_id
INNER JOIN [catalog].[packages] C on C.project_id=B.project_id

Query to retrieve column information using catalog views

The below query helps to retrieve information on columns in a table:

Select A.Name,B.Name,D.name,A.max_length,A.precision,
A.is_identity,A.is_nullable,E.[definition]
from sys.columns A inner join sys.tables B on A.object_id=B.object_id
inner join sys.schemas C on C.schema_id=B.schema_id
inner join sys.types D on D.user_type_id=A.user_type_id
left outer join sys.default_constraints E on E.parent_column_id=A.column_id and A.object_id=E.parent_object_id
where C.name='<<SchemaName>>' and B.name='<<TableName>>'

sp_columns system stored procedure can also be used to get the information.

System Stored Procedures

There are several catalog views that can be used to query database related info. We also have system stored procedures to get same basic information.

Some of the commonly used one’s are:
1.sp_helpdb
2.sp_help
3.sp_helpfile
4.sp_databases
5.sp_tables: list the table names
sp_tables @table_owner='HumanResources',@table_type="'Table','view'"
6.sp_columns: lists the column names and types and other columns details
sp_columns @table_owner='Person',@table_name='Person'
7.sp_pkeys: lists the primary key of the table
sp_pkeys @table_owner='Person',@table_name='Person'
8.sp_fkeys: lists all the tables where the key is referred.
sp_fkeys @pktable_name='Employee',@pktable_owner='HumanResources'