Author Archives: johnsterrett

Why is My SQL Server Execution Plan Changing and How do I fix It!

Speaking at Performance Virtual Chapter

Speaking at Performance Virtual Chapter

Have, you noticed an important query suddenly takes longer to run? Have you noticed the execution plan looks completely different than what you normally expect for your query? Today, I talked at the Performance Virtual Chapter going over how to identify plans that change and went over options to fix them in SQL Server 2005 to 2016 by forcing plans.

Where is the Code?

As promised, the demo code is attached here.

How Can Query Store Get Better?

I would love to see Query Store have the ability to capture statistics for AG Secondaries that are enabled for read traffic. A bonus, would be the ability to force plans for AG Secondaries as well.

Blue Screen Your SQL Server On-Demand

These days I do a lot of testing with SQL Server.  When I am testing new features or helping clients implement SQL Server High Availability solutions I want to have several tests including blue screens.

Simulating blue screens actually might be much easier than you think.  For example, you can use Not My Fault from the system utilities to create blue screens whenever your heart desires.

Blue Screen with Not My Fault

Do you really want to push that Crash button?

Just remember, if you use Not My Fault and click on the Crash button, It Aint My Fault.

Instantly after clicking on the crash button you should see the following blue screen.

SQL Server Blue Screen on Demand

SQL Server Blue Screen on Demand

Looking Back: 2016 was Awesome!

With 2017 starting this week I would like to take a moment and reflect on 2016. I have to admit that 2016 was a great year for many different reasons, both personally and professionally. It’s nice just to stop and acknowledge some significant accomplishments that occurred last year!

2016 in Review

I always try to keep my Community contributions up to date here on my blog. I would have never gotten to where I am today without others who helped me along the way, so I am always happy to give back.  It’s been an honor to host another SQL Saturday in Austin for over 250 attendees, be a leader for the Austin SQL Server User Group and continue to grow the High Availability and Disaster Virtual Chapter to 2145 members.

Professionally, it was also a great year in 2016.  I was able to obtain two major goals on my career bucket list. I completed the Microsoft Certified Solution Expert for Data Platform certification (Yes, my procrastination took me to the

Microsoft Certified Solution Expert on Data Platform

Microsoft Certified Solution Expert on Data Platform

last work day in 2016, but I got it done!).  As Tom LaRock says, “They do not hand out certifications like candy. You have to earn them.” I also was acknowledged as a Microsoft Data Platform MVP which goes to about 100 people in the USA.

In 2016, I was also able to continue to grow as a speaker.  Once again, I was blessed with an opportunity to speak at the prestigious PASS Member Summit. I never take getting accepted lightly. This year even though I was sick and almost

Almost a perfect score for the biggest stage.

Almost a perfect score at the Superbowl for SQL Server Speakers!

had to cancel I was able to deliver my session while obtaining my best evaluation score to date (ranked 1 to 3).  I gave 15 sessions in 2016; my highest attended session of the year was at the Data Architecture Virtual Chapter to 457 attendees.

My company, Procure SQL LLC also achieved some great milestones in 2016. I was able to hire an employee, Angela Tidwell as our Marketing DBA. We were able to procure our very first office as well. Finally, after spending several hours working with an excellent health care broker, I was proud to announce that we were able to set up our very first group health plan at Procure SQL. While this isn’t required, it was a huge goal of mine to help make sure that our employees are taken care of as we grow in the future.

Personally, I was able to catch up with some great friends and spend some quality time with my family.  We took the family to Galveston, TX for our very first beach trip as a family.  I learned that it was Nina’s Dads first time in the Gulf as well.  Bonus; the weather was so nice in December my Brother and rootsportsroyjohndecided to bring our kids back for a weekend.  During my birthday weekend, I was able to see my Pirates and Wheeling Nailers in person in Dallas which is both very rare.   During the weekend I was able to hangout with one of my best friends from college.  We even made it on Root Sports Pittsburgh raising the jolly roger.

Finally, my loyalty with some friendships was tested in 2016. While one could have only focused on themselves,  I decided to help my real friends in need in 2016. I remember asking myself (yes I talk to myself), “what would I hope my friends would do for me in my hour of need?” I then tried to do that.

2017 Goals

These days, I am not a huge fan of publicly sharing my goals.  With that said, I wanted to share three goals to help keep me accountable.

Build Two Local Speakers

In the past, I have had the opportunity to help build some new speakers. I cannot think of a more rewarding experience in the SQL Community than helping someone grow. While I have more than two different people in mind, I hope to have at least two new speakers give their very first SQL Server presentations in 2017. Who knows? Maybe they will love it, and it can change their careers like it did for me.

Fill My Office

