SQL SERVER – How To Improve Performance by Offloading Backups to a Secondary Replica – Notes from the Field #108

[Notes from Pinal]: If we get one server, we want two servers, and if we get two servers, we want four servers. If we know we are going to get only two servers, we try our best to get maximum out of our available server. Maximum utilization of resources is always our primary goal. In this blog post we are going to talk about almost the same story where we try to get maximum out of our servers. Let us assume that we have two servers, how do we get maximum performance from them. Well, our generic answer would be that we will keep the most important task on our primary server and move all the not so important task on secondary server. This is common sense and essential too. This is when I reached out to Eduardo and asked him what can we do to make our primary server faster by offloading backups to secondary replica.

Eduardo SQL SERVER   How To Improve Performance by Offloading Backups to a Secondary Replica   Notes from the Field #108Linchpin People are database coaches and wellness experts for a data driven world. In this 108th episode of the Notes from the Fields series database expert Eduardo Castro (partner at Linchpin People) shares very interesting conversation related to how to improve performance of SQL Server by offloading backups to a secondary replica in SQL Server standard edition.


Microsoft introduced AlwaysOn in SQL Server 2012 as a way to bring a high availability option to scenarios where the database administrator doesn’t have a SAN.

AlwaysOn is based on the concept of availability groups that support the grouping of several user databases that can fail over together to other server in case of an interruption of the main server. Each availability group defines partners known as availability replicas. Each replica is part of the availability group and is hosted in a separate instance of SQL Server.

The following picture shows a basic configuration with one primary and two replicas:

108 1 SQL SERVER   How To Improve Performance by Offloading Backups to a Secondary Replica   Notes from the Field #108

One of the main features of AlwaysOn, besides the high availability scenarios, is the option to have active secondary replicas.

Two common questions I get after my sessions on this topic are:

  1. How to improve OLTP performance in scenarios where there is a lot of reporting being done during peak hours?
  2. How to improve the speed of backups without affecting our main server throughput?

This is where AlwaysOn Active Secondary Replicas come to work. Basically in AlwaysOn, you have the option to use your replicas to distribute the load from the primary server and send the backups and read-only operations to one of the replicas.

If you are creating the AlwaysOn for the first time, you need to configure the backup priority during the Availability Group Wizard. The following picture shows how you can set it up so the backups are run in the secondary replicas. In this way, you can specify that the backups are run in the replica.

108 2 SQL SERVER   How To Improve Performance by Offloading Backups to a Secondary Replica   Notes from the Field #108

If you have already created your AlwaysOn Availability Group, and you haven’t configured where the backups are run, you must alter you group using T-SQL as shown below.

ALTER AVAILABILITY GROUP [@MyOLTPAvailablityGroup] MODIFY REPLICA ON <@MyOLTPInstanceA> WITH (BACKUP PRIORITY = 80)

If you need to automate this task you can create a PowerShell script as shown:

Set-SqlAvailabilityReplica -BackupPriority 80 -Path SQLSERVER: \Data\AvailabiltiyGroups\AvailabiltiyReplicas\&lt;@ MyOLTPAvailablityGroup &gt;

In case you want to configure a Read-only secondary after you have created the Availability Group, then you must alter the current configuration to include the read only routing, as show below:
ALTER AVAILABILITY GROUP MyOLTPAvailablityGroup
MODIFY REPLICA
ON 'MySQLServerName'
WITH (
SECONDARY_ROLE (
READ_ONLY_ROUTING_URL = 'TCP://address:port' )
)

Once you have run the scripts, you can modify the connection string of the applications that are read-only to include the following parameter ApplicationIntent=Read-only, in this way the Availablity Group will redirect the read-only connections to the proper secondary replica.

Conclusion

If you want to leverage all the potential of AlwaysOn you should consider its high availability features, but the value of spending some time configuring the secondary read-only replicas and backups will also help you balance the request of your systems, optimize the resource usage and speed up your SQL Server.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Create a Readable Secondary Server in SQL Server Standard – Notes from the Field #107

[Notes from Pinal]: The basic nature of human is greedy. When we get one thing which we desire the next thing. In the early world of SQL Server we got a secondary server as a backup or high availability. In earlier times the secondary server was not readable, it just served as a backup. At this point of time our human nature kicked in and we want to get more from the server, which was just sitting there most of the time. We wanted to make our secondary server readable. This is when I reached out to Kenneth and asked him what can we do to make our secondary server as a readable.

Kenneth SQL SERVER   How to Create a Readable Secondary Server in SQL Server Standard   Notes from the Field #107Linchpin People are database coaches and wellness experts for a data driven world. In this 107th episode of the Notes from the Fields series database expert Kenneth Urena (partner at Linchpin People) shares very interesting conversation related to how to create readable secondary server in SQL Server standard edition.


AlwaysOn Availability groups are a great technology to create up-to-date, readable secondary’s and distribute read-only load to servers that are not involved in read/write operations. There is one main license requirement, though: your servers need to be running SQL Server Enterprise edition.

So, what happens if you need this functionality, but you are running SQL Server Standard Edition? Transactional Replication is a tool you might want to look to for answers.

This post will demystify some of the misconceptions about transactional replication, and review the considerations and tips you need to successfully configure this technology:

Understanding Transactional Replication

Transactional replication is composed of 3 main roles: The Publisher, The Distributor and The Subscriber.

notes107 1 SQL SERVER   How to Create a Readable Secondary Server in SQL Server Standard   Notes from the Field #107

This is how it works – The Publisher tracks what Objects of the database (Articles) are going to be published, then the Distributor get the changes from the Publisher and makes it available for the Subscriber to consume.

Transactional Replication Requirements

The physical implementation of this technology requires at least 3 databases:

  • The Source Database: This database is your actual production database, and it is going to hold the publication on the Publisher.
  • The Distribution Database: This database will host all the articles modifications per subscriber per database. It also has a timeframe to keep this information available.
  • The Destination database: This database is where all the data will get replicated, and potentially you can redirect the read only queries to take place. This database is hosted on the subscriber host.

Configuring Transactional Replication Properly

In replication the roles can be hosted by the same server. But this choice may actually cause a worse problem than the one we are trying to solve (load balancing). It is because of this that you should keep the following tips in mind before configuring transactional replication:

  1. Make sure all of the tables on the source database have primary keys, otherwise that article can’t be include in the publication.
  2. Since the goal is take load out of the primary server, make sure to configure the distribution role on a different server than the primary. If you don’t have the budget to configure a distribution server, you can use the subscriber as distributor. Otherwise you will be overloading the server during high traffic.
  3. The configuration can be done as follows:
    notes107 2 SQL SERVER   How to Create a Readable Secondary Server in SQL Server Standard   Notes from the Field #107
  1. If the subscriber is going to host the distributor database, make sure the secondary server has the same version (or greater) of SQL server than the publisher.
  2. If you are planning to host publications from different servers, with multiple databases on each server, on the same distributor server, it is possible to configure one distribution database per publisher server as follows:

