Steps to enable Alwayson in SQL Server 2012

Steps to configure Always on availability groups in SQL Server 2012

1. Always on availability groups can be configured in standalone or clustered SQL Server instance.

2. All SQL Server instance (principal and all replicas) must reside on servers which are part of same Windows Server Failover Cluster.

3. The server instance must be running an edition of SQL Server that supports AlwaysOn Availability Groups

To install and configure windows failover cluster follow the steps in HOW TO CREATE CLUSTER USING HYPER-V

Ensure all the systems which has SQL Server instances and will host principal and all replica databases are part of same windows cluster.  

Note: Windows failover cluster can be installed and configured without shared disk starting from windows2008 unlike windows2003.

Once the cluster is configured follow the below step by step guide to setup always on

In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server for which you want to enable AlwaysOn Availability Groups, and click Properties.

clip_image001[3]

Select the AlwaysOn High Availability tab.

Verify that Windows failover cluster name field contains the name of the failover cluster. If this field is blank, this server instance currently does not support AlwaysOn Availability Groups. Either the local computer is not a cluster node, the Windows Server Failover cluster has been shut down, or this edition of SQL Server 2012 that does not support AlwaysOn Availability Groups.

Select the Enable AlwaysOn Availability Groups check box, and click OK.

clip_image001[5]

 

Open SQL Server Management Studio.

Choose any one instance to become PRIMARY. Expand Alwayson High availability->Right-click Availability Groups and select New Availability Group wizard.

clip_image001[4]

New Availability group introduction window will appear,Click Next.

clip_image001[6]

Specify availability group name and click Next.

clip_image001[8]

Select the user databases for the availability group(Database Should have Full backup) and click Next.

clip_image001[16]

Specify an instance of SQL Server to host a secondary replica by selecting Add replica.

clip_image001[18]

Add NODE2 as secondary replica by connecting it.

clip_image001[20]

After selecting secondary replica,Optionally, you can specify endpoint details or leave them default.

clip_image001[22]

Set the backup preference.

clip_image001[24]

Specify your preference for an availability group listener that will provide a client connection point or else select do not create an availability group listener now. 

clip_image001[26]

Select your data synchronization preference as Full and specify a shared network location accessible by all replicas.

clip_image001[28]

Summary of availability group validation will be displayed,Click Next.

clip_image001[30]

Verify the choice made in this wizard.Click Finish.

clip_image001[32]

Wizard completed successfully,Click close and finish the availability group wizard.

clip_image001[34]

Successfully we have created a availability group called AGroup.

clip_image001[38]

You can suspend and resume data movement by expanding Availability groups->Group name->Availability databases->Choose the database –>resume or suspend database.

clip_image001[6]

You can view the synchronization status through the dashboard reports.

clip_image001

If you want to do a failover,right click the available group->group name->Failover.

clip_image001[4]

Advertisements

How to create database mirroring

What is database mirroring and  How to create database mirroring?

Database mirroring is software solution  for increasing database availability.It maintains two copies of single database,one as principal database and other as mirror database.So during disaster if primary database server fails then mirror database can be used by application to access the data.This reduces the down time caused during disasters. Mirrored database can not be accessed by application unless roles are switched (Transferring principal role to mirror server) but database snapshot can be created on mirrored database which can be used for read only queries (Load balancing). Refer SQL Server database snapshot steps to create snapshot   

Database  Mirroring can be  implemented  only on databases,that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. 

Database mirroring is not applicable in system databases(master,msdb, tempdb, or model databases)

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.

There are two mirroring operating modes.

1.High-safety mode:Commit changes at the principal and then transfer them to the mirror.It is asynchronous.

2.High-performance mode:Always commit changes at both the principal and mirror server.It is synchronous

All database mirroring sessions support only one principal server and one mirror server.

Steps to create mirroring:

I have two instances SQLPC\PRADEEPA  which will act as principal server and SQLPC\PK92 will act as mirrored server.

Choose a database in the principal instance which will act as Principal database,

I have created database named “Mirrordatabase” for which we are going to start mirroring.

Create a backup for the Mirrordatabase in the principal server.

clip_image001

Set the backup type in full recovery mode.Select back up destination as disk.Select add to add the destination folder.

clip_image001[4]

  In destination,add the destination folder with .bak extension and click ok. 

clip_image002

Click ok and complete the backup. 

clip_image001[6]

Now restore the backup of the database in mirrored server (destination server) with same name.

clip_image001[8]

  Type the name of the database and restore the database from device.

