blog.kaniski.eu I just wanna learn!

30Jul/180

How to Leverage SSIS in ADF (Packt)

Learn how to leverage SSIS on premises to interact with cloud data in ADF v2 in this tutorial by Christian Cote, Michelle Gutzait, and Giuseppe Ciaburro, the authors of Hands-On Data Warehousing with Azure Data Factory.

SQL Server Integration Services (SSIS) has been the Microsoft ETL predilection tool for more than a decade. A lot of enterprises have used SSIS to load their on-premise data warehouses since its inception in SQL Server 2005.

In the last couple of years, IT departments have had to deal with different kinds of data and specific toolsets to process them. SSIS has successfully been able to access cloud data from on-premise ETL servers since 2015 with the Azure Feature Pack (https://docs.microsoft.com/en-us/sql/integration-services/azure-feature-pack-for-integration-services-ssis?view=sql-server-2017). However, issues occur when most of the ETL is in the cloud and SSIS is in a small part of the chain. And, up until now, it was very complex to use ADF v1 as the orchestrator in the cloud, with some SSIS package calls in the pipeline.

The following sections will describe how SSIS on-premises can be successfully leveraged to interact with cloud data in ADF v2.

Sample setup

The first thing that you need to do is set up an SSIS solution. You can use Microsoft wide world importers. The samples can be found at https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0.

From the list, you can download the databases and the SSIS solution:

  • Daily.ETL.ispac: This is the SSIS solution that takes data from WideWorldImporters to the WideWorldImportersDW database
  • WideWorldImporters-Full.bacpac: This is the transactional database that is used as a source for the data warehouse
  • WideWorldImportersDW-Full.bacpac: This is the sample data warehouse database that you'll use in this article

Sample databases

The first step is to set up sample databases. For this, you’ll need to install SQL Server Management Studio (SSMS). This program is freely available from Microsoft at https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.

Start SSMS to connect to your server and right-click on the Databases folder in Object Explorer. Select Import Data-tier Application... from the contextual menu as shown in the following screenshot:

The Import Data-tier Application wizard will open. Now click on Next.

In the Import Settings step, make sure that the Import from local disk option is selected and click on Browse.... From the Open window, navigate to the location from where you previously downloaded the .bacpac files and click on Open, as shown in the following screenshot:

Click on Next on the Import Settings screen.

You can now proceed to the Database Settings step. Make sure that the entry in the New database name textbox is WideWorldImporters and click on Next.

The Summary will be displayed as shown in the following screenshot. Click on Finish to start the BACPAC import process:

The database is created as well as all the objects in it. This process may take a few minutes. Click on Close to terminate the process:

Repeat the same process for the other BACPAC WideWorldImportersDW-Full.bacpac. Make sure that you specify WideWorldImportersDW as the database name.

SSIS components

The SSIS setup usually requires an existing installation of SQL Server, with the Integration Services feature selected. With ADF v2, this step is less mandatory since you can deploy the packages in an Azure database.

That being said, it's always better to test your SSIS solution on premise before deploying it in Azure. The next section will describe how to set up a sample SSIS solution.

Integration Services Catalog setup

This step is necessary for brand new SQL Server installations where you may want to deploy SSIS packages. When logged in to the server with SSMS, simply right-click on the Integration Services Catalogs folder and select Create Catalog...

The Create Catalog window will open up. You’ll need to fill in the properties and click on OK to create the integration services catalog:

Next, you need to create a folder for your project. In Object Explorer, expand the Integration Services Catalogs node, right-click on SSISDB, and select Create Folder... from the contextual menu:

The Create Folder window will open up. Set the properties as shown in the following screenshot and click on OK:

You're now ready to open the solution in Visual Studio. Here, you can deploy it to your newly created on-premises Integration Services Catalogs folder.

Sample solution in Visual Studio

For this section, you'll need an existing installation of Visual Studio 2015 or 2017 and SQL Server Data Tools (SSDT) installed. Visual Studio is available at https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017.

Once SSDT is opened, click on FILE | New | Project...:

The New Project window will open up. Select Integration Services Import Project Wizard, type WWImportersDaily in the textbox beside Name, and click on OK:

The Integration Services Import Project Wizard window will open; click on Next to proceed to the next step:

In the Select Source window, select the Project deployment file radio button and click on Browse.... This will open up the Open file window. Navigate to the folder where .ispac, the SSIS sample file, has been downloaded and click on Open:

Once back to the wizard, click on Next to proceed to the next step.

In the Review window, click on the Import button.

Once the project has been imported, you can see it in Solution Explorer:

You’ll have to modify the two connection managers if your server is not the default instance. To do so, double-click on WWI_DW_Destination_DW.conmgr and WWI_Source_DB.conmgr, and change their server properties.

If the connections are online, you can run the package and see if everything is working fine.

Deploying the project on premises

You're now ready to deploy and test your project on your local server:

1. From the Solution Explorer, right-click on the WWImportersDaily project and select Deploy. Integration Services Deployment Wizard will open up as shown in the following screenshot. Click on Next to proceed to the next step:

2. Connect to your SQL Server where the integration services catalog was created before and click on Browse.... The Browse for Folder or Project window will open.

3. Expand SSISDB and select the DWDailyRefresh

4. Click on OK to close the window:

5. On the Select Destination window, click on Next.

6. The next step is to validate. However, the on-premise deployments don't use it.

7. Click on Next to proceed to the Review window. Here, you can review whether you have selected the right .ispac file and confirm that you’re going to deploy it to the right folder in SSIS:

8. Now click on Deploy to start the deployment of the .ispac file.

9. The last step, Results, shows the progress of your deployment. If everything is successful (as it should be), click on the Close button:

You're now done with the deployment.

10. The next step is to execute the package via the catalog. You can switch to SQL Server Management Studio for this.

11. In the Object Explorer, expand Integration Services Catalogs and navigate through the DailyETLMain.dtsx package.

12. Right-click on it and select Execute... from the contextual menu to open up the execution window:

13. Go to the Connection Managers tab, check the Connection String property for both connectors, and adjust them if necessary. If everything is okay, click on OK to start the package execution:

14. Click on Yes to see the report. Once the report has opened, you might notice that the package is still running. When you refresh it, though, you’ll see that the package has been successfully executed:

You have successfully leveraged SSIS on-premises. If you found this article to be an interesting read, you can explore Hands-On Data Warehousing with Azure Data Factory to learn how to build your own ETL solutions and address the key challenges faced while building them.

Cheers!

P.S. This post was provided by good people at Packt Publishing. Thank you, Ron!

28Jun/180

PowerShell helps with Altaro

I really like Altaro VM Backup! It's so simple, fast and gets the job done. If you haven't tried it yet, please do – it fulfills the backup needs of small and medium businesses. I'm using it for backup of my Hyper-V virtual machines, of course.

Have I mentioned that they also have the free version, because – they do!

With this little digression out of the way, the thing I want to write now is something that really helped me the other day. I created (yet another) virtual machine on my Hyper-V host and then I tried to add it to backup as well. It is really simple to do this in Altaro – you just select your virtual machine and then drag & drop it to the desired backup location and schedule and that's it!

As I was connected to my host via Remote Desktop, I was having trouble with drag & drop. I wasn't able to add my new virtual machine to either backup location or schedule. I'm stuck.

Altaro VM Backup

Altaro VM Backup

So… when all things fail, you're usually saved by "reading the friendly manual" (RTFM). Or by using PowerShell. I've decided to try the latter.

How do you use PowerShell to add the virtual machine to backup when using Altaro VM Backup?

There are a couple of steps, but basically you need to establish the connection to backup server, make Altaro VM Backup aware of your virtual machine, assign it to the desired backup location and schedule. And that’s it! And, even better – the good people at Altaro have written the PowerShell scripts that help you do all that!

The steps are:

  • (inside PowerShell console) go to the C:\Program Files\Altaro\Altaro Backup\Cmdlets where here you can see all the scripts that come out-of-the-box:

Altaro VM Backup

  • all scripts are equipped with help and examples, accessible by adding the --help parameter:

Altaro VM Backup

  • first, we need to establish connection to backup server by using the StartSessionPasswordHidden.ps1 scripts (that will give us connection to the backup server and also Session token (Data field) which we need as first parameter for all the next steps):

Altaro VM Backup

  • next, we need to make Altaro VM Backup aware of our new virtual machine by getting the HypervisorVirtualMachineUuid of this virtual machine with GetVirtualMachines.ps1 script:

Altaro VM Backup

  • then we can add this virtual machine to Altaro with AddVirtualMachineToConfig.ps1 script by passing the Data (actually the VirtualMachineRefId) value from the previous step:

Altaro VM Backup

  • next, we need to check our available backup locations with GetBackupLocations.ps1 script:

Altaro VM Backup

  • by using the AddVirtualMachineToBackupLocation.ps1 with BackupLocationId from the previous step, we will assign our virtual machine to desired backup location:

Altaro VM Backup

  • next, we need to add this virtual machine to a desired schedule as well – with GetSchedules.ps1 script, we can get the ScheduleId:

Altaro VM Backup

  • and with this parameter in hand, we can start the AddVirtualMachineToSchedule.ps1 script:

Altaro VM Backup

  • last, but not least, we need to close all sessions by using the EndAllSessions.ps1 script:

Altaro VM Backup

  • finally, we can see the results in the GUI (our machine should be added to the backup location and schedule – everything that’s needed to start backing it up!):

Altaro VM BackupAltaro VM Backup

Pretty simple (and cool), right?! Hope it helps!

Cheers!

6May/180

Getting started with Microsoft Azure Stack (ASDK) (8)

After seven posts on how to get started with Microsoft Azure Stack/Azure Stack Development Kit, I think it’s finally time to share some additional resources that can help you on this amazing journey!

First (and the most important one… and the one that I already mentioned a few times) is the official documentation – hosted at docs.microsoft.com, regularly updated… with everything you need… just at the tip of your fingers! Amazing!

Next, there are two books I’ve been reading (feel free to leave a comment if you know any other book or resource I’ve missed) – the first one is Packt’s Building Hybrid Clouds with Azure Stack by Markus Klein and Susan Roesner (thank you, Ron!). The second book is Sams’ Microsoft Hybrid Cloud Unleashed with Azure Stack and Azure by Kerrie Meyler, Steve Buchanan, Mark Scholman, Jakob Gottlieb Svendsen and Janaka Rangama. Great books written by great authors!

Also, PluralSight is offering the short Microsoft Azure Stack: The Big Picture by Jason Helmick – totally free!

And if you plan to certify on Azure Stack – the official exam 70-537: Configuring and Operating a Hybrid Cloud with Microsoft Azure Stack is available
(accompanied by the official MOC, of course)!

Lastly – make also sure that you don’t miss Jeffrey’s inspiring talk!

Hope it helps!

4May/180

Getting started with Microsoft Azure Stack (ASDK) (7)

One other important topic of running your Microsoft Azure Stack/ASDK environment is updating. You’ll need to update your deployment regularly to stay supported.

So… how do we do it?

First, you’ll download updates by using the provided Azure Stack Updates Downloader utility.

If you remember the following video, starting at 1:04, you could see the Downloader in action:

Next, you’ll unpack and upload the updates to your Stack deployment (to the updateadminaccount storage account, to be exact):

As the last step, Azure Stack will pick the info about uploaded updates and let you press the (magic) Update now button.

Then… we wait and our updates will be (magically) installed. And that's it! Cool!

I've recorded a short video about this one as well:

Note, though, that updating of the nested Azure Stack/ASDK (i.e. installed inside of a virtual machine, either on Hyper-V, Azure or somewhere else) will probably not work… and this is perfectly fine (and expected)!

Cheers!

P.S. The whole YouTube playlist is available here.

2May/180

Getting started with Microsoft Azure Stack (ASDK) (6)

Once you’re all set with preparing your Azure Stack/ASDK infrastructure, it’s time to let other users actually use it by forming tenants.

For this, you’ll first create a Plan which contains all the services a tenant is allowed to use with setting the appropriate Quotas on these services. Next, you’ll create an Offer consisting of the plan you just created (and any other you may have created earlier). With that, you can create a Subscription which will, finally, allow your user to consume Azure Stack/ASDK services (i.e. provision virtual machines and any other service you may have put in your plan).

I’ve made a short video about provisioning the above mentioned for my newly created TestUser@azurestack.local user:

Note that my user is using the "tenant portal" (https://portal.local.azurestack.external/) for provisioning its services, and not the "admin portal" (https://adminportal.local.azurestack.external/).

Cheers!

30Apr/180

Getting started with Microsoft Azure Stack (ASDK) (5)

It’s not like we can deploy only WordPress or Ubuntu on top of our ASDK – we can do so much more! And now I’ll show you how to add Windows 2016 image to your Azure Stack (ASDK) environment.

First, you’ll need to connect to your ASDK environment by using the Login-AzureRmAccount command. Then you’ll need the Windows Server 2016 ISO, and with an easy New-AzsServer2016VMImage command, you’ll import the Windows Server 2016 bits and create Windows Server 2016 image/template for your Azure Stack environment.

There is also a video showing you all the steps and note that you can also add the latest Cumulative Update (CU) to the template with a simple switch called -IncludeLatestCU (how cool is that?!):

After that, you can easily create a new virtual machine based on Windows Server 2016 template you’ve just created, like in the following video:

Cheers!

28Apr/180

Getting started with Microsoft Azure Stack (ASDK) (4)

And now, a short and sweet one – once you have downloaded, installed and registered your ASDK, you can finally start using it!

First thing I would do is to grab something from the Azure Marketplace – inside your Admin Portal (https://adminportal.local.azurestack.external/), you can open up the Marketplace management section and then click on Add from Azure button:

This gives you a whole selection of images available on Azure, which can be downloaded and used on you ASDK. Just for fun, in the next video, I’ll download WordPress (on Ubuntu) and a VM extension called Microsoft Antimalware (hmmm… I wonder what it does? Smile):

Once downloaded, you can provision yourself a brand new WordPress instance, running on top of Ubuntu, on top of your own ASDK, as you can see in the following video:

Happy weekend!

26Apr/180

Getting started with Microsoft Azure Stack (ASDK) (3)

As a final preparation step (following my previous post) in using the ASDK, we need to first register it. For that, an Azure subscription is required!

So… let’s do it.

First, we need to make sure we have the required Azure Stack PowerShell modules. It’s also convenient to make the PSGallery trusted repository for installation of the modules, if we need to install them. Then we can download all the tools we’ll need, and finally register our (connected) ASDK environment, using the following commands:

You can find the whole procedure explained in more detail inside the official documentation, of course.

And... once registered – we can finally start using our ASDK!

As before – I’ve also prepared a short video about registering the ASDK:

Happy exploring!

24Apr/180

Getting started with Microsoft Azure Stack (ASDK) (2)

In my previous post, I’ve discussed how you can get your hands on the Microsoft Azure Stack Development Kit (ASDK) – now, I’ll show you how to install it.

As I’ve mentioned there already, we will install the ASDK inside of a Hyper-V virtual machine with nested virtualization enabled (a scenario that is not officially supported, but will give you an opportunity to work with ASDK in your lab, if you don’t have all the required hardware).

So… let’s start.

The things you’ll need, as per official article:

  • Hyper-V (2016) host
  • 12-core CPU
  • 96+GB of RAM
  • ~200GB free space for CloudBuilder.vhdx (OS)
  • 4 x 250+ GB free space for data disks
  • Internet/NTP access (time is important!) through a single network adapter (without proxy!)

Once you’ve downloaded and extracted the ASDK bits, you’ll get the CloudBuilder.vhdx file, which we will use for the boot disk of our newly created virtual machine.

But first, we will expand it a bit (I’ve expanded it to 200GB). Don’t forget to expand the partition inside as well!

Then, we can create a new (Generation 2) virtual machine with the following settings, our CloudBuilder.vhdx attached as the first disk and four freshly created data disks:

One other thing we need to ensure is to enable nested virtualization for this virtual machine (allowing us to run Hyper-V inside this virtual machine):

And now we are ready to start our virtual machine, hosting the ASDK.

Once started, virtual machine will finish its configuration (specialization phase of Windows installation), so let it finish and then configure password, computer name and IP address.

After this initial configuration, we configure the NTP settings by using the following:

Now we are ready to use the asdk-prechecker.ps1 script, to check if everything is OK:

And…. we are ready for the installation!

Now we need to run the asdk-installer.ps1 script, which will actually start the wizard which will help with entering the required IP adresses and checking the network connection – as a result, we will get the final commands to kick-off the installation:

One more thing needs to be configured for our virtual ASDK installation to succeed inside in nested enviroment – when the extraction process starts, we need to edit the C:\CloudDeployment\Roles\PhysicalMachines\Tests\BareMetal.Tests.ps1 file by changing the every "-not $IsVirtualizedDeployment" to "$IsVirtualizedDeployment" (that is, remove the "-not"):

Final thing to do is to wait for the whole process to complete (~4,5 hours on my hardware), and the result looks like this:

And there is another (shortened) video of the installation process:

And now we can start playing with our own ASDK!

Stay tuned!

22Apr/180

Getting started with Microsoft Azure Stack (ASDK) (1)

So… you’ve heard all about the "hybrid clouds" (all around us), and you’ve finally decided to go with the truly hybrid one – the one from Microsoft, which consists of Microsoft Azure, extended to Microsoft Azure Stack on-premises.

If you are just starting and want to learn about it, you may find it difficult (and rather costly) to obtain the fully integrated OEM solution for your lab. But, there is a solution – Microsoft provides the development kit (ASDK), which can be used for playing around, learning and development (of course).

Where to get started?

First stop should definitely be the official Azure Stack Development Kit (ASDK) page. There, you can register for the download of ASDK software:

After that, you’ll receive a page with all the info you need – hardware and deployment requirements, download and step-by-step guidance links:

First, I recommend you to read through the requirements, and then you can run the prerequisites check script, just to double-check you have all that is needed. Don’t forget that, with hardware, you’ll also need an Azure subscription!

Script should give you output similar to mine (note that I’m using the virtual machine as my “ASDK host” and will be nesting all of it inside Hyper-V, of course):

If everything is fine, you’re ready to download the ASDK, using the provided downloader:

Download of ~12 GB may take a while, so “Please sit back and relax…” (as during the Windows 98 installation, some time ago).

The last step is to unpack the downloaded ADSK binaries (actually, the CloudBuilder.vhdx, as you’ll see):

There is also a short video to help you with the first steps inside the "brave new ASDK world":

In my next post, I’ll show you how to prepare a Hyper-V virtual machine for hosting the ASDK – not the most performing environment, but it’s 'good enough for a simple lab, if you don’t have the hardware one.

Stay tuned!