Monday, October 2, 2017

Troubleshooting SQL Server AlwaysOn AG Auto Seeding

SQL Server 2016 introduced a new capability with AlwaysOn Availability Groups called Automatic Seeding. This allows you to automatically start streaming the backup using VDI across the log stream transport to the secondary replica. With this capability enabled you do not have to manually backup and restore the database and transaction logs before starting data synchronization with the primary replica.

Now there are some prerequisites for using Automatic Seeding such as:

  • Data and Log file paths must be the same on all Replicas
  • Databases must be in Full recovery model and at least one Full backup must have been taken of the database (to start the recovery chain)
  • All other prerequisites for a database to be added to an Availability Group must also be met


You can enable Automatic Seeding either before or after creating the Availability Group.

Sometimes you will find that Automatic Seeding doesn't work, in our example it will be because a data file already exists with the same name as the database we are seeding. Which is a common problem if you have previously removed the replica from the AG and are trying to re-join it. Unfortunately the UI doesn't give you any indication that seeding failed.

Enter Extended Events.

To demonstrate this I will create an Availability Group across two replicas. I am using the SQL Server AlwaysOn solution available from the Azure Market Place as this has an Availability Group already built, but for this demonstration I will create a new AG and Demo databases.

1. Setup the environment
I have added an additional disk to the SQLSERVER-0 node, and formatted this new volume and assigned the drive letter G:. I also created the path G:\Log and set the file permissions to full control for the sql service account.

2. Create the Demo Database
The following script can be used to setup the environment.

[01.CreateDemoDbs.sql]

3. Create the Availability Group
The following script can be used to setup the environment.

[02.CreateAG.sql]


4. Verify Environment
Now you should have a working Availability Group with the AutoseedDb01 synchronized and healthy between two replicas.



5. Create AlwaysOn_AutoseedMonitor Extended Events session
While we enabled the standard AlwaysOn Health extended events session, run the following script to create a new Extended Events session for monitoring Autoseeding

[03.ExtendedEventsSession.sql]

If you want to create this manually or to explore what other events are available you will need to make sure you select the Debug channel when using the Wizard to select events.



You can also filter the category to "dbseed" to further just view the events that relate to auto seeding



6. Add the 2nd database to AG
Now use the following script to add the 2nd database to the AG. NOTE: This DB has the log file on G:\ which does not exist on the replica.

[04.AddDbToAG.sql]

7. Investigating the Auto Seeding
You will notice that the DB was not created on the replica and it is listed in the AG's DBs but with a warning.



If you open the AG Dashboard you will see the warning message as well.



The messages do not include much detail.

8. Using DMVs
We can use the following DMVs to query the status of the seeding.

[05.DMVs.sql]

9. Using Extended Events
We are capturing the "error_reported" event along with the dbseed events. If you view the Event File on the replica where the failure happened. Locate the hadr_physical_seeding_progress event. There will be a lot of these events. In the below screen shot you can see the progress event for one of the last stages of the seeding process where it reports the failure.



Locate the hadr_physical_seeding_progress event with the internal_state_desc of "WaitingForBackupToStartSending". This is an early step in the auto seed process, then you should see error_reported events. Here is where you can find the real errors. Most likely it will be the first one reported. In our case it is:
Directory lookup for the file "G:\LOG\AutoseedDb02_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).




There will be many more cases where this is useful. I have also used it before when I had old data or log files on the server from previous versions of a database I was adding back into the AG as sometimes that operation will fail (depending on permissions).

Let me know what situations you find


All the scripts used in this post are located in my GitHub https://github.com/Matticusau/SQLDemos/tree/master/SQLAlwaysOnAGAutoSeeding



Reference

Automatic seeding for secondary replicas
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatic-seeding-secondary-replicas

Automatically initialize Always On Availability group
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group



Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.


