Q: How do I enable SQL Server 2012 AlwaysOn replication?
A: SQL Server 2012 AlwaysOn allows synchronous and asynchronous replication of databases between SQL Server instances (even between on-premises ones and those running in Windows Azure IaaS). To enable, you need to follow these steps.
When installing SQL Server, choose the New SQL Server stand-alone installation option (you don't need to select failover cluster options since AlwaysOn doesn't use shared storage nor is an entire SQL Server instance moving between nodes).
Add the Failover Cluster feature to the servers. Create a new cluster with the SQL Servers in it using a file share witness.
Ensure a firewall exception for TCP port 5022 is enabled on all SQL Servers for inbound connections. If you aren't using the same domain account for the SQL Server service on both servers (such as using local system), on each of the SQL Server boxes you must give the connect permission to the endpoint to the machine account of the other SQL Server:
USE master; GO CREATE LOGIN [\ $] FROM WINDOWS; GO GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [ \ $]; GO
If you're unsure of the endpoint name in SQL Server Management Studio, expand Server Objects, Endpoints, Database Mirroring to show the endpoint name.
Next, ensure AlwaysOn Availability Groups is enabled on all the SQL Servers. In SQL Server Management Studio, right-click the server and select properties; select the Is HADR Enabled property. If set to True, AlwaysOn is enabled. If False, it must be enabled via SQL Server Configuration Manager, SQL Server Services; right-click the SQL Server instance "SQL Server (
Before setting up AlwaysOn for a database, ensure a full backup of the database has been taken, otherwise AlwaysOn can't be enabled. Launch the New Availability Group Wizard within Microsoft SQL Server Management Studio,
Click Next to the Introduction screen. Enter a name for the availability group and click Next. Select the databases to replicate with AlwaysOn (see screen shot below) and click Next.
Add additional servers to the availability group and click Next.
Additional options related to the endpoints used for communications and how backups should be performed are available in the other tabs (see screen shot below). A listener is also configurable here, which is required for client communications or it can be added after the wizard is complete.
Specify the data synchronization (which is full by default), specify a share available to all servers, then click Next. A validation is performed; after it's finished, click Next. The Summary is shown and the entire configuration can be output in a script if required. Click Finish to perform the actions. You now have configured replication!
Microsoft has a good article that provides pointers at the TechNet site, and you might also check out Brent Ozar's SQL Server Pro article "Be Invincible with AlwaysOn Availability Groups."