clip_image002[4]

Select the backup file and click ok.

clip_image001[16]

Now backup location is added.click ok and continue.

clip_image001[18]

Select the backup sets to restore.Then click Options from select a page.

clip_image001[20]

Select the recovery state as RESTORE WITH NORECOVERY.Also change the restore file name in restore as if you are creating mirror on same server for testing.Click ok.

clip_image001[22]

Database restore completed successfully.

clip_image001[24]

Configure mirroring in primary database by selecting properties.

clip_image001[26]

In Database properties window select mirroring and configure it.

clip_image001[28]

Here we dont have witness server.But we can include witness server instance also.Click Next.

clip_image001[30]

Specify principal server instance,listener port and endpoint name.Click Next.

clip_image001[32]

Connect mirror server instance.

clip_image001[34]

Connect it.

clip_image001[36]

Click Next and continue.

clip_image001[38]

Service accounts could be created in this window.

clip_image001[40]

Click Finish and complete the wizard.

clip_image001[42]

Close the window after configuring database mirroring.

clip_image001[44]

Start the mirroring by clicking start mirroring.

clip_image001[46]

We could select either High performance mode or High safety mode based upon our application need from below window.

clip_image001[48]

Mirroring is successfully created. 

clip_image001[50]

You could perform failover between primary database and mirror database by selecting Failover option in properties window.

clip_image001[52]

How to create log shipping in SQL Server

What is log shipping and how to create log shipping in SQL Server 2008?

SQL Server Log shipping automatically allows you to send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases.

Log shipping can be used with databases using the full or bulk-logged recovery models.

Steps to create log shipping in SQL Server 2008:

On the primary server, right click on the database for which you want to enable log shipping and select Properties. Then select the Transaction Log Shipping Page. Check the “Enable this as primary database in a log shipping configuration” check box.

clip_image001

Select Backup settings.Specify the network path to backup folder and Click ok.

clip_image001[4]

Select secondary database settings.Connect secondary server instance.

clip_image001[6]

After connecting,select the secondary database.

Then initialize secondary database by Specifying how you want to restore the backup in secondary database.

clip_image001[8]

Select copy files option.Specify the destination folder for copied files.

clip_image001[10]

Select the Restore transaction log option.Select the database mode as standby mode.Click ok.

clip_image001[12]

View the configuration options we selected and click ok.

clip_image001[14]

successfully we have  configured Log shipping.Click ok and finish the configuration. 

clip_image001[16]

HOW TO INSTALL SQL Server CLUSTER IN HYPER-V

To install SQL Server failover cluster we need to have windows cluster configured with a shared disk.

Step 1

If you do not have a windows cluster configured you can create using Hyper-v

Follow the steps in HOW TO CREATE CLUSTER USING HYPER-V to create Domain controller and two member servers which will act as nodes of the cluster.

Once the domain controller and two member servers are created proceed to step 2

Step 2

Once the domain controller and nodes are created install failover cluster feature and create cluster 

To install the failover cluster feature and create a cluster follow the steps in How to install cluster in windows 2008 and windows 2012

Step 3

Once the cluster is created we have to create shared Disk for installing failover aware clustered application like SQL Server.

Follow the steps in ”How to create shared disk using iSCSI Software Target” to create a shared disk using iSCSI target software.

 

Once shared disk is configured invoke Setup from  SQL server installation media .Make sure the node in which you perform the  installation has shared disk online.

image

Run the setup.

Start the installation by selecting New SQL Server Failover Cluster Installation option.

image

Setup support rules will analyze and identify the problems that might occur during setup installation.

clip_image001

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

clip_image001[4]

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

image

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

image

Select the developer features which you want and click Next.

clip_image001[6] 

Perform instance configuration.Give SQL server network name.Either you could have  default instance or named instance.But you could have only one default instance.

Click Next.

clip_image001[28]

Allocate required disk space for your installation.Click Next.

clip_image001[12]

Select the SQL Server cluster resource group name.Click Next and continue.

clip_image001[14]

Cluster disk selection will help us to view the disk we have selected for SQL Server failover cluster.

clip_image001[16]

Configure the network resource for your SQL Server failover cluster.Select DHCP check box if you are going to use DHCP network.Otherwise give the IP address for your network(IP address which you provide not be in use currently).

clip_image001[18]

Select the database engine domain group and SQL Server agent domain group. These groups have to be pre created in AD before the installation and startup account of SQL Server services will be added to these groups.  You can also use service SIDs option.

