SQL Server 2012 : Availability Groups Creation Error

Views 406

If you are looking at implementing a High Available / Disaster Recovery Solution using SQL Server 2012’s AlwaysOn Availability Groups then you will have already built your Windows Server Failover Cluster (WSFC), Installed and configured SQL Server 2012 on each of the Replica’s in the WSFC and having identified the databases that you wish to participate.

If you have not configured your environment yet then have a read of my article taking you through the How To Steps in my Article SQL Server 2012 AlwaysOn.

Configuring Availability Groups

Now that you are ready to create your Availability Group(s), you have a couple of choice available to you to create them. The simplest to start with is using the Wizard available in Management Studio. Recently on a client site while creating a Proof of Concept (POC) using these new features I received this error while using the Wizard.

Windows-Live-Writer-SQL-Server-2012--Availability-Groups-Cre_11F83-image_2

When clicking on the error hyper link to see what the error was the following error details are shown:

Windows-Live-Writer-SQL-Server-2012--Availability-Groups-Cre_11F83-image_4

This error is telling you that the directory structure that exists on the primary replica is different to the directory structure for the data files and possibly t-log files on the secondary server. This prevents you from progressing with the client. This did not surprise me as the Primary server was utilising a Named Instance and the the secondary server was utilising a default instance.

The reason this error comes about is because using the Wizard, the databases that you have included into the Availability Group need to be initialised on the secondary server(s). To achieve this the Wizard is performing a full backup , restoring the database leaving it in a non-recoverable state, performs a TLog backup to capture the latest lot of transactions to ensure that the database is synchronised. However to achieve this the directory structures need to be the same.

To get past this error, you can go onto your secondary server and create the directory structure to match the primary server. Re-Run the validation checks, the checks will be passed giving you the ability to click Next. On the Summary Window you will find the Script Button:

Windows-Live-Writer-SQL-Server-2012--Availability-Groups-Cre_11F83-image_6

At this point in time do not Click the Finish Button as this will restore the database into the directory structure that you have just created. However this is not where you want the data files and log files to be placed. You want to script out the process. Once scripted you will be able to find the section that has the Restore Database and include the With Move to place the data files and log files in the appropriate location on each of the secondary servers.

Now that you have made the changes to the script for the locations, you can run the creation script manually.

Alternatively to ensure that you do not receive this error, then have all of your Instances participating to be named the same. Ie Default, Default.

I hope this helps you get past this issue and good luck with your SQL Server 2012 Availability Groups.

0

Your Cart