How to configure distribution for replication

Replication allows as to automatically distribute copies of data from one server to one or more distributor server.In replication we will have Distributor,Publisher and Subscriber. 

First we have to configure a Distributor to set up replication.Distributor is a server that contains the distribution database.It  stores all the transactions for transactional replication and meta data for all types of replication .  Each Publisher can have only one Distributor instance.But multiple publishers can share one Distributor.We can also Configure a centralized Distributor for multiple Publishers.

There are two types of distributor

1.Local distributor.

2.Remote distributor. 

Let us see how to configure distribution:

Right click Replication->Configure distribution

clip_image0014_thumb1

Configuration Distribution wizard window will appear.Click Next and continue.

clip_image0016_thumb2

Select the distributor which will act as server and responsible for storing replication information used during synchronizations. If you want to choose  remote  distributor select the second option.

image_thumb3

Configure SQL Server agent to start  automatically.To start the sever agent automatically, when computer is started.Click Next and continue.

image_thumb6

Specify the distribution database name.Also specify the valid path for database file and log file.Click Next

clip_image0018_thumb1

Enable the servers which will use the distributor when they become publishers.

clip_image00110_thumb1

In complete wizard window,we will get the options which we selected during the process.Click Finish.

image_thumb5

Close the wizard and complete the distribution database creation. 

clip_image00114_thumb1

Advertisements

How to add node to SQL Server cluster

We can add nodes to the existing SQL server failover cluster by following below steps. (Below method can be followed to add node in SQL Server 2008, SQL Server 2008r2 and SQL Server2012)

1. To add new node to existing SQL Server failover cluster, from new node invoke Setup.exe installation media . 

2.The Installation Wizard will show the SQL Server Installation Center as shown in below image.Click Installation an then, select Add node to a SQL Server failover cluster.

image

3.Install the setup support files.

clip_image001[36]_thumb[1]

4.After setup validation ,you will get details about operations completed,Click Next and continue.

clip_image001[38]_thumb[1]

5.On the Product key page, specify the Product ID key of the product. Click Next.

clip_image001

 

6.Then read and select the check box to accept terms and agreements.Click Next.

image

 

7.Then in cluster Node configuration choose the SQL Server instance name and Current Node which we are adding would automatically appear in “Name of this node” . Click Next and continue.

clip_image001[40]_thumb[1]

8.On the Service Accounts page, specify login accounts for SQL Server services.Specify strong password for each account.Click Next after finishing.

image

9.On the Reporting page by default  the option for error reporting is enabled.If you don’t want, disable it and continue.

10.Add Node rules page provides  the information about operation completed.

image

11.Ready to Add Node page displays the options that were specified during Setup. Click Install.

image

12.Add Node Progress page provides the status of the installation process.

13.After installation, the  page provides the summary for the installation.

image

To complete the SQL Server installation process, click Close.

Move services or application between nodes to verify if SQL Server resources are able to failover successfully:

1.After installation you could move the service between all nodes.

2.Open failover cluster manager->expand SQL Cluster->expand services and applications->select the SQL Server group –> Move this service or application to another node –>Choose the newly added node.

clip_image001[44]_thumb[1]

3.After moving the resources to newly added Node check if all the resources are able to come online on newly added node. 

clip_image001[46]_thumb[1]

HOW TO CREATE CLUSTER USING HYPER-V

Hyper –V can be used to create virtualized computers to improve the hardware efficiency. In this blog post I will try to cover how to create windows failover cluster using Hyper-V with out using multiple physical computers. Cluster we are building can be used to install Clustered SQL Server and similar cluster aware applications.

1. We will create a Domain controller

2. We will create two member servers which will act as two nodes of the cluster.

We will start the installation by  Turning on window feature Hyper-V.

To enable Hyper-V Open control panel—>Programs –>Get Programs->Turn windows features on or off –>Enable Hyper-V  as shown in picture below

image

Once the Hyper-v feature is enabled open the hyper v manager and follow the below steps one by one.

Before creating virtual machine create virtual switch to configure network access between the computers which will create and name it.

clip_image001[1]

Select the type of virtual switch for your virtual machines.

clip_image001[3]

Give the name for your Virtual switch and click ok.

clip_image001[5]

Now create New virtual machine by Right clicking SQLPC –>New->Virtual machine.

image

2.New virtual machine wizard will appear.Click Next to continue.

 

clip_image001

Specify the name for your virtual machine.Let us Create domain controller  as DC.

image

