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

Advertisements

Configuring Database Mirroring using T-SQL

After we succeeded configuring database mirroring using database mirroring wizard. So another option to configuring database mirroring using T-SQL. Here it is step by step configuration using T-SQL.

1. Full recovery model for principal database. How to check recovery model use the following Query.

For change Recovery model in database use the following query.

2. Set Compatibility of Database to level 90 (SQL 2005) or higher. use the following query to set compatibility database level.

3. Create Full & Transaction Log Backup of Principal Database. Disable all backup jobs (Full & Transaction Log Backup of Principal database) before establishing Database Mirroring. Use the following query for backup database Full & Transaction Log.

4. After Backup Database (Full & Transaction Log), copied Backup file to mirror server, make sure you have disabled AdventureWorks2008 backup job in principal server. After copied Backup file, Restore Full & Transaction Log into mirror server. Use the following Query for Restore Backup Full & Transaction Log with NoRecovery Option.

5. Create end point & listener port on Principal server. Here it is Query for creating end point & listener port on principal assume you have SQL Instance named “XMS”

— =============================================
— Author: Aries Budi Susilo
— Create date: 13 Juli 2012
— Description: 1. Creating End Point & Listener Port on Principal Server
— Run : Principal Server Instance
— =============================================
Use master
IF Exists(Select * From sys.endpoints Where name =’Principal’)
Drop EndPoint Principal
GO
CREATE ENDPOINT [Principal]
AUTHORIZATION [XMS\Administrator]
STATE=STARTED
AS TCP (LISTENER_PORT=5042, LISTENER_IP=ALL)
FOR DATA_MIRRORING (ROLE=PARTNER, AUTHENTICATION=WINDOWS NEGOTIATE,
ENCRYPTION=REQUIRED ALGORITHM RC4)
GO
Select * From sys.endpoints Where name =’Principal’
GO

6. Create end point & listener port on Mirror server. Here it is Query for creating end point & listener port on Mirror assume you have SQL Instance named “XMS\Mirror”

— =============================================
— Author: Aries Budi Susilo
— Create date: 13 Juli 2012
— Description: 2. Creating End Point & Listener Port on Mirror Server
— Run : Mirror Server Instance
— =============================================
Use master
IF Exists(Select * From sys.endpoints Where name=’Mirror’)
Drop EndPoint Principal
GO
CREATE ENDPOINT [Mirror]
AUTHORIZATION [XMS\Administrator]
STATE=STARTED
AS TCP (LISTENER_PORT=5043, LISTENER_IP=ALL)
FOR DATA_MIRRORING (ROLE=PARTNER, AUTHENTICATION=WINDOWS NEGOTIATE,
ENCRYPTION=REQUIRED ALGORITHM RC4)
GO
Select * From sys.endpoints Where name=’Mirror’
GO

7. Establish Mirroring session on Principal server.

— =============================================
— Author: Aries Budi Susilo
— Create date: 13 Juli 2012
— Description: 3. Establish Mirroring Session
— Run : Principal Server Instance
— =============================================
ALTER DATABASE AdventureWorksDW
SET PARTNER=’TCP://XMS:5042′
GO

8. Create partnership between mirror and principal server.

— =============================================
— Author: Aries Budi Susilo
— Create date: 13 Juli 2012
— Description: 4. Create Partnership
— Run : Mirror Server Instance
— =============================================
ALTER DATABASE AdventureWorksDW
SET PARTNER=’TCP://XMS:5043′
GO

Now we have configure Database Mirroring using wizard and T-SQL Script. Next article will be discuss how to fail over Database Mirroring and test Database Mirroring with inserting data. Failover on database mirroring it is the step to switching role (Principal server become Mirror Server & Mirror server become Principal Server).

Database Mirroring

Database Mirroring

Database Mirroring (DBM) is one of high availability feature in SQL Server. Database Mirroring are high availability solution in database level. It protects at database level. What is database mirroring? Database mirroring is technology from SQL Server to increase High availability by creating standby database on secondary instance or perhaps secondary machine named “Mirror Server” as an alternate of production server in case of emergency. Database mirroring works to create exact copy of principal database and synchronize every transaction change in principal database to mirror database and then in case of emergency DBM can switch role between principal and mirror database. Database mirroring require same sql server edition for principal & mirror database. According to Microsoft High Availability “Solution masks the effects of a hardware or software failure and maintains the availability of applications so that perceive downtime for user is minimized”.

Key Terms in Database Mirroring

  • Principal: Primary server that contain source database (principal database). Database must be user database with Full Recovery Model.
  • Mirror: Secondary server or target server that contain destination database (Mirror Database). Database must be user database with Full Recovery Model.
  • Witness: Optional server to create quorum that allow automatic failover.
  • Partners: Opposite server that refer to principal or mirror server.
  • Session: Active relation between principal database and mirror database that contain state information about one to another.
  • Operating Mode: Database Mirroring operating modes which High Protection Mode (Synchronous without witness), High Availability Mode (Synchronous with witness), High Performance Mode (Asynchronous without witness).
  • Role: Server role refer to principal, mirror, witness.

Database Mirroring Operating Modes.

There are several operating modes in database mirroring. Let’s we discuss the different between operating modes and consideration to choose configuration between them.

  1. High Protection Mode (Synchronous without witness): High Protection mode ensure transaction chances from principal database has been synchronous in mirror database. In this operating modes Database Mirroring ensure transaction committed on mirror database and send acknowledgment to principal database so principal database committed same transaction. Transaction committed on both principal & mirror database.  So application must wait until transaction committed on both database. In order to switching role mirror database become principal database, recovery database process is faster because there isn’t uncommitted transaction to be applied. Fail over can be done manually in this mode. High Protection Mode Require at least Two SQL Server Instance (Principal, Mirror) which can be on the same physical server or on another server.
  2. High Availability Mode (Synchronous with witness): High Availability mode works same as High Protection mode. In order to automatic failover High Availability mode use Witness server. Witness servers verify connection between Principal & Mirror server and perform automatic failover capabilities. Witness servers perform Automatic failover with established communication check between principal & Mirror server and can switching Mirror role in Mirror server to became Principal Role if Principal server unavailable. High Availability Mode Require at least Three SQL Server Instance (Principal, Mirror & Witness) which can be on the same physical server or on another server.
  3. High Performance Mode (Asynchronous without witness): High Performance mode perform Asynchronous operation between Principal & Mirror database. Transaction committed in principal database without wait acknowledgement from mirror database. In this mode application doesn’t wait committed transaction in mirror server. Automatic failover couldn’t be applied in this mode, because High Performance mode doesn’t need quorum (Witness Server) for establish communication between principal & mirror database. High Performance Mode Require at least Two SQL Server Instance (Principal, Mirror) which can be on the same physical server or on another server.

Prerequisites

1. Full recovery model for principal database. How to check recovery model use the following Query.

For change Recovery model in database use the following query.

2. Set Compatibility of Database to level 90 (SQL 2005) or higher. use the following query to set compatibility database level.

3. Create Full & Transaction Log Backup of Principal Database. Disable all backup jobs (Full & Transaction Log Backup of Principal database) before establishing Database Mirroring. Use the following query for backup database Full & Transaction Log.

4. After Backup Database (Full & Transaction Log), copied Backup file to mirror server, make sure you have disabled AdventureWorks2008 backup job in principal server. After copied Backup file, Restore Full & Transaction Log into mirror server. Use the following Query for Restore Backup Full & Transaction Log with NoRecovery Option.

5. Let’s get back to database in principal server. Right click database click on properties. On mirroring page click configure security button.

6. Click next on Configure Database Mirroring Security Wizard. In Include Witness Server page, If we want to configure database mirroring with High Protection Mode or High Performance Mode choose option No. If we want to configure database mirroring with High Availability Mode we should choose option with Yes. We will configure with High Availability Mode, so choose yes.

7. In choose servers to configure Page, check list Witness Server instance to configure Witness server security.

