Eugene Meidinger (@SQLGene) did a fantastic job answering the question, “Is Power BI Right for You?” today!
Our state-of-the-art DILM Suite tools in the capable hands of our experienced data engineers combine to drastically reduce the amount of time to manually migrate and apply SSIS Catalog configuration artifacts – Literals, Catalog Environments and Catalog Environment Variables, References, and Reference Mappings – while simultaneously improving the quality of the migration effort.
Check out our Lift and Shift page to learn more!
I got pinged about a new trusted site requirement when I tried to connect to Azure SQL DB this morning. https://aadcdn.msftauth.net is the URL.
I added the URL to Internet Explorer’s Trusted Sites:
I was then able to connect to Azure SQL DB and add a rule for my IP using SQL Server Management Studio (SSMS).
Lift and shift is a bridge.
If your enterprise is considering migrating enterprise data and data integration workloads from on-premises to the cloud, lifting and shifting is not the end of the story. Lifting and shifting is, rather, the mechanism – the conversion project – that positions your enterprise to leverage the economies of scale afforded by cloud technology.
Andy’s Lift and Shift FAQ
“Should We Lift and Shift?”
I hear this question often and my response is, “When it makes sense to do so, yes, you should lift and shift.” This begs the next question, which is…
“How Do We Know It’s Time to Lift and Shift?”
My engineer-y response to this question is, “You will know it’s time to lift and shift to the cloud when you want to leverage functionality available in the cloud that is not (or not yet) available in on-premises versions of the enterprise platform(s) in use in your enterprise.”
“What Are Some Advantages of Migrating to the Cloud?”
The biggest advantage of lifting and shifting enterprise data to the cloud is the ability to efficiently scale operations. By efficiently, I mean quickly and easily – especially when compared to the time and expense (don’t forget opportunity cost when calculating expense) to scale up systems on-premises.
The ability to scale up and scale down on demand is a huge advantage for some business models which experience “spike-y” demand for operations at different times of the year, quarter, or month. But even if that’s not the case, all data scales. It’s very handy to be able to connect to the Azure Portal and move a slider (as opposed to purchasing and provisioning more hardware…).
There’s a brand new (in my opinion) “knob” exposed by cloud-enabled efficient scaling. As I wrote in my post titled Time and Money in the Cloud:
Let’s say you pay $100 to incrementally load your data warehouse and the load takes 24 hours to execute at the scale you’ve selected in the cloud. Prior to thinking in DTUs, engineers and business people would think, “That’s just the way it is. If I want more or faster, I need to pay for more or faster.” But DTU math doesn’t quite work that way. Depending on your workload and DTU pricing at the time (FULL DISCLOSURE: DTU PRICING CHANGES REGULARLY!), you may be able to spend that same $100 on more compute capabilities and reduce the amount of time required to load the same data into the same data warehouse to minutes instead of hours…
The fact that the cost/performance curve can be altered in seconds instead of months meta-changes everything.
“Are There Disadvantages of Migrating to the Cloud?”
It depends. (You knew that was coming, didn’t you?)
Enterprise Data & Analytics helps enterprises migrate data, data integration, lifecycle management, and DevOps to the cloud. In some cases (~30%), the enterprises spend a little more money in the near-term. There are two reasons for this:
- When upgrading, it’s always a good idea to operate new systems in tandem with existing systems. In a lift and shift scenario, this means additional expenses for cloud operations while maintaining the expense of on-premises operations. As cloud operations are validated, on-premises operations are shut off; thereby reducing operating expenses. In truth, though, this dynamic (and expense) exists whether one is lifting and shifting to the cloud or simply upgrading system on-premises.
- “Standing on the bridge” (more in a bit) can cost more than remaining either on-premises or lifting and shifting the entire enterprise workload to the cloud.
- Regulatory requirements – including privacy and regulations about which data is allowed to leave nation-states – will constrain many industries, especially government agencies and NGOs (non-governmental organizations) who interact heavily with government agencies.
Standing On The Bridge
One option we at Enterprise Data & Analytics consider when assisting enterprises in lift and shift engagements is something we call “standing on the bridge.”
Standing on the bridge is present in each lift and shift project. It’s one strategy for implementing hybrid data management, which almost every enterprise in the cloud today has implemented. Hybrid means part of the enterprise data remains on-premises and part of the enterprise data is lifted and shifted to the cloud.
Hybrid is implemented for a variety of reasons which include:
- Mitigating regulatory concerns; and
- As part of the normal progression of lifting and shifting enterprise data and data workloads to the cloud.
Standing on the bridge for too long is a situation to avoid.
“How Do We Avoid Standing on the Bridge For Too Long?”
Planning. Planning is how an enterprise avoids standing on the bridge too long. Your enterprise wants advice from experienced professionals to shepherd the lift and shift operation.
Enterprise Data & Analytics can help.
Enterprise Data & Analytics has been delivering, and writing and speaking about Data Integration Lifecycle Management for years.
We’ve built helpful tools and utilities that are available at the DILM Suite. Most of the DILM Suite tools are free and some are even open source.
Shannon is a data engineer, data scientist, BimlHero (though not listed on the page at the time of this writing), and shepherd of the Biml Interrogator open source project. If you use Biml to generate SSIS projects that load flat files, you need Biml Interrogator.
Shannon, Kent Bradshaw, and I are also co-authoring a book tentatively titled Frameworks. (Confession: Kent and Shannon are mostly done… I am slacking…)
Shannon brings a metric ton of experience to serve our awesome clients. He has years of experience in data analytics, serving recently in the role of enterprise Lead Data Scientist. Shannon’s experience spans supply chain management, manufacturing, finance, and insurance.
In addition to his impressive data skills, Shannon is an accomplished .Net developer with enterprise senior developer experience (check out Biml Interrogator for a sample of his coding prowess).
Shannon is a regular speaker at SQL Saturday events, presenting on topics that include Business Intelligence, Biml, and data integration automation. He is a gifted engineer with experience in framework design, data integration patterns, and Azure who possesses a knack for automation. Shannon is an avid “tinkerer” who enjoys learning. He has experience implementing Azure Machine Learning and applying AI to predictive analytics using sources classified Big Data. Shannon is also a practitioner of data integration DevOps with SSIS. In other words, he fits right in with our team here at Enterprise Data & Analytics!
As Shannon writes on his LinkedIn profile:
I am a data guy with a passion for partnering with clients to solve their database and technology issues. Over my career, I’ve played all the roles: database developer, administrator, business intelligence developer, and architect, and now consultant. I’m the guy you call in when you have the impossible problem and everyone tells you it cannot be solved. I automate solutions in order to free your current staff to do the higher value tasks. I bring solutions outside of traditional relational database solutions, in order to find the shortest path between you and your goals.
As an accomplished Microsoft SQL data professional, recognized BimlHero, and practicing Data Scientist, I’m the resource you need to extract the most value from your data.
I’m humbled and thankful and excited to watch Enterprise Data & Analytics continue to (quietly) grow – adding cool people (another announcement is forthcoming) and service offerings like Data Concierge. It’s very cool to watch!
Welcome Shannon! I’m honored to work with you, my brother and friend.
For more information, please contact Enterprise Data & Analytics!
I am honored to announce Fundamentals of Azure Data Factory – a course from Enterprise Data & Analytics!
The next delivery is 04 Mar 2019, 9:00 AM – 4:30 PM ET.
Azure Data Factory, or ADF, is an Azure PaaS (Platform-as-a-Service) that provides hybrid data integration at global scale. Use ADF to build fully managed ETL in the cloud – including SSIS. Join Andy Leonard – author, blogger, and Chief Data Engineer at Enterprise Data & Analytics – as he demonstrates practical Azure Data Factory use cases.
In this course, you’ll learn:
- The essentials of ADF
- Developing, testing, scheduling, monitoring, and managing ADF pipelines
- Lifting and shifting SSIS to ADF SSIS Integration Runtime (Azure-SSIS)
- ADF design patterns
- Data Integration Lifecycle Management (DILM) for the cloud and hybrid data integration scenarios
I hope to see you there!
Follow Andy Leonard’s SSIS Training page for more information.
Azure Data Factory – ADF – is a cloud data engineering solution. ADF version 2 sports a snappy web GUI (graphical user interface) and supports the SSIS Integration Runtime (IR) – or “SSIS in the Cloud.”
Attend this session to learn:
– How to build a “native ADF” pipeline;
– How to lift and shift SSIS to the Azure Data Factory integration Runtime; and
– ADF Design Patterns to execute and monitor pipelines and packages.
I hope to see you there!
I was honored to write an article titled Introducing Azure Data Factory Design Patterns featured in this month’s PASS Insights newsletter!
Introducing Azure Data Factory Design Patterns
The article covers a couple execution patterns:
- Execute Child Pipeline
- Execute Child SSIS Package
I demonstrate a cool SSIS Catalog Browser feature that helps ADF developers configure the Execute SSIS Package activity.
To see it in action, download SSIS Catalog Browser – it’s one of the free utilities available at DILM Suite. Connect to the instance of Azure SQL DB that hosts an Azure Data Factory SSIS Integration Runtime Catalog, select the SSIS Package you desire to execute using the Execute SSIS Package activity, and then copy the Catalog Path from the Catalog Browser status message:
Paste that value into the Package Path property of the Execute SSIS Package activity:
You can rinse and repeat – Catalog Browser surfaces Environment paths as well:
Enjoy the article!
If you have any questions about Azure Data Factory – or need help getting started – please reach out!
Attend my full-day pre-conference session titled Intelligent Data Integration at the PASS Summit 2018 on 5 Nov 2018.
Check out this 1-day course on Fundamentals of Azure Data Factory delivered in cooperation with Brent Ozar Unlimited 10 Dec 2018!
AndyWeather.com is a site I’ve maintained for about 10 years now. I use the site and related hardware, software, and services to test concepts and perform experiments.
I then apply my experience in delivering Internet of Things (IoT) solutions for Enterprise Data & Analytics customers and for SSIS and Biml training, such as my upcoming course titled Fundamentals of Azure Data Factory delivered in cooperation with Brent Ozar Unlimited.
It all started when GoDaddy created a DMZ for SQL Server databases. I found this functionality in 2008 and asked myself, “Self, how might we use this?”
Since That Time…
There have been two major iterations of AndyWeather. I use weather data collected during the first iteration for training purposes at SSIS Academy and when delivering training to Enterprise Data & Analytics customers.
The setup of the second iteration is fairly straightforward:
- The Acurite Weather Station consists of an instrument pack plus a base station. The instruments collect weather measurements and transmit them to the base station.
- The base station is connected to an older e-Machine running Windows 7 Ultimate (32-bit) on 2GB RAM.
- An Acurite application interfaces with the base station and the application stores data locally in a single CSV file.
- I wrote a very simple C# console application named “abt” (an acronym for “Azure Blob Transfer”) to transfer the CSV file to Azure Blog Storage.
- An Azure Data Factory pipeline that loads an Azure SQL DB staging table.
- The AndyWeather website which reads the latest weather data from the Azure SQL DB staging table.
- I wrote another very simple C# application named “awt” (an acronym for “AndyWeather Tweets”) that tweets updates to the @AndyWeather twitter account.
Acurite Weather Station
The latest iteration began in early 2018 when I purchased an updated package of instruments and a new base station made by Acurite. So far, I like this station a lot. It was less expensive than the previous station and appears more rugged (again, so far – time will tell).
I recently relocated the weather station to improve connectivity between the instruments and the base station. I recorded a Data Driven *DataPoint* about it:
(Pay no attention to the exploding pecans in the background…)
I intentionally use an under-powered PC for the server. Why? I want to learn how the base station – and then everything downstream of the base station – responds to busy server conditions. This is Engineering 101 stuff and I’ve learned a lot:
I love this old machine!
The Acurite people maintain an application for communicating with base stations:
The PC Connect application allows me to configure how and when weather data is collected from the base station – which collects measurements from the instruments. The application lets me configure the units-of-measure and file location – and I can even share my weather data with Weather Underground. How cool is that?
The Azure Blob Transfer Console Application
The Azure Blob Transfer (abt) application is a very simple console application written in C#. It picks up the CSV file containing weather data stored by the Acurite PC Connect application and writes the file to an Azure Blob Storage container:
The CSV file in Azure Blob Storage is overwritten each time abt successfully executes. You can download a copy of the abt solution here.
Azure Data Factory Pipeline
An Azure Data Factory (ADF) pipeline calls a stored procedure that first truncates a staging table in a Azure SQL DB using a Stored Procedure activity, followed by a Copy Data activity that copies the weather data from the CSV file in Azure Blob Storage to an Azure SQL DB staging table:
At the time of this writing, ADF version 2 is current.
You can download the ARM template for the pipeline here.
The AndyWeather Website
The AndyWeather website has been around since the days of the first iteration of AndyWeather – the one that stored data in a SQL Server instance hosted at GoDaddy’s DMZ. It’s fairly straightforward code, which helps it perform fairly on desktops and mobile devices:
The biggest performance hit comes from executing the stored procedure against an Azure SQL DB, which can sometimes take 5-10 seconds to complete.
The AndyWeather Tweets Console Application
You can download a copy of the awt solution here.
The @AndyWeather Twitter Account
It makes me happy every time I see a tweet from @AndyWeather:
I tell people, “It’s just a dumb little app,” but I really had fun building it. I learned a bunch, too!
The AndyWeather IoT solution uses hybrid technology – on-premises instruments and servers, combined with cloud services – to deliver weather data to a website and Twitter account. It’s accessible from social media and the web from desktops and mobile devices.
Just so you know, this isn’t everything I’ve built using the AndyWeather instruments. There’s a bunch more – some of which is still in the experimental phase. I’ll share more as time permits. But I want you all to know, I consider Azure a great big cyber-playground!