clip_image001[20]

Specify the  account name and password for SQL server agent and SQL server database engine.Click Next.

clip_image001[24]

Specify the authentication mode for your SQL Server failover cluster.Click Next and continue your installation.Verify the cluster installation rules.

clip_image001[26]

  Enable error reporting service if you want or else,click Next and continue.

image

Cluster installation rules will run the setup to determine whether any operations will block the installation.After operation completes.Click Next and continue. 

image

Ready to install page will display the summary of options which we have selected during the setup process.If you want to make any changes,go back and make the required changes or else Click Install and start the installation.

image

Click Close and Complete the installation process.

clip_image001[1]

Successfully we have installed the SQL server failover cluster in first node.

clip_image001[30]

We can install the SQL server failover cluster in remaining nodes by selecting Add node to a SQL Server failover cluster in  installation centre window. To add node to existing SQL Server failover cluster follow the steps in How to add node to SQL Server cluster

 

How to create merge replication in SQL Server

How to create merge replication in SQL Server?

For all types of replication it is mandatory to create distribution for the publisher instance before configuring replication. For configuring distribution follow the steps in “How to configure distribution for replication

Once the distribution is configured follow the below steps to create publisher

Let us create New publisher:

Right click Local publication->New Publication

clip_image001

New publication wizard appears.Click Next and continue.

 

clip_image001[4]

Select the database that contains the data or objects you want to publish.Click Next.

clip_image001[6]

There are different types of publication.

Select the publication type that supports your application.Click Next.

clip_image001[8]

Specify the SQL Server version that will be used by subscribers for this publication.Click Next.

clip_image001[10]

Select the tables to publish as articles.Click Next.

clip_image001[12]

If you don’t have unique identifier in the table.Then SQL Server will automatically add unique identifier to the table.Click Next.

clip_image001[14]

Add filters to exclude unwanted rows from published tables.Click Next.

clip_image001[16]

Specify when to run the snapshot agent and click Next.

clip_image001[18]

Select security settings.

clip_image001[20]

Specify the domain or machine account under which the snapshot agent process will run.

Select Run under the SQL Server agent service account.Click ok.

clip_image001[22]

After selecting the account for snapshot agent and log reader agent,click Next.

clip_image001[24]

Check create the publication.Click Next.

clip_image001[26]

Give the publication name and finish the wizard.

clip_image001[28]

Close the window after creating publication successfully.

clip_image001[30]

 

clip_image001[32]

Let us create the subscribers:

Before creating subscribers create one empty database where we could subscribe the publisher database.

Right click Local subscriptions->New subscriptions’

clip_image001[34]

 

New subscription wizard window appears.Click Next and continue.

clip_image001[36]

Choose the publisher and click next.

clip_image001[38]

Select the distributor agent location.Click Next.

clip_image001[40]

Select subscription database which you created.The database should be empty database.Click Next.

clip_image001[42]

Specify the process account and connection option for each distribution agent.

clip_image001[46]

Specify the domain or machines account under which the distribution agent process will run and click ok.

clip_image001[44]

 

Schedule the agent and click Next.

clip_image001[48]

Specify the type of subscription as client or server and click Next.

clip_image001[50]

Check create the subscription and click Next.

clip_image001[52]

Click Finish and complete the wizard.

clip_image001[54]

Close the window after creating subscription.

clip_image001[56]

Successfully we have created publisher,distributor and subscriber for merge replication.

clip_image001[58]

How to start replication agent in command prompt.

In this blog I will explain how to run replication agents from command prompt and enable additional parameters for agent’s while running from command prompt.

All the replication agent’s  (Snapshot agent, Logreader agent, distributor agent, Merge agent) can be run from command prompt.

All these replication agents will have corresponding exe in C:\Program files\Microsoft SQL Server\100\COM  folder

Snapshot agent –>Snapshot.exe

Logreader agent—>Logread.exe

distributor agent  –>Distrib.exe

Merge agent  –>Replmerge.exe

Before we start running the replication agents from command prompt we have to stop and disable the corresponding agent job in management studio by expanding jobs->Right click job->Stop Job

clip_image001[20]  

After stopping the job.Disable it.

clip_image001[22]

Copy the folder path in which replication agent exe’s are located ( C:\Program files\Microsoft SQL Server\100\COM  I have highlighted the agent binaries for visibility)

clip_image001[4] 

Open a command prompt and Change the directory to C:\Program files\Microsoft Sql Server\100\COM in command prompt.

