SQL Server AlwaysOn

Installing/configuring SQL Server 2014 AlwaysOn Availability Group

The basic desired scenario was a configuration with a primary database (writeable) and a secondary (readable) replica, targeting reports (no actual clustering/fail-over).

The main goal of this post is to point out some prerequisites/main steps not always obvious/present in internet posts/youtube videos.

Installing WSFC (Windows Server fail-over Cluster)

Run the configuration steps as an user with rights to create AD objects. Being a local admin will not suffice. Install first, on both servers (Win 2012 Standard in my case) Failover Cluster feature.

From one of the servers (I run it from main server, I suppose there is no difference) run Create Cluster from Failover Cluster Management.

Add both servers, run validation (for sure you will get some warnings about storage since we do not have any common storage, maybe about the network, as a single point of failure and some warnings about not having the same updates applied on both server; ignore them).

Create the cluster; since in my infrastructure I had DHCP enabled no cluster IP configuration was required.

Configuring AlwaysOn

The operations described below were done with an domain account, member of local Adminsitrators Windows Group and member of sysadmin SQL Server group.

On each server:

• Open for inbound access TCP port 5022
• Have the same directory structure where databases file support will be installed
• Have installed SQL Server 2014, Enterprise or Developer
• From SQL Server configuration->SQL Server Configuration Manager->SQL Server Service->Properties->AlwaysOn High Availability tab->Enable AlwaysOn Availability Groups
• Restart SQL Server service

On main server (my choice, can be anywhere in the LAN):

• create a share \\Server\MyShare with everyone full access (this is required for initial synchronization since we want to leave the default SQL service user assignment (NT Service\MSSQLSERVER)).

Now, on main server open SQL Enterprise Management Studio, go to AlwaysOn High Availability->Availability Groups->New Availability Group Wizard. Choose the database, secondary replica, share for initial synchronization, Endpoint URL, etc. I set (for both servers):

• Availability Mode to Synchronous (virtual servers are in the same ESX)
• Failover to Manual
• Availability to Allow all Connections
• Readable Secondary Yes

Backup preferences was set to Primary since I already had a full backup daily/log backup every 15 minutes Maintenance Jobs on primary

When creating the group initial join for secondary replica will fail since (as stated above) we will not change the default user accounts for SQL Service.

Go on each server and create/grant rights for pair computer accounts by running:

 /*
 USE master;
 GO
 SELECT name, state_desc, port from sys.tcp_endpoints where type_desc='DATABASE_MIRRORING'
 GO
*/
 USE master;
 GO
 CREATE LOGIN [DOMAIN\SERVER$] FROM WINDOWS;
 GO
 GRANT CONNECT on ENDPOINT::Hadr_endpoint TO [DOMAIN\SERVER$];
 GO

Read more on https://blogs.msdn.microsoft.com/svarukala/2014/03/31/sql-alwayson-failed-to-join-the-database-to-the-availability-group-error-35250/

Go back to SQL Enterprise management studio and manually join the secondary database.

Now we should have a working AlwaysOn Availability Group.

A final challenge now: all database objects are automatically synchronised (DML as well as DDL). What about logins?

We want to add a secondarylogin that has read only access to the database, mainly for accessing it on secondary replica. By adding the login on primary server the AlwaysOn technology will synchronously create the login in secondary database but...no surprise...will not create the SQL Server wide login; so we will get a 'traditional' orphan login. Since the database is read only we can not use the standard fix for orphan users. So, read https://support.microsoft.com/en-us/kb/918992 on how to create clone a SQL Server login from one instance to another (altogether with SID and password hash).

IyBJbnN0YWxsaW5nL2NvbmZpZ3VyaW5nIFNRTCBTZXJ2ZXIgMjAxNCBBbHdheXNPbiBBdmFpbGFiaWxpdHkgR3JvdXANCg0KVGhlIGJhc2ljIGRlc2lyZWQgc2NlbmFyaW8gd2FzIGEgY29uZmlndXJhdGlvbiB3aXRoIGEgcHJpbWFyeSBkYXRhYmFzZSAod3JpdGVhYmxlKSBhbmQgYSBzZWNvbmRhcnkgKHJlYWRhYmxlKSByZXBsaWNhLCB0YXJnZXRpbmcgcmVwb3J0cyAobm8gYWN0dWFsIGNsdXN0ZXJpbmcvZmFpbC1vdmVyKS4gDQoNClRoZSBtYWluIGdvYWwgb2YgdGhpcyBwb3N0IGlzIHRvIHBvaW50IG91dCBzb21lIHByZXJlcXVpc2l0ZXMvbWFpbiBzdGVwcyBub3QgYWx3YXlzIG9idmlvdXMvcHJlc2VudCBpbiBpbnRlcm5ldCBwb3N0cy95b3V0dWJlIHZpZGVvcy4NCg0KIyMgSW5zdGFsbGluZyBXU0ZDIChXaW5kb3dzIFNlcnZlciBmYWlsLW92ZXIgQ2x1c3RlcikNCg0KUnVuIHRoZSBjb25maWd1cmF0aW9uIHN0ZXBzIGFzIGFuIHVzZXIgd2l0aCByaWdodHMgdG8gY3JlYXRlIEFEIG9iamVjdHMuIEJlaW5nIGEgbG9jYWwgYWRtaW4gd2lsbCBub3Qgc3VmZmljZS4NCkluc3RhbGwgZmlyc3QsIG9uIGJvdGggc2VydmVycyAoV2luIDIwMTIgU3RhbmRhcmQgaW4gbXkgY2FzZSkgRmFpbG92ZXIgQ2x1c3RlciBmZWF0dXJlLg0KDQpGcm9tIG9uZSBvZiB0aGUgc2VydmVycyAoSSBydW4gaXQgZnJvbSBtYWluIHNlcnZlciwgSSBzdXBwb3NlIHRoZXJlIGlzIG5vIGRpZmZlcmVuY2UpIHJ1biBDcmVhdGUgQ2x1c3RlciBmcm9tIEZhaWxvdmVyIENsdXN0ZXIgTWFuYWdlbWVudC4NCg0KQWRkIGJvdGggc2VydmVycywgcnVuIHZhbGlkYXRpb24gKGZvciBzdXJlIHlvdSB3aWxsIGdldCBzb21lIHdhcm5pbmdzIGFib3V0IHN0b3JhZ2Ugc2luY2Ugd2UgZG8gbm90IGhhdmUgYW55IGNvbW1vbiBzdG9yYWdlLCBtYXliZSBhYm91dCB0aGUgbmV0d29yaywgYXMgYSBzaW5nbGUgcG9pbnQgb2YgZmFpbHVyZSBhbmQgc29tZSB3YXJuaW5ncyBhYm91dCBub3QgaGF2aW5nIHRoZSBzYW1lIHVwZGF0ZXMgYXBwbGllZCBvbiBib3RoIHNlcnZlcjsgaWdub3JlIHRoZW0pLiANCg0KQ3JlYXRlIHRoZSBjbHVzdGVyOyBzaW5jZSBpbiBteSBpbmZyYXN0cnVjdHVyZSBJIGhhZCBESENQIGVuYWJsZWQgbm8gY2x1c3RlciBJUCBjb25maWd1cmF0aW9uIHdhcyByZXF1aXJlZC4NCg0KIyMgQ29uZmlndXJpbmcgQWx3YXlzT24NCg0KVGhlIG9wZXJhdGlvbnMgZGVzY3JpYmVkIGJlbG93IHdlcmUgZG9uZSB3aXRoIGFuIGRvbWFpbiBhY2NvdW50LCBtZW1iZXIgb2YgbG9jYWwgQWRtaW5zaXRyYXRvcnMgV2luZG93cyBHcm91cCBhbmQgbWVtYmVyIG9mIHN5c2FkbWluIFNRTCBTZXJ2ZXIgZ3JvdXAuDQoNCk9uIGVhY2ggc2VydmVyOg0KLSBPcGVuIGZvciBpbmJvdW5kIGFjY2VzcyBUQ1AgcG9ydCA1MDIyDQotIEhhdmUgdGhlIHNhbWUgZGlyZWN0b3J5IHN0cnVjdHVyZSB3aGVyZSBkYXRhYmFzZXMgZmlsZSBzdXBwb3J0IHdpbGwgYmUgaW5zdGFsbGVkDQotIEhhdmUgaW5zdGFsbGVkIFNRTCBTZXJ2ZXIgMjAxNCwgRW50ZXJwcmlzZSBvciBEZXZlbG9wZXINCi0gRnJvbSBTUUwgU2VydmVyIGNvbmZpZ3VyYXRpb24tPlNRTCBTZXJ2ZXIgQ29uZmlndXJhdGlvbiBNYW5hZ2VyLT5TUUwgU2VydmVyIFNlcnZpY2UtPlByb3BlcnRpZXMtPkFsd2F5c09uIEhpZ2ggQXZhaWxhYmlsaXR5IHRhYi0+RW5hYmxlIEFsd2F5c09uIEF2YWlsYWJpbGl0eSBHcm91cHMNCi0gUmVzdGFydCBTUUwgU2VydmVyIHNlcnZpY2UNCg0KT24gbWFpbiBzZXJ2ZXIgKG15IGNob2ljZSwgY2FuIGJlIGFueXdoZXJlIGluIHRoZSBMQU4pOg0KLSBjcmVhdGUgYSBzaGFyZSBcXFxcU2VydmVyXFxNeVNoYXJlIHdpdGggZXZlcnlvbmUgZnVsbCBhY2Nlc3MgKHRoaXMgaXMgcmVxdWlyZWQgZm9yIGluaXRpYWwgc3luY2hyb25pemF0aW9uIHNpbmNlIHdlIHdhbnQgdG8gbGVhdmUgdGhlIGRlZmF1bHQgU1FMIHNlcnZpY2UgdXNlciBhc3NpZ25tZW50IChOVCBTZXJ2aWNlXE1TU1FMU0VSVkVSKSkuDQoNCk5vdywgb24gbWFpbiBzZXJ2ZXIgb3BlbiBTUUwgRW50ZXJwcmlzZSBNYW5hZ2VtZW50IFN0dWRpbywgZ28gdG8gQWx3YXlzT24gSGlnaCBBdmFpbGFiaWxpdHktPkF2YWlsYWJpbGl0eSBHcm91cHMtPk5ldyBBdmFpbGFiaWxpdHkgR3JvdXAgV2l6YXJkLg0KQ2hvb3NlIHRoZSBkYXRhYmFzZSwgc2Vjb25kYXJ5IHJlcGxpY2EsIHNoYXJlIGZvciBpbml0aWFsIHN5bmNocm9uaXphdGlvbiwgRW5kcG9pbnQgVVJMLCBldGMuIA0KSSBzZXQgKGZvciBib3RoIHNlcnZlcnMpOg0KDQotIEF2YWlsYWJpbGl0eSBNb2RlIHRvIFN5bmNocm9ub3VzICh2aXJ0dWFsIHNlcnZlcnMgYXJlIGluIHRoZSBzYW1lIEVTWCkgDQotIEZhaWxvdmVyIHRvIE1hbnVhbA0KLSBBdmFpbGFiaWxpdHkgdG8gQWxsb3cgYWxsIENvbm5lY3Rpb25zDQotIFJlYWRhYmxlIFNlY29uZGFyeSBZZXMNCg0KQmFja3VwIHByZWZlcmVuY2VzIHdhcyBzZXQgdG8gUHJpbWFyeSBzaW5jZSBJIGFscmVhZHkgaGFkIGEgZnVsbCBiYWNrdXAgZGFpbHkvbG9nIGJhY2t1cCBldmVyeSAxNSBtaW51dGVzIE1haW50ZW5hbmNlIEpvYnMgb24gcHJpbWFyeQ0KDQpXaGVuIGNyZWF0aW5nIHRoZSBncm91cCBpbml0aWFsIGpvaW4gZm9yIHNlY29uZGFyeSByZXBsaWNhIHdpbGwgZmFpbCBzaW5jZSAoYXMgc3RhdGVkIGFib3ZlKSB3ZSB3aWxsIG5vdCBjaGFuZ2UgdGhlIA0KZGVmYXVsdCB1c2VyIGFjY291bnRzIGZvciBTUUwgU2VydmljZS4NCg0KR28gb24gZWFjaCBzZXJ2ZXIgYW5kIGNyZWF0ZS9ncmFudCByaWdodHMgZm9yIHBhaXIgY29tcHV0ZXIgYWNjb3VudHMgYnkgcnVubmluZzoNCg0KYGBgU1FMDQogLyoNCiBVU0UgbWFzdGVyOw0KIEdPDQogU0VMRUNUIG5hbWUsIHN0YXRlX2Rlc2MsIHBvcnQgZnJvbSBzeXMudGNwX2VuZHBvaW50cyB3aGVyZSB0eXBlX2Rlc2M9J0RBVEFCQVNFX01JUlJPUklORycNCiBHTw0KKi8NCiBVU0UgbWFzdGVyOw0KIEdPDQogQ1JFQVRFIExPR0lOIFtET01BSU5cU0VSVkVSJF0gRlJPTSBXSU5ET1dTOw0KIEdPDQogR1JBTlQgQ09OTkVDVCBvbiBFTkRQT0lOVDo6SGFkcl9lbmRwb2ludCBUTyBbRE9NQUlOXFNFUlZFUiRdOw0KIEdPDQpgYGANClJlYWQgbW9yZSBvbiBbaHR0cHM6Ly9ibG9ncy5tc2RuLm1pY3Jvc29mdC5jb20vc3ZhcnVrYWxhLzIwMTQvMDMvMzEvc3FsLWFsd2F5c29uLWZhaWxlZC10by1qb2luLXRoZS1kYXRhYmFzZS10by10aGUtYXZhaWxhYmlsaXR5LWdyb3VwLWVycm9yLTM1MjUwL10oaHR0cHM6Ly9ibG9ncy5tc2RuLm1pY3Jvc29mdC5jb20vc3ZhcnVrYWxhLzIwMTQvMDMvMzEvc3FsLWFsd2F5c29uLWZhaWxlZC10by1qb2luLXRoZS1kYXRhYmFzZS10by10aGUtYXZhaWxhYmlsaXR5LWdyb3VwLWVycm9yLTM1MjUwLykNCg0KR28gYmFjayB0byBTUUwgRW50ZXJwcmlzZSBtYW5hZ2VtZW50IHN0dWRpbyBhbmQgbWFudWFsbHkgam9pbiB0aGUgc2Vjb25kYXJ5IGRhdGFiYXNlLg0KDQpOb3cgd2Ugc2hvdWxkIGhhdmUgYSB3b3JraW5nIEFsd2F5c09uIEF2YWlsYWJpbGl0eSBHcm91cC4NCg0KQSBmaW5hbCBjaGFsbGVuZ2Ugbm93OiBhbGwgZGF0YWJhc2Ugb2JqZWN0cyBhcmUgYXV0b21hdGljYWxseSBzeW5jaHJvbmlzZWQgKERNTCBhcyB3ZWxsIGFzIERETCkuIFdoYXQgYWJvdXQgbG9naW5zPw0KDQpXZSB3YW50IHRvIGFkZCBhIGBzZWNvbmRhcnlsb2dpbmAgdGhhdCBoYXMgcmVhZCBvbmx5IGFjY2VzcyB0byB0aGUgZGF0YWJhc2UsIG1haW5seSBmb3IgYWNjZXNzaW5nIGl0IG9uIHNlY29uZGFyeSByZXBsaWNhLiBCeSBhZGRpbmcgdGhlIGxvZ2luIG9uIHByaW1hcnkgc2VydmVyIHRoZSBBbHdheXNPbiB0ZWNobm9sb2d5IHdpbGwgc3luY2hyb25vdXNseSBjcmVhdGUgdGhlIGxvZ2luIGluIHNlY29uZGFyeSBkYXRhYmFzZSBidXQuLi5ubyBzdXJwcmlzZS4uLndpbGwgbm90IGNyZWF0ZSB0aGUgU1FMIFNlcnZlciB3aWRlIGxvZ2luOyBzbyB3ZSB3aWxsIGdldCBhICd0cmFkaXRpb25hbCcgb3JwaGFuIGxvZ2luLiBTaW5jZSB0aGUgZGF0YWJhc2UgaXMgcmVhZCBvbmx5IHdlIGNhbiBub3QgdXNlIHRoZSBzdGFuZGFyZCBmaXggZm9yIG9ycGhhbiB1c2Vycy4NClNvLCByZWFkIFtodHRwczovL3N1cHBvcnQubWljcm9zb2Z0LmNvbS9lbi11cy9rYi85MTg5OTJdKGh0dHBzOi8vc3VwcG9ydC5taWNyb3NvZnQuY29tL2VuLXVzL2tiLzkxODk5Mikgb24gaG93IHRvIGNyZWF0ZSBjbG9uZSBhIFNRTCBTZXJ2ZXIgbG9naW4gZnJvbSBvbmUgaW5zdGFuY2UgdG8gYW5vdGhlciAoYWx0b2dldGhlciB3aXRoIFNJRCBhbmQgcGFzc3dvcmQgaGFzaCku