(upbeat music)
(audience applauds)
- Hello and welcome to Microsoft Mechanics Live.
In the next 15 minutes, on our special episode
of How We Built It, we're joined by Walgreens' IT manager
and supply chain analytics architect, Anne Cruz
to chronicle the approach that she took with her team
in their race against time to migrate
their mission critical retail inventory system
with about a 100 terabytes of data from IBM Netezza
to Azure SQL Data Warehouse,
all before their looming end-of-support deadline.
Now we're gonna explore the approaches
that they took to migrate to a production-ready state
within a record three months time
and where they're going next.
But everybody, please join me in giving a warm welcome
to Anne Cruz from Walgreens.
(audience applauds)
- Thank you Jeremy, glad to be on the show.
- Thank you.
So, Walgreens, it's a huge company.
It's got a massive footprint.
You know, it's got a number of sub-brands.
People know things like Boots maybe in the UK.
We also, we have Rite-Aid here in the US
and I know this particular migration effort
was really US only but what type of scale
are we actually talking about?
- So as you know, Walgreens is expansible
with pharmacy and retail.
Our particular migration effort focused on
the retail part of our business here in the US.
Walgreens has more than 9,000 stores
and more than 20 distribution centers nationwide.
Their operations are supported by my team
which is responsible for maintaining
a mission critical, large-scale inventory management system
called the Merchandising Portal.
This application processes on the average
17 billion records daily.
Every item that we keep in stock
has a cost associated to us.
This application is critical in ensuring
that we have the right inventory at the right quantities
in each store.
- All right and I can really see
how if you get inventory right and do it properly,
you're actually saving a lot more costs
than just the IT system's potentially,
just with the logistics side of the costing alone.
So, it sounds like it was a really big deal
to consider even moving to the Cloud at all
and moving your systems at all, right?
- It is so one big motivating factor for us
was the end of life of support of one of the two
Netezza boxes that is standing up
our Merchandising Portal application.
Second, we needed to integrate the data
from the Rite-Aid acquisition into our infrastructure.
And third, we are just running out of space.
With appliances, as you mentioned earlier,
you cannot just add another 10 terabytes.
You have to buy another 48 terabytes
and you have to pay 20% of annual maintenance costs.
- Right.
- It also takes an average of six months
from the decision to buy an appliance
to standing it up into your data center
and wiring it correctly.
And that's not gonna work for where we are
because we only have until June of 2019
before the end-of-support deadline.
As you say, it is actually a race against time.
In fact, we set to complete our migration effort
to Azure SQL Data Warehouse in just three months.
- So that's a lot of pressure.
Just to put this in perspective,
you kind of started around,
I think, December or so of last year.
You had until June if you wanted to order
a new piece of equipment
but then you wanted to get this done
into Azure in a couple of months.
That's a lot of pressure, I think.
Even with all these challenges in mind,
you made the decision in this case to re-platform.
But before we get into kind of how you did it,
can you give us an idea of where you're coming from,
what your system looked like before?
- Right, so to tell you guys where we were before,
at the center, we had two data warehousing appliances.
The first one was dedicated to hosting
our Java-based Merchandising Portal system
to ensure that it is highly available.
We kept a copy of the system data and diverted
all ad help queries to our second Netezza server
which handled all of our batch processes.
- All right, so how many queries then were you handling?
- It would handle thousands of queries per week,
around 25 large queries weekly,
averaging 20 million rows per query
from our business users and vendors.
The real challenge here is keeping the data in sync.
About a third of the time, we would see that our stock data
is not keeping up-to-date.
So, to avoid any disruption in the batch processes,
we'd often terminate query that is running longer
than five minutes to manage the workload demand.
- And it's terrible because you kind of actually,
kind of training people not to ask for custom queries
which has an impact on the business effectively
'cause they want these type of,
this data and everything to work.
So, what data sources then were feeding
into your merchandising system?
- We had a number of systems feeding into
our Netezza warehouse appliance,
ranging from our financial database to store inventory
to space planning, forecasting.
Because of capacity issues over the year,
we even brought in Hadoop for our supply chain management
for more cost-effective storage of our long-term data
for certain batch processes
that we do not have capacity for in Netezza.
We even used an ETL process to bring the data
into our primary merchandising system.
- Right and you had a huge capacity and scale issue
in this case.
You we're really trying to manage the cost
of the data volume, lots of the demand on the business
for reporting, I think,
a lot of things you had to turn away, unfortunately.
But what did you end up then with?
- We lifted and shifted our Merchandising Portal front end
which is a Java application onto Azure BS
to avoid any data latency.
We ended up replacing the Netezza systems
with one single source of truth
into Azure SQL Data Warehouse,
connecting to all our legacy systems.
One big surprise is the openness of the Azure platform.
It integrates with our productivity applications
with Office 365 and of course identity management
with Azure Active Directory
which is really great for our users.
- Right and I can see here, I can see our elephant.
You're still using Hadoop as well, right?
- We also plan to bring Hadoop into Azure soon,
once we have rationalized all of our batch processes.
- What approach then did you take
in terms of capacity management?
'Cause I know that's different, I guess,
when you're gonna move into the Cloud
versus some of the things that you might be doing
with an appliance.
- The best part about the migration effort
is the unlimited elasticity.
We aren't turning away users or killing their query
that is running longer than five minutes.
Azure Data Warehouse was three times faster
in terms of processing.
And we can ramp up our data warehouse unit for compute,
if ever there is a need for it.
- Right, one of the cool things
that we can do now with modern data warehousing
and the architecture in Azure is that
it actually separates the compute layer
for kind of the horsepower and the storage layer,
so you can individually scale either of those.
- So, I can show you, if you wanna see,
how easy it is to actually scale in Azure.
- [Jeremy] All right, yeah.
Let's take a look.
- [Anne] So if you see in the screen,
all you have to do is click on scale
and on the right you would see a slider.
You can select whatever DW you need
and it will show you the associated cost
and you click on scale.
- Okay, so then in terms of automating
some of these processes,
how do you go about doing that so you don't have to always,
as awesome as this scaler is or the slider is,
how do we automate this process
to make it even more automated?
- Good question Jeremy.
We actually created a way
so that we can automate the scaling.
- [Jeremy] Okay, let's have a look.
- [Anne] So in the screen,
you guys can actually see our runbooks.
- [Jeremy] Looks like that's the data warehouse, so.
- [Anne] It is, so let's go to the schedules.
In here you can see the schedule that we have
for a scaling up.
Let's open one of the runbooks.
Let's click on edit so that you can see how we did it.
So in here you can see the code of a PowerShell script
that we actually created so that we can define
the variables on how we scale up and down.
It's really simple.
- Right it's really good.
This is actually part of the runbooks gallery
and it let's you kind of set the timings.
Once you set the timing schedule up,
in your case, you can scale it up in the morning
during kind of the work hours,
scale it back down in the evening
and then turn it down on the weekends
when people aren't coming into the office.
So, great for automation.
But what differences then are you seeing
in terms of supporting concurrent users?
You mentioned before, you had to terminate
those five minute and longer queries,
how is that now?
- So as I mentioned in the past,
if we saw a query running longer than five minutes,
we'd kill it and notify our business user.
- [Jeremy] Okay.
- We had an agreement with the business
that they cannot execute queries at times of day
where our important batch processing is running.
Now because of workload management in Azure,
we don't have constraint at all.
I can show you.
Let's run this query.
Here you can see all of our users, about 70 of them
and about 20 is running longer than five minutes
and it's fine.
- And the nice thing is you don't have to worry about
terminating the queries.
Again, you're not training kind of users
to be afraid to ask you to run these kind of longer queries
which is gonna help in terms of decision-making.
But it's really great to see that those can continue to run.
Were there any surprises though that you noticed,
as part of the move then to Azure SQL Warehouse?
- One big surprise is the operational costs.
So, everyone told us that while the upfront cost
would be less in moving to the Cloud,
we would have higher ongoing costs.
As we've found, even if you are depreciating
an appliance over time, it is still more cost-effective
because we only pay for the computing storage
that we utilized.
We've eliminated hardware maintenance
and administrative overhead
and also there are different performance benefits
of modern infrastructure.
We're running three times faster
and the cost is a third in terms of total cost of ownership.
- This is just amazing.
To think about it, you've got a depreciated asset
for the most part, a lot of these.
It's less expensive now that you're in Azure.
It's faster, really pretty much upside on all fronts.
Really great results.
So, how did you end up then or how easy was it
to kind of maintain the system for your team
given they were kind of new to the Azure SQL Data Warehouse?
- Basically if you guys know traditional SQL,
you will learn it.
SQL is the lingo front of analytics.
In your left, you will see a query in Netezza
and on the right, you will see a query
in Azure SQL Data Warehouse.
There are a few syntax differences
but it's largely the same.
This served us really well because it reduced
the time it took us to re-platform.
And we actually changed some of the queries too
which produced really optimal results for us.
- And one awesome resource as well
if you're adapting existing queries and scripts,
the nice thing is you can actually contact Microsoft
and we can adapt sometimes hundreds
or thousands of these using automation
so just work with your Microsoft teams.
What other benefits did we see then
when moving to Azure SQL Data Warehouse?
- The other thing we can do
now that we never able to do before
is to speed up development and test environment
as we add new features.
We can have this environments run side-by-side
because it's no longer cost-prohibitive
and we are not capacity-constrained.
- Very cool so it's not all that helped I think,
in this case, you were moving a massive amount of data,
100 terabytes uncompressed.
And that's all going into Azure SQL Data Warehouse
while running the existing system in parallel.
How did you pull off that massive move?
- How quickly we could migrate the data
was largely a question of physics and our approach.
We were able to migrate the data and re-platform
the batch process with zero disruption
to the productivity of our business users.
What we did is, we chose to do data sharding
to carve out data into incremental chunks.
We transported the data over ExpressRoute
from our data center in Illinois to Microsoft Data Center
in North Central.
Another important aspect of this was network management.
We used a network management utility
to do a VM to VM transfer inside the VNet
and to throttle the amount of data that we transfer,
passing over ExpressRoute.
We got the data into Azure Blob Storage,
as I've said earlier using AzCopy
and then we used Folly Base to load the data
from Azure Blob into SQL Data Warehouse.
We basically run the Netezza in parallel
with Azure SQL Data Warehouse until June 2019.
And now we are totally operational
in Azure SQL Data Warehouse.
- Awesome, so really pragmatic and also incremental approach
then to doing a live migration
but let's go back to compatibility.
How was that in terms of compatibility
with your existing systems?
- Well, there was 95% compatibility
with exception of some complex syntax and few queries
which we ended up rewriting
to get some more optimization from them.
- Okay, so beyond compatibility,
from an operational standpoint,
did you have to plan for things like disaster recovery?
- That actually has been another awesome surprise for us.
With the previous appliance solution,
we would do data backup but it would take about
three to five days if something is to happen.
Now, what we do if ever something is about to happen,
all you have to do is click restore,
select a copy of the data that you want to restore,
create an instance of data warehouse
and click on review and restore, click on restore,
it will start initializing the deployment,
it will deploy.
Now it's in progress and this will take minutes.
- Right so it was kind of like three to five days.
Now, it's just a few minutes.
Under 20 is what we say
but I think it's around five to 10 minutes, right?
- [Anne] Yes.
- So super cool in terms of being able to do this
much much much quicker than before.
So, what's the future then look like
from a supply chain analytics perspective then at Walgreens?
- I always feel like whatever we have accomplished right now
is just foundational.
I'm at the beginning of everything.
The first move has been laid as a foundation
for our business users to have all of the data
that they need currently in our Cloud platform
and the limitations are gone.
The future for us is really what we can do with our data
that we are building a full analytic stack
so that we can be more predictive
to adhere to our customer needs.
This will improve our core goal of having
the right inventory at the right quantities in each store.
- Thanks Anne, thanks for joining us today
and really sharing your tips for making a pragmatic
and incremental approach for migrating to the Cloud.
Now, one thing to point out here is that
the next evolution of Azure SQL Data Warehouse
is Azure Synapse Analytics.
You're gonna wanna check out more information on that.
You can also check out on Walgreens' specific migration
at aka.ms/WalgreensAzure to learn more
about what Anne and team did and also at Azure.com
to check out any information around Azure Synapse Analytics.
You'll find all of that there.
So, thanks everyone for watching.
That's all the time we have
from the Microsoft Mechanics Live stage here in Orlando.
Thank you and we'll see you next time.
(upbeat music)