SQL Server 2012 Always On Technology

In SQL Server 2012 Always On Technology, we have Availability Group option, using which Databases can now be grouped together. When a failure happens, the entire group of database is treated as one. When one fails over they all fail over. This is very powerful when you have an application that uses more than one database in an instance.We are successfully replaced the Database Mirroring and Log Shipping solution ,by use of the Alwayss On Availability Group solution with multiple secondary’s features in SQL Server 2012. We are following combined Shared Storage architecture solution. It is provides an integrated set of options including automatic and manual failover of a group of databases, support for up to four secondary including two synchronous secondary and seamless application failover using availability group listener. We are implemented automatic failover for one important server up to two secondary groups.

Why HA SQL is Important

Highly available (HA) SQL Server services are the cornerstone of many enterprise database applications. Without an HA mechanism for database services, enterprise applications and e-commerce websites cannot offer maximum available uptime. Even perfectly managed servers require periodic restarts for updates and maintenance; and you always need to be prepared for equipment failure such as extended outages of particular servers or disk drives.

The traditional way to offer HA SQL is by creating a SQL Server failover cluster based on shared storage. That is, a storage area network (SAN) presents shelves of disk drives to two or more servers at the same time ("shared storage"). The SAN and shared storage is often the most expensive component in the datacenter.

SQL Server 2012 AlwaysOn, no need for SAN

With SQL Server 2012 AlwaysOn, two or more complete copies of each HA database can exist, synchronized by AlwaysOn technology. The independent database copies are presumed to exist locally in

direct attached storage (DAS) on each SQL node, or over on the network using economical Windows Server 2012 SMB 3.0 file shares on dedicated Windows Server 2012 file servers.

We can use Windows Server 2012 Standard for the SQL server nodes-unlike in previous Windows releases, you can enable the failover cluster feature in the Standard edition of Windows Server 2012 as well as Windows Server 2012 Datacenter.

SQL Server 2012 does require the Enterprise edition of SQL Server 2012 to use the AlwaysOn feature.

Steps to deploy a SQL Server 2012 AlwaysOn Availability Group

What We’re Going To Build

My imaginary business in Atlanta is running a mission-critical CRM application that need to be online at all times. We need to be able to tolerate the following problems:

If the production server dies, I need to fail over automatically to a second server in the same Savis DC datacenter. (This also means the production and secondary servers can’t be on the same SAN, because SANs do fail.)

If the datacenter goes down, I need to fail over to a DR Datacenter offsite in DC. The business is okay with losing some data in the event of a failure that large, and they’re okay with this failover being a manual process.

The BI team wants to run reports on the live database with as little delay as possible. They know they should be building a separate warehouse, but they’re too busy, and they want this to work right now.

Backups need to run faster, and ideally, I don’t want to run backups on my main production server.

To solve this, I’m going to implement four SQL Servers with Availability Groups. The four servers will be:

SQL2012PROD1 – the primary production SQL Server in Atlanta. Data will be stored on a SAN.

SQL2012PROD2 – the secondary production SQL Server in DC. It’s located in the next rack over, and it’s got all local storage so that we can tolerate a SAN failure without changing datacenters. We’ll be doing synchronous commits between SQL2012PROD1 and SQL2012PROD2, which means I do still need fast storage on #2 – a good fit for SSDs.

SQL2012DR1 – an offsite SQL Server in Australia. Because the business is okay with some data loss in the event of a complete Atlanta datacenter failure, we’ll be doing asynchronous commits to this server.

SQL2012RPT1 – a read-only SQL Server in atlanta. It can be on any storage (SAN or local). We’ll be doing asynchronous commits here, which means we might be running 5-60 seconds behind depending on load, but that’s still more than current enough for the BI team’s reports. After hours, when users aren’t running reports, we’ll run full backups from this server.

Preparing your SQL Server for AlwaysOn

SQL2012PROD1 DNS Name: SQL2012PROD1.merial.com IP Address: need to take it from windows team SQL Service Account: nam\sqlsupervisor

SQL2012PROD2 DNS Name: SQL2012PROD2.merial.com IP Address: need to take it from windows team SQL Service Account: nam\sqlsupervisor