8. In the Principal Server Instance page, review security information about 1. Principal Server Instance, 2. Listener Port, 3. End Point Name. Click next.

9. In the Mirror Server Instance Page, click button connect to choose Mirror server. Review security information about 1. Mirror Server Instance, 2. Listener Port, 3. End Point Name. Click next.

10. In the Witness Server Instance Page, click button connect to choose Witness server. Configure Listener Port for Witness Server. Review security information about 1. Witness Server Instance, 2. Listener Port, 3. End Point Name. Click next.

11. In the Service Account page, specify service accounts for each Instance. If you use different domain accounts, enter accounts for each instance. Click next to display complete.

Click Start Mirroring to start Database Mirroring with High Availability Mode.

12. Let’s we review database mirroring configuration.

Right now we have Database Mirroring with High Availability Mode, next article we will try to testing & monitoring our Database Mirroring with inserting data and try automatic failover between principal & Mirror server.

How to Create Dim Dates Table via T-SQL Query

Microsoft SQL Server Release with Sample database Adventure Works (OLTP, DW).  These sample database (AdventureWorks, AdvetureWorksDW) can be download on www.codeplex.com. In this article we will create customize DImDates Table. As you already know DimDates are one of table in AdventureWorksDW database. Here is sample script:
— =============================================
— Author: Aries Budi Susilo
— Create date: 9 Juli 2012
— Description: Create customize DimDates Table
— =============================================
IF EXISTS(select TOP 1 1 From sys.sysobjects Where id=OBJECT_ID(‘DimDates’) and xtype=’U’)
BEGIN
Drop Table DimDates
END
GO
CREATE TABLE dbo.DimDates(
DateKey int NOT NULL,
FullDateKey date NOT NULL,
DayNumberOfWeek tinyint,
DayNameOfWeek nvarchar(10),
INA_DayNameOfWeek nvarchar(10),
DayNumberOfMonth tinyint,
DayNumberOfYear smallint,
WeekNumberOfYear tinyint,
EnglishMonthName nvarchar(10),
INAMonthName nvarchar(10),
MonthNumberOfYear tinyint,
CalendarQuarter tinyint,
CalendarYear smallint,
CalendarSemester tinyint,
FiscalQuarter tinyint,
FiscalYear smallint,
FiscalSemester tinyint
) ON [PRIMARY]
GO
ALTER TABLE DimDates ADD CONSTRAINT PK_DimDate_FullDateKeys PRIMARY KEY CLUSTERED(FullDateKey) on [PRIMARY]
GO
ALTER TABLE DimDates ADD CONSTRAINT [AK_DimDate_DateKeys] UNIQUE NONCLUSTERED(DateKey ASC)ON [PRIMARY]
GO

— =============================================
— Author: Aries Budi Susilo
— Create date: 9 Juli 2012
— Description: Create Stored Procedure to Populate DimDates Table
— =============================================

IF OBJECT_ID (N’dbo.usp_CreateDimDates’, N’P’) IS NOT NULL
DROP PROCEDURE dbo.usp_CreateDimDates;
GO

CREATE Procedure dbo.usp_CreateDimDates(@StartDate Smalldatetime, @EndDate Smalldatetime)
AS
DECLARE @DimDates TABLE
(
DateKey int,
FullDateKey date Primary Key,
DayNumberOfWeek tinyint,
DayNameOfWeek nvarchar(10),
INA_DayNameOfWeek nvarchar(10),
DayNumberOfMonth tinyint,
DayNumberOfYear smallint,
WeekNumberOfYear tinyint,
EnglishMonthName nvarchar(10),
INAMonthName nvarchar(10),
MonthNumberOfYear tinyint,
CalendarQuarter tinyint,
CalendarYear smallint,
CalendarSemester tinyint,
FiscalQuarter tinyint,
FiscalYear smallint,
FiscalSemester tinyint
)
Declare @FiscalDate smalldatetime, @LoopDate smalldatetime
SET @LoopDate=@StartDate
BEGIN
WHILE @EndDate>=@LoopDate
BEGIN
SET @FiscalDate=Dateadd(Month, 6, CAST(CAST(DATEPART(Year, @LoopDate) as nvarchar(4))+’/’+
RIGHT(‘0′ + CAST(DATEPART(Month, @LoopDate) as nvarchar(2)), 2)+’/1’ AS smalldatetime))