12 comments:

  1. Tips on Buying a replica here uk replica watches replica watches for men online at low prices- click to buyreplica rolex watches Explore latest collection of branded mens watches at great offers from top brands. Buy watches online at best prices in from popular watch brands such as replica watches, buy cheap sale perfect best replica fake copy discount, best breitling replica watches and more watches perfect best replica popular brands .

    ReplyDelete
  2. Hello It’s really a nice and helpful piece of info. I am happy that you shared this helpful info with us. Please stay us informed like this. Thank you for sharing. bye
    Tangki Panel
    Tangki Fiberglass
    Jual Septic Tank

    ReplyDelete
  3. replica Cartier watches has recommended many classic and successful watch collections, such as replica Cartier Ballon bleu watches, Cartier Santos and Cartier Ronde and so on. Every Cartier watch has its special charm, and Cartier watches are many people's dreams. Our shop provides different Cartier replica watches at very low prices. Just enjoy shopping here.

    ReplyDelete
  4. High quality from a trust worthy replica raybans sunglasses here can offer you the best in high quality, including fake raybans clubmaster , etc.

    ReplyDelete
  5. For over 80 years Ray-Ban has been forging its name as the most iconic eyewear brand. Its glasses tells a story through decades, Cheap Ray-Ban Sunglasses uk trends and the people that wear it. It’s about seeing and being seen, cheap raybans caravan and ownership of one’s own individuality – of dictating who we are instead of being told. But most of all it’s about sharing what it is to be you, with people who understand it. It’s about that feeling of belonging, without conforming.

    ReplyDelete
  6. Alexander McQueen serves up a collection Cheap ALEXANDER MCQUEEN Shoes for men and women of elevated everyday styles, cheap ALEXANDER MCQUEEN shirt womenstailoring rich in tradition and singular pieces with a gothic-chic sensibility. Dramatic accessories – think leather and chains and skull-motif embellishments – top off the line that's a hit with all the critics.

    ReplyDelete
  7. Louis Vuitton himself was born the son of a miller in 1821 in Anchay, a hamlet in the Jura Mountains, not far from the Swiss border. Replica bagsThe region was a poor one - serfdom had only been abolished less than 40 years previously, so Louis left to seek his fortune when he was a teenager, arriving in the French capital aged 16. This was the Paris of Victor Hugo's Les Misérables, with nearly one million inhabitants. As the composer Chopin said in a letter to a friend at the time, "Here you find the greatest luxury and the greatest filth, the greatest virtue and the greatest vice."replica Louis Vuiton bags

    ReplyDelete
  8. Tips for buying a copy here. People spend a lot of time searching on the Internet, replica iwc watches uk where there are many replicas of famous designer brands on sale, and we can be sure that they are of high quality and quality. This is the first time I bought a copy watch. This watch is very surprising to me. replica iwc ingenieur watches The color is very low-key and the quality is very good. Good value for money

    ReplyDelete
  9. Louis Vuitton himself was born the son of a miller in 1821 in Anchay, a hamlet in the Jura Mountains, not far from the Swiss border. Replica bagsThe region was a poor one - serfdom had only been abolished less than 40 years previously, so Louis left to seek his fortune when he was a teenager, arriving in the French capital aged 16. This was the Paris of Victor Hugo's Les Misérables, with nearly one million inhabitants. As the composer Chopin said in a letter to a friend at the time, "Here you find the greatest luxury and the greatest filth, the greatest virtue and the greatest vice."replica Louis Vuiton bags

    ReplyDelete
  10. cheap Soccer Jerseys provides you high quality and cheap soccer jerseys, cheap virgil van dijk Jerseyswholesale soccer jerseys. You can buy cheap club soccer jerseys and national team soccer .

    ReplyDelete
  11. Now designed by Mr McQueen’s long-time collaborator, replica boots Ms Sarah Burton, Alexander McQueen collections are characterised by replica alexander mcqueen immaculate tailoring and a dark, dramatic edge. Look out for sharp suiting and the label's signature skulls.

    ReplyDelete
  12. This article was composed by a genuine speculation essayist. I concur a hefty portion of the with the strong focuses made by the essayist. I'll be back. A huge collection of all the Celebrity net worth of the world. How much is celebrity worth? Compare yourself to your favorite celebrity.

    ReplyDelete