Migrating On-Premises SQL Server Database to Azure SQL Database
Today I was trying to migrate an On-Premises SQL Server 2016 Database to Azure SQL Database using .bacpac file option i.e. Export Data-Tier Application, but bad luck! I got this error every time I tried, and to my surprise, all credentials and configurations were correct.
The error that I got was: “Deployment fails with ‘Unable to connect to master or target server ‘xyz’. You must have a user with the same password in master or target server ‘xyzdb“. I really couldn’t get it why it’s happening as everything was correct. Whether I try to do it using Azure Portal or SSMS, the result remain same – Error.
Somehow I figured it out that I have been using SQL Server Management Studio ver. 13.0 and I need to have the latest SSMS with me. I used SQL Server 2016 and SSMS version for the same. Hence, I downloaded the SQL Server Management Studio ver. 17.4, which is for SQL Server 2017. And guess what??? It did work without any error at the very first try. 🙂
So, the above error message was completely misleading. Just have the latest SSMS with you, doesn’t matter if you are using SQL Server 2014/2016. And, you will be able to migrate SQL Server DB to Azure SQL DB.
Hope this helps you out! 🙂
OLE DB versus ADO NET Components in SSIS
ETL Package developers often ignore this question: What is the difference between OLE DB and ADO NET components? Should I use OLE DB Source/Destination or ADO NET Source/Destination? Which one is faster?
Let me explain what the difference is. The only difference is both of them use drivers/providers behind the scene, which have been developed using different technologies. ADO NET providers use ADO.NET along with C# behind the scene, while OLE DB providers use something else. Recently, the Microsoft said that they would not be enhancing the features in OLE DB providers. So, don’t expect more with OLE DB. Hence, you need to use ADO NET providers more and more. Don’t get scared! In fact, if you wish to connect to Azure SQL Database, you need to use ADO NET providers, as the OLE DB doesn’t offer flexibility to connect to Azure SQL Database or Data Warehouse. However, the OLE DB has got more providers to connect to different types of data stores compare to the ADO NET, at least as of now.
As far as performance is concerned, I personally did R&D with both the Sources/Destinations with huge data i.e. 1-5 GB, Database to Database, Database to File, File to Database, all types of package. Eventually, the statistics say that OLE DB has got an edge over ADO NET, and give you 60% more performance than that of ADO NET. Yes! ADO NET Sources/Destinations process data slower than OLE DB. Here, I would like to mention that I tweaked all option like DefaultBufferMaxRows, DefaultBufferSize, AutoAdjustBufferSize for SSIS 2016, etc. and then reached to this conclusion. And my experience of package development corroborates with this.
Hence, I recommend using OLE DB Source/Destination for the time being, until Microsoft enhances the ADO NET providers. Yes! You may need to use ADO NET in case of Azure, which is becoming very common now-a-days as every client is moving databases to Azure.
There are many other factors which affect the package performance, which I am going to discuss in other blogs. So, stay tuned!
Hope this helps you out.
Why Not to Use Sort and Aggregate Transformations in SSIS
I often come across queries about the SSIS package optimization techniques. There are many, and all depends up on scenarios. However, there are a few fundamental principals to architect SSIS packages. These help us to design a package, which runs faster. I am going to tell about two transformations that I always avoid: Sort Transformation and Aggregate Transformation.
You can categorize all the transformation in two broader categories: Synchronous and Asynchronous. Or, you may divide them into three types: Non-Blocking, Semi-Blocking and Blocking. It’s not right place to discuss all Categories/Types here. I will be discussing only Blocking Transformations here. Just for the sake of your knowledge, the Non-Blocking Transformation belong to Synchronous category, while Semi-Blocking and Blocking belong to Asynchronous category.
The Blocking transformations accept entire input at first i.e. all rows from source, perform the assigned operation, and then give the output of the operation. During this process, you have to wait for the transformation to collect entire required rows before getting output/desired result. This is where it bring degradation in the Data Flow performance, and in turn, slow package.
Both the Sort and Aggregate transformations belong to the Blocking types. This is the reason I always recommend not to use them altogether in your pipeline. Then question arises: How to sort the rows and, if required, to get aggregate?
There are ways! In case of OLE DB Source and SQL Server as source, you need to use SQL query, not the Table Name from drop down list, and perform these operations in the query. These operations will run at the database engine machine and will give you output instantly. In case of flat file/raw file, request your team to calculate these values and put it either in anther file or in first one/two lines into the source file. I am sure you know how to read/escape initial lines while reading flat files. There is an option available. Check it out! This is how you can avoid using Blocking transformations like Sort and Aggregate and, in turn, achieve better package performance.
I will be blogging about other techniques to optimize the SSIS package. So, stay tuned!
Microsoft Launches Preview of Quantum Development Kit and Q# Programming Language
Wow! Amazing! I’m excited!
Microsoft just released the preview of Quantum Development Kit for programming for Quantum computers. We’re getting a new programming language today called Q# for quantum programming.
Here is the link from where you can get more information about Quantum Development Kit. Go ahead and download it…
https://blogs.microsoft.com/ai/?p=73792
Click here to download Quantum Development Kit
Best of luck!
Editors for Python Programming
Several guys asked me which editor to use for Python programming, which one I use and why, which one is better and why, blah…blah…
I say, there are many! Believe me!
I recommend only two editors: 1.) Visual Studio Code, and 2.) Python’s IDLE Editor (Integrated Development & Learning Environment).
I use the Visual Studio Code as I have been working with Visual Studio since last 17 years. And, it provides me environment to code with other languages/scripts, etc. as well. It’s really fast, having lots of extensions, and more extensions coming up. The most important thing – It’s reliable as Microsoft product. And on top of that, it’s FREE, OPEN SOURCE and RUNS EVERYWHERE! 🙂 I have no reason why I should opt for any other editor when I am getting everything, more than that, using Visual Studio Code. Hence, my first preference and recommendation is Visual Studio Code for Python. Here is the link to download Visual Studio Code
Yes! If you’re not a Microsoft guy, then you can just use Python IDLE and learn the language, suffice for beginners. Once done, just install Visual Studio Code and use it.
Hope it gives you a direction to kick off your Python learning! Happy learning!
Connecting Oracle and Teradata Using SSIS
SSIS offers various options in order to connect to Oracle, but of course, not Teradata. Though, we have third party connectors to connect to Oracle as well as Teradata. And, the Microsoft officially recommends these connectors. The first choice being Microsoft Connectors for Oracle and Teradata by Attunity.
If you use OLE DB Source/Destination, you get Microsoft OLE DB Provider for Oracle connector. While if you use ADO NET Source/Destination, you get OracleClient Data Providers connector. Both of these options work fine, however the ETL process becomes slower, rather I would say VERY SLOW, if volume of data is huge. On top of this, if you are using 32-bit machine version, then it hurts more.
Hence, if you wish your ETL process to work faster, I recommend using Microsoft Connectors for Oracle and Teradata by Attunity. These connectors provide 60-70% performance upgrade, and THAT’S PROVEN. You can download these connectors from Microsoft site for free. Click here to download it. Though, these are also available on Attunitty.com, but, of course, at a price. You will get connectors for SQL Server 2016 and SQL Server 2017 as shown in below image:
Having installed these connectors, restart your Visual Studio, and they will appear in SSIS Toolbox under Common category as shown in below image:
Hope this blog helps you out!
Connecting SAP HANA Using SSIS
In order to connect to SAP HANA, all we need to do is to install SAP HANA Client tools, download from SAP web site. Unfortunately, the Microsoft has officially not yet released any connector to help us out.
The SAP HANA Client tools are available in both 32-bit and 64-bit versions. What all we did is installed 64-bit of SAP HANA Client tools, but to our surprise, it didn’t work. We always received an error message: “The type initializer for Sap.Data.Hana.HanaConnection threw an exception.” despite providing correct credentials to log in. And, we had no idea what’s wrong. Then, just a thought to install 32-bit SAP HANA Client tools did the magic. And, we’re able to connect using whatever manner you wish i.e. by creating DSN on system, using Server Explorer in Visual Studio, using ADO.NET Source in SSIS, etc.
We recommend you to install 32-bit SAP HANA Client tools in Development environment. It may be possible that 64-bit version might work on Production environment. However, we’re yet to test that possibility. For the time being, good to go.
Hope this helps you out!
