Infrastructure at your Service

Stéphane Haby

SQL Server 2012 ‘Denali’ : AlwaysOn High Availability – Restore is blocked

During our test to create a failover clustering with the new function ‘AlwaysOn High Availability’, we had a few difficulties to overcome like the databases restore on the secondary cluster node.

In this article, we will explain how to avoid it before encountering the problem.

Environment

VMTESTSQL02 is an AD-DC (Active Directory – Domain Controller) server (It should not be confused with ACDC, the Hard Rock Group :roll:) with the domain TESTSQL.
VMTESTSHPT01 is a SQL Server ‘Denali’ with AdventureWork and is the Primary Server for the Failover Clustering
VMTESTSQL03 is a SQL Server ‘Denali’ without database and will be the Secondary Server for the Failover Clustering

The Cluster VMTESTSQL is a Windows Cluster and not a SQL Server cluster.

00-Denali-archi

Installation

For the SQL Server installation, we install a new SQL Server in stand-alone mode.

The most important for this article is the account name for the SQL Server Database Engine Services in the Server Configuration page within the installation.

Note that by default the services are secured through their account.
They are no needs to create Windows service accounts unlike with previous versions.

SQL Server Database Engine Service

If you open your Sql Server Configuration Manager, you can see that SQL Server Database Engine Services run with the account NT ServiceMSSQL$.

To create an AlwaysOn High Availability Group, you must enable it on the service property like this example:

Logins Configuration

If you look in SSMS (Sql Server Management Studio), the Server Roles for the account NT ServiceMSSQL$, you can see that this account is sysadmin and have all right of the instance.

Create a ‘AlwaysOn High Availability’ Group

For the Step 2,3 and 4, it’s the same for the 2 “SQL Server 2012” Servers.
To create the ‘Availability Group’, we launch the SSMS of VMTESTSHPT01 with the database AdventureWork.

  • Step 1 : Choose Availability group name

  • Step 2 : Select databases for the Availability Group

  • Step 3 : Specify the Primary and the Secondary Server

  • Step 4 : Create your Availability Group Listener

  • Step 5 : Select a shared network location for the backup

To make sure that we have the appropriated rights on this share, we shared it with read/write rights for everyone.

  • Step 6 : Test the validity of the configuration

  • Step 7 : Verify the configuration

Before you click on the Finish button, create the script from your Availabily Group.
This is to play again your scenario or to create a template with sqlcmd variables.

  • Step 8 : Creating the Availabily Group

The last step is the creation itself. If you look the detail, you can see that the last step failed.

Here are the errors details :

Verify the Services on the cluster

It’s running…

Verify the Secondary server

We can see that the database AdventureWorks stay in a restoring mode in the server VMTESTSQL03.

Why it doesn’t work ?

The account NT ServiceMSSQL$ is sysadmin but have not the right to restore a database from a shared directory.
The first test is to grant the account with the role dbcreator. This is just to verify but it doesn’t work.

The point is that for database mirroring, Domain service accounts is a prerequisite :

http://msdn.microsoft.com/en-us/library/ff878487(v=SQL.110).aspx

Finnally, we have change the account to a domain account for the 2 servers.
If you don’t change it for both servers, you have the same problem.

17-Denali-changeAccount

Take care, this operation needs a restart of the service.

Add the user in the instance with sysadmin server role:

And finally, it’s magic,  your failover is operational!

You can see of the secondary server and the database is online.. That’s running:-D

sys.dm_server_services : A new DMV

Before concluding this article, here is a new dynamic management view introduced by SQL Server 11 CTP3 which returns property information associated with the SQL Server services like SQL Server, Full-Text, and SQL Server Agent services in the current instance of SQL Server.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant