Adding a VLDB database to an SQL Server Availability Group

Recently, I was tasked with making an 60TB database highly available by adding it to an availability group. The following, is how I successfully completed adding the very large database (VLDB) to an Availability Group that had two replicas in the primary data center and a third replica in another data center with no downtime. We wanted to utilize the least amount of time during the initial synchronization process so we leverage log shipping to prepare the database on each replica.

We will call the three servers DC1Server1, DC1Server2, and DC2Server3.  We will have a pre-created AG called SQL2014AG1. We will say that the 60TB database is named VLDB.

Previously we did a side by side upgrade to upgrade VLDB from running on SQL Server 2012 to SQL Server 2014 on new hardware.  Previously, we also leveraged the new hardware to upgrade a subset of the data and build out the Availability Group for testing and training purposes.

  1. Create Availability Group. This was created in advance to deliver a proof of concept and to provide training for understanding how to use and manage an Availability Group. Therefore, there was no reason to break this and recreate it. If needed, you could obviously create the AG as part of this deployment where we just Add the VLDB into the AG.  Creating an Availability Group will not be covered here.
  2. Configure Log Shipping. The primary database in log shipping should be on the instance that is also the read/write replica for the availability group. In this case, VLDB already exists and is configured as primary on DC1Server1. This is also the primary replica for the SQL2014AG1 availability group. If you need examples on how to configure log shipping checkout my article on MSSQLTips.com.
  3. Identify the Log Shipping backup Job. This job usually starts with LSBackup_ for your database. This will be on DC1Server1.
    1. Execute the SQL Agent Job to take a log transactional log backup
    2. Disable the SQL Agent Job when the backup completes.
  4. On the secondary servers (DC1Server2 and DC2Server3) identify the LSCopy_ job for your database named VLDB.
    1. Execute the SQL Agent Job to copy the last log backup that you just created in step three.
    2. Disable the SQL Agent Job when copy backup job completes.
  5. On the secondary servers (DC1Server2 and DC2Server3) identify the LSRestore_ job for your database named VLDB.
    1. Execute the SQL Agent Job to restore the backups pending to be restored. This should include your backup taken in Step four.
    2. Disable the SQL Agent Job when the restore completes.
  6. Validate that the last log backups were copied and restored. This can be done using sp_help_log_shipping_monitor stored procedure on each instance. We would do this on DC1Server1 to validate the last log backup that was completed. On DC1Server2 and DC2Server3 we can validate that the backup was copied and also restored.
  7. On the primary server DC1Server1 add the VLDB to the existing availability group named SQL2014AG1. You can do this via the GUI.
    1. In SSMS under Availability Groups expand “SQL2014AG1″ Availability Group and right click on Availability Databases and select “Add Database” to add VLDB.
    2. Select the “VLDB” database in the checkbox
    3. Select Join Only to start data synchronization as we used Log shipping to keep the VLDB in sync across all servers.
    4. Connect to all existing secondary servers (DC1Server2, and DC2Server3)
    5. Validate adding the Database to the Availability Group
    6. Add the Availability Group to your Availability Group named SQL2014AG1
  8.  Validate that Adding VLDB was successful to SQL2014AG1
    1. Remove log shipping on DC1Server1 for the VLDB database.
    2. Enable your log backup process for all three servers. This would be used to replace log shipping for taking transactional log backups.

 

 

8 Hours of SQL Server Performance Training This Week!

Check out the annual Performance Palooza hosted by the Performance Virtual Chapter on July 23rd, 2015.  This is a virtual online event open to the public and it’s absolutely free.  We’ll have 8 back to back sessions with start times from 10am to 5pm Central Time.

I am honored to be selected to speak and kick off the event.  I think you will love the other sessions too. Check them all out below.

 

Central Time Speaker Topic
10am John Sterrett Make Your SQL Server Queries Go Faster
11am Warner Chaves Building High Performance SQL Servers Virtual Machines on AWS and Azure
12pm Andy Galbraith Performing a SQL Server Health Check
1pm Matan Yungman Columstore Indexes – Questions and Answers
2pm Neil Hambly Performance Troubleshooting Using DMVs
3pm Tim Mitchell Maximizing SSIS Package Performance
4pm Trayce Jordan Troubleshooting Seconday Replica Latency
5pm Mike Fal SQL Server Benchmarking: The Powershell Speedometer

SQL Saturday Austin is looking for Pre-con Speakers

In January we the Austin SQL Server User Group had a blast putting on SQL Saturday #361. We had so much fun we wanted to do it again on January 30th 2016.  We are currently looking for both regular speakers and also pre-con speakers.  If you would be interested in speaking the call for abstracts is open until November 15th.

If you are interested in giving an all-day paid session on Friday, January 29th 2016 please send your abstracts and marketing plans to promote the event to sqlsaturday461 @ sqlsaturday.com. We plan to have selected pre-cons announced by the end of July.

Developers Make Your SQL Server Queries Go Faster!

