Skip to main content

Posts

Showing posts from January, 2013

SQL 2012 AlwaysOn - Recovery from a Disaster

We have configured 3 SQL server in an AlwaysOn Availability group (see  here) Two servers (SQLDEV1 and SQLDEV2) are configured for automatic failover. To test how long it takes for SQL Server to fail over from SQLDEV1 to SQLDEV2 we connect to the availability group listener name (sqldevcluster1) and on the "Additional Connection Parameters" tab we need to add Multisubnetfailover=TRUE We can check the current status of the servers in the availability group with this query select rs . role_desc , rs . connected_state_desc , rs . synchronization_health_desc , cs . replica_server_name from sys . dm_hadr_availability_replica_states AS rs JOIN sys . dm_hadr_availability_replica_cluster_states as CS ON rs . replica_id = cs . replica_id and it returns If we connect to SQLDEV2 and initiate manual failover ALTER AVAILABILITY GROUP AG1 FAILOVER ; this statement takes 17 seconds to complete. And if we check the status of the a

SQL 2012 AlwaysOn: Configuring 2 Synchronous and 1 Asynchronous Replicas

To configure highly available and disaster resilient  databases with SQL 2012 AlwaysOn, 3 servers will be used as show below In the Primary Data Center SQLDEV1 and SQLDEV2 will host synchronous replica of the databases and in the Secondary Data Center on SQLDEV3 an asynchronous replica will reside. To enable AlwaysOn, SQL Server must be installed on a node in the Windows Failover Cluster (WFC) (how to install 3-Node WFC on Windows Server 2012  see  here  ). SQL 2012 needs .NET 3.5. So the first step is to add .NET Framework 3.5 on each of the three servers. The next step is to install SQL 2012 on each of the servers. Even if these servers are part of the WFC the stand-alone installation is required (this WFC has no shared storage and this won't allow you to install SQL failover cluster). When SQL 2012 is installed we need to enable AlwaysOn and Named Pipes. In SQL Server Configuration Manager right-click on the SQL Server and on the "AlwaysOn High Availability&qu

SQL 2012 AlwaysOn: Synchronous vs. Asynchronous commit. Performance impact

Recently I've had a chance to build a 3-server AlwaysOn environment distributed between the primary and secondary data centers. The configuration looks like this: Primary Data Center                         Secondary Data Center                        SQLDEV1                                        SQLDEV3          SQLDEV2 The availability group was crated with synchronous commit replicas on SQLDEV1 and SQLDEV2 and the replica on SQLDEV3 was configured for asynchronous commit. The link between the data centers was not great and when I pinged SQLDEV3 from SQLDEV1 I got these results Approximate round trip times in milli-seconds:     Minimum = 39ms, Maximum = 63ms, Average = 42ms I also created a very simple application that: - generates a random INT - if the table has a record with such key its updates the record otherwise it adds a new record to the table. - every 5 seconds the app also records to a  different (result)  table StartTime, EndTime, and total numbe