While I plan on getting us a fridge, when I say, “Fill My Office” I wasn’t just planning on filling the fridge with adult beverages. Our office fits one more person easily, and I hope to find that FTE employee in 2017.  I will provide more details on this later on as we get closer to that point of pulling that trigger.

Building Training Plan

 

Testing out the new office white board.

Literally, this was the first thing written on our new six-foot whiteboard

With the hiring of Angela last year, one of my goals was to build some high-Quality training material that could help someone become a SQL Server Database Administrator (DBA). I want this to be online content that could be used on demand to help as many people as possible to get a great start as a DBA. I hope to have this implemented in 2017.  It might also force me to get out of my shell and focus on building some great video content which will be a new challenge.

 

Three Reasons Why I Am Attending PASS Member Summit 2016

Three reasons why I am attending PASS Member Summit in 2016

Three reasons why I am attending PASS Member Summit in 2016

Over the past few weeks, I saw on social media that many of my #sqlfamily members were not attending the PASS Member Summit conference this year.  It made me want to blog about why I am attending this year.

Many years ago, I heard that the PASS Member Summit conference could change your career as a data professional. I thought that statement was a great marketing pitch until I attended for the first time in 2011.  These days, I get more excited for other people than myself. With that said, here are three reasons why I am attending this year’s PASS Summit.

Local Grass Roots

Being a chapter leader, I always want to do everything I can to help my local user members, and I love seeing them succeed and grow.  In fact, it’s been fun watching some of the members grow professionally and in the community. One of Austin’s Finest SQL Server presenters, Lance Tidwell will be doing a full session at 3:30 pm on Friday on Parameter Sniffing the Good, the Bad, and the Ugly. There is not another session I look forward to seeing more this year (Yes, I am also presenting but luckily not at the same time as Lance).

The Dream I Never Had

After coming back from my very first PASS Summit in 2011, I had all kinds of thoughts on how my career would evolve. I had dreams of making six figures, working from home, being my boss.  I even had mentors in the PASS community who helped me realize all those dreams were possible.  I never imagined that I would hire my first my first employee directly from conversations I had with a local user group member. I tried to help Angela Tidwell (Yes, Lance’s wife) break into the IT field a few times.  After several conversations, I learned we could help each other out so this week Angela became my second employee.  Angela is at the PASS Member Summit this week as a first timer.  I hope I can do a good job introducing her to everyone just like Tom LaRock did for me when I was a first timer.

If you are at the PASS Member Summit and you see Angela, please say hello. Just please don’t do it during the middle of Lance’s session on Friday.

The Speaker That Almost Never Presented

Many years ago, I had a boss who I knew would be a great speaker in the PASS Community. Like most people, he was afraid of public speaking. I had to dare him to go to the local Pittsburgh SQL Server User Group with me and co-present. When I say co-present, I meant just stand next to me and share some real-world stories while I do demos.  Now he is speaking at the PASS Member Summit for the second time in a row. I love being able to say I knew him when. Now he is a superstar, and I look forward to watching him succeed and continue to grow in the SQL Server Community.

[Update 9:48 PST]

Why are you attending this year’s PASS Member Summit? I would love to hear your reasons. If you couldn’t make it this year you can still watch parts of the conference live on the internet.

Benchmark Azure SQL Database Wait Stats

Today, I want to focus on how we can monitor wait statistics in an Azure SQL Database.  In the past, I blogged about how you should benchmark wait stats with the box product.  This process will give you misleading data in Azure SQL Database.  You will want to focus on wait stats that are specific to your database as you are using shared resources in Azure SQL Databases.

Finding Database Waits Statistics

Query we are talking to you!

Query we are talking to you!

With an instance of SQL Server regardless of using IaaS or on-premise, you would want to focus on all the waits that are occurring in your instance because the resources are dedicated to you.

In database as a service (DaaS), Microsoft gives you a special DMV that makes troubleshooting performance in Azure easier than any other competitor.  This feature is the dm_db_wait_stats DMV.  This DMV allows us specifically to get the details behind why our queries are waiting within our database and not the shared environment.  Once again it is worth repeating, wait statistics for our database in a shared environment.

The following is the script is a stored procedure I use to collect wait statistics for my Azure SQL Databases.  I hope it is a helpful benchmarking tool for you when you need to troubleshoot performance in Azure SQL Database.

The Good Stuff

/***************************************************************************
    Author : John Sterrett, Procure SQL LLC

    File:     AzureSQLDB_WaitStats.sql

    Summary:  The following code creates a stored procedure that can be used
                 to collect wait statistics for an Azure SQL Database.
                        
    Date:     August 2016

    Version:  Azure SQL Database V12 
  
  ---------------------------------------------------------------------------
  
  For more scripts and sample code, check out 
    http://www.johnsterrett.com

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
************************************************************************/

