Testing Database Mirroring

Testing Database Mirroring.

After we succeeded configure Database Mirroring with High Availability Mode, Right now we try to test Database Mirroring 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 Principal Database.
4. Failover Database Mirroring.
5. View data table in Mirror Database.

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

Monitoring Transaction on Database Mirroring.

During transaction query run we can monitor transaction on database we have mirrored. For monitoring transaction Right click on database we have mirrored and select Task | Launch Database Mirroring Monitor.

Principal Database Monitoring

Mirror Database Monitoring

View data table in Principal Server.

After we succeeded populate data in table DimCustomer_Trans, let’s we connect to Principal Server transaction query finished. As we can see table fill with 184.840 Row.

Failover Database Mirroring.

Now we try to failover database mirroring and try to see changes on database during failover process, failover mirroring it’s to make sure data inserted on Principal database has been inserted too on Mirror database. For failover database mirroring use the following query.

ALTER DATABASE AdventureWorksDW

SET SAFETY ON

GO

WAITFOR DELAY ’00:00:30′;

View data table in Mirror Server.

After we succeeded Failover Database Mirroring, let’s we connect to Mirror Server and from Object Explorer we can see new table created named DimCustomer_Trans and table fill with 184.840 Row.

Before Failover

After Failover