Enabling Log Shipping in SQL Server 2008 R2

Introduction

SQL Server Log Shipping allow to transfer data from one database to a lot of databases. So in Log Shipping we have 3 servers

1. Primary
2. Secondary
3. Monitor (Optional)

*The secondary database can be more than one database.

Log Shipping involves copying a database backup and subsequent transaction log backups from the primary server and restoring the database and transaction log backups on one or more secondary servers

Steps

Create a folder in primary server (LogShipping_Primary) and also in secondary server (LogShipping_Secondary).This two folders must be sharing.

Take full and transaction log backup from the database in the primary Server

Restore this database on secondary server with stand by option.

On primary server right click >> properties >> Transaction Log Shipping >> Enable Log Shipping

Configure the backup setting for the transactional log backups

• Network backup path folder: the folder name LogShipping_Primary in primary server put the network path for this folder \\Servername\Foldername
• Local path: the local path for the folder in primary server like C:\users\DBA\Desktop\LogShipping_Primary

Configure Scheduled job to backup database

Add Secondary Server

Connect to select your secondary server

Initialize Secondary Database

• If you want to take full backup and restore it to the secondary server.
• If you want to restore the existing backup from primary server to secondary server.
• if you have manually restored the database on secondary server.

Copy Files

In this table you will put the path if the folder in the secondary server \\Servername\\Foldername

Configure the schedule to take the backup files copy from the primary server  to secondary server

Restore transactional log : we will select Standby option and Disconnect all user in restore option

Configure the schedule to restore the backup taken from the primary server

Click OK

Optional (Check on monitor sever instance to monitor the backup status and the job status for the jobs in primary server and secondary server)

Last step in Log Shipping Configuration

After you finish from the Log Shipping go to the Primary Server to Check the new jobs

Return to the secondary server to check the new jobs

Note: Check the functionality of Log Shipping by executing all the SQL Jobs which have been scheduled during log shipping. All the jobs should execute successfully.

Installing Microsoft SQL Server 2008 R2 Cluster Step-By-Step

This is a step by step guide on SQL Server 2008 R2 Cluster.
Let's begin with the pre-requisites

Pre-requisites:

In order to plan everything ahead, we need to prepare some IP's and names as well as Network Configuration. During the deployment you will be using IP's and Names for the following

1. Windows Cluster
2. DTC
3. SQL

You can plan for those and document them for use ahead of time, just to save yourself some time.

Please refer to the table below

Description	Hostname	IP Address	Subnet Mask	Default Gateway
Cluster IP Address	XXCLUSTER	172.21.XX.XX	255.255.255.128	N/A
SQL Instance IP Address	XX2010SQL	172.21.XX.XX	255.255.255.128	N/A
MSDTC Virtual IP	XX2010SQLDtc	172.21.XX.XX	255.255.255.128	N/A
SQL Cluster Node 1	Node1	172.21.XX.XX	255.255.255.0	172.21.X.1
SQL Cluster Node 2	Node 2	172.21.XX.XX	255.255.255.0	172.21.X..1

We then need to setup 2 Network Interfaces to be used in production and other as a heartbeat.

We need to configure the HeartBeat interface on each node as follows

Node 1

Node 2

We do a little test and make sure they can communicate

Now the heart beat has been configured, you need to assign IP's to the production interfaces, those are the interfaces that will communicate with the servers.

Now that the networking part is out of the way, let's go ahead and start the Failover Cluster Role installation.

Windows Failover Cluster Installation

The following steps need to be performed on both database tier machines.

1. Click on Server Manager > Features > Add Features 
2. Check the Failover Clustering checkbox and click Next
3. Click on Install to install the feature 
4. Click Close to conclude the installation wizard 

Host Validation Tests:

Now we need to run the validation tests to make sure our nodes are correctly configured

1. Click on Validate Configuration 
2. Click Next at the validation wizard's intro screen 
3. Click Browse in the Select Servers or a cluster window 
4. Set the Object Types to Computers and search for the two cluster nodes 
5. Once the two cluster nodes have been selected click on Next to proceed 
6. Leave radio button at its default position (Run all tests) and then click Next 
7. The validation wizard summary will be displayed. Review it and click Next to initiate the cluster validation 
8. If the validation wizard returns any errors review and resolve them before proceeding. Errors need to be resolved before creating the cluster. Ensure that you review warnings.

Creating Windows 2008 Cluster:

SQL Server 2008 R2 clustering requires a windows cluster to be setup prior to creating the SQL node. To create the windows cluster perform the following steps.

1. In the Failover Cluster Console click on Create a cluster 
2. Click Next on the welcome screen 
3. Click Browse and select the appropriate cluster nodes 
4. Click Next to run pre-configuration validation (essentially the same validation as before, but recommended to run again right before the cluster setup). 
5. Enter the following information in the Access point for Administering the Cluster window
• Cluster Name: SPSLUSTER
• IP Address: 172.21.x.x
• 
6. Review the summary in the confirmation window and click Next to proceed with the cluster installation 
7. If any warnings/errors were generated review them and retry if necessary. If none were generated click on Finish to close the wizard.

Verifying Cluster Storage and Quorum

Now in  windows server 2008 R2, the cluster Quorum and storage will be configured automatically, we need check on that 

And this seems right, now we need to check on the Quorum type to make sure that it is set to Node and Disk Majority.''

Configuring Windows 2008(R2) Application Server

We now need to start deploying the application server role on both the nodes

Configuring MSDTC

Installing SQL Server 2008 R2 Cluster

Now we are about to start the installation of the First Node in the cluster.
Enter your product key

Install the support files

Install Features

Configure the Network Name (this is going to be the cluster name) and the instances




Choose your Cluster Resource Group (I left it at default)









Configure the Service account Users and Passwords (Best practice is to use individual accounts)





Configure your locations (Best practice to separate Database File from Log Files)



The Install, But do not configure is the only option available as we haven't installed the pre-requisites for Reporting Services.



Make sure everything passes on the cluster installation Rules



Review your installation summary and click install



Now wait for installation to finish.



Now after the installation finishes, make sure your resources are up.



Now all that is good and green we need to start the installation on the second node.
So fire up the setup  and choose


Install Support rules and make sure all passes



Enter the Product Key





You will see that the setup has detected the current single node cluster from above, click next



Enter the same accounts and password and make sure everything is green



Review your installation



Click on install and wait the install to finish

Verify the Cluster

Now if you go to the SQL Server Clustered application properties you see both nodes added

I hope this document will help you in "Installing Microsoft SQL Server 2008 R2 Cluster".