USE MASTER
EXEC
sp_adddistributor @distributor = N'DESKTOP-QVDC9JE\SQL2016', @password = N'secret'
GO
EXEC sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
USE [distribution]
IF (NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'UIProperties' AND TYPE = 'U '))
CREATE TABLE UIProperties(id INT)
IF (EXISTS (SELECT * FROM:: fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', NULL, NULL)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
ELSE
EXEC
sp_addextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
GO
EXEC sp_adddistpublisher @publisher = N'DESKTOP-QVDC9JE\SQL2016', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

  1. If you plan to have multiple subscribers on different servers to the same publication, make sure to configure that publication as pull, so the distribution database doesn’t get overloaded.
  1. If your publication contains most of the tables of the source database, it will be quicker to initialize the subscriber from backups. Just make sure the publication is configured to allow initialization from backups as follows:
  • Expand the Replication folder
  • Expand the Local Publication folder
  • Right click over the Publication
  • Click on Properties
  • Select Subscription Options
  • Make sure Allow Initialization from Backup Files is on true

notes107 3 SQL SERVER   How to Create a Readable Secondary Server in SQL Server Standard   Notes from the Field #107

Readable Secondary’s and More Balanced Loads – voila!

Although it may require a bit more setup, transactional replication provides a satisfactory replacement for Enterprise level AlwaysOn Availability Groups in the Standard Edition of SQL Server. So long as you take care to configure and maintain the tool properly, this technology should go a long way towards helping you create up-to-date, readable secondary’s and distribute load balancing in a workable manner.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Edition Upgrade from Evaluation to Enterprise Core – AlwaysOn Warning!

While preparing for a demo about SQL migration, one of the demo was to show the steps needed to upgrade Evaluation edition to full version of SQL Server. The steps are pretty simple and already blogged at many place on the internet. One of my own blog also shows that:

SQL SERVER – Evaluation Period Has Expired – How to Activate SQL Server?

While doing that, I saw a strange warning message during upgrade:

The AlwaysOn Availability Groups feature is enabled on this instance of SQL Server and it is not supported in the edition being changed to. Before proceeding, disable AlwaysOn Availability Groups on the server instance. For more information, see SQL Server Books Online.

Here is what I saw in SystemConfigurationCheck_Report.htm saved in the same folder where setup logs are stored. I have been fortunate to get into such warnings that help me go over the internet and do my own research. But the best way is to learn to get into such pitfalls and get help from experts online and from friends circle. Fortunate to get help from a number of them lately ESP when working with AlwaysOn environments.

aag warn 01 SQL SERVER   Edition Upgrade from Evaluation to Enterprise Core   AlwaysOn Warning!

Then I searched on the internet and checked Detail.txt to see if there is anything interesting.

(10) 2013-08-21 05:31:39 Slp: Sco: Attempting to connect script
(10) 2013-08-21 05:31:39 Slp: Connection string: Data Source=.;Initial Catalog=master;Integrated Security=True;Pooling=False;Connect Timeout=300;Application Name=SqlSetup
(10) 2013-08-21 05:31:39 Slp: Connected successfully…
(10) 2013-08-21 05:31:39 SQLEngine: –IsAlwaysOnFeatureEnabled: Engine_IsAlwaysOnFeatureEnabled: IsHADREnabled: = 1
(10) 2013-08-21 05:31:39 Slp: Sco: Attempting to dispose script
(10) 2013-08-21 05:31:39 Slp: Sco: Attempting to disconnect script
(10) 2013-08-21 05:31:39 Slp: Sco:SqlScriptStatementCompleted: Rows affected 1
(10) 2013-08-21 05:31:39 Slp: Current SqlServer Connection closed…
(10) 2013-08-21 05:31:39 Slp: Evaluating rule        : Engine_IsAlwaysOnFeatureEnabled
(10) 2013-08-21 05:31:39 Slp: Rule evaluation done   : Warning
(10) 2013-08-21 05:31:39 Slp: Rule evaluation message: The AlwaysOn Availability Groups feature is enabled on this instance of SQL Server and it is not supported in the edition being changed to. Before proceeding, disable AlwaysOn Availability Groups on the server instance. For more information, see SQL Server Books Online.

Above script confirmed that SQL Server was connected to get the feature. I checked my setup media by installing one of the instance on test server. To double check, on the page where I need to accept the license terms I can see it’s the Enterprise Core edition. Now, I was wondering why I receive this warning. I also checked the Summary.txt file and saw below

Package properties:
Description:                   Microsoft SQL Server 2012
ProductName:                   SQL Server 2012
Type:                          RTM
Version:                       11
SPLevel:                       0
Installation location:         E:\x64\setup\
Installation edition:          Enterprise Edition: Core-based Licensing

aag warn 02 SQL SERVER   Edition Upgrade from Evaluation to Enterprise Core   AlwaysOn Warning!

I checked with my few expert friends and they told me that if the destination is enterprise, then I can ignore the warning and proceed with the upgrade. I would guess it is an issue with the setup. In my case, the edition upgrade just completed successfully and I was able to continue using AlwaysOn Availability Group.

If you ever receive this warning, you should be able to move forward and trust me, nothing would happen to the availability group. Have you ever received this warning earlier and what did you do?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Get High Availability with SQL Server 2012

I have received a lot of inquiries about how to learn High Availability. Please reach out to my friends at Koenig Solutions.

The SQL Server 2012 offers a plethora of solutions for high-availability that assures 99.999% server and database Availability. These solutions enhance database and server availability, hide the failures of software or hardware and maintain application availability to curtail user downtime. It simplifies management and deployment of high availability systems with incorporated monitoring and configuration tools.  It also enhances performance and cost efficiency of IT utilizing up to four Active Secondary. An SQL Server 2012 High Availability course helps you develop capabilities that contribute in taking your career to new horizons. Some of the key capabilities are utilizing standby hardware to the fullest, implementing disaster recovery and high availability and raising the total application uptime significantly.

UPDATE: Since the blog post I have received a lots of inquiries about how to learn High Availability. Please reach out to my friends at Koenig Solutions.

SQL Server 2012 High Availability Features

  • AlwaysOn Availability Groups- This is a feature of enterprise level that helps in maximizing user database availability. It entails the Server instances of SQL to exist in WSFC (Windows Server Failover Cluster) nodes.
  • AlwaysOn Failover Cluster Instances – This feature influences work of WSFC (Windows Server Failover Cluster). It offers high availability locally using the redundancy feature at the level of the server-instance.
  • Database Mirroring- It enhances DB availability by providing support to instantaneous failover. DB mirroring can easily be employed for maintenance of a single standby DB or mirror DB, for an equivalent production DB which is called a principal DB.
  • Log Shipping-It executes at the DB level. Users can maintain warm standby DBs (or secondary DB) for just one production DB, called the primary DB.
  • Multi-site Clustering – Various enterprises have their data centers located in multiple locations. The main reason for such a step is to maintain redundancy and use a secondary data center for disaster recovery. In this way, organizations can protect their site from failure; whether it is infrastructure, power, network or any other on-site disaster.

Several solutions are there that have executed failover clustering of SQL Server and Windows Server with the multi-site model.  Multiple nodes are included in a multi-site failover cluster. These nodes are scattered across data centers or various physical sites, aiming to offer availability through data centers if a disaster occurs at any site. At times, multi-site failover clusters are considered as multi-subnet clusters, stretch clusters or failover clusters that are dispersed geographically.

High Availability and Disaster Recovery Methodologies

Mastering the Disaster Recovery and High Availability capabilities in SQL Server 2012 environment helps add on to an IT professional’s existing skill-set and allows them to enjoy a promising career ahead. You can build the competencies necessary for a database administrator, developer or architect to understand and manage SQL Server 2012 internal infrastructure. Here are a few methodologies for high availability and disaster recovery-

  • Easy monitoring of the high availability configuration with AlwaysOn dashboard. It is an illustrative configuration to keep your DB up to the mark.
  • In case a failover happens, it helps in controlling the same. Also, it helps to remove false instances and DB failover.
  • Setting up Availability Group will ensure multiple database failover as a single unit. This ascertains that the application has all databases available, up and running.
  • With Availability Group Listener, you get a comprehensive set of alternative choices and a faultless application failover.

SQL Server 2012 High Availability Benefits

SQL Server 2012 has developed a platform which brings you a good amount of benefits that can add to your SQL server set up. These benefits include-

  • Diminished cost of Hardware without shared storage using AlwaysOn.
  • For performance improvement, secondary replica can take the load of performing backups rather than unnecessarily burdening the primary server.
  • With the AlwaysOn High Availability feature, availability of your applications is enhanced.
  • Automated page repair and high latency system support.
  • Provides confidence to deliver in mission critical situations.

SQL Server 2012 High Availability is a new course which can prove to be a feather in the cap of IT professionals. They can further their skills in High Availability and Disaster Recovery in SQL Server 2012 set up. SQL Server 2012 offers you everything required for addressing data dependability and availability at correct time and correct price at every level of the enterprise. With this, you will learn various techniques for leveraging SQL Server technologies for acquiring high-availability of database solutions.

I have received a lot of inquiries about how to learn High Availability. Please reach out to my friends at Koenig Solutions.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Microsoft Whitepaper – AlwaysOn Solution Guide: Offloading Read-Only Workloads to Secondary Replicas

SQL Server 2012 has many interesting features but the most talked feature is AlwaysOn. Performance tuning is always a hot topic. I see lots of need of the same and lots of business around it. However, many times when people talk about performance tuning they think of it as a either query tuning, performance tuning, or server tuning. All are valid points, but performance tuning expert usually understands the business workload and business logic before making suggestions. For example, if performance tuning expert analysis workload and realize that there are plenty of reports as well read only queries on the server they can for sure consider alternate options for the same. If read only data is not required real time or it can accept the data which is delayed a bit it makes sense to divide the workload.

A secondary replica of the original data which can serve all the read only queries and report is a good idea in most of the cases where there is plenty of workload which is not dependent on the real time data. SQL Server 2012 has introduced the feature of AlwaysOn which can very well fit in this scenario and provide a solution in Read-Only Workloads. Microsoft has recently announced a white paper which is based on absolutely the same subject. I recommend it to read for every SQL Enthusiast who is are going to implement a solution to offload read-only workloads to secondary replicas.

Download white paper AlwaysOn Solution Guide: Offloading Read-Only Workloads to Secondary Replicas

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – 2 Whitepapers Announced – AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution

Understanding AlwaysOn Architecture is extremely important when building a solution with failover clusters and availability groups. Microsoft has just released two very important white papers related to this subject. Both the white papers are written by top experts in industry and have been reviewed by excellent panel of experts. Every time I talk with various organizations who are adopting the SQL Server 2012 they are always excited with the concept of the new feature AlwaysOn. One of the requests I often here is the related to detailed documentations which can help enterprises to build a robust high availability and disaster recovery solution. I believe following two white paper now satisfies the request.

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups

SQL Server 2012 AlwaysOn Availability Groups provides a unified high availability and disaster recovery (HADR) solution. This paper details the key topology requirements of this specific design pattern on important concepts like quorum configuration considerations, steps required to build the environment, and a workflow that shows how to handle a disaster recovery.

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups

SQL Server 2012 AlwaysOn Failover Cluster Instances (FCI) and AlwaysOn Availability Groups provide a comprehensive high availability and disaster recovery solution. This paper details the key topology requirements of this specific design pattern on important concepts like asymmetric storage considerations, quorum model selection, quorum votes, steps required to build the environment, and a workflow.

If you are not going to implement AlwaysOn feature, this two Whitepapers are still a great reference material to review as it will give you complete idea regarding what it takes to implement AlwaysOn architecture and what kind of efforts needed. One should at least bookmark above two white papers for future reference.

Reference: Pinal Dave (http://blog.sqlauthority.com)