INSERT @DimDates
SELECT
CAST(CAST(DATEPART(Year, @LoopDate) as nvarchar(4))+
RIGHT(‘0’ + CAST(DATEPART(Month, @LoopDate) as nvarchar(2)), 2)+
RIGHT(‘0’ + CAST(DATEPART(Day, @LoopDate) as nvarchar(2)), 2) AS CHAR(8)) as [DateKey]
,@LoopDate as [FullDateKey],DATEPART(DW, @LoopDate) as DayNumberOfWeek,Datename(dw, @LoopDate) as DayNameOfWeek
,(CASE WHEN DATEPART(DW, @LoopDate)= 1 Then ‘Minggu’
WHEN DATEPART(DW, @LoopDate)= 2 Then ‘Senin’
WHEN DATEPART(DW, @LoopDate)= 3 Then ‘Selasa’
WHEN DATEPART(DW, @LoopDate)= 4 Then ‘Rabu’
WHEN DATEPART(DW, @LoopDate)= 5 Then ‘Kamis’
WHEN DATEPART(DW, @LoopDate)= 6 Then ‘Jum”at’
WHEN DATEPART(DW, @LoopDate)= 7 Then ‘Sabtu’ END) AS INA_DayNameOfWeek
,Datepart(D, @LoopDate) as DayNumberOfMonth,Datepart(Dy, @LoopDate) as DayNumberOfYear
,Datename(ww, @LoopDate) as WeekNumberOfYear
,Datename(Month, @LoopDate) as EnglishMonthName
,(CASE WHEN Month(@LoopDate)= 1 Then ‘Januari’
WHEN Month(@LoopDate)= 2 Then ‘Februari’
WHEN Month(@LoopDate)= 3 Then ‘Maret’
WHEN Month(@LoopDate)= 4 Then ‘April’
WHEN Month(@LoopDate)= 5 Then ‘Mei’
WHEN Month(@LoopDate)= 6 Then ‘Juni’
WHEN Month(@LoopDate)= 7 Then ‘Juli’
WHEN Month(@LoopDate)= 8 Then ‘Agustus’
WHEN Month(@LoopDate)= 9 Then ‘September’
WHEN Month(@LoopDate)= 10 Then ‘Oktober’
WHEN Month(@LoopDate)= 11 Then ‘November’
WHEN Month(@LoopDate)= 12 Then ‘Desember’ END) AS INAMonthName
,Datepart(Month, @LoopDate) as MonthNumberOfYear
,(Case When Month(@LoopDate) Between 1 and 3 Then 1
When Month(@LoopDate) Between 4 and 6 Then 2
When Month(@LoopDate) Between 7 and 9 Then 3
ELSE 4 END) as CalendarQuarter
,Datepart(Year, @LoopDate) as CalendarYear,(Case When Month(@LoopDate) Between 1 and 6 Then 1 Else 2 END) as CalendarSemester
,(Case When Month(@FiscalDate) Between 1 and 3 Then 1
When Month(@FiscalDate) Between 4 and 6 Then 2
When Month(@FiscalDate) Between 7 and 9 Then 3
ELSE 4 END) as FiscalQuarter
,Datepart(Year, @FiscalDate) as FiscalYear,(Case When Month(@FiscalDate) Between 1 and 6 Then 1 Else 2 END) as FiscalSemester

IF @LoopDate>@EndDate
Break
ELSE
SET @Loopdate=DATEADD(DAY, 1, @Loopdate)
Continue
END
Select * From @DimDates
END;
GO

— =============================================
— Author: Aries Budi Susilo
— Create date: 9 Juli 2012
— Description: Script for populated DimDates Table
— =============================================

–Truncate Table DimDates
Truncate Table DimDates

–Insert New Row into DimDates
Insert DimDates
Exec usp_CreateDimDates ‘20010101’, ‘20121231’

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