Create a new folder or use the existing folder to store the virtual machine.Specify that location in location tab.Click Next 

image

Specify the amount of memory you want to allocate for this machine.Then,click Next.

image

 

Inorder to do OS installation ,Connect to virtual hard disk and specify the size for it.Click Next

image

Then install an Operating system from CD or as image file which you have in your system.

image

Browse and select the file for installation.Click open.

 

image

Now,click finish and complete the virtual machine wizard.

image

Similarly,Follow the above steps and create two cluster nodes as clusternode1 and clusternode2 .

Now Hyper –V manager will have three virtual machines which we created one as DC and others as clusternodes.

Right click DC->Start->connect.(similarly do for all)

image

Cluster nodes will start running.

image

DC virtual machine connection window will appear,click next.

image

Installation window will appear,click install now to start installation.

image

Select the operating system you want to install.Select  windows server 2008 R2 Enterprise(Full installation) for DC(domain controller).Click Next.

image

Accept the license term and click next

 

image

Select the type of installation as Custom(advanced)

clip_image001[5]

Click Next and continue

image

 

image

After installation restart your virtual machine to update with new setting

image

Give the password for login.

image

Domain controller is installed with OS.

image

Perform the task  which you want or else close the window.

image

Configure TCP/IP address and enable LAN connection.

To configure TCP/IP, open control panel->Network and internet->Network connections->click IPV4

clip_image001[7]

Give IP address ,subnet mask and default gateway ,click ok.

clip_image001[9]

Open  dcpromo .

image

Active directory domain service wizard will appear.Click Next and continue

image

Select create new domain in a new forest

clip_image001[11]

 

Give forest root domain which acts as domain.

clip_image001[13]

 

Set forest functional level .Default Functional level is windows sever 2003.

clip_image001[15]

 

Check DNS server and click Next.

clip_image001[17]

 

Click Yes and continue.

clip_image001[21]

 

Select the folder to store databases,log files and click Next.

clip_image001[23]

Give the administrator password and click Next.

clip_image001[25]

Summary of your selections will appear,review your selection and click next.

 

clip_image001[27]

 

DNS installation will be completed.

clip_image001[29]

 

Click Finish and complete domain services installation wizard.

clip_image001[31]

Restart your computer after installation.

 

clip_image001[33]

Now do the similar things for cluster nodes 1&2.

Select computer->properties->computer name.Change the computer name and give domain name  which you gave in DC.

clip_image001[35]

 

Configure IPV4 properties for cluster nodes.

clip_image001[37]

 

clip_image001[39]

Click OK.

clip_image001[41]

Restart your computer to apply the changes.

clip_image001[43]

Click Ok,to restart.

clip_image001[45]

Now we have domain controller and member servers. We have to install failover cluster feature and make this members servers as node of cluster which we are going to create.

To install the failover cluster feature and create a cluster follow the steps in https://sqlserverscribbles.com/2013/06/17/how-to-install-cluster-in-windows-2008-and-windows-2012/

Once the cluster is created we have to create  shared Disk for installing failover aware clustered application like SQL Server. Follow the steps in  https://sqlserverscribbles.com/2013/06/16/how-to-create-shared-disk-using-iscsi-software-target/  to create a shared disk using iSCSI target software.

How to install cluster in windows 2008 and windows 2012

Failover cluster feature can be installed enterprise and datacenter edition of windows2008,windows2008R2 and windows2012.

There are changes to the quorum configuration in windows2008,windows2008R2 and windows2012 cluster compared with earlier versions in which it is mandatory to have a shared disk to install the cluster (Exceptions Geo clusters).  In this blog I will explain how to install failover cluster feature and create cluster. Shared disk is not mandatory for installing windows cluster but it is required for server applications like SQL Server when you are installing them on cluster.

If you would like to learn how to create shared disk for using in cluster follow the steps in How to create shared disk using iSCSI Software Target

To install failover cluster feature Open Sever Manager (Start—>Run—>Servermanager.msc),Expand Features.Select Add Features.

clip_image00178_thumb1

Then install Failover Clustering.

clip_image00180_thumb1

Confirm the installation and Click install.

clip_image00182_thumb1

Click close and complete the installation.

clip_image00184_thumb1

After installation ,open failover cluster manger.

Right click failover cluster manager->Create a cluster wizard.

clip_image00186_thumb1

Enter Server name of all the nodes which will be part of cluster you are creating.If you do not know the server name you can click on browse and select the servers. 