Being that today is my birthday I thought I would share a nice gift to everyone who follows my blog. Below is a free link to a recorded video of one of my latest presentations that goes over multiple SQL Server developer anti-patterns I see repeated over and over again in the field. Most of these are patterns that any IT professionals can identify and fix just by identifying the pattern and changing the pattern to another one shown in the video that is optimizer friendly.  I promise no brain surgery is required 😉

Make Your SQL Server Queries Go Faster

I hope you enjoy the hour long video on making your SQL Server queries go faster!

Bonus Material

If you want to play along feel free to download the sample scripts.  If you want to learn more about being a proactive performance tuner check out my root cause performance analysis blog series which includes my What Is Running Script, Disk LatencyWait Stats monitor processes and more..

May 2015 SQL Server Speaker of the Month

I was honored this morning to see that I was selected by Grant Fritchey ( blog | twitter) as the Speaker of the Month for May 2015.  It feels great to be

May 2015 Speaker of the Month

May 2015 Speaker of the Month

acknowledged by your peers.  Some people might think I am crazy to spend my own money on travel to deliver 30 SQL Server sessions over the past year and they might be right. With that said, every time I attend SQL Saturdays, Virtual Chapters, and User Group meetings I always learn something. I am blessed to be in a community that gives me the opportunity to connect, share and learn.

The following are some upcoming speaking engagements. If you are attending any of these events I look forward to seeing you.

Kansas City Developers Conference
IT/Dev Connection
Austin SQL Server User Group
SQL Saturday Pittsburgh (Assuming I get Selected)

 

 

Converting identifiers into SQL Server table variables in Management Studio

If I had a nickel for every time someone gave me a list of identifiers and asked me to pull a result set for them I would be a very wealthy man. Over the years this is an endless request. I used to use several different tools like excel for example to convert the list of identifiers into SQL Statements. I would then use set based logic to complete my task inside SQL Server.

Being a SQL Server Database Administrator I like to use one consistent tool for working with queries. One day I noticed an interesting feature inside of the search and replace functionality inside Management Studio. I could utilize regular expression as a tool in my tool belt for manipulating text inside of SQL Server Management Studio. This gave me the functionality to stay within my preferred tool for doing SQL Server development work.

If you want to see how you can quickly manipulate the identifiers and convert them into table variable check out the three minute video below.

Microsoft BI Days in Texas (Recap)

This week I had the pleasure to join  Mark Moore my local SQL Server TSP in Austin to deliver two separate events in Austin and San Antonio to help get people up to speed on some of the Self-Service BI tools included in the Microsoft stack. I had a great time connecting, sharing and learning with new friends.

The following are some links I wanted to share on PowerBI and PowerPivot that I talked about during my session.

PowerPivot for DBAs (Slide Deck) -> http://johnsterrett.com/presentations/powerpivot-for-dbas/

Dallas BI Edition SQL Saturday on May 2nd ->https://www.sqlsaturday.com/396/eventhome.aspx

Channel 9 PowerPivot Free Videos -> https://channel9.msdn.com/Search?term=PowerPivot#ch9Search

Channel 9 PowerBI Free Videos -> https://channel9.msdn.com/Search?term=PowerBI#ch9Search&lang-en=en

SQLPASS Virtual Chapters -> http://www.sqlpass.org/PASSChapters/VirtualChapters.aspx

Business Analytics Virtual Chapter -> http://bavc.sqlpass.org/

Business Intelligence Virtual Chapter -> http://bi.sqlpass.org/

Austin SQL Server User Group -> http://cactuss.org/

San Antonio SQL Server User Group -> http://www.salssa.com/

Outstanding PASS Volunteer Award

I am a Outstanding PASS Volunteer!

I am a Outstanding PASS Volunteer!

It is an honor to receive the Outstanding PASS Volunteer Award for February 2015.  It means a lot to be nominated for this award from my peers. My single goal last year was to bring SQL Saturday back to Austin. On January 31st 2015 we had a great day that allowed 280 people the chance to connect, share and learn.  It would have never happened without these key individuals who spent a lot of time helping make the SQL Saturday happen. Thank you Steve Ormrod, Richard Heim, Mike Burek, Jim Murphy and AJ Mendo.

Free Video for Accidental DBA’s

Recently, Mike Walsh and John Sterrett teamed up with Embarcadero to give a 60 minute presentation focusing on the skills that are necessary for accidental DBA’s to master to become DBA’s with a successful long lasting career.

I wish I had all this advice when I started as a SQL Server DBA.

Resources:

All Resources mentioned in the video to complete the tasks discussed can be found at http://linchpinpeople.com/where

Video

You’re a DBA… Now What?

One of my goals for the year is to do great things with great people.  I will be making my first step at achieving this goal today.  Speaking of firsts, I will also be doing my first vendor focused webinar at the same time. Thank you Embarcadero!

Congratulations, You're A DBA.. Now What?

Today, February 11th 2015 at 1pm CST I will be doing a webinar with my friend Mike Walsh titled “Congratulations, You’re a DBA… Now What?”  If you are a Jr. DBA or even a DBA with just a few years of experience I would highly recommend attending this session.  I look forward to seeing you there!