If NOT EXISTS (Select 1 from sys.schemas where name = N'Waits')
        execute sp_executesql @stmt = N'CREATE SCHEMA [Waits] AUTHORIZATION [dbo];'
GO


CREATE TABLE Waits.WaitStats (CaptureDataID bigint, WaitType varchar(200), wait_S decimal(20,5), Resource_S decimal (20,5), Signal_S decimal (20,5), WaitCount bigint, Avg_Wait_S numeric(10, 6), Avg_Resource_S numeric(10, 6),Avg_Signal_S numeric(10, 6), CaptureDate datetime)
CREATE TABLE Waits.BenignWaits (WaitType varchar(200))
CREATE TABLE Waits.CaptureData (
ID bigint identity PRIMARY KEY,
StartTime datetime,
EndTime datetime,
ServerName varchar(500),
PullPeriod int
)

INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('CLR_SEMAPHORE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('LAZYWRITER_SLEEP')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES  ('RESOURCE_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SLEEP_TASK')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SLEEP_SYSTEMTASK')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SQLTRACE_BUFFER_FLUSH')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES  ('WAITFOR')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('LOGMGR_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('CHECKPOINT_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('REQUEST_FOR_DEADLOCK_SEARCH')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('XE_TIMER_EVENT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES  ('BROKER_TO_FLUSH')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_TASK_STOP')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('CLR_MANUAL_EVENT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('CLR_AUTO_EVENT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('DISPATCHER_QUEUE_SEMAPHORE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('FT_IFTS_SCHEDULER_IDLE_WAIT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('XE_DISPATCHER_WAIT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('XE_DISPATCHER_JOIN')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_EVENTHANDLER')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('TRACEWRITE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('FT_IFTSHC_MUTEX')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_RECEIVE_WAITFOR')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('ONDEMAND_TASK_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('DBMIRROR_EVENTS_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('DBMIRRORING_CMD')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_TRANSMITTER')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SQLTRACE_WAIT_ENTRIES')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SLEEP_BPOOL_FLUSH')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SQLTRACE_LOCK')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('DIRTY_PAGE_POLL')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SP_SERVER_DIAGNOSTICS_SLEEP')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('HADR_FILESTREAM_IOMGR_IOCOMPLETION')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('HADR_WORK_QUEUE')

insert Waits.BenignWaits (WaitType) VALUES ('QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP');
insert Waits.BenignWaits (WaitType) VALUES ('QDS_PERSIST_TASK_MAIN_LOOP_SLEEP');
GO

--DROP PROCEDURE Waits.GetWaitStats
CREATE PROCEDURE Waits.GetWaitStats 
    @WaitTimeSec INT = 60,
    @StopTime DATETIME = NULL
AS
BEGIN
    DECLARE @CaptureDataID int
    /* Create temp tables to capture wait stats to compare */
    IF OBJECT_ID('tempdb..#WaitStatsBench') IS NOT NULL
        DROP TABLE #WaitStatsBench
    IF OBJECT_ID('tempdb..#WaitStatsFinal') IS NOT NULL
        DROP TABLE #WaitStatsFinal

    CREATE TABLE #WaitStatsBench (WaitType varchar(200), wait_S decimal(20,5), Resource_S decimal (20,5), Signal_S decimal (20,5), WaitCount bigint)
    CREATE TABLE #WaitStatsFinal (WaitType varchar(200), wait_S decimal(20,5), Resource_S decimal (20,5), Signal_S decimal (20,5), WaitCount bigint)

    DECLARE @ServerName varchar(300)
    SELECT @ServerName = convert(nvarchar(128), serverproperty('servername'))
    
    /* Insert master record for capture data */
    INSERT INTO Waits.CaptureData (StartTime, EndTime, ServerName,PullPeriod)
    VALUES (GETDATE(), NULL, @ServerName, @WaitTimeSec)
    
    SELECT @CaptureDataID = SCOPE_IDENTITY()
     
/* Loop through until time expires  */
    IF @StopTime IS NULL
        SET @StopTime = DATEADD(hh, 1, getdate())
    WHILE GETDATE() < @StopTime
    BEGIN

        /* Get baseline */
        
        INSERT INTO #WaitStatsBench (WaitType, wait_S, Resource_S, Signal_S, WaitCount)
        SELECT
                wait_type,
                wait_time_ms / 1000.0 AS WaitS,
                (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
                signal_wait_time_ms / 1000.0 AS SignalS,
                waiting_tasks_count AS WaitCount
            FROM sys.dm_db_wait_stats
            WHERE wait_time_ms > 0.01 
            AND wait_type NOT IN ( SELECT WaitType FROM Waits.BenignWaits)
        

        /* Wait a few minutes and get final snapshot */
        WAITFOR DELAY @WaitTimeSec;

        INSERT INTO #WaitStatsFinal (WaitType, wait_S, Resource_S, Signal_S, WaitCount)
        SELECT
                wait_type,
                wait_time_ms / 1000.0 AS WaitS,
                (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
                signal_wait_time_ms / 1000.0 AS SignalS,
                waiting_tasks_count AS WaitCount
            FROM sys.dm_db_wait_stats
            WHERE wait_time_ms > 0.01
            AND wait_type NOT IN ( SELECT WaitType FROM Waits.BenignWaits)
        
        DECLARE @CaptureTime datetime 
        SET @CaptureTime = getdate()

        INSERT INTO Waits.WaitStats (CaptureDataID, WaitType, Wait_S, Resource_S, Signal_S, WaitCount, Avg_Wait_S, Avg_Resource_S,Avg_Signal_S, CaptureDate)
        SELECT  @CaptureDataID,
            f.WaitType,
            f.wait_S - b.wait_S as Wait_S,
            f.Resource_S - b.Resource_S as Resource_S,
            f.Signal_S - b.Signal_S as Signal_S,
            f.WaitCount - b.WaitCount as WaitCounts,
            CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.wait_S - b.wait_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Wait_S,
            CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.Resource_S - b.Resource_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Resource_S,
            CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.Signal_S - b.Signal_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Signal_S,
            @CaptureTime
        FROM #WaitStatsFinal f
        LEFT JOIN #WaitStatsBench b ON (f.WaitType = b.WaitType)
        WHERE (f.wait_S - b.wait_S) > 0.0 -- Added to not record zero waits in a time interval.
        
        TRUNCATE TABLE #WaitStatsBench
        TRUNCATE TABLE #WaitStatsFinal
 END -- END of WHILE
 
 /* Update Capture Data meta-data to include end time */
 UPDATE Waits.CaptureData
 SET EndTime = GETDATE()
 WHERE ID = @CaptureDataID
END

Special Wait Statistics Types

The following are wait statistics you will want to focus on specifically in Azure SQL Database.  If you made it this far, I strongly encourage you to read how DTU is measured.  That blog post will help you understand exactly why these waits can be signs of DTU pressure.

IO_QUEUE_LIMIT :  Occurs when the asynchronous IO queue for the Azure SQL Database has too many IOs pending. Tasks trying to issue another IO are blocked on this wait type until the number of pending IOs drop below the threshold. The threshold is proportional to the DTUs assigned to the database.

LOG_RATE_GOVERNOR :  Occurs when DB is waiting for quota to write to the log.  Yes,  Azure SQL Database is capping your transactional log writes to adhere to DTU.

SOS_SCHEDULER_YIELD: This occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait, the task is waiting in the runnable queue to get a scheduler to run.  If your DTU calculation is based on CPU usage you will typically see these waits.

Want More Azure Articles?

If you enjoyed this blog post I think you will also enjoy the following related blog posts.

John Sterrett is a Microsoft Data Platform MVP and a Group Principal for Procure SQL. If you need any help with your on-premise or cloud SQL Server databases, John would love to chat with you. You can contact him directly at john AT ProcureSQL dot com or here.

Photo Credit:  

Calculating DTU in Azure SQL Database

A few months ago, I posted a question over on ask.sqlservercentral.com.  In a nutshell, it was how do you measure DTU? How could you pull the data shown in the Azure Portal graphs with T-SQL?  No one answered, so this motivated me get off my butt and answer my question.  In doing so, I wanted to share the results with all my readers as well.

I started thinking like a detective.  If I was DTU how would I want to be found with T-SQL?

It was the Azure Portal in the Query Store

It was the Azure Portal in the Query Store

Knowing, that both “sys.dm_db_resource_stats” and “sys.resource_stats” holds data that is used to calculate DTU. I decided to leverage Query Store on an Azure SQL Database to see if I could quickly see how DTU is calculated. Behold, I was right.

 

Query Store

How DTU is calculated in Azure Portal

The Secret Sauce

The whole query is below. Right now, let’s just focus on the secret sauce. The secret sauce is how DTU percentage gets calculated.  In a nutshell, the maximum of CPU, Data IO, Log Write Percent determine your DTU percentage.  What does this mean to you? Your max consumer limits you. So, you can be using 1% of your IO but still be slowed down because CPU could be your max consumer resource.

(SELECT MAX(v) FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS value(v)), 0) as dtu_consumption_percent

DTU Calculating Query

SELECT ((CONVERT(BIGINT, DATEDIFF(day, 0, [end_time])) * 24 * 3600 + DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, [end_time]), 0), [end_time])) / @timeGrain) * @timeGrain as start_time_interval
                , MAX(cpu_percent) as cpu_percent
                , MAX(physical_data_read_percent) as physical_data_read_percent
                , MAX(log_write_percent) as log_write_percent
                , MAX(memory_usage_percent) as memory_usage_percent
                , MAX(xtp_storage_percent) as xtp_storage_percent
                , MAX(dtu_consumption_percent) as dtu_consumption_percent
                , MAX(workers_percent) as workers_percent
                , MAX(sessions_percent) as sessions_percent
                , MAX(dtu_limit) as dtu_limit
                , MAX(dtu_used) as dtu_used
             FROM
                 (SELECT
                     end_time
                    , ISNULL(avg_cpu_percent, 0) as cpu_percent
                    , ISNULL(avg_data_io_percent, 0) as physical_data_read_percent
                    , ISNULL(avg_log_write_percent, 0) as log_write_percent
                    , ISNULL(avg_memory_usage_percent, 0) as [memory_usage_percent]
                    , ISNULL(xtp_storage_percent, 0) as xtp_storage_percent
                    , ISNULL((SELECT MAX(v) FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS value(v)), 0) as dtu_consumption_percent
                    , ISNULL(max_worker_percent, 0) as workers_percent
                    , ISNULL(max_session_percent, 0) as sessions_percent
                    , ISNULL(dtu_limit, 0) as dtu_limit
                    , ISNULL(dtu_limit, 0) * ISNULL((SELECT MAX(v) FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS value(v)), 0) / 100.0 as dtu_used
                 FROM sys.dm_db_resource_stats 
                 WHERE [end_time] >= @startTime AND [end_time] <= @endTime
                 ) t
             GROUP BY ((CONVERT(BIGINT, DATEDIFF(day, 0, [end_time])) * 24 * 3600 + DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, [end_time]), 0), [end_time])) / @timeGrain) * @timeGrain