Log Shipping

What is Log Shipping?

Log Shipping is one of feature high availability in SQL Server. Log shipping is technology from SQL Server to synchronize databases on 2 or more servers, utilize backup and restore technology for High availability or reporting purpose. Log Shipping works to ships Transaction log file from the one server to another server. Log shipping consist three operations:

  1. Backup transaction log at primary server.
  2. Copy backup transaction log file to the secondary server.
  3. Restore the log backup on the secondary server instance.

Prerequisite for implementing log shipping

  • Log shipping required Full or Bulk-Logged Recovery Model.
  • SQL server Agent logins: Make sure SQL Agent Services  on a both the servers have sysadmin role. This logins use for Backup and Restore database logs on the servers.
  • Share folders: Make sure SQL Services logins have Read and write access for share folders on a both servers. Share folders for copy and write log backup on both share folder.

Log shipping step by step

Create sharing folders on both servers.

  1. Create share folder in drive C with following structures.  
  2. Make sure SQL Agent service login are same on both servers. This is for Backup database log file on primary server and then restore database log file to secondary server.
  3. Give permission for share folders on both server to use SQL Agent Logins.

Make sure Database for log shipping used Full or Bulk Logged Recovery Model

We will use AdventureWorksDW2008 or AdventureWorksDW database for this tutorial. Use the following Query.  

to change database Recovery model use following query

ALTER DATABASE <YourDatabase Name> SET RECOVERY FULL

Backup Database on Primary Server

Once database have Full or Bulk copy Recovery Model, next step is backing up full and transaction log on the Primary Server. Use the following query.

 

Restore Database on Secondary Server

We have Full & Transaction Log database backup on primary server, next step is to restore database on Secondary Server. For Restore database on the secondary server we have to option:

  1. RESTORE WITH NO RECOVERY, We can use this option to have database with restore mode on secondary server which is mean database on the secondary server can’t be access.
  2. RESTORE WITH STANDBY, We can use this option to have Read-Only database, every changes on object or data in primary database on Primary server can be view or access in secondary database on secondary server.

First restore full database backup WITH NO RECOVERY option, use the following query.

Second restore transaction log backup WITH STANDBY option and create standby file, use the following query.

Now we have standby/read-only database on the secondary server.

Configure Log Shipping Agent Job on Primary Server

Let’s start to configure Log Shipping on primary database, use the following steps

1. Right click on database AdventureWorksDW2008 or AdventureWorksDW-Task-Ship Transaction Logs. Click Enable this as a primary database in a log shipping configuration. Click button Backup settings.

 

2. Once we click button Backup Settings, we will configure Log Shipping on primary server. Configuration of Log Shipping on primary server are:

  1. Network share folder for backup transaction log.
  2. Local folder which located on primary server.
  3. How long transaction log should retain on primary server.
  4. Log Shipping SQL Agent job name on primary server.
  5. Backup compression type (Non Compressed Backup, Compressed Backup, use Default server setting).

3. Configure schedule for Backup Job, we will configure Log Shipping on primary server. Configuration of Backup Job on primary server are:

  1. Log Shipping Backup job name on primary server.
  2. Backup Job Frequencies.
  3. Frequency of Backup Job Frequency.
  4. Duration of Backup Job.

 4. Configure Log Shipping for Secondary Server. Click button add from secondary database box for add Log Shipping secondary server, Log Shipping  Configuration for secondary server are:

  1. SQL Server secondary instance. Server name of secondary server.
  2. Secondary database. Database name on secondary server for restore backup transaction log file.
  3. Initialize secondary database. Secondary database initialization, configuration of initialization secondary database are:

A. Secondary server will create full backup database of primary database in network share folder and restore backup to create new database on secondary server. These options allow us to initialize folder for data and log file.

B. Secondary server will create full backup database of primary database and restore backup on secondary server with default restore option. These options allow us to initialize folder for data and log file.

C. Secondary database has been initializing on secondary server.