SQL2012DR1 DNS Name: SQL2012DR1.merial.com IP Address: need to take it from windows team SQL Service Account: nam\sqlsupervisor

SQL2012RPT1 DNS Name: ag-node4.sqlpassion.com IP Address: need to take it from windows team SQL Service Account: nam\sqlsupervisor

Prerequisites: Before We Install SQL Server 2012

We can use Windows Server 2012 Standard for the SQL server nodes-unlike in previous Windows releases, you can enable the failover cluster feature in the Standard edition of Windows Server 2012 as well as Windows Server 2012 Datacenter.I strongly recommend using the same drive letters across all servers: for example, if you want to use E for apps, F for data, and G for logs, then all four servers need to use those same drive letters. If the production server has an H drive that no other server has, and we add a data file on the H drive, then that alter-database statement will fail across the rest of our servers – instantly marking their databases as suspect. We can fix that by restoring files to the other servers manually, but the whole point of our solution is to make it easy and automatic, so keep those drive letters identical from the start.

Each server needs the .NET Framework 3.5.1 feature and the Failover Clustering feature already installed. To install these features, go into Server Manager, Features, and click Add Feature. In the Add Features wizard, check the boxes for .NET Framework 3.5.1 and Failover Clustering:

Adding the Features

After those two features are installed on all of our servers, we need to configure the cluster. Go into Control Panel, Administrative Tools, Failover Cluster Manager. Click Validate a Configuration, and enter the names for your SQL Servers.

Validate a Configuration Wizard

Note that all four of my servers are in the same domain. You can’t use servers in different AD forests here.

Click Next through the wizard, and you’ll be asked what tests you want to run. I run all tests every time, but technically, you could skip the storage tests if you’re only using Availability Groups. These servers will not be sharing storage, so I don’t need to test whether any of them can see each others’ drives. If you run all tests, you’ll see warnings in the Storage section:

Whether you pass or fail, click the View Report button to get more insight on your servers. A detailed HTML report pops up to give you the skinny:

Network Warnings

The Network section of validation is giving me warnings because I’ve got a few single points of failure. Each of my servers only has one network card and no network teaming. If a network card went bad, if someone tripped over a patch cable, or if somebody assigned an IP address that duplicated my SQL Server’s, then one of my nodes would go down. In a real production environment, you’d want to address these issues.

If you got errors that you want to change, go fix them and rerun validation. Otherwise, if you don’t see any showstopper errors, close the validation report and click the “Create the cluster now using the validated nodes” link.

Creating The Cluster

The Create Cluster Wizard will ask for the new cluster’s name, IP address, and network. The cluster is like a virtual server that doesn’t really exist, but just gets passed around from server to server depending on who’s in charge.

Cluster Name and IP Address

The cluster’s name is not the name of the SQL Server.

In my example, I’m naming my cluster SQL2012Cluster1. I’m only naming it that because all of my machines are named SQL2012*. Your naming scheme may vary, but I just wanna drive that point home that the cluster name doesn’t really have anything to do with SQL Server.

After the wizard finishes, Failover Cluster Manager will show our newly created cluster with four nodes, a network, and no services or applications.

Next: The SQL Server 2012 Setup

Download SQL Server 2012 Enterprise Edition and start the setup on one of the Windows machines. The very first screen in setup is just a little tricky because it offers one link for “New SQL Server stand-alone installation” and another for “New SQL Server failover cluster installation.” The one we want is stand-alone.

Installation Choices

In the Setup Role step, choose SQL Server Feature Installation.

In the Feature Selection step, the only feature you absolutely need is Database Engine, but here’s what I choose for lab boxes:

The Usual Suspects

In real life, I wouldn’t include Distributed Replay on my production servers, but in a 2012 lab environment, it’s a fun feature to start playing with. Distributed Replay holds the promise of making load testing easier.

In the Server Configuration step, specify a domain account for SQL Server to use. you should use the same service account for all instances in the same AvailabilityGroup.

The screenshot shows that I use the server name as part of the service account name. My personal naming standard is:

‘svc’ – all my service accounts start with this so I can quickly pick them out of AD ‘MachineName’ – because I use a different account per Windows machine for each service ‘ServiceName’ – because I might be running multiple services on the same Windows box

So in the example below, I’m using LAB\svcSQL2012PROD1_SQL for the database service accounts on SQL2012PROD1.

Service Accounts

In the Database Engine Configuration step, make sure to click Add Current User as an administrator for the database engine. Also in this step, click on the Data Directories tab and configure the default file paths. Use the same paths across all of the SQL Server instances we’re

using for AlwaysOn.

Installation Paths

Next your way through the installation, and a few minutes later, you’ll have yourself one working SQL Server. Repeat that process for all of your 2012 machines.

Configuring SQL Server 2012

By default, SQL Server does not accept remote connections. This is because end users are the root of all our problems. Click Start, Programs, Microsoft SQL Server 2012, Configuration Tools, SQL Server Configuration Manager. Drill into SQL Server Network Configuration, click on Protocols for MSSQLSERVER, and double-click on the TCP/IP entry. Change the “Enabled” option to Yes, and click OK.

Back in Configuration Manager, enable AlwaysOn by clicking SQL Server Services in the left pane, then double-click SQL Server. Click on the AlwaysOn High Availability tab.

On and On Like Donkey Kong to the Break of Dawn

Click OK. Our changes won’t take effect until we restart the SQL Server instance, so now’s a good time to do that. Repeat this process for all of your 2012 instances.

Finally! Let’s Play with Availability Groups

Back to SQL Server Management Studio. On the main production server (in my case, SQL2012PROD1), set yourself up a few lab databases – either create a few new databases or restore databases from your other servers. If you create new databases, take a full backup of each database before we go on. It’s not that I treasure your work, but rather your newly created databases aren’t really in full recovery mode until the first full backup. (And yes, the Availability Groups setup wizard checks for this.)

In SSMS, go into the main production server, click Management, right-click on Availability Groups, and click New Availability Group Wizard. We ’ll be presented with a list of databases that you can include in the Availability Group. Keep in mind that all of the databases in the Availability Group will be failed over from your primary server to your secondary server together, automatically. In my case, my accounting application consists of the databases

Accounting, AccountingPayroll, and AccountingReports. I have some applications that make cross-database SELECT queries for reporting purposes, so I want to make sure they all fail over together.

Picking Databases for the Availability Group

Click Next, and you’ll be able to specify which servers are part of the group and what their roles are. This below screen has so much of what I’ve always wanted in a database product. I’ll need just a moment by myself. Excuse me.

Initial Role – SQL2012PROD1 will be my Primary instance because it’s got the current live copy of the data.

Replica Mode – PROD1 and PROD2 will do Automatic Failover. Only two instances can be involved in Automatic Failover at a time. For my business configuration, DR1 and RPT1 are in High Performance mode, which is akin to asynchronous database mirroring. High Safety is also an option, meaning synchronous mirroring.

explanation to get to this point, but it’s way easier than any other scale-out method I’ve seen. It’s all in the GUI, for crying out loud.

Click Next, and the next screen will set up a listener. You can just take the defaults here.

On the Select Initial Data Synchronization screen, put in the UNC path for the file share that you set up for all of the SQL Servers to access. The production server is going to take a backup to this path, and the replicas will restore that backup. You can also skip this step entirely if you’d rather initialize the replicas yourself, and in many production environments, you’ll need to do that. For example, environments with SAN snapshot capabilities or small WAN pipes will need customization.

Next your way through the wizard and validation, and next thing you know, you’ve got an Availability Group.  Right-click on your new Availability Group and click Show Dashboard. Presto:

What to Play With Next

Here’s some of the cool things you can experiment with:

Take your full and transaction log backups on a replica Query the replicas Run DBCC on a replica Query the new DMVs for AlwaysOn features Remove a replica, and add a different server as a replica Automatic page repair – 2012 automatically detects corrupt pages on the replicas and repairs

them by fetching a good copy from any other instance Replication – Availability Groups can include a replication publisher, but not the distributor