clip_image00188_thumb1

Below window appears when you click browse–>Advanced->Find now. Select the systems which you want to add in the cluster.

In the below example I have select Node1

clip_image00194_thumb1

Configure the validation warning and click Next.

clip_image00196_thumb1

Enter the IP address for cluster and Click Next.

clip_image00198_thumb1

Click Finish and complete the cluster creation. 

clip_image001100_thumb1

Once  you have created the cluster follow the steps in http://blogs.msdn.com/b/clustering/archive/2010/05/14/10012930.aspx to configure the Quorum setting for your cluster

 

If you liked this post, do like us on Face book at https://www.facebook.com/mssqlwiki and join our Facebook group

Thank you,

Pradeepa

How to create shared disk using iSCSI Software Target

One of the mandatory requirement to install SQL Server and similar application in cluster is to have shared disk.

I will explain how to create shared disk using iSCSI software target in this blog. You can download iSCSI Software Target from This link.

Installing Microsoft iSCSI software target.

When you invoke the self extracting package from downloaded above link it extracts  X64 and x86 version of installer.

  • Select iSCSI_target  for installation on the system which will act as iSCSI target. We will create iSCSI target disks in this machine and these disks will be shared by nodes using iSCSI initiator.
  • Select iSCSItargetClient_public and install in nodes in which you would like to have shared disk probably all nodes which is going to be part of cluster you are installing.

 

INSTALLATION IN CLUSTER NODES:(Follow same steps in all the nodes which will be part of cluster which you are installing)

Install iSCSItargetClient _public in all nodes which are going to be part of cluster which you are installing .

Run the file to start the installation.

clip_image001[6]

Click Next and continue

Select the type of installation as Typical.

clip_image001[12]

Specify users account that is member of administrators group in system which is acting iSCSI target and iSCSI initiator (current system).

clip_image001[16]

Configure other settings.

clip_image001[18]

Start the installation by clicking Install button.

clip_image001[20]

 

clip_image001[22]

 

Click finish to complete the installation

 

Install the  iSCSI_target on the system which will act as iSCSI target probably file server (or) Domain controller if you are installing failover clustering for testing.

 

CONFIGURE iSCSI INITIATOR IN CLUSTER NODE’S:

After installation,Open ISCSI initiator.

clip_image001[24]

  • Select yes  and continue.

clip_image001[26]

  • Type the target server name and click on Quick Connect

clip_image001[28]

                                       (Or)

  • Click Discover panel and select the target sever.

 

clip_image001[30]

  • Give the server IP address.Click ok.

clip_image001[32]

Similarly Follow the above steps and select the target in all nodes of cluster.

 

Configuring iSCSI target

Now after selecting the target,we have to add the all the iSCSI clients in iSCSI target.

Open MS iSCSI Target in system which is acting as iSCSI target.

 

clip_image001[34]

Right click iSCSI target->create ISCSI target.

clip_image001[36]

Create target wizard will open.

clip_image001[38]

 

Specify the target node name,click Next.

clip_image001[40]

Browse and select the iSCSI initiator,click ok. 

clip_image001[42]

Selected initiator will appear.Click Next.

clip_image001[44]

Click Finish and complete the target creation.

clip_image001[46]

Target Node1 is added.

clip_image001[48]

Follow the above steps in all nodes.

 

CREATING VIRTUAL DISK FOR  ALL NODES:

To create virtual disk in iSCSI target right click Node1->Create virtual disk.

Click Next.

 

clip_image001[52]

Specify the file name which will be used as virtual disk.Specify it with .vhd extension.Click Next.

clip_image001[54]

Specify the size for your virtual disk.Click Next.

clip_image001[56]

  Click Finish.

clip_image001[58]

For node2,Right click Node2->Add Existing virtual disk to iSCSI target.

clip_image001[60]

Click OK.

clip_image001[62]

CLUSTER NODES:

Now after adding the nodes as target in DC,we have to initiate the server in cluster nodes.

Select iSCSI initiator.Click Refresh,if you didn’t get any discovered target.

clip_image001[64]

Select the target  and connect it.Click OK.

clip_image001[66]

Open Disk management in cluster nodes:

clip_image001[68]

Right click Disk1->Online

clip_image001[70]

  Again ,Right click Disk1->New Simple Volume.

clip_image001[72]

New Simple Volume Wizard->Format this volume with the following setting.Click Next.

  clip_image001[76]

Then Finish the setting.

 

Open Failover cluster manager.Expand SQL Cluster.mssql.wiki.com.