5. Configure Copy files option on secondary server. Copy files configuration on secondary server are:

  1. Destination folder on secondary server for copied transaction log file backup.
  2. Length of time for keep transaction log files on secondary server.
  3. Configure Copy Job on Secondary Server. Setting SQL Agent Copy schedule on secondary server.

 6. Configure Restore Transaction Log option on secondary server. Configuration for Restore Transaction Log on secondary server are:

1.  Configure Database State on Secondary Server when SQL Agent trying to restore transaction log. No Recovery Mode mean database on secondary server couldn’t be access. Standby Mode mean database on secondary server have read-only mode.

2. Configure SQL Agent Restore Job on Secondary server. setting up schedule for Restore Job on secondary server.

We Already configure Log shipping on Primary and secondary server. to configure Log Shipping & Backup Job on Primary Server use the following query :

DECLARE @LS_BackupJobId ASuniqueidentifier

DECLARE @LS_PrimaryId   ASuniqueidentifier

DECLARE @SP_Add_RetCode Asint

 

 

EXEC @SP_Add_RetCode =master.dbo.sp_add_log_shipping_primary_database

–Database Name on Primary Server

@database =N’AdventureWorksDW2008′

–Backup directory on Primary Server

,@backup_directory =N’C:\LogShipping\Backup\Primary’

–Network share folder for share Backups

,@backup_share =N’\\localhost\LogShipping\Backup\Primary’

–Backup Job Name on Primary Server

,@backup_job_name =N’LSBackup_AdventureWorksDW2008′

–Retention Period for Transaction Log Files on Primary Server (in Minute)

,@backup_retention_period = 2880

–Type of Backup Compression (0:No Compressed, 1:Compressed, 2:use server default)

,@backup_compression = 1

,@history_retention_period = 5760

,@backup_job_id = @LS_BackupJobId OUTPUT

,@primary_id = @LS_PrimaryId OUTPUT

,@overwrite = 1

 

 

IF (@@ERROR= 0 AND @SP_Add_RetCode = 0)

BEGIN

 

DECLARE @LS_BackUpScheduleUID Asuniqueidentifier

DECLARE @LS_BackUpScheduleID  ASint

 

EXEC msdb.dbo.sp_add_schedule

@schedule_name =N’LSBackupSchedule_XMS1′

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20120707

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_BackUpScheduleUID OUTPUT

,@schedule_id = @LS_BackUpScheduleID OUTPUT

 

EXEC msdb.dbo.sp_attach_schedule

@job_id = @LS_BackupJobId

,@schedule_id = @LS_BackUpScheduleID 

 

EXEC msdb.dbo.sp_update_job

@job_id = @LS_BackupJobId

,@enabled = 1

END

 

EXECmaster.dbo.sp_add_log_shipping_alert_job

EXECmaster.dbo.sp_add_log_shipping_primary_secondary

–Primary database name

@primary_database =N’AdventureWorksDW2008′

–Secondary server

,@secondary_server =N’XMS\SQL2KM’

–Secondary database name

,@secondary_database =N’AdventureWorksDW2008′

,@overwrite = 1

 

Use the following query to configure Copy & Restore Job on Secondary Server.

 

 

DECLARE @LS_Secondary__CopyJobId    ASuniqueidentifier

DECLARE @LS_Secondary__RestoreJobId ASuniqueidentifier

DECLARE @LS_Secondary__SecondaryId  ASuniqueidentifier

DECLARE @LS_Add_RetCode Asint

 

 

EXEC @LS_Add_RetCode =master.dbo.sp_add_log_shipping_secondary_primary

@primary_server =N’XMS’

,@primary_database =N’AdventureWorksDW2008′

,@backup_source_directory =N’\\localhost\LogShipping\Backup\Primary’

,@backup_destination_directory =N’\\localhost\LogShipping\Backup\Secondary’

,@copy_job_name =N’LSCopy_XMS_AdventureWorksDW2008′

,@restore_job_name =N’LSRestore_XMS_AdventureWorksDW2008′

,@file_retention_period = 10080

,@overwrite = 1

,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT

,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT

,@secondary_id = @LS_Secondary__SecondaryId OUTPUT

 

IF (@@ERROR= 0 AND @LS_Add_RetCode = 0)

BEGIN

 

DECLARE @LS_SecondaryCopyJobScheduleUID   Asuniqueidentifier

DECLARE @LS_SecondaryCopyJobScheduleID    ASint

 

 

EXEC msdb.dbo.sp_add_schedule

@schedule_name =N’DefaultCopyJobSchedule’

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20120707

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT

,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT

 

EXEC msdb.dbo.sp_attach_schedule

@job_id = @LS_Secondary__CopyJobId

            ,@schedule_id = @LS_SecondaryCopyJobScheduleID 

 

DECLARE @LS_SecondaryRestoreJobScheduleUID      Asuniqueidentifier

DECLARE @LS_SecondaryRestoreJobScheduleID ASint

 

 

EXEC msdb.dbo.sp_add_schedule

@schedule_name =N’DefaultRestoreJobSchedule’

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20120707

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT

,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT

 

EXEC msdb.dbo.sp_attach_schedule

@job_id = @LS_Secondary__RestoreJobId

            ,@schedule_id = @LS_SecondaryRestoreJobScheduleID 

 

 

END

 

 

DECLARE @LS_Add_RetCode2      Asint

 

 

IF (@@ERROR= 0 AND @LS_Add_RetCode = 0)

BEGIN

 

EXEC @LS_Add_RetCode2 =master.dbo.sp_add_log_shipping_secondary_database

@secondary_database =N’AdventureWorksDW2008′

,@primary_server =N’XMS’

,@primary_database =N’AdventureWorksDW2008′

,@restore_delay = 0

,@restore_mode = 1

,@disconnect_users      = 0

,@restore_threshold = 45  

,@threshold_alert_enabled = 1

,@history_retention_period    = 5760

,@overwrite = 1

 

END

 

 

IF (@@error= 0 AND @LS_Add_RetCode = 0)

BEGIN

 

EXEC msdb.dbo.sp_update_job

@job_id = @LS_Secondary__CopyJobId

            ,@enabled = 1

 

EXEC msdb.dbo.sp_update_job

@job_id = @LS_Secondary__RestoreJobId

            ,@enabled = 1

 

END

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

Next article will will test log shipping server.

 

Pentingnya rasa bersyukur kepada Allah.

Manusia sering kali lalai dalam memanfaatkan berkah & rejeki yang telah diberikan Allah S.W.T, dan banyak rejeki yang dihambur-hamburkan demi kegiatan yang tidak bermanfaat. segala hal yang menghambur-hamburkan sesuatu bertentangan dengan Al-Qur’an :

“Dan berikanlah sebagaian hakmu kepada keluarga-keluarga terdekat, orang-orang miskin, musafir-musafir dan janganlah kamu menghambur-hamburkan (Harta-mu) dengan cara yang boros. Sesungguhnya orang-orang yang boros adalah saudara-saudara syaitan dan syaitan itu sangat ingkar terhadap tuhannya. ” (QS. Isra (17-(26-27)).

Seorang yang lalai dalam memanfaatkan nikmat dan rejeki dari Allah S.W.T menunjukkan kurangnya rasa bersyukur kepada-Nya. Sementara rasa bersyukur terhadap Allah S.W.T harus dimiliki oleh setiap umat muslim yang mengharapkan surga-Nya.

Rasa bersyukur terhadap nikmat yang telah diberikan oleh Allah S.W.T merupakan modal awal yang harus dimiliki oleh seorang muslim dalam menjalani kehidupannya.  Menurut Al-Qur’an Allah menginginkan hamba-hambaNya memperoleh manfaat dari berkahNya menurut cara yang terbaik, sekalipun mereka dapat menghindari kesia-siaan:

“Wahai anak cucu Adam, pakailah pakaianmu yang bagus setiap (memasuki) masjid, makan dan minumlah, tetapi jangan berlebihan. Sungguh, Allah tidak menyukai orang-orang yang berlebihan.” (QS. Al-A`râaf, (7):31)