clip_image001

Running Logreader agent from command prompt:

Double click Log reader Job.

  clip_image001[6]

Select steps –>Double click Run agent.

clip_image001[10]

Copy the command and paste it in command prompt.

clip_image001[8]

To run Log read agent from cmd  type logread.exe in command prompt.Then paste the command that you copied from Job step properties.

clip_image001[12]

We can monitor the progress of agent from  command prompt.

clip_image001[14]

We can also add additional parameters when we run the agents from command prompt. I will show you how to add  verbose level parameter to increase the level of logging when running agent from CMD. (–Outputverboselevel 3 increases the level of logging by replication agents).

clip_image001[40]

Like below.

clip_image001[42]

Running Replication distributor agent:

Double click the distributor job.

clip_image001[16]

Then select steps->Run agent.

clip_image001[18]

Copy the command.

clip_image001[24]

Change the directory to C:\Program files\Microsoft SQL Server\100\COM.

Then give distrib.exe –>copy the command from Job step properties.

clip_image001[28]

Now we could start distributor agent in command prompt itself.

clip_image001[30]

Running Snapshot agent:

Double click snapshot agent.

clip_image001[32]

Select Steps->Run agent.

clip_image001[34]

Change the directory to C:\Program files\Microsoft SQL Server\100\COM.

Then give snapshot.exe –>copy the command from Job step properties.

clip_image001[36]

Then we could start the snapshot agent in command prompt.

clip_image001[38]

How to create transactional replication in SQL Server

How to create transactional replication in SQL Server?

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.

In  transactional replication  snapshot of the publication database objects and data is saved.If there is any data changes and schema modifications made at the Publisher,it will be delivered to the Subscriber. The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher.Therefore, within a publication, transactional consistency is guaranteed. 

Transaction replication has three agents namely,Snapshot agent,Log reader agent and distribution agent .

The Snapshot Agent is an executable file that prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder.

The  Log Reader Agent monitors the transaction log of each database that is configured for transactional replication and copies the transactions from the transaction log into the distribution database.

The  Distribution Agent is an executable that moves the snapshot and the transactions held in the distribution database tables to the destination tables at the Subscribers.

For all types of replication it is mandatory to create distribution for the publisher instance before configuring replication. For configuring distribution follow the steps in “How to configure distribution for replication

Once the distribution is configured follow the below steps to create publisher

Let us create New publisher:

Right click Local publication->New Publication

 clip_image001[16]

New publication wizard appears.Click Next and continue.

clip_image001[18]

Select the database that contains the data or objects you want to publish.Click Next.

clip_image001[20]

There are different types of publication.

Select the publication type that supports your application.Click Next.

clip_image001[22]

Select the tables to publish  as articles.Click Next.

clip_image001[24]

Add filters to exclude unwanted rows from published tables.Click Next.

clip_image001[26]

Specify when to run the snapshot agent and click Next.

clip_image001[28]

Select  security settings

clip_image001[30]

Specify the domain or machine account under which the snapshot agent process will run.

Select Run under the SQL Server agent service account.Click ok.

clip_image001[32]

After selecting the account for snapshot agent and log reader agent,click Next.

clip_image001[34]

Check create the publication.Click Next.

clip_image001[36]

Give the publication name and finish the wizard.

clip_image001[38]

Close the window after creating publication successfully.

clip_image001[40]

Let us create the subscribers:

Before creating subscribers create one empty database where we could subscribe the publisher database.

Right click Local subscriptions->New subscriptions’

clip_image001[42]

New subscription wizard window appears.Click Next and continue.

clip_image001[44]

Choose the publisher and click next.

clip_image001[46]

Select the distributor agent location.Click Next.

clip_image001[48]

Select subscription database which you created.The database should be empty database.Click Next.

clip_image001[50]

Specify the process account and connection option for each distribution agent.

clip_image001[52]

Specify the domain or machines account under which the distribution agent process will run and click ok.

clip_image001[54]

Schedule the agent and click Next.

clip_image001[56]

Initialize the subscription as immediately and click next.

clip_image001[58]

Check  create the subscription and click Next.

clip_image001[60]

Click Finish and complete the wizard.

clip_image001[62]

Close the window after creating subscription.

clip_image001[64]

Launch replication monitor to view the connection.

To open Replication monitor Right click Replication->Launch replication monitor. 

clip_image001[66]

Successfully we have created publisher,distributor and subscriber.

clip_image001[68]