Storage->Add a disk.

clip_image001

Click OK and add disk to a cluster.

clip_image001[4]

Disk is added. (If disk is created for one node,automatically it will be shared by other node)

clip_image001[8]

 

Now the shared disk is ready for use and can be used for installation of SQL Server cluster (or) any other cluster aware application which needs shared disk.

NON CLUSTERED COLUMNSTORE INDEX

What is non clustered column store index in SQL Server?

Column store index is a new feature introduced in SQL Sever 2012.In Non clustered column store index data is stored as column.Column order is not important in column store index.In this non clustered column store index we don’t have traditional row execution mode instead of that,we have batch execution mode.This improves query execution.Batch execution mode  executes several rows at a time as a batch.This reduces CPU consumption.

In column store execution, only columns that query needs will be read.This reduces I/O and memory utilization.

We cant have Unique,Primary or Foreign key constraints in column store index and also it does not have feature like INCLUDE.Once we have created the Non clustered column store index for a table,we can’t update the table with new values because the table is READ-ONLY.We could have only one non clustered column store index.Column store index could have only 1024 columns.And we don’t have clustered column store index only Non clustered is available.

This column store index uses the benefits of segment elimination based on some conditions.It gives faster performance for common data warehousing queries.

Create a Column Store Index in SQL Server by using below steps.

Using GUI:

1.In Object Explorer, expand Tables, right click -> Indexes and click New Index. Select non clustered column store index .

 

image

2.Add the column store columns.

image

  •   select the table column to be added to the index.

 

image

  • Selected Column store column will be added.

 

image

3.Set the options by selecting options from select page.In that give the value for Max degree of parallelism as greater than or equal to (2) to have batch execution.

Set other extended properties you need by clicking extended properties node.

 

image

4.Non clustered column store index is successfully created.

image

USING T-SQL:

NONCLUSTERED COLUMNSTORE index without options:

Create database product;

Use product;

CREATE TABLE Test

(ID [int] NOT NULL,

RecDate [int] NOT NULL,

DelDate [int] NOT NULL,

ExtDate[int] NOT NULL);

GO

CREATE CLUSTERED INDEX cl_test ON Test (ID);

GO

CREATE NONCLUSTERED COLUMNSTORE INDEX csindex_test ON Test (RecDate, DelDate, ExtDate);

GO                                

                                                (or)

NONCLUSTERED COLUMNSTORE index with options:

CREATE NONCLUSTERED COLUMNSTORE INDEX csindex_test ON Test

(RecDate, DelDate, ExtDate)

WITH

(DROP_EXISTING = ON, MAXDOP = 2)

ON “default”

GO

image

How to create clustered and non-clustered index

Index is a database object, which can be created on one or more columns(max 16 columns).The index will improve the performance of data retrieval and adding.indexes are created in an existing table to locate rows quickly and efficiently.

What is clustered index?

Table can have only one clustered index.In clustered index data’s in table is sorted in particular order based on index keys(either AESC/DESC).In clustered index page chain that holds data pages is also sorted in same order as index keys.So,SQL server follows the page chain in order to retrieve the data rows.By this new rows could be added just by adjusting the links in the page chain without moving entire pages.The leaf nodes of a clustered index contains the data pages with index keys.
What is Non clustered index?
For a table we could have more than one non clustered index because it doesn’t affect data pages organization.The leaf node of a non clustered index does not consist of data pages. Instead, the leaf nodes contain index rows.If we have both the clustered index and non clustered index for a table then index row will have Clustered index key columns that point to the data row. If there is no clustered index then the index row contains Non-Clustered index key columns which is stored along with row locator (or)  row identifier.The pointer from an index row to a data row is called a row locator.
Both clustered and non clustered indexes can be unique. That is no two rows can have the same value for the index key. Also we have one special type of index called Non clustered column store index.
 
Creating a clustered index  using Object Explorer:
1.In Object Explorer, expand the table for which you want to create a clustered index.

image

2.Following window will allow as to select the key columns for index.

image

3.We could add other options by selecting options node from select page.

 

image

4.Click ok.Clustered index will be created.

Create Non-Clustered index using object explorer:

1.Similarly Select Non clustered index from New index.Select the Key columns for an index.

image

2.In non clustered index we could add included columns for your index.In order to overcome existing index limits.After including needed included columns ,Click ok.

image

3.Similarly other options could be added by selecting different nodes from select page.

4.Click ok .Index will be created.