A Future Blog Post

Now that we can calculate DTU we could trend this data and automatically make changes like moving up and down from the current performance tier.  I would love to wrap this into an Azure SQL Database Alert but a process in PowerShell might be a great starting point.

John Sterrett is a Microsoft Data Platform MVP and a Group Principal for Procure SQL. If you need any help with your on-premise or cloud SQL Server databases, he would love to chat with you. You can contact him directly at john AT ProcureSQL dot com or here.

WordPress Community Podcast

I had the pleasure of catching up with John Parkinson, one of my mentors and a great friend of mine. It feels like yesterday John, GWC of AITP, and I brought two SQL Saturday’s to Wheeling, WV (#36 and #$80).

It’s fun to see how we both stayed highly involved in the IT Community over the years. John is highly involved in the Greater Wheeling Chapter of AITP and WordPress while I am highly involved in the SQL Server Community.

John reached out to me to do a podcast with him talking about my SQL Saturday experiences so people in the WordPress community could learn about other all-day training events. The result is the 10-minute video which is provided below.

Azure SQL Database Live Migrations

Unfortunately, with Azure SQL Database you are not able to take an existing SQL Server Backup and restore it on an Azure SQL Database server.  We are talking about Microsoft’s database as a service (DaaS)  offering, not Azure VMs also known as infrastructure as a service (IaaS).  The only current way to migrate an existing database is to move the schema and the data, period. We have some good tools that can make this seamless, especially with smaller databases and outage windows. You can easily use SQLPackage.exe (My recommended tool), SSMS Wizard , or import/export bacpacs.

Migrating Existing Databases to Azure SQL Database

Migrating Existing Databases to Azure SQL Database

This is great, except for the case I want to talk about today. What if you need to do a live migration with as little downtime (business wants no downtime) as possible with bigger databases. For example, say and existing 50 GB to 500GB database? Your only, option today is a good old friend of mine called transactional replication. You see, you can configure transactional replication and have the snapshot occur and all data in your current production system can be syncing live with your Azure SQL Database until it’s time to cutover which will make your cutover downtime as short as possible.

Below I will give you step by step instructions on how you can configure your subscriber. This would be your Azure SQL Database. The publisher would be your existing production database which could either be on-premise or an Azure VM.

Prereqs

To make this blog post as consumable as possible, we will assume a few prereqs have been completed.  Links in the reference section are provided in case you might want help with the following prereqs.

  • Current production database has a primary key on all tables that need to be migrated. This is a requirment for transactional replication.
  • A new empty Azure SQL Database exists.
  • Azure SQL Database Firewall includes your distributor SQL Server (more in this below)
  • You have an SQL Authenticated Account configured for your Azure SQL Database
  • Publication currently exists

Step One:

Below we will start creating our subscription by utilizing an existing publication. For this article, we will use AdventureWorks. We start by creating a new subscription. I assume you have an existing publication on your primary server and that it is configured. We will use the same server for the distribution agent and the distribution database.

Create Subscription Azure DB

Next, we will walk through the wizard.
17_CreateSubscriptionAzureDB

 

Step Two:

Select the publication you would want to use to add the new subscriber to use the empty Azure SQL Database. Remember in this case, were going to use this subscriber to migrate the schema and data to a new Azure SQL Database.

18_CreateSubscriptionAzureDB

Step Three:

Now, you will need to configure the distribution properties. With Azure SQL Databases you will have no access to the OS and only the database. Therefore, you will need to create a push subscription. A push subscription is a nice way of saying all the agents that initialize the data sync, capture and send data are configured on the distributor server.

19_CreateSubscriberAzureDB

NOTE: If you plan on having a lot of publications it is recommended to use an individual SQL Server to isolate your distribution databases. 

Step Four:

Now we will select where our subscriber will reside. The subscriber will be the Azure SQL Database that we are migrating too.

20_CreateSubscriberAzureDB

21_CreateSubscriberAzureDB_Adjusted

Now you will be able to select your database. In this case, we are going to use SQL 2016ctp3 database. This is an empty database that was created in the prereqs.

22_CreateSubscriptionAzureDB_adjusted

NOTE: Your publisher and distribution SQL Servers must be on a supported version of SQL Server to have Azure SQL Database as a subscriber or you will not be able to select your Azure SQL Database Server as a new subscriber. Trust me, I learned this initially the hard way.

23_CreateSubscriptionAzureDB_altered

Step Five:

Now that our subscriber database is selected we need to define how we are going to connect to our subscriber.

We will use SQL Server authentication using an account on your Azure SQL Database that will allow you to create the schema and move the data. You will also need to update your SQL Azure firewall to include your existing distribution SQL Server as mentioned in the prereq section.

For simplicity of this guide, we are going to run the distribution agent under the SQL Agent Service account. Ideally, you would want a separate account that only had the security needed for transactional replication.

24_CreateSubscription_DistributionSettings_Altered

25_CreateSubscriptionAzureDB_altered

Next, we will define how often we will synchronize the data. We will select to run continuously. You could also have this run on a schedule as well.

27_CreateSubscription_AzureDB_altered

Now we select when to initialize the subscription. Initializing the subscription will execute the snapshot agent (process) that will initialize building the schema and bulk inserting data from the publisher to the distributor and then this data will be sent to the subscriber.

NOTE: The snapshot process will require a schema lock on the publisher database so make sure you initialize at a time when the schema lock is appropriate. In this article, we will initialize immediately.

Step Six:

Now the hard work is done. We will go through the final processes of adding the new subscription with the wizard provided with SSMS.

28_CreateSubscription_AzureDB

29_CreateSubscriptionAzureDB_Altered

30_CreateSubscriber_AzureDB

Finally, once the New Subscription Wizard is successful, you will see your subscription like shown in the image below. You can then monitor the synchronization just like you would with any on-prem transactional replication configuration.

31_CreateSubscription_Review_ALTERED

Conclusion

Configuring an Azure SQL Database as a subscriber is a lot easier than many IT Professionals would initially think. It’s also the best current option to reduce downtime required to synchronize data during a migration of an existing database. The data sync is done in advance and data changes will be kept in sync.

Once data is synchronized, you can stop access to the current production server and remove the subscriber and let the users access the new Azure SQL Database.

Keep in mind, as of the time of publishing this blog post an Azure SQL Database can only be a subscriber and not a publisher in transactional replication. What does this mean to you? Do performance and acceptance testing because you would have to generate a bacpac and export it and import it if you decide to move away from Azure SQL Database.

Additional Reference

John Sterrett is a Microsoft Data Platform MVP and a Group Principal for Procure SQL. If you need any help with your on-premise or cloud SQL Server databases, he would love to chat with you. You can contact him directly at john AT ProcureSQL dot com or here.

I am a Microsoft Data Platform MVP

My First Microsoft SQL Server MVP Award

My First Microsoft SQL Server MVP Award

Today, I have achieved an SQL Someday moment.  I am excited to share some exciting news. Microsoft has chosen me to receive the Microsoft “MVP” award. I am completely shocked as this is my first time obtaining this award.

It is a tremendous honor to be given this award. Words cannot describe how humbling it is just to be nominated for this award by peers within the Data Platform community. I am blessed to be part of the community.  I am thankful to work in a career that supports the ability for everyone to connect, share and learn. I look forward to doing the same as a new member of the MVP community.

Who Are Microsoft MVP’s?

The following excerpt comes directly from the Microsoft MVP website.

“Microsoft Most Valuable Professionals, or MVPs, are community leaders who’ve demonstrated an exemplary commitment to helping others get the most out of their experience with Microsoft technologies. They share their exceptional passion, real-world knowledge, and technical expertise with the community and with Microsoft.”

How My Journey Started

I would never forget and always be thankful to Adolph Santorine my boss at the time who told me, “John, I think you should get involved in our local Association for Informational Technology Professionals (AITP) chapter.” He shared how it help him personally and how he thought it could be beneficial for my career.   Adolph was right. I learned so much from some well experienced IT leaders. The Greater Wheeling Chapter of AITP is where my community involvement started, and I was lucky to start with a community who has been connecting, sharing and learning since 1960’s.  When I decided to get my local community involved in the SQL Server community my fellow AITP members had my back and helped make our event a success. From this moment, I was introduced into PASS and the SQL Server Community.  I have been connecting, sharing and learning ever since. 

How Will Things Change?

They will not change, and to me, that is the greatest part of this award. I still plan to connect, share and learn with as many people as possible. The data platform is evolving.  I plan on being involved in the community and building relationships with individuals who are interested in seeing the Microsoft Data Platform grow and succeed.

Special Thanks

I would like to thank my family, friends, and mentors.  Without their help and support, I would never be the person who I am today or tomorrow.

I would also like to thank everyone I have worked with in my career.  Thank you to everyone at Deloitte, Schedule Star, Orrick Herrington & Sutcliffe, RDX, Dell and Linchpin People. I am truly blessed to have a lot of great mentors and friends.   Thank you for giving me the opportunity to learn, grow, and have a career doing what I love to do.

DONT FORGET TO NOMINATE

Finally, you might not know this, but anyone can nominate someone for a Microsoft MVP Award. You don’t have to be an MVP or a Microsoft employee. I have personally been nominating people for years and will continue to do so as an MVP. If you know of anyone who you think is deserving of the Microsoft MVP Award nominate them.

Automatic Seeding Very Large Databases in SQL Server 2016

There are a lot of new features in SQL Server 2016. Availability Groups by itself got a lot of new features.  Being that I am the founder of the High Availability and Disaster Recovery Virtual Chapter, I plan on blogging about the new availability group features.

Today, I wanted to write about Automatic Seeding.  Microsoft did an excellent job of explaining how to enable and monitor automatic seeding.  I wanted to focus this post on my experience utilizing automatic seeding to seed an existing 600gb database in a test environment to test my endpoint network throughput.

The initial data synchronization easy button.

When you add a database to an availability group, the replicas must synchronize the data between the availability groups to join the database on the replicas.  In the past, data initialization has been done with mirroring, log shipping, backup, and restores.  Personally, I have been a big fan of log shipping for the initial data synchronization of VLDB’s especially when you need more than two replicas. Here is how I added a 60TB (Yes, TB not GB) database to an availability group that utilized multiple data centers.

Automatic seeding is a feature that has been in Azure SQL Databases for a while. It’s how the initial data synchronization occurs for Geo-Replication of Azure SQL Databases.  Automatic seeding utilizes a VDI Backup to take a copy only backup and send it over the endpoint network to seed the replicas and then join the databases with the replicas. This eliminates the need to manually take full and log backups from the primary replica to all the secondary replicas. It will also join the database on the replicas for you.

Bonus Feature of Automatic Seeding

There is also a bonus feature of automatic seeding for DBA’s and Information Technology professionals.  Even if you decide to not use automatic seeding I recommend testing this feature as automatic seeding can be a great way to stress your endpoint network to validate its throughput.

Background Information

This availability group has been configured with a separate 10Gbps network dedicated to endpoint traffic. Nothing else is active on the network or the replicas during the time of testing.

Setup

I configured the following performance monitor counters.

  • Bytes Received/sec on Secondary replicas
  • Bytes Sent/sec on Primary replica.

I also configured the following extended event session to monitor seeding activity on the primary and secondary replicas. We will focus on the “hadr_physical_seeding_progress” event today. We will talk about others in a future blog post.

CREATE EVENT SESSION [AlwaysOn_autoseed] ON SERVER 
ADD EVENT sqlserver.hadr_automatic_seeding_state_transition,
ADD EVENT sqlserver.hadr_automatic_seeding_timeout,
ADD EVENT sqlserver.hadr_db_manager_seeding_request_msg,
ADD EVENT sqlserver.hadr_physical_seeding_backup_state_change,
ADD EVENT sqlserver.hadr_physical_seeding_failure,
ADD EVENT sqlserver.hadr_physical_seeding_forwarder_state_change,
ADD EVENT sqlserver.hadr_physical_seeding_forwarder_target_state_change,
ADD EVENT sqlserver.hadr_physical_seeding_progress,
ADD EVENT sqlserver.hadr_physical_seeding_restore_state_change,
ADD EVENT sqlserver.hadr_physical_seeding_submit_callback
ADD TARGET package0.event_file(SET filename=N'autoseed.xel',max_file_size=(20),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

The following T-SQL script is then used to read the results once your seeding process has completed. We will talk about the results in the results section below.

DECLARE @XFiles VARCHAR(300) = 'S:\MSSQL13.MSSQLSERVER\MSSQL\Log\autoseed*'

;WITH cXEvent
AS (
     SELECT    object_name AS event
              ,CONVERT(XML,event_data) AS  EventXml
     FROM      sys.fn_xe_file_target_read_file(@XFiles, NULL,NULL,NULL)
     where object_name like 'hadr_physical_seeding_progress')

 SELECT 
c1.value('(/event/@timestamp)[1]','datetime') AS time
,c1.value('(/event/@name)[1]','varchar(200)') AS XEventType
,c1.value('(/event/data[@name="database_id"]/value)[1]','int') AS database_id
,c1.value('(/event/data[@name="database_name"]/value)[1]','sysname') AS [database_name]
,c1.value('(/event/data[@name="transfer_rate_bytes_per_second"]/value)[1]','float') AS [transfer_rate_bytes_per_second]
,(c1.value('(/event/data[@name="transfer_rate_bytes_per_second"]/value)[1]','float')*8)/1000000.00 AS [transfer_Mbps]
,c1.value('(/event/data[@name="transferred_size_bytes"]/value)[1]','float') AS [transferred_size_bytes]
,c1.value('(/event/data[@name="database_size_bytes"]/value)[1]','float') AS [database_size_bytes]
,(c1.value('(/event/data[@name="transferred_size_bytes"]/value)[1]','float') / c1.value('(/event/data[@name="database_size_bytes"]/value)[1]','float'))*100.00 AS [PctCompleted]
,c1.value('(/event/data[@name="is_compression_enabled"]/value)[1]','varchar(200)') AS [is_compression_enabled]
,c1.value('(/event/data[@name="total_disk_io_wait_time_ms"]/value)[1]','bigint') AS [total_disk_io_wait_time_ms]
,c1.value('(/event/data[@name="total_network_wait_time_ms"]/value)[1]','int') AS [total_network_wait_time_ms]
,c1.value('(/event/data[@name="role_desc"]/value)[1]','varchar(300)') AS [role_desc]
,c1.value('(/event/data[@name="remote_machine_name"]/value)[1]','varchar(300)') AS [remote_machine_name]
,c1.value('(/event/data[@name="internal_state_desc"]/value)[1]','varchar(300)') AS [internal_state_desc]
,c1.value('(/event/data[@name="failure_code"]/value)[1]','int') AS [failure_code]
,c1.value('(/event/data[@name="failure_message"]/value)[1]','varchar(max)') AS [failure_message]

FROM cXEvent
    CROSS APPLY EventXml.nodes('//event') as t1(c1)

Results

The 600 GB databases took about 66 minutes to seed across the network from a primary replica to the secondary replica.  I noticed 1.4 Gbps of consistent throughput during the seeding process. This makes a lot of sense as it caps out around what the storage system can deliver in this environment.

The first thing I would look at for benchmarking throughput for network activity would be the bytes sent per second from the primary replica and bytes received per second on the secondary replicas.

AG Seeding Primary Replica's Bytes Sent per Second

AG Seeding VLDB Primary Replica – Bytes Sent per Second

 

AG Seed VLDB Secondary Perfmon

AG Seed VLDB Secondary Replica – Bytes Received per Second

I am seeing average around 1.4 Gbps.  Normally, just looking at bytes sent and bytes received will be good enough for you to measure your throughput.  Especially, when nothing else is utilizing the dedicated endpoint network. In the field, I usually do not see dedicated networks for endpoint traffic so I wanted to take this a step further and monitor with some of the new extended event events for automatic seeding.

Here is a look at the raw data from the extended event capture showing the progress and throughput of the seeding.

AG Seed Xevent Transfer

The following is a nice excel graph showing the throughput in Mbps. I added an extra row with zero for throughput just to show the rise and fall of network usages as seeding starts and completes.

AG Seeding Excel Graph

 

My Thoughts

Initial data synchronization process just got a lot easier.  I will use this for sure for adding new databases to availability groups.  If you can live with your transactional log not being truncated during the seeding process I strongly encourage you to use automatic seeding.

I also did not use trace flag 9567  to enable compression during this test. It is why you saw compression not being enabled.  If you have the CPU resources I recommend you test this as well.

Reference Links

For more great information on SQL Server subscribe to my blog and follow me on twitter.

Photo Credit: Mike Mozart (Creative Commons)