How to Testing Log Shipping

Testing Log Shipping.

After we succeeded configure Primary & Secondary server for Log Shipping, Right now we try to test log shipping with inserted data. Here is test scenario:

  1. Create new table to inserted data.
  2. Populate table with inserting data.
  3. View data table in secondary server.

Considering use Log Shipping for reporting purpose.

Log Shipping could create standby/read-only database for Reporting purpose (RESTORE WITH STANDBY option) but what’s factor you have to consider to implementing this mode:

  1. Size of one Transaction Log file Backup in Primary Server?
  2. How long estimate time to copy one transaction log file to secondary server?
  3. Application design Query (Begin Tran…Commit Tran) if Query to modifies data with Begin Tran…Commit Tran locking may occur in table until process finished.
  4. How long estimate time to restore one transaction log file into secondary server.

Create new table to inserted data.

IF EXISTS(select TOP 1 1 From sys.sysobjects Where id=OBJECT_ID(‘DimCustomer_Trans’) and xtype=’U’)

BEGIN

Drop Table DimDates

END

GO

CREATE TABLE [dbo].[DimCustomer_Trans](

[CustomerKey] [int] IDENTITY(1,1) NOT NULL,

[GeographyKey] [int] NULL,

[CustomerAlternateKey] [nvarchar](15) NOT NULL,

[Title] [nvarchar](8) NULL,

[FirstName] [nvarchar](50) NULL,

[MiddleName] [nvarchar](50) NULL,

[LastName] [nvarchar](50) NULL,

[NameStyle] [bit] NULL,

[BirthDate] [date] NULL,

[MaritalStatus] [nchar](1) NULL,

[Suffix] [nvarchar](10) NULL,

[Gender] [nvarchar](1) NULL,

[EmailAddress] [nvarchar](50) NULL,

[YearlyIncome] [money] NULL,

[TotalChildren] [tinyint] NULL,

[NumberChildrenAtHome] [tinyint] NULL,

[EnglishEducation] [nvarchar](40) NULL,

[SpanishEducation] [nvarchar](40) NULL,

[FrenchEducation] [nvarchar](40) NULL,

[EnglishOccupation] [nvarchar](100) NULL,

[SpanishOccupation] [nvarchar](100) NULL,

[FrenchOccupation] [nvarchar](100) NULL,

[HouseOwnerFlag] [nchar](1) NULL,

[NumberCarsOwned] [tinyint] NULL,

[AddressLine1] [nvarchar](120) NULL,

[AddressLine2] [nvarchar](120) NULL,

[Phone] [nvarchar](20) NULL,

[DateFirstPurchase] [date] NULL,

[CommuteDistance] [nvarchar](15) NULL,

CONSTRAINT [PK_DimCustomer_Trans] PRIMARY KEY CLUSTERED

(

[CustomerKey] ASC

) ON [PRIMARY]

) ON [PRIMARY]

GO

Populate table with inserting data.

SET NOCOUNT ON

DECLARE @CustomerKey int

DECLARE @Message char(300)

DECLARE @Loopint int

SET @Loopint=1

WHILE @Loopint<=10 –Loop condition

BEGIN

PRINT ‘——– Insert DimCustomer_Trans Step ‘ + cast(@Loopint as char(1)) + ‘——–‘

DECLARE Customer_Cursor CURSOR FOR

select Distinct CustomerKey From DimCustomer Order by CustomerKey

OPEN Customer_Cursor

FETCH NEXT FROM Customer_Cursor

INTO @CustomerKey

WHILE @@FETCH_STATUS = 0

BEGIN

Begin Transaction

INSERT INTO dbo.DimCustomer_Trans

(GeographyKey,CustomerAlternateKey,Title,FirstName

,MiddleName,LastName,NameStyle,BirthDate,MaritalStatus

,Suffix,Gender,EmailAddress,YearlyIncome,TotalChildren

,NumberChildrenAtHome,EnglishEducation,SpanishEducation

,FrenchEducation,EnglishOccupation,SpanishOccupation

,FrenchOccupation,HouseOwnerFlag,NumberCarsOwned                  ,AddressLine1,AddressLine2,Phone,DateFirstPurchase,CommuteDistance)

Select GeographyKey,CustomerAlternateKey,Title,FirstName

,MiddleName,LastName,NameStyle,BirthDate,MaritalStatus

,Suffix,Gender,EmailAddress,YearlyIncome,TotalChildren

,NumberChildrenAtHome,EnglishEducation,SpanishEducation

,FrenchEducation,EnglishOccupation,SpanishOccupation

,FrenchOccupation,HouseOwnerFlag,NumberCarsOwned            ,AddressLine1,AddressLine2,Phone,DateFirstPurchase,CommuteDistance

From dbo.DimCustomer

Where CustomerKey=@CustomerKey

Order by CustomerKey asc

Commit Transaction

FETCH NEXT FROM Customer_Cursor

INTO @CustomerKey

END

CLOSE Customer_Cursor

DEALLOCATE Customer_Cursor

IF @Loopint>10

Break

ELSE

SET @Loopint=@Loopint+1

Continue

END

View data table in secondary server.

After we succeeded populate data in table DimCustomer_Trans, let’s we connect to secondary server after last backup job in primary server finished and last copy & restore job in secondary server finished. As we can see Log Shipping Create new table DimCustomer_Trans and the table fill with 184.840 Row. Thank You…:)

FailOver LogShipping

In case of emergency we will failover on secondary database, for failover on secondary database use the following query.

USE master

RESTORE DATABASE <DatabaseName>

WITH RECOVERY

Advertisements

2 thoughts on “How to Testing Log Shipping

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s