Microsoft SQL Server 2012 AlwaysOn Group Availability Failover in VMware Workstation

This is the 1st post of high availability. This post contains always on configuration and its failover.

Required Software:

  1. VMware Workstation
  2. Windows Server 2012
  3. SQL Server 2012

Three Virtual Machines for Configuring Active Directory and Windows Server Cluster

1: Installation of VMWare to Create Virtual Machine.

On VMWare tool create a new virtual machine.

1

Welcome to new virtual machine wizard select typical and click to next.

2

Here we have to select windows operating system iso file while we will select it later so I am selecting last option ‘I will install the operating system later’ and click on next.

3

Here we have to select type of operating system which we are going to install. Select Microsoft Windows and as version select Windows Server 2012 then click on next.

4

Here we are going to put the windows server machine name and give the path for setup files. After click on next.

5

Here we allot the C:\ Drive space for virtual server which we are going to install then click on next.

6

On the customize Hardware we can initialize the RAM 1024MB for the virtual machine and review which we are selected for virtual machine and then click on finish.

0

Now go to AD on left pane and open the properties of the virtual machine.

8

Here we are giving the path of Windows Server 2012 ISO image file. Then click on next tab option.

9

Here we are sharing the local machine path for future need. Click on ok.

10

Right click on the virtual machine and click on power.

Select language and click on next.

11

Click on install now.

12

Select operating system, Windows Server 2012 Standard (Server with a GUI), Click on next.

13

Accept the licence and then next.

14

Select custom type.

15

Check the drive space and Click on next.

16

Operating system installing. It takes few time to install.

17

Operating System has been installed, Now reset the password then click on finish.

18

After reseting the password login to server.

19

Windows Server 2012 has been installed. Now change some settings as per our requirements.

20

Change the Windows Server Machine name, go to properties of the computer, Click on change settings, System Properties popup will open.

21

Click on change and change the computer name.

22

After changing the machine name click on OK.

23

It will ask to restart the system. So click on OK.

24

Restart Now.

25

After Restarting System, Open the network sharing Center, On the Network and Sharing Center Go to Change adapter setting on left pane.

26

Right Click on Ethernet0 and go to properties.

27

28

Select Internet Protocol Version 4 (TCP/IPv4) and click on properties.

29

Here give the IP address for server and click on ok.

30

After that go to firewall settings.

31

And turnoff the windows firewall.

32

Go to VM and Install VM-Ware tools, Click on Run setup64.exe.

33

Click on next.

34

Select typical and click on next.

35

Click to install.

36

After completion for installation it ask to restart the system, Click on yes and Restart the system.

37

Now install two more windows servers as just like above. And give the IP address as below mentioned.

For Node1:

IP Address: 192.168.10.11

Subnet Mask: 255.255.255.0

Default Gateway: 192.168.1.1

Preferred DNS: 192.168.10.10

For Node 2:

IP Address: 192.168.10.12

Subnet Mask: 255.255.255.0

Default Gateway: 192.168.1.1

Preferred DNS: 192.168.10.10

After configuring all three Windows Machines. Ping to each machine, make sure all are pinging to each other.

Now we are going to Windows Machine 1 (AD) and install the Active Directory Domain Service.

Login to AD and go to Server Manager and add roles and features.

38

Click on Next.

39

On the Installation Type Select Role-based or feature-based installation and click on next.

40

On server selection select server from server pool and click on Next.

41

On server roles Add role as selected on below snapshot and click on next.

42

Right now we are only adding the active directory roles so leave it as usual and click on next.

43

Click on next.

44

And on the confirmation click to install.

45

Installation in progress. After completion click on close.

46

Now we are going to configure post deployment, it shows a notification as below snapshot.

47

On the deployment configuration we are going to create a new forest so click on add a new forest and give the root domain name and then click on next.

48

On the domain control option give password for domain controller and click on next.

49

Currently we are configuring active directory domain services so no need to specify DNS hence leave specification DNS and click on Next.

50

Verify the NetBIOS domain name as shown above snapshot and click on next.

51

Check for paths of configuration of AD Domain Services and click on next.

52

Review the Complete Configuration and click on next.

53

Check prerequisites and go to install.

55

Installation is progress, after completion the installation and Active Directory Domain Services Windows Server takes reboot the server.

56

After restart AD will become as domain controller.

57

After login to server we can check the system properties as configured sql.ind.in domain.

58

After configuration active directory domain services on AD. Now go to Node1 and Node2 server and add these servers on domain.

Go to system change properties and click on member of as domain and give the domain name and click on ok.

59

Give the active directory domain credentials.

60

After clicking Ok it will open the pop-up for welcome to the domain. Click on ok.

61

62

Asking for restart the machine for reflecting the domain services.

63

Restart the machine and login to server.

64

Apply the same process with Node2 machine.

2: Installation of Windows Cluster Services on Node1 and Node2 Server.

Now we are going to configure the Windows Cluster between Node1 and Node2.

On the Node1 Server, install failover cluster feature. Go to server manager and add role and features.

65

Click on add roles and features.

66

Select Role-based or feature-based installation and click on next.

67

Select a server from the server pool and click on next.

68

69

Select feature failover clustering and click on next.

70

Confirm installation selections and click to install.

71

After completion the installation close the wizard and cofigure the failover cluster manager on node2 server as well.

72

On Node1, Go to server manager and click on tools and select failover cluster manager.

73

Go to Validate Configuration for checking the compatibility of cluster nodes.

74

Click on next.

75

Enter the Server Name which we want to configure the cluster, click on next.

76

On the testing option select Run all tests and click on next.

77

Confirmation for validate tests, click on next.

78

Validation tests are running, wait a while.

79

Check the validation summary and click on finish.

80

On Create Cluster Wizard, Click on Next.

81

Here we are providing the Windows Server Cluster Name and Cluster IP Address. Click on Next.

82

Verify once Cluster Name, Nodes and IP Address, Click on Next.

83

Wait a while for configuration of cluster.

84

After configuration of Cluster, Click on Finish.

85

Now WinCluster is available on Failover Cluster Manager on both nodes.

86

3: Installation of SQL Server on Node1 and Node2 Server.

On Node 1 and going to install the SQL Server 2012.

Make sure you run the setup ‘Run as Administrator’.

87

The SQL Server Installation Center window will display. Along the left side of the window are the categories showing how the SQL Server Installation Center is organized. On the right side of the window are different actions the installer can take.

88

Clicking on Installation on the left pane will display the different installation options. For this, we are clicking on “New SQL Server stand-alone installation or add features to an existing installation”. This will launch the SQL Server 2012 Setup application.

89

On the Setup Support Rules.

90

Check the Setup Support Rules and Click on OK, On the below snapshot select the edition of sql server and click on next.

91

Accept the licence terms and click on next.

92

Check the products updates and click on next.

93

Installing updates.

94

On setup support rules check the rules and click on next.

95

Select SQL Server Feature Installation and click on next.

96

Select features which we have to install for our sql server on the instance feature we have selected required features and click on next.

97

Installation rules checking for visual studio 2010 updates, click on next.

98

Instance configuration, select default instance and click on next.

108

SQL Server checks the available free disk space for installation files. Click on next.

100

SQL Server select service accounts for which account our sql service will run. Click on next.

101

On the database engine configuration select mixed mode authentication and give the password for SA account and specify the sql server installation user. Then click on next.

102

On the reporting service configuration sql server installs reporting service share point integrated mode. Click on next.

103

Specify the user for distributed replay controller and click on next.

104

On the distributed replay client click on next.

105

On error reporting there are no errors so click on next.

106

Installation configuration rules check the rules status and click on next.

107

Check the configuration summary and click to install.

108

On the below snap shot SQL Server has been installed successfully.

109

Now we have to install SQL Server on Windows Server Node2 as well.

After installing SQL Server on both nodes, go to SQL Server Configuration Manager.

110

Right click on SQL Server Service and go to properties, Go to tab of AlwaysOn High Availability and Enable AlwaysOn Availability Group and Click apply and OK.

111

It will ask for restart the service, hit ok and Restart SQL Service.

112

Apply this on both Nodes.

4: Configuring AlwaysOn Availability Group on Node1 and Node2 Server.

Now Connect to SQL Server Management Studio on node1 server and restore AdventureWorks2012 Database where we have to configure AlwaysOn Availability.

Go to Object Explorer, Right Click on Databases and select restore database.

As below snapshot click on device and select the backup file which we have to restore.

Click on OK.

113

After restoration database it will show on object explorer under database tab.

Now we are going to share the read write permission to backup location for node2 server where we are going to configure AlwaysOn replica.

Go to property of folder which we are going to share with node2 server replica.

114

Click on edit, add the node2 server and give the full control as shown in below snapshot. Then click apply and OK.

115

Now on share tab and click to share.

116

Share with Node2.

117

After sharing keep this folder a side and take the backup of database AdventureWorks2012 with this location.

Right Click on database go to task and then hit on backup.

118

Take full database backup for database AdventureWorks2012, make sure database recovery model is full.

119

After completion database backup, go to object explorer in SSMS and right click on AlwaysOn High Availability and select New Availability Group Wizard.

120

Click to next.

121

On specify Availability Group Name we are going to mention the Availability Group Name and click on next.

122

Select the database for configure alwayson availability group and click on next.

Note: For AlwaysOn Configuration database should be in full recovery mode and at-least took a full database backup.

123

Configure replica for always group availability.

124

Configure the Endpoints for AlwaysOn Availability Group.

125

Configure preferred replica for backup.

126

Configure the listener for connecting the database and click to next.

127

Here we are selecting the database synchronization preference.

Note: For full preference we required a file share path which is accessed by all replica’s.

128

Verify validation checks and click to next.

129

Verify the selections which going to configure and click to finish.

130

On the completion wizard check the summary status and close.

131

After refresh the instance, database is in synchronous state, visible Availability Group under AlwaysOn High Availability.

132

5: Failover AlwaysOn Availability Group from Node1 to Node2 replica.

For the AlwaysOn Group failover, Right click on Availability Group under Primary Replica and select Failover.

133

Select node where we have to failover and click to next.

134

Connect the Instance Replica and click on next.

135

Verify the replica and click to finish.

136

Check the roles and close.

137

After refresh the instance we can able to see the failover replica.

138

Congratulations. You have successfully configured and failover AlwaysOn Group Availability on SQL Server 2012 on your personal computer.

If you get stuck with the setup somewhere, please leave a comment here and I will be glad to help. 
Have fun!

2 thoughts on “Microsoft SQL Server 2012 AlwaysOn Group Availability Failover in VMware Workstation

Leave a Reply

Your email address will not be published